|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Generate data entry and reporting .NET Web apps in minutes, straight from your database. Read our FREE whitepaper “Build Web 2.0 Applications Without Hand-Coding” Download now! |
|
#1
July 22nd, 2004, 01:38 PM
|
|||
|
|||
|
INSERT to Access DB with VBScript
I am trying to use INSERT to add records to an Access database
from a webpage using VBScript coupled with an entry form. I have already used this form with a recordset.AddNew and recordset.Update structure which works fine but updates every record in the database. The code: <Script Language="VBScript"> Sub DBAdd Const adOpenStatic = 3 Const adLockOptimistic = 3 Const adUseClient = 3 Set objConnection = CreateObject("ADODB.Connection") Set objRecordset = CreateObject("ADODB.Recordset") objConnection.Open "DSN=LabData;" objRecordset.CursorLocation = adUseClient objRecordset.Open "SELECT * FROM Table1" , objConnection, adOpenStatic, adLockOptimistic objRecordset.AddNew objRecordset("Ident") = document.DBAddForm.Ident.value objRecordset("Username") = document.DBAddForm.Username.value objRecordset("Password") = document.DBAddForm.Password.value objRecordset("Pin/Code") = document.DBAddForm.Pin.value objRecordset("Registration Code/Key") = document.DBAddForm.Key.value objRecordset("Type") = document.DBAddForm.Type.value objRecordset.Update objRecordset.Close objConnection.Close End Sub </script> </head> <body> <h3>Add to Pin Database</h3> <FORM NAME="DBAddForm" OnSubmit="DBAdd()"> <table cellspacing=0 cellpadding=5 border=0> <tr><td>Enter Ident:</td><td><INPUT TYPE="TEXT" NAME="Ident"></td></tr> <tr><td>Enter Username:</td><td><INPUT TYPE="TEXT" NAME="Username"></td></tr> <tr><td>Enter Password:</td><td><INPUT TYPE="TEXT" NAME="Password"></td></tr> <tr><td>Enter Pin/Code:</td><td><INPUT TYPE="TEXT" NAME="Pin"></td></tr> <tr><td>Enter Registration/Key:</td><td><INPUT TYPE="TEXT" NAME="Key"></td></tr> <tr><td>Enter Type:</td><td><INPUT TYPE="TEXT" NAME="Type"></td></tr> <tr><td><br></td><td><input TYPE="Submit" VALUE="Submit"></td></tr> </table> </FORM> I modified the code to use INSERT to add a single record at a time but am not getting the expected output (db input) when running the script. The code has been changed to the following (the Form code is the same): <Script Language="VBScript"> Sub DBAdd Const adUseClient = 3 Set conn = CreateObject("ADODB.Connection") conn.Open "DSN=LabData;" conn.CursorLocation = adUseClient strCmd = "INSERT INTO Table1 ([Ident],[Username],[Password],[Pin/Code],[Registration Code/Key],[Type]) VALUES ('document.DBAddForm.Ident.value','document.DBAddForm.Username.value','document.DBAddForm.Password.v alue', 'document.DBAddForm.Pin.value','document.DBAddForm.Key.value','document.DBAddForm.Type.value')" conn.Execute strCmd conn.Close End Sub </script> The database gets filled with the values Ident->document.DBAddForm.Ident.value, etc. I have also tried substituting intermediate variables such as: a = document.DBAddForm.Ident.value b = document.DBAddForm.Username.value c = document.DBAddForm.Password.value etc. but no luck. I am new to all of this and have read about syntax error problems when using the INSERT statement to add to an Access database, but I think my problem revolves around properly inserting the entered values into the VALUES section of INSERT. Can anyone point out why this isn't working correctly or what I have to add/change to make it work correctly? Is there a better way to do this?
|
|
#2
July 22nd, 2004, 03:51 PM
|
|||
|
|||
|
Well, I suppose I should know better, but I just don't work with
VBScript that much. After experimenting with the usual circle in a spiral syntax, this is what works: a = document.DBAddForm.Ident.value b = document.DBAddForm.Username.value c = document.DBAddForm.Password.value d = document.DBAddForm.Pin.value e = document.DBAddForm.Key.value f = document.DBAddForm.Type.value strCmd = "INSERT INTO Table1 ([Ident],[Username],[Password],[Pin/Code],[Registration Code/Key],[Type])" &_ "VALUES ('"& a &"','"& b &"','"& c &"','"& d &"','"& e &"','"& f &"')" Notice the double quotes inside the single quotes surrounding the VBScript concatenation characters containing the variable reference. I'm beginning to remember why I only use VBScript when it is absolutely necessary.
|
|
| Viewing: Dev Shed Forums > Web Site Management > Scripts > INSERT to Access DB with VBScript |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
