ADODB.Command Using Microsoft JET 4.0 Provider just will not execute.

[Jables]

Can anyone tell me why this code:

set oConn = Server.CreateObject("ADODB.Connection")
DBPATH = Server.MapPath("/") & "\subdir\dbasename.mdb;"
keyarch_noDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPATH & "Persist Security Info=False"
oConn.Open(keyarch_noDSN)

strSQL = "INSERT INTO tblUser (Username,Password) VALUES ('clay','pass')"

Set cmdTest = Server.CreateObject("ADODB.Command")
cmdTest.ActiveConnection = oConn
cmdTest.CommandText = strSQL
cmdTest.Execute
cmdTest.ActiveConnection.Close

Set cmdTest = Nothing
Set oConn = Nothing

Keeps producing the error:

Microsoft JET Database Engine error '80040e14' 

Syntax error in INSERT INTO statement. 

/debug.asp, line 14

The SQL statement works just fine in my local copy of the database, but it just will not work when I run it against the database on the remote server.

 

[dilettante]

Have you tried:

strSQL = "INSERT INTO tblUser (Username,Password) VALUES ('clay','pass');"

Tiny difference I know. Just a semicolon at the end.

 

[baddos]

Response.Write DBPATH
Response.End

See if that location is the real location of your DB file.

 

[Jables]

Baddos,

I have tried your suggestion, but to no avail. The DBPATH variable is pointing to the correct location. I have been able to retrieve records from the database using this method, but I just can't seem to get the INSERT INTO statement to work.

Dillettante,

I haven't tried your suggestion yet, but I will.

I am starting to think it may be a problem of the connection string or the connection type. I have used this method to add records before, but it was using a DSN and not a DSN-less connection string. I'm wondering if I need to open an ADODB.Recordset and use the .AddNew, .Update method to add records. If this doesn't work, then I suspect that the webhost doesn't have the correct permissions set on the directory that holds the database, although I have checked with them and they assure me the permissions are correct.

 

[JamesLean]

Rather than use the command object, use the excute method of the connection object:

oConn.Execute(strSQL)

--James

 

[gpzcrasher]

I think you'll find 'Password' is a reserved name in access databases. Try changing the field name to something else.

Hope this helps.

--------------------------------
If it ain't broke, don't fix it!

 

[JamesLean]

Or add delimiters to the fieldnames:

strSQL = "INSERT INTO tblUser ([Username],[Password]) VALUES ('clay','pass')"

--James

 

[GaryC123]

That it is, weirdly if you did it like
rs.addnew
rs("password&quot=pass
...
it works

http://www.intouch-media.co.uk

 

[Jables]

Using the same method to connect to the database as in my initial post, this is what I ended up using:

'Open a recordset for inserting new client record
Set RS = Server.CreateObject("ADODB.Recordset")
RS.ActiveConnection = oConn
RS.Source = "SELECT * FROM tblUser"
RS.CursorType=3
RS.CursorLocation=2
RS.LockType=3
RS.Open()

'Update the database with form values
RS.AddNew FieldList, FieldValues
RS.Update
RS.Close

FieldList

and

FieldValues

are each arrays containing the contents they describe. Oddly, I didn't have to change the name of the field,

Password

, and I actually had to remove the delimiters from the field names in order for this to execute correctly. Thanks everyone for the helpful suggestions.