Dynamically change HTML - Access2k

DViking
04-19-2004, 09:11 AM
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!

HardCode
04-19-2004, 09:20 AM
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?

MKoslof
04-19-2004, 12:42 PM
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.

TheDutch IceMan
04-19-2004, 12:49 PM
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

HardCode
04-19-2004, 01:08 PM
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?

DViking
04-20-2004, 01:01 AM
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

DViking
04-20-2004, 01:03 AM
The first field is of course not ID but nameMyTable :whoops:

-Renzo

HardCode
04-20-2004, 09:05 AM
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.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum