programming full time

I am transfered to the programming departement (if such a thing exist in my compagny hehe)

Next project:
product management in sharepoint portal, ASP, SQL 2008

Since we do not sell software i will publish here the best code or most difficult one
There sill be a lot of ASP to manage sharepoint
My goal is to have 1 database in read only in our software that manage production
the sharepoint database
and a custom database to link theses 2 and add custom columns

Everything will have to be dynamic because it's a new project to manage our products and will evolve a lot on the way

The challenges will be:
1. security, using LDAP logon
2. dynamic query that will be able to read and write in 3 different database with on query or stored procedure (obviously a view will not do it, since it was created to read only)
3. implement an interface to edit database with some column in read only (there again a grid will not do it, because of the nature of the link between 3 different database and update we will need)
4. make theses data easily available in excel for everyday uses

the goal of this is to make product evolve without always basing the next generation on the old generation

the next generation / production of a product will be base on a model, that will evolve, and so on

each product will need:
discussion board
document models
drawing list for pieces
drawing models
diagrams for reprogramming models
programming sub that will be reusable for each automates
tag list for each categories for programming

all of this linked to 2 already existing databases

had to adjust buttons creation
when there is no image corresponding to button name.jpg, i use a standard button (grey)

Now working on the datebase creation part

It was done in script.VBS but not adjusted to vb.net 2010

I will start with MDB creation, then move on to MDB editor, then adjust to SQL editor (skipping creator)

This will be just like UTE (universal table editor, that was done in ASP, no code from UTE was used :P)

i programmed an event on all html page buttons
to manage them in my program

i had a button to call the window print (javascript print)
the button id was "print"

now when this button was pressed, i was simply returning the event to window

not working, you know why? because you cannot call a button using javascript print, id="print"
i called it "print2" and everything worked... strange

back to working on interface

i need to change all writing to do the "do loop" "try" for every writing in any frame in internet explorer

the timeout will be 3 sec before it stop trying every .1 sec and generate a fatal error

sql query building

first the user will enter many words to search, we will have to split them:

    Function SPLIT2(ByVal AA, ByVal SS)

        Dim cc As String

        bb = Nothing

        bb = Split(AA, SS)

        aa2 = Nothing

        aa2 = {"newsplittedarray"}

        ii = 0

        For Each cc In bb

            If cc <> "" Then

                ReDim Preserve aa2(ii)

                aa2(ii) = cc

                ii = ii + 1

            End If

        Next

        SPLIT2 = aa2

    End Function

then with a few array that contain the column name for every database used in the program:

    '=== ssea = search string 

    '=== tab 0 symptoms

    '=== tab 1 components

    '=== tab 2 diagnostics

    '=== tab 3 causes

    '=== ttyp 0 search

    '=== ttyp 1 fast results

    '=== ttyp 2 detail results

    '=== ssufix = order by or something at the end of query - order  and   %

    Function makque( _

    ByVal ssea As String, _

    ByVal ttab As Integer, _

    ByVal sselect As Integer, _

    ByVal wwhere As Integer, _

    ByVal ssufix As String, _

    ByVal ooper As String, _

    ByVal wwild As String)

        Dim ss As String

        Dim aa As String

        ssea = Replace(ssea, "'", "''")

        ssea = Trim(ssea)

        sseaara = Split(ssea, " ") '=== array of all words for search

        ss = "select "

        ii = 0

        '=== columns to search in

        For Each aa In allara(ttab, sselect)

            ss = ss & aa

            If ii <> UBound(allara(ttab, 1), sselect) Then ss = ss & ","

            ii = ii + 1

        Next

        '=== from table

        ss = ss & " from [" & alltab(ttab) & "] "

        If Len(ssea) <> 0 Then

            ss = ss & "where ("

            '=== search word(S) in table, AND operator

            ii = 0

            For Each ss2 In sseaara

                ii2 = 0

                For Each aa In allara(ttab, wwhere)

                    If wwild = "" Then

                        ss = ss & "[" & aa & "] " & ooper & " " & wwild & ss2 & wwild

                    Else

                        ss = ss & "[" & aa & "] " & ooper & " '" & wwild & ss2 & wwild & "'"

                    End If

                    If ii2 <> UBound(allara(ttab, wwhere), 1) Then ss = ss & " or "

                    ii2 = ii2 + 1

                Next

                If ii <> UBound(sseaara, 1) Then ss = ss & ") and ("

                ii = ii + 1

            Next

            '=== order by a column name

            ss = ss & ")" & ssufix

        Else

            ss = ss & ssufix

        End If

        makque = ss

    End Function

This will result in building a query, with all column name, where all words are searched for

a "dumb google" (not searching for most found etc, just the words in all columns we want to search in)

Just before, we call all thoses subs with a value we get from a html box, in form1 web object:

        '=== symptom words to search (separated by a space)

        TAB = 0 '=== table to seach in    (alltab)

        sel = 1 '=== columns to list      (allara)

        whe = 0 '=== columns to search in (Allara)

        sql01 = makque(strsea, TAB, sel, whe, " order by [probabilité] DESC", "LIKE", "%")

        If logall = 1 Then fil02.writeline(DateValue(Now) & " " & TimeValue(Now) & " before executing query")

        tag = exesql(objcon, dummy, sql01)

as you can see (and i saw it too!) i do not split the search string for symptom

because i search for all words, all together

objcon is my sql or mdb connection

dummy is a dummy :P

sql01 is the query text

strsea is the text to search for in all columns

% is the caracter used to start and end a chain to search for the words anywhere in the chain of text in the column

i numbered my array for each table, it look complicated but is so more simple to program

again, theses numbers allow me to add an array containing all columns name anytime

thus, adding a table in the database dynamically in my program, and manually in the database :)

thoses are specials arrays containing only the column names i want to search in

        '=== search columns

        allara(0, 0) = {"symptôme", "problème"}

        '=== fast results (for table)

I am not going into details right now because the goal in this post is to show a dynamic query building

Dynamic entry!

Dynamic programming, is the base of all code evolution
It is what made us the best programmers on the planet
Sometime, Dynamic programming take a big leap in evolution...

But you have to use it first!

So to create our control button in internet explorer, in left frame, we use dynamic programming
Is it a new science? Not at all. Simply a good organisation of variable

first we create the arrays:


    Public ARABUTNAM(0) As String
    Public ARABUTDES(0) As String
    Public ARADEPNAM(0) As String
    Public ARADEPCOL(0) As String

Then we fill them dynamically (that can be expanded to infinite)


        X = 0
        ReDim Preserve ARABUTNAM(X) : ReDim Preserve ARABUTDES(X) : ReDim Preserve ARADEPNAM(X) : ReDim Preserve ARADEPCOL(X)
        ARABUTNAM(X) = "symptomssearch"
        ARABUTDES(X) = "Problem search"
        ARADEPNAM(X) = "SEARCH"
        ARADEPCOL(X) = "cccccc"

        X = X + 1
        ReDim Preserve ARABUTNAM(X) : ReDim Preserve ARABUTDES(X) : ReDim Preserve ARADEPNAM(X) : ReDim Preserve ARADEPCOL(X)
        ARABUTNAM(X) = "causessearch"
        ARABUTDES(X) = "Causes search"
        ARADEPNAM(X) = "SEARCH"
        ARADEPCOL(X) = "cccccc"

        X = X + 1
        ReDim Preserve ARABUTNAM(X) : ReDim Preserve ARABUTDES(X) : ReDim Preserve ARADEPNAM(X) : ReDim Preserve ARADEPCOL(X)
        ARABUTNAM(X) = "componentssearch"
        ARABUTDES(X) = "Components Search"
        ARADEPNAM(X) = "SEARCH"
        ARADEPCOL(X) = "cccccc"


        X = X + 1
        ReDim Preserve ARABUTNAM(X) : ReDim Preserve ARABUTDES(X) : ReDim Preserve ARADEPNAM(X) : ReDim Preserve ARADEPCOL(X)
        ARABUTNAM(X) = "clrframes"
        ARABUTDES(X) = "Clear Frames"
        ARADEPNAM(X) = "OTHER"
        ARADEPCOL(X) = "cccccc"

        X = X + 1
        ReDim Preserve ARABUTNAM(X) : ReDim Preserve ARABUTDES(X) : ReDim Preserve ARADEPNAM(X) : ReDim Preserve ARADEPCOL(X)
        ARABUTNAM(X) = "info"
        ARABUTDES(X) = "Information/Help"
        ARADEPNAM(X) = "OTHER"
        ARADEPCOL(X) = "cccccc"


        X = X + 1
        ReDim Preserve ARABUTNAM(X) : ReDim Preserve ARABUTDES(X) : ReDim Preserve ARADEPNAM(X) : ReDim Preserve ARADEPCOL(X)
        ARABUTNAM(X) = "quit01"
        ARABUTDES(X) = "Quit"
        ARADEPNAM(X) = "All"
        ARADEPCOL(X) = "cccccc"

So, we have:

the button name (sub to be called when button is pressed)

the description (text inside button)

the category or departement (simply to group button of same kind)

the departement color (category color)

as all arrays are expanded everytime we add an element, it's dynamic
If you copy past a group of lines that define a button and change the name and description you can add a control button in my web interface

the add the condition to execute the code

adding:


        X = X + 1
        ReDim Preserve ARABUTNAM(X) : ReDim Preserve ARABUTDES(X) : ReDim Preserve ARADEPNAM(X) : ReDim Preserve ARADEPCOL(X)
        ARABUTNAM(X) = "test"
        ARABUTDES(X) = "test multiple buttons"
        ARADEPNAM(X) = "test"
        ARADEPCOL(X) = "cccccc"
'=== sub to draw html buttons in left frame

'=== sub to open internet explorer, define 3 frame and define event for quit, and button press to call subs to 'change a public variable button called differently for each frame


'=== main loop

        Do

            If resbutlefstr = "test" Then
               msgbox("test"
            end if
        Loop Until bReady Or resbutlefstr = "quit01"


Of course we have a web event that will execute code to change the value of resbutlef when a button is pressed in internet explorer frame

bready was there if the user close internet explorer
but i changed it to an event that execute this sub if internet explorer is closed:


    Private Sub oIE_onQuit()
        'bReady = True
        '=== if internet explorer is closed, we exit the program
        End '(end program)
    End Sub

If you fix all array length and define buttons name, you could not add buttons
The frame number is a fixed number
the elements in the frame can be infinite (big database)
so we will display them 15 at one time, with a next button and previous
Also a search button

That will lead us to dymanic sql query building