Dynamically change HTML - Access2k

Hi,

I hope somebody can help me with this one. I want to enter some data in an Access 2000 database through a webpage. On this page I have a listbox that I fill dynamically with some data of the database. I have written a bit of code which does the trick, but when I try to send the data from the form back to (another table) in the database, the value of this dynamically created listbox is NULL, no matter what I select in the list.

My code:
---------------
Dim dbConn As New ADODB.Connection
Dim cmdCommand As New ADODB.Command
Dim rstRecordSet As New ADODB.Recordset
Dim arr_nameMyTable() As String
Dim i, totMyTable As Integer
Dim htmlCode As String
Dim divMyTable As HTMLDivElement

dbConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
localPath & databaseSource & ";Mode=Read|Write"
dbConn.CursorLocation = adUseClient
dbConn.open

With cmdCommand
.ActiveConnection = dbConn
.CommandText = "SELECT * FROM MyTable;"
.CommandType = adCmdText
End With

With rstRecordSet
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.open cmdCommand
End With

totGUI = rstRecordSet.RecordCount
ReDim arr_idMyTable(totMyTable)
ReDim arr_nameMyTable(totMyTable)
rstRecordSet.MoveFirst
i = 1
htmlCode = "<P>GUI: <SELECT id=lstMyTable name=lstMyTable size=1>" + vbNewLine
htmlCode = htmlCode + "<OPTION value='' selected>Choose</OPTION>" + vbNewLine
With rstRecordSet
Do Until .EOF
arr_nameMyTable(i) = .Fields.Item("nameMyTable").Value
.MoveNext
htmlCode = htmlCode + "<OPTION value='" & arr_idMyTable(i) & "'>" & _
arr_nameMyTable(i) & "</OPTION>" + vbNewLine
i = i + 1
Loop
End With

htmlCode = htmlCode + "</SELECT></P>" + vbNewLine

Set divMyTable = Document.All("divMyTable")

If divMyTable Is Nothing Then
Document.body.insertAdjacentHTML "BeforeEnd", "<DIV><HR SIZE=2></DIV>"
Document.body.insertAdjacentHTML "BeforeEnd", "<DIV id=divMyTable>"
Document.body.insertAdjacentHTML "BeforeEnd", htmlCode
Document.body.insertAdjacentHTML "BeforeEnd", "</DIV>"
Else
divMyTable.innerHTML = htmlCode
End If

rstRecordSet.Close
dbConn.Close

Set dbConn = Nothing
Set rstRecordSet = Nothing
----------

When I submit the form on the webpage I get the error:

Field 'MyTable.nameMyTable' cannot be a zero-length string


HELP!

What are you using from the web page to send the data to the DB? VBScript? Can you post this code from the web page?

Well, one problem is, if you are literally sending this data back to the database in an effort to update the table, you need to do a .update after assigning the combo box value. You loop until EOF, but you never update the recordset.

In asp (as far as i can remember) you can retrieve the value of the selected item in the list as:

Request.QueryString(lstMyTable.value) when the formaction is Get! :D

Correct me if I'm wrong, but the code you posted so far only retrieves data from the DB to the web page. Where is the code that sends the data back to the DB via INSERT and/or UPDATE?

Sorry, I forgot the other code:

-------
Dim dbConn As New ADODB.Connection
Dim cmdCommand As New ADODB.Command
Dim rstRecordSet As New ADODB.Recordset

dbConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
localPath & databaseSource & ";Mode=Read|Write"
dbConn.CursorLocation = adUseClient
dbConn.open

With cmdCommand
.ActiveConnection = dbConn
.CommandText = "SELECT * FROM MyTable;"
.CommandType = adCmdText
End With

With rstRecordSet
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.open cmdCommand
End With

With rstRecordSet
.AddNew
.Fields.Item("nameMyTable") = lstMyTable.Value
.Fields.Item("callName") = txtCallName.Value
.Fields.Item("callDep") = txtCallDep.Value
.Fields.Item("callTelnr") = txtCallTelnr.Value
.Fields.Item("callMail") = txtCallMail.Value
.Fields.Item("callDate") = txtCallDate.Value
.Update
End With

rstRecordSet.Close
dbConn.Close

Set dbConn = Nothing
Set rstRecordSet = Nothing

--------

In which the first field (ID) is supposed to get the value of the listbox on the HTML-page (lstMyTable). The strange thing is that the HTML-code does change, but when I select an option in the new listbox, it doesn't get a value.
I haven't considered an ASP-page yet because I thought I could do it with an 'ordinary' HTML file. Can it be done?

-Renzo

The first field is of course not ID but nameMyTable :whoops:

-Renzo

Ohhhhhh, so that is in an ordinary HTML page. You could use VBScript on the page, but this limits the app to Microsoft's IE browser (I don't think any other browser supports VBScript.) Best bet is to make it an ASP page, this way the script runs server-side and you don't have to be browser dependent. Try that out, because maybe the HTML page's VBScript is having trouble with the HTML list box object.