excel database management

excel database management

Working at my excel file macros

there is titles with sub titles in a  few titles lines in excel (2)
i programmed this in a static way
now i want to reprogramm it with a recursive sub to generate sub titles everytimes a new array is in an array

this way the excel table will be generated with titles and sub titles, infinitly (recursiveness)
this sub have to be done object so it can be used by any part of my programming

including displaying other tables

ideally, one objecty should contain all arrays that will generate all titles and sub titles recursively

i also have to think about futur use, like editing thoses table and adressing the right column name while doing it even though there will be sub titles for evey columns, only the last sub title will be the column name used to access the data, the rest of the main titles will be there only for appearence and clarity

dynamic entry! Infinite join query based on column names

Game on!

My last baby, a vb code to build a query made of arrays

suppose you want to update information in a table
but to make that decision you need info from another table, from another application that is read only
both table are related by an id column

so there is:
arabd(1) array of database
aratab(1) array of tables
aracol(1) array of columns

(read only)
aradb(0)=array("db01")
aratab(0)=array("product")
aracol(0)=array("id", "productacronym","datafromautocadetc")

(write/read)
aradb(1)=array("db02")
aratab(1)=array("product")
aracol(1)=array("id","id_product","description")
aralin(1)=array("left join")

the vb code, will analyse the second db
it will find a column that contain the table name in preceding database
in this case:
"id_product" --> contain product, wich is a table name in the database element in array just before the actual table

so the link will be made with a left join from (on) column "id_product" to "id" in product table, in db01 database

the query will be generated to make this left join and executed

that will result in a table containing columns from both tables / databases

now the moment of truth, yes, i know you can do this with a view in sql

BUT when come the moment to write in one of the database, but not the other, the view cannot do it
you will need a specific update or insert to do that

since all our table parameters are in arrays, we can generate another query to write back in one table with the right id to change one record related to another table, in witch we have no right to write (db01, tab01)

so the acronym would be a column read only from another application
the description would be in a table where we have write access

of course if we add more databases arrays, more table arrays, and more columns arrays, the query generated will be longer, but still work, up to infinite! Or up to how much ram your sql server have ;)

next step:
write the code to generate the write query that will write in only one table to update the record we can or have the right to write

this is all done in excel, to edit databases from different applications. Some we want the data from as complementary information to make the modifications, but have no right to write in

of course for this query to work you will have to respect a certain naming convention
always have an id, always have an id with table name after it in next database you want to join with

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

internet explorer bypass for ready state

Hello again!

The event technique to see if internet explorer was ready did not work out at all
The event check were taking lots of cpu (on my i7!!!)

So i simply made a do loop with a try inside, to try every 1/10th of a second to access internet explorer (write in frame) for 1 sec. That was enough to remove any crash in windows xp

Futur: i'll need to manage every writing in internet explorer or any interacting with it, with the same do loop and try.

So here is the code, before i write in internet explorer, i put all my html in htmtab.


        try01 = 0
        err01 = 0
        Do
            Try
                ffra = oIE.Document.frames.item("f" & ffranam.ToString).document
                System.Threading.Thread.Sleep(100)
            Catch ex As Exception
                If logall = 1 Then fil02.WriteLine(DateValue(Now) & " " & TimeValue(Now) & " crash creating frame again " & ex.Message)
                err01 = err01 + 1
            End Try
            If err01 = 0 Then Exit Do
        Loop Until err01 > 10
        If err01 > 10 Then
            If logall = 1 Then fil02.WriteLine(DateValue(Now) & " " & TimeValue(Now) & " error trying to recreate frame: " & err01)
            End
        End If

internet explorer is ready, is it?

still working in visual studio 2010, vb
with internet explorer interface

i need to know if internet explorer object is ready to receive data

many time in the past my interface was crashing because it was not ready to receive data

i changed the ready management

i used VB events to manage the value of a global (public) variable

it is said that the document download event will trigger everytime a frame load too
that would explain why the downloadbegin and downloadcomplete event does not exist for a frame object
so i will have to identify the frame that triggered the event in the sub (begin and complete)

one for OIE (object internet explorer)
and one for each frame (flef, fmif, fbot)


Public oieready01 As Integer = 1
    Public flefready As Integer = 1
    Public fmidready As Integer = 1
    Public fbotready As Integer = 1

event management after oie was created:

        oIE = New SHDocVw.InternetExplorer

        If logall = 1 Then fil02.WriteLine(DateValue(Now) & " " & TimeValue(Now) & " AddHandler oIE.onQuit, AddressOf oIE_onQuit")

        AddHandler oIE.onQuit, AddressOf oIE_onQuit

        AddHandler oIE.DocumentComplete, AddressOf oIE_onDocumentComplete

        AddHandler oIE.DownloadBegin, AddressOf oIE_onDownloadBegin

event for each frame:

        'AddHandler flef.DownloadBegin, AddressOf oIE_onflefDownloadBegin

        'AddHandler flef.DocumentComplete, AddressOf oIE_onflefComplete

        'AddHandler fmid.DownloadBegin, AddressOf oIE_onfmidDownloadBegin

        'AddHandler fmid.DocumentComplete, AddressOf oIE_onfmidComplete

        'AddHandler fbot.DownloadBegin, AddressOf oIE_onfbotDownloadBegin

        'AddHandler fbot.DocumentComplete, AddressOf oIE_onfbotcomplete

note: that is not working for frames, thus, the comment marks -->  '

apparently, a document object from a frame does not have the downloadbegin property

    '=== OIE internet explorer
    Private Sub oIE_onDownloadBegin()
        If logall = 1 Then fil02.WriteLine(DateValue(Now) & " " & TimeValue(Now) & " oie download begin was called")
        oieready01 = 0
    End Sub
    Private Sub oIE_onflefDownloadBegin()
        If logall = 1 Then fil02.WriteLine(DateValue(Now) & " " & TimeValue(Now) & " flef download begin was called")
        flefready = 0
    End Sub


    Private Sub oIE_onfmidDownloadBegin()
        If logall = 1 Then fil02.WriteLine(DateValue(Now) & " " & TimeValue(Now) & " fmid download begin was called")
        fmidready = 0
    End Sub


    Private Sub oIE_onfbotDownloadBegin()
        If logall = 1 Then fil02.WriteLine(DateValue(Now) & " " & TimeValue(Now) & " fbot download begin was called")
        fbotready = 0
    End Sub
    '=== navigation is completed
    '=== caca
    Private Sub oIE_onDocumentComplete(ByVal pdisp As Object, ByRef url As Object)
        Dim stra As String
        stra = LCase(TypeName(pdisp))
        If stra <> "iwebbrowser2" Then
            If logall = 1 Then fil02.WriteLine(DateValue(Now) & " " & TimeValue(Now) & " type of pdisp: " & stra)


            'stra = pdisp.getattribute("name")
            'MsgBox(stra)
        End If
        oieready01 = 1
    End Sub


    '=== FRAMES
    Private Sub oIE_onflefComplete()
        If logall = 1 Then fil02.WriteLine(DateValue(Now) & " " & TimeValue(Now) & " oie flef complete")
        flefready = 1
    End Sub


    Private Sub oIE_onfmidComplete()
        If logall = 1 Then fil02.WriteLine(DateValue(Now) & " " & TimeValue(Now) & " oie fmid complete")
        fmidready = 1
    End Sub


    Private Sub oIE_onfbotcomplete()
        If logall = 1 Then fil02.WriteLine(DateValue(Now) & " " & TimeValue(Now) & " oie fbot complete")
        fbotready = 1
    End Sub


    Sub oieready()
        Dim maxwai As Integer = 50
        Do
            System.Threading.Thread.Sleep(50)


        Loop Until oieready01 <> 0
        'And flefready <> 0 And fmidready <> 0 And fbotready <> 0
    End Sub

array discussion

Now let's discuss about arrays

An array is a group of elements
It can have many dimensions
It can be displayed in a table, that is limited to 2 dimensions

The problem when you generate a table from an array is you don't know if there is 1 or 2 dimension(s)
An array with one line will have 1 dimension (one y (line) with many x (columns))
An array with 2 line, with 5 columns each will have 2 dimensions (the y and the x)
Y is the line (vertical dimension)
X is the column (horizontal dimension)

example:

Select	S/N	S/N 2	Symptom	Problem	Probability	Confirmation
	9	1	The carrier moves only in one direction	One coil of the proportional valve is not well connected	0,65	2
	67	1	FAULT HYDRAULIC OIL LOW LEVEL	Cumulative of minor leaks of oil during maintenance	0,4	2
	41	1	Hydraulic oil level in tank too low	Cumulative of minor leaks of oil during maintenance	0,4	2

We do not count the first column, because it's just a selection column (will see later)
We do not count the first line, because it's a title

Here is the array:
model: ara01( Y line, X column )

ara01(0,0)="9"
ara01(0.1)="1"
ara01(0,2)="The carrier moves only in one direction"
...
ara(2,2)="Hydraulic oil level in tank too low"
etc.

Now let's not forget we are always using Y first (line) and X second (column)

PROBLEM:
The problem is when the array only have one dimension, Y become X:
(ths first digit of the dimensions, is now the column, not the line)

Hello,

how

are

you?

model: ara01(X column)
ara01(0)="hello"
ara01(1)="how"
etc.

We can also represent the array like in our precedent code:
ara01 = array( _
"Hello,", _
"how", _
"are", _
"you?")

Or without the vbscript line separator aka " _ " (space, _, space)

dynamic definition: (the elements will fill automatically each numbered element of the array, starting at 0)
ara01 = array("Hello,", "how", "are", "you?")

Fixed definition: (we put each elements in a part of the array, telling the right number ourselves, but we cannot insert a new element without changing all numbers)
ara01(0) = "Hello,"
ara01(1) = "how"
ara01(2) = "are"
ara01(3) = "you?"

Back to the single dimension array problem
So the first digit is now X and Y vanish (never existed in fact)
Because we do not have lines (Y), only columns (X)
Now that is a problem when we generate a HTML table

Because we do not know how many dimension the array have

We do not decide how many dimensions we have, because we will fill the array with data from a table, and the table might have only one line...

Since our data is not know, it can have one line or many, one dimension or many

If we ask how many elements there is in the second dimension and there is only one dimension, the script will generate an error

NEXT:
We will see how to generate an html table with an array that can have 1 or 2 dimension(s)

Because it's late and i'll go to bed, good night!

STEP 004 html table to display array

Now let's make it a little more complicated

We will display our results in internet explorer, but in an html table

note: we do not check the ready state of internet explorer yet, so the script can crash if it write to internet explorer, and the object is not ready to receive data from the script (slow computers)

first part:

'=== initialize the table in html
htmtab=""
htmtab = htmtab & "<b><table  width=""100%"" BORDERCOLOR=""black"" class=MsoTableGrid border=1 CELLSPACING=0 cellpadding=2 style='border-collapse:collapse;border:none'>"
htmtab = htmtab & "<CAPTION></CAPTION><span style='color:purple'>"
htmtab = htmtab & "<TR>"


It's the html code to generate a table in internet explorer
We put the string in HTMTAB, because we want to accumulate all the data we want to send to internet explorer in a variable


Now, i was stating in precedents post how 100% of width in a table will facilitate printing
When you press print, a table with 100% width will adjust to the paper you print on
If the paper is not large enough, try go landscape or 11x17 inches landscape
Eventually, if it's really too big, work on screen only and do not print anything :P

Then the loop for each column:

'=== put each element in a cell
for each stra in ara01
   htmtab = htmtab & "<td><p>" & stra & "</td>"
next


Then we close the table:

'=== close the html table
htmtab = htmtab & "</tr>"
htmtab = htmtab & "</table></span></b><br>"


Later we will use a two dimension array, to display more than one line
But as we will see, it's more complicated than it seem
We need to determine how many dimensions the array have before deciding if our table will have only one line or many lines

Here is the code for our first table in internet explorer:
(copy in notepad, save on desktop as .VBS, double click to execute)


'===== start of test4.VBS ===============


'=== object for files and filenames
Set objFSO    = CreateObject("Scripting.FileSystemObject")
'=== object to rerun the script in 32 bits
Set objshe    = CreateObject("WScript.Shell")


'=== actual drive, actual directory, and "\"
thepath=WScript.ScriptFullName
p = instrRev(thepath,"\")
basedir  = left(thepath,p)
'=== script name to rerun it in 32 bits
filnam = right(thepath,len(thepath)-p)


'=== windows dir
WinDir = objfso.GetSpecialFolder(0)
'=== restart the script in 32 bits if we are on a 64 bits system
'=== (databases drivers issues)
a64 = windir & "\syswow64\wscript.exe"


if objFSO.fileEXISTS(a64) and instr(lcase(wscript.fullname),"syswow64")=0 then
   '=== 64 bits system
   a = """" & a64 & """ """ & basedir & filnam & """"
   objshe.Run a,0, false
   wscript.quit
end if


'=== create an internet explorer object to display stuff
set oIE = wscript.CreateObject("InternetExplorer.Application", "IE_")
oie.FullScreen = False
oIE.left=0 ' window position
oIE.top = 0 ' and other properties
oIE.height = 500
oIE.width = 500
oIE.menubar = 1 '=== no menu
oIE.toolbar = 1
oIE.statusbar = 1
oIE.RegisterAsDropTarget = True
oie.Navigate("about:blank")
oie.document.title = doctit
oiewid = oie.document.parentwindow.screen.width
oiehei = oie.document.parentwindow.screen.height
'=== we generate a size in % of the screen height and width for the internet explorer window
sizwidpercent = 100
sizheipercent = 95
loswid = 100-sizwidpercent
loshei = 100-sizheipercent
newwid = oiewid*sizwidpercent*.01
newhei = oiehei*sizheipercent*.01
oie.document.parentwindow.resizeto newwid,newhei
newx = oiewid * loswid * .01 /2
newy = oiehei * (loshei/2) * .01 /2
oie.document.parentwindow.moveto newx, newy
oIE.visible = 1 '=== visible on
oie.addressbar=false


'====== main program, or loop


ara01 = array( _ 
"Hello,", _ 
"how", _ 
"are", _ 
"you?")


'=== initialize the table in html
htmtab=""
htmtab = htmtab & "<b><table  width=""100%"" BORDERCOLOR=""black"" class=MsoTableGrid border=1 CELLSPACING=0 cellpadding=2 style='border-collapse:collapse;border:none'>"
htmtab = htmtab & "<CAPTION></CAPTION><span style='color:purple'>"
htmtab = htmtab & "<TR>"




'=== put each element in a cell
for each stra in ara01
   htmtab = htmtab & "<td><p>" & stra & "</td>"
next


'=== close the html table
htmtab = htmtab & "</tr>"
htmtab = htmtab & "</table></span></b><br>"


'=== send the result in internet explorer all at one time
oie.document.WriteLn(htmtab)


'=== end of program
wscript.quit


'=== at the end of the program, we have the sub and functions


'=== if internet explorer is closed, we leave script
sub IE_onQuit()
   wscript.quit
end sub


'=========== end of script .VBS =================

STEP 003 dynamic variable for futur tables

Now to output something and think about the futur of the output

Most programmer use fixed variables or array
(fixed table of data)

We are better than that, so we use dynamic programming
If there is more columns, we display more columns
If there is more lines, we display more lines
(later we will learn to display them 15 at one time not to overload an internet explorer page with 1 millions lines that would take minutes to generate)

We will not open a database right now
So we will put the data in an array that we will build dynamically to be able to expand it later with more data

Let us display a few lines of data in internet explorer

Now let's explain the main part of the code:


First we create an array with a dynamic number of elements:

ara01 = array( _ 
"Hello,", _ 
"how", _ 
"are", _ 
"you?")

we do it this way so we can add an element just by inserting a new line in the middle of the construction of our array

Then we loop trough all elements of the array to write them all in internet explorer:
OIE is the internet explorer object
WRITELN is to write a line of text or html
FOR EACH is a loop that will go through all elements in the array
NEXT will tell the FOR EACH to go back to FOR EACH and get the next element
STRA is the variable in wich FOR EACH will put every elements of the arrray, one at a time
WRITELN will write the content of STRA in the internet explorer object document
Internet explorer is an object, and document is the white page in wich you write stuff
Why? Because the internet explorer object can be given command, or size change, not only text to display
(see the beginning of the code where we change the size and many attributes of the internet explorer object aka, OIE)

for each stra in ara01
   oie.document.WriteLn(stra & "<br>")
next

we added a <br> for a carriage return in HTML, internet explorer will interprete it and make a carriage return (change line)

The name of the variables we use is very important
ARA will tell the programmer this is an array (group of elements)
STR will tell this is a string (chain of caracters)

Now i hear poeples yell: your names are too shorts! not significatives!
this is to learn programming, not to lose hours with long variables names to type ;)

of course when your program will grow bigger, you will have to use name like this:
ara_texttodisplay = array("longer variable name is good")

no  spaces in variables names, no number to start, must start with a letter


now why use an array?
because later, what you will display will come from a database (table), and will have many elements
So we want to learn how to display many elements now
And how to add a line change for every elements
if the line change is in the loop, we do not have to type it in every element, we are programmers, we are lazy
Later we will even make a html table to display them



in notepad:


'===== start of test4.VBS ===============


'=== object for files and filenames
Set objFSO    = CreateObject("Scripting.FileSystemObject")
'=== object to rerun the script in 32 bits
Set objshe    = CreateObject("WScript.Shell")


'=== actual drive, actual directory, and "\"
thepath=WScript.ScriptFullName
p = instrRev(thepath,"\")
basedir  = left(thepath,p)
'=== script name to rerun it in 32 bits
filnam = right(thepath,len(thepath)-p)


'=== windows dir
WinDir = objfso.GetSpecialFolder(0)
'=== restart the script in 32 bits if we are on a 64 bits system
'=== (databases drivers issues)
a64 = windir & "\syswow64\wscript.exe"


if objFSO.fileEXISTS(a64) and instr(lcase(wscript.fullname),"syswow64")=0 then
   '=== 64 bits system
   a = """" & a64 & """ """ & basedir & filnam & """"
   objshe.Run a,0, false
   wscript.quit
end if


'=== create an internet explorer object to display stuff
set oIE = wscript.CreateObject("InternetExplorer.Application", "IE_")
oie.FullScreen = False
oIE.left=0 ' window position
oIE.top = 0 ' and other properties
oIE.height = 500
oIE.width = 500
oIE.menubar = 1 '=== no menu
oIE.toolbar = 1
oIE.statusbar = 1
oIE.RegisterAsDropTarget = True
oie.Navigate("about:blank")
oie.document.title = doctit
oiewid = oie.document.parentwindow.screen.width
oiehei = oie.document.parentwindow.screen.height
'=== we generate a size in % of the screen height and width for the internet explorer window
sizwidpercent = 100
sizheipercent = 95
loswid = 100-sizwidpercent
loshei = 100-sizheipercent
newwid = oiewid*sizwidpercent*.01
newhei = oiehei*sizheipercent*.01
oie.document.parentwindow.resizeto newwid,newhei
newx = oiewid * loswid * .01 /2
newy = oiehei * (loshei/2) * .01 /2
oie.document.parentwindow.moveto newx, newy
oIE.visible = 1 '=== visible on
oie.addressbar=false


'====== main program, or loop


ara01 = array( _ 
"Hello,", _ 
"how", _ 
"are", _ 
"you?")


for each stra in ara01
   oie.document.WriteLn(stra & "<br>")
next


'=== end of program
wscript.quit


'=== at the end of the program, we have the sub and functions


'=== if internet explorer is closed, we leave script
sub IE_onQuit()
   wscript.quit
end sub


'=========== end of script .VBS =================

STEP 002 web interface hello world

Now for the interface
open notepad

first we have to assume most objects in windows are accessible in 32 bits only
so we will make a script that restart himself in 32 bits if your windows is in 64 bits
for that we have to find if windows is 64 bits
we need a few windows objects for that
so we will create a filesystemobject to chek folders and if we are in syswow64 (the 32 bits wscript.exe that will run our script)

-------------------------- test3.vbs -----------------------
'=== object for files and filenames
Set objFSO    = CreateObject("Scripting.FileSystemObject")

'=== object to rerun the script in 32 bits
Set objshe    = CreateObject("WScript.Shell")


'=== actual drive, actual directory, and "\" we need it to rerun the script in 32 bits
thepath=WScript.ScriptFullName
p = instrRev(thepath,"\")
basedir  = left(thepath,p)
'=== script name to rerun it in 32 bits
filnam = right(thepath,len(thepath)-p)

'=== windows dir

WinDir = objfso.GetSpecialFolder(0)

'=== restart the script in 32 bits if we are on a 64 bits system
'=== (databases drivers issues)
a64 = windir & "\syswow64\wscript.exe"

if objFSO.fileEXISTS(a64) and instr(lcase(wscript.fullname),"syswow64")=0 then
   '=== 64 bits system
   a = """" & a64 & """ """ & basedir & filnam & """"
   objshe.Run a,0, false
   wscript.quit
end if

'=== create an internet explorer object to display stuff
set oIE = wscript.CreateObject("InternetExplorer.Application", "IE_")
oie.FullScreen = False

oIE.left=0 ' window position
oIE.top = 0 ' and other properties
oIE.height = 500
oIE.width = 500
oIE.menubar = 1 '=== no menu
oIE.toolbar = 1
oIE.statusbar = 1
oIE.RegisterAsDropTarget = True
oie.Navigate("about:blank")
oie.document.title = doctit
oiewid = oie.document.parentwindow.screen.width
oiehei = oie.document.parentwindow.screen.height
'=== we generate a size in % of the screen height and width for the internet explorer window
sizwidpercent = 100
sizheipercent = 95
loswid = 100-sizwidpercent
loshei = 100-sizheipercent
newwid = oiewid*sizwidpercent*.01
newhei = oiehei*sizheipercent*.01
oie.document.parentwindow.resizeto newwid,newhei
newx = oiewid * loswid * .01 /2
newy = oiehei * (loshei/2) * .01 /2
oie.document.parentwindow.moveto newx, newy
oIE.visible = 1 '=== visible on
oie.addressbar=false

'=== write a line in the internet explorer window, no html for now
oie.document.WriteLn("Hello World")


oie.document.WriteLn("<br><br>You can close internet explorer now, the program have ended")

'=== end of program
wscript.quit

'=== at the end of the program, we have the sub and functions

'=== if internet explorer is closed, we leave script

sub IE_onQuit()
   wscript.quit

end sub

------------------- end of .vbs --------------------

Now you might find this a little big just to display hello world

But it's a frame you can use for many purpose

Our purpose here is to learn how to program

So we needed a fast way to have an interface and use it to display our results

(input of results in a box, will come later)

For the near futur we will concentrate on programming nice code, with a nice structure

and have a few output to see if the code is working ;)

STEP 001 hello world

The first language (ok the second, first was machine language) was BASIC

Many of use learned to program on color computer, commodore 64 with the integrated basic

Later with QBASIC, on pc computers

QBASIC cannot be used anymore as it is a dos application and take input and display results in a dos window or a low resolution graphic screen.

So you need to learn programming from a basic language still, but you do not have any free basic interpreter

You can learn with windows host script (.WSH or .VBS)
All you need is notepad, and save your file as .VBS

This basic scripting language have many commands and many objects that can be created in windows
A internet explorer object can be created for input and output
Now the "pure" will say: I want to use firefox. Well, you can't because the internet explorer object is registrated in windows and can be called from VBS, but firefox is not registrated with windows. So you cannot access firefox objects from VBS and use it as interface.

Let's start with the famous HELLO WORLD

open notepad
make a new file called --------------- test.VBS -------------
msgbox("Hello World")
-------------- end of file -----------------
save it on desktop
double click on it
and here is your box with "hello world" in it

Now thoses box are limited to a few line of text in output
and on input, one line of input

that is why we have to use an interface
since we don't want to multiply the developement time by 3 or 4, we will use internet explorer as interface
(who want to build grids, and crystal reports for two third of their programming time...)

let's talk about programming a little now

First the is the IN and the OUT
Usually the client will provide you the IN and OUT and you will program the TREATMENT to achieve both

IN:
screen of the input the client want to do
as example let's say: a baseball team, member of team, first presence to the bat, simple, double, triple, homerun, retired, strike, ball
you can use numbers for players to enter statistics faster

OUT:
the statistics
a table with all teams, with highest score at top
another table, with all players for general point scored
another table for homeruns

The client need to draw thoses according to his wishes
the talk to the programmer about limitations (the screen is not large enough etc)

Now, why do i want to use a web interface for my IN and OUT
First, if i use a normal visual basic interface, i have to deal with:

IN CON:
1. static input interface, no zoom, no autoscale, no print option for inputs screen

2. the input need to be done in a grid, wich have certain limitations, mostly, it will show every column of the database, and will not allow half of them to be writable, and half of them to be read only in a visual sens of thing (like a different color if you cannot change a column)

3. fixed query. when you "program" a query in vb, you need the dataset to be linked to the database to do so. With dynamic query, where you can add or remove column according to permissions, or login name, you have a much better control of what you want to show to the user. Example: you can test each column permission and display thoses which the user have permission. In a grid, the grid would simply return an error if you dont have a permission on a column. Aditionnaly, you can manage noview, nowrite, readonly permissions on a column basis with a dynamic query. Plus sometimes, you need certains columns to manage the data, aka, the id column. But you don't want to display it in the grid. But it need to be in the dataset if you want to be able to update data with the id column.

OUT CON:
with vb, you have to construct a crystal report to view your results
if you add columns to the output, you have to modify your crystal report everytime, because it's not build dynamically

IN PRO: (for web interface)
you can zoom in or zoom out and the table in html will scale even if it goes out of the screen, a scroll bar magically appear

you can decide to skip a column you don't want the user to see, since you build the table yourself

OUT PRO:
you can output in internet explorer, and print. if your page scale is 100% it will automatically adjust to the margin of the size of paper

you can copy past the whole result table in excel and it will go in every excel cell all alone since it's an html table

you can also copy past the table of results in word processor, that will accept an html table easily to make a better looking report (that will still be text, and not a heavy image)

since we can imagine the futur, you can change your vb code to asp and generate the web page dynamically from a server web with basically the same code

and all of it will be much lighter than a web app or a grid, a loading time, and a complete absence of possibility to customize the output

Enough talking let's do some code next!

all done

i finished at work my programming for dynamic web interface in visual studio 2010

i had logic problem clearing frames
and also the program crash on start sometimes, because the internet explorer is not ready to process commands

i tried to add some "ready state" wait loop, but it report never ready when the object internet explorer is not visible, i guess the ready status is based on the status bar, and if it's not visible, the ready status never come to "ready"

i'll try different ways for this, like after creating the object, i'll chek if it's equal to nothing and loop until it's different from nothing

also instead of pusing many line from an array in internet explorer object, i'll push them in a variable, then push the whole string in one command, so my internet explorer object will not report not ready and crash the program if i send too many line in a short amount of time

(although if the object is not ready for the big string, it will still crash)

it will be perfect! ;) one day

i'll start programming it for other applications than the troubleshooting database for crucible cleaner for STAS

not working with simply the files

i will have to check the references i addded
i added the 2.8 for adodb
maybe i should have added the 2.7
the computer i tried it on was windows server 2008 fully updated with frame work 4.0

first test on another computer

i did a first test on another computer and obviously it's not working

i'll simply copy the exe and all it's files and see if it's working

publish status include

setup working

i had to go to the publish tab in visual studio 2010
then on my .MDB, publish status: include (no auto)

and all is published and work fine when i click setup

i still wonder why it's not compiling a MSI file, but a setup that publish the application in a temp folder instead of "program files"

will check that later to make a true MSI

funny visual studio

after i told studio my images were embedded ressources
it did not include them in the setup

so much for honesty and protocol haha
ill revert thoses properties to CONTENT

compiling the application

compile work, half of it

when i insert a MDB file, visual studio does not let me edit it's properties and set it as embedded ressource
so he does not copy the mdb in the executable directory, thus program not working...

for the images it work fine

all done

all done, the program:

crucible cleaning troubleshooting database

is now fully working in visual studio 2010

with internet explorer as interface

now i need to find how to compile a setup with visual studio 2010

add the images in this setup and the database (mdb)

found how to integrate to vb 2010 onquit for internet explorer

found how to integrate to vb 2010 onquit for internet explorer without linking to the SHDocVw.dll as least not directly in the syswow64 directory but with the com reference that was created for it


right click on project in visual studio 2010:

'add reference, com, microsoft internet control

    Public oIE As SHDocVw.InternetExplorer

        oIE = New SHDocVw.InternetExplorer

        AddHandler oIE.onQuit, AddressOf oIE_onQuit

    Private Sub oIE_onQuit()
        End
    End Sub

so when my user quit internet explorer (wich is the main interface), the program end

now working on the 2 other functions of the project

causes search and component search

web interface raising custom event for when internet explorer close

working on the closing event

for internetexplorer.application

enough for tonight event handling

so i had to fiin d how to create an event handling if the OIE object disappear
(if the user close internet explorer)

it's long... lots of research

going to bed

hide console window

simply changed console application to windows application in visual studio, project, application panel

programming again

added the path variable


        Dim strAppPath As String
        strAppPath = System.Reflection.Assembly.GetExecutingAssembly.Location()
        BASEDIR = Left(strAppPath, InStrRev(strAppPath, "\"))


now working on hiding the system.console (the dos windows that pop up when program execute

keyboard working in web interface onkeypress event handler now working

keyboard now working in web interface

that was tricky
i had to prevent the return of the code 13 (enter) in the onkeypress handler
wich i use in my program, as you enter something in a textbox, then press enter or escape
or else my textbox was not returning a string type, but something else... tum tum tum...
that was poping a conversion type error

for escape, it was not returning anything because i cancel the input when esc is pressed

           AddHandler CType(flef, mshtml.HTMLDocumentEvents2_Event).onkeypress, AddressOf prekeylef
        AddHandler CType(fmid, mshtml.HTMLDocumentEvents2_Event).onkeypress, AddressOf prekeymid
        AddHandler CType(fbot, mshtml.HTMLDocumentEvents2_Event).onkeypress, AddressOf prekeybot


'==================


 Private Function prekeylef(ByVal e As mshtml.IHTMLEventObj) As Boolean
        'System.Console.Write("button clicked: " & e.srcElement.tagName.ToString() & vbCrLf)
        reskeylef = e.keyCode
        If reskeylef <> 13 And reskeylef <> 27 Then
            Return e.keyCode
        End If
        'System.Console.Write("keypressed " & reskeylef.ToString)
    End Function
    Private Function prekeymid(ByVal e As mshtml.IHTMLEventObj) As Boolean
        'System.Console.Write("button clicked: " & e.srcElement.tagName.ToString() & vbCrLf)
        reskeymid = e.keyCode
        If reskeymid <> 13 And reskeymid <> 27 Then
            'eee
            'MsgBox(" keycode" & reskeylef.ToString)
            Return e.keyCode
        End If


        'System.Console.Write("button clicked: " & resbutmidstr & vbCrLf)
    End Function
    Private Function prekeybot(ByVal e As mshtml.IHTMLEventObj) As Boolean
        'System.Console.Write("button clicked: " & e.srcElement.tagName.ToString() & vbCrLf)
        reskeybot = e.keyCode
        If reskeybot <> 13 And reskeybot <> 27 Then
            Return e.keyCode
        End If


        'System.Console.Write("button clicked: " & resbutbotstr & vbCrLf)
    End Function

corrected the button click event in a grey area

corrected the button click event in a grey area


    Private Function butclilef(ByVal e As mshtml.IHTMLEventObj) As Boolean
        'System.Console.Write("button clicked: " & e.srcElement.tagName.ToString() & vbCrLf)
        If e.srcElement.id IsNot Nothing Then
            resbutlefstr = LCase(Trim(e.srcElement.id.ToString()))
        End If
        'System.Console.Write("button clicked: " & resbutlefstr & vbCrLf)
    End Function

first part working:
problem search to symptoms to component to diagnostic
(4 table related in a mdb)

as it's 3 loop search one in another it was the longest part
strangely i had to correct some column name ([num1] is now [s/n] wich is not dynamic at all...) for second search and third search results

i also noticed if i click in the web page white area, the click event is executing and returning an error as i try to convert the result to a string

as a result of the compilation in vb.net instead of a wsh (wscript) the result display much faster

number of row corrected

i did not change this sub the right way
my catch was bad, now it's ok

the ubound fonction will return an error id you chek the number of elements in the second dimension and there is only one line in the array

so if there is only one line we return the number of line of the first dimension, not the second


    '=== count the number of record in the array
    Function cnttotrec(ByVal aara As Array)
        Dim aa As Integer


        xmax = UBound(aara, 1) 'Returns the Number of columns  --- elements in first dimension
        '=== chek the number of elements in second dimension
        '=== if there is none, then the array have only 1 row
        Try
            ymax = UBound(aara, 2) 'Returns the Number of rows     --- elements in second dimension
            aa = ymax
        Catch ex As Exception
            aa = xmax
        End Try


        '=== number of dimensions in case there is only 1 column
        Return (aa)
    End Function

array and adodb recordset

now my array is filled up with 6 rows
ara03 = tag.GetRows()

but i see clearly the problem, as the number of row the array get is in fact the number of columns

working on it...

record number in dataset

I found my first problem

my adodb.recordset was not configured to count the number of records correctly

of course there was 1000 solution on internet, but none was for visual basic net 2010
I finally found the good one!

here is my new code for the sql request:


    Function exesql( _
    ByVal oobjcon As ADODB.Connection, _
    ByVal dummy As ADODB.Recordset, _
    ByVal SQL01 As String)


        Dim cmd As New ADODB.Command()
        Dim aaa As String
        Dim count As Integer
        'On Error Resume Next
        Const adOpenStatic = 3
        Const adLockOptimistic = 3
        Const adOpenForwardOnly = 0


        ttag = New ADODB.Recordset
        ttag.CacheSize = 1
        ttag.CursorLocation = ADODB.CursorLocationEnum.adUseClient
        ttag.CursorType = ADODB.CursorTypeEnum.adOpenStatic
        ttag.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic


        Try
            If logall = 1 Then
                fil02.WriteLine(DateValue(Now) & " " & TimeValue(Now) & " query:")
                fil02.writeline(SQL01)
            End If
            '=== caca3
            '=== ADODB.Recordset how many did we get? bug


            'objRecordset.Open source,actconn,cursortyp,locktyp,opt


            'ttag.Open(SQL01, oobjcon)
            'ttag.CacheSize = 1000
            'ttag = objcon.Execute(SQL01)


            ttag.Open(SQL01, objcon)


            'ttag.CacheSize = 1000
            'ttag.MoveLast()


            'count = ttag.Fields.Count
            count = ttag.RecordCount


            If logall = 1 Then
                fil02.WriteLine(DateValue(Now) & " " & TimeValue(Now) & " query done without error")
                fil02.WriteLine(DateValue(Now) & " " & TimeValue(Now) & " total count in recordset: " & count)
            End If
        Catch ex As Exception
            aaa = Err.Description
            l1 = "error in query"
            l2 = "query: " & SQL01
            l3 = "error: " & aaa
            l4 = "the program will now end"
            fbot.WriteLn(l1 & "<BR><br>" & l2 & "<BR><br>" & l3 & "<BR><br>" & l4 & "<BR><br>")


            If logall = 1 Then
                fil02.writeline("query:")
                fil02.writeline(SQL01)
                fil02.writeline(Err.Description)
            End If
        End Try
        'wscript.quit()
        '=== END PROGRAM 
        Return (ttag)
    End Function

vb.net 2010 web interface programming

i got 6 results instead of 600

finding problem...

that one was hard validate dynamically an input of wich the frame and name change

ok as i make the program more dynamic

i had to validate the input result dynamically

but as i do the loop, i generate my webform once for the first input:
(learned that in school, first input, validate, then input again in a loop is result is bad)




                X = 0
                ReDim Preserve distmp(X) : ReDim Preserve namtmp(X) : ReDim Preserve deftmp(X) : ReDim Preserve typtmp(X) : ReDim Preserve errtmp(X)
                distmp(X) = "Problem"                           '===== description displayed in front of the field
                namtmp(X) = "symptom"                           '===== name of variable for programming purpose
                deftmp(X) = ""                                  '===== default value inside the form (the form will be 20 char long if no value here, but you can enter more)
                typtmp(X) = "textbox"                           '===== type of data: textbox password (futur: more to come)
                errtmp(X) = "enter a keyword to search in symptoms" '=== text to display after the form "facultatif" 
                '=== blue text "error" red text are special keywords


                BUTtmp = {"ok", "cancel"}


                a = dynforgen(distmp, namtmp, deftmp, typtmp, errtmp, BUTtmp, tit)

                SYMPTOM = valres("fmid", "symptom", 1, distmp, namtmp, deftmp, typtmp, errtmp, BUTtmp, tit)



so, as i was getting my result in a web object in the web form, when i was regenerating my form, the reference to the object was vanishing

so i passed as paramater the frame name (fmid), and the input name (a textbox named symptom)

then i loop through all html elements in the frame to find the input textbox named symptom

then i can validate my result again dynamically
as i use this validation very often in my code, it will really free my program lines a lot

    Function valres( _
        ByVal strfra As String, _
        ByVal strval As String, _
        ByVal minlen As Integer, _
        ByVal distmp As Array, _
        ByVal namtmp As Array, _
        ByVal deftmp As Array, _
        ByVal typtmp As Array, _
        ByVal errtmp As Array, _
        ByVal BUTtmp As Array, _
        ByVal TIT As String)


        Dim inpdon As Integer
        Dim err01 As Integer
        Dim val01 As String
        Dim tmpstr As String
        Dim tmpfra As Object
        Dim str01 As String
        Dim str02 As String


        Do
            inpdon = 0
            err01 = 0
            '=== flag to say input is done, since there might be a defaut value, we must validate if user was finished


            '=== "ok" button or "enter" key are the same
            '=== and nothing was pressed on left frame (control frame)


            If resbutlefstr = "" And (resbutmidstr = "ok" Or reskeymid = 13) Then


                '== User has clicked the OK button, retrieve the values
                '=== frame, value of object to read in the frame
                '=== we loop through all objects to find the right one
                'strfra strval


                'flef = oie.document.frames("flef").document
                If logall = 1 Then
                    fil02.WriteLine(DateValue(Now) & " " & TimeValue(Now) & " frame name: " & strfra)
                    fil02.WriteLine(DateValue(Now) & " " & TimeValue(Now) & " textbox name: " & strval)
                End If
                tmpfra = oIE.document.frames(strfra.ToString).document


                For Each HTMLEle In tmpfra.getElementsByTagName("input")
                    str01 = LCase(HTMLEle.getAttribute("name"))
                    If str01 = strval Then
                        OBJ = HTMLEle
                        If logall = 1 Then
                            fil02.WriteLine(DateValue(Now) & " " & TimeValue(Now) & " found symptom")
                        End If
                        str02 = LCase(HTMLEle.GetAttribute("Value"))
                    End If


                    If logall = 1 Then
                        fil02.WriteLine(DateValue(Now) & " " & TimeValue(Now) & " symptom value: " & str02)
                    End If
                Next


                astr = OBJ.Value '=== namtmp is the variable name used earlier, before we call the dynamic form generation for input


                'astr = fmid.form01.symptom.Value '=== namtmp is the variable name used earlier, before we call the dynamic form generation for input


                If logall = 1 Then
                    fil02.WriteLine(DateValue(Now) & " " & TimeValue(Now) & " value of objval.value: " & astr)
                End If


                val01 = LCase(astr)


                If Len(val01) < minlen Then
                    '=== this is disabled to 0 caraters, cause we can search empty string
                    tmpstr = "error - you must enter at least one caracter"
                    err01 = 1
                    errtmp(0) = tmpstr


                    '=== if the value was not good, we generate the dynamic input for again, with an error message after the form in red
                    a = dynforgen(distmp, namtmp, deftmp, typtmp, errtmp, BUTtmp, TIT)
                    reskeymid = 0
                    resbutmidstr = ""
                Else
                    '=== the input was validated, we flag err01 to none, and flag inpdon to exit the loop
                    inpdon = 1
                    err01 = 0
                End If
            End If


            System.Threading.Thread.Sleep(50)


            '=== while we wait for input value, user can press "escape" key, "cancel" button or close internet explorer
            If resbutmidstr = "cancel" Or reskeymid = 27 Or bReady = True Or resbutlefstr <> "" Then
                '=== if user pressed escape or cancel, we clear the frames
                If resbutmidstr = "cancel" Or reskeymid = 27 Then
                    a = clefra({"fmid", "fbot"})
                    resbutmidstr = "cancel"
                End If
                Exit Do
            End If


            '=== if there was an input error and no one used left control frame to exit, we keep asking for input
        Loop While err01 <> 0 Or inpdon = 0


        Return (val01)


    End Function