Syntax Error in INSERT INTO statement

[Catrina]

I am just learning VB. I have a very simple Database that I am trying to access. I'm getting an error in the INSERT INTO statement. I am using sample code to put together what I need to do. The sample this came from is identical(I think) except for the tables and field names and it works. Here is my code:

''Connecting to the database
''
Dim stSQL as string
Dim Pass as string
Dim Comm as string
Dim stClientNumber as string

set objAccessConnection = new ADODB.Connection
objAccessConnection.CursorLocation = adUseClient

objAccessConnection.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
" Data Source=c:\host\custlist.mdb"

sSQL = "SELECT ClientNumber, Password, CommPass FROM Cust"

set rsAccess = new ADODB.Recordset
rsAccess.Open sSQL, objAccessConnection, adOpenKeyset, adLockOptimistic

''Insert statement
''

With frmAdd
Pass = .txtAddPassword
Comm = .txtAddComm
stClientNumber = .txtAddClient

stSQL = "INSERT INTO Cust(ClientNumber,Password,CommPass) VALUES "& _
"(stClientNumber,Pass,Comm)"
objAccessConnection.Execute stSQL



Any ideas as to why it won't work? Thanks

Catrina

[law]

Try using a space between Cust and the first (

greets
Law

[Johnny101]

You are including the variable names as the data instead of what the variables are holding. this is a very common situation. The trick is to know when to stop direct quotation when building your SQL statement. here's what you have:


stSQL = "INSERT INTO Cust(ClientNumber,Password,CommPass) VALUES (stClientNumber,Pass,Comm)"




this is what it should be:

stSQL = "INSERT INTO Cust(ClientNumber,Password,CommPass) VALUES ('" & stClientNumber & "'"
stSQL = stSQL & ",'" & Pass & "', " & Comm & ")"




i'm assuming that the CommPass is an integer value. the other trick is to know when to put single tics ( ' ) around the data so the database knows what data type they are. since yuo are using access, always surround string data with single tics (apostrophes), numerics dont get anything and dates get the pound sign (#).

as for the above statement here's how it works:

you are building a string - so start with the INSERT ... then when you need to pass the VALUE of a variable, you want to stop quotation and append to the string -hence all the &'s. think of it as building a string and always appending, that should help. when you are done building the string, check it's value in the debug window to see if you built it correctly.

hope this helps,

john

John Pirkey
MCSD
http://www.ShallowWaterSystems.com
http://www.stlvbug.org

[Clothahump]

Your current line reads:
stSQL = "INSERT INTO Cust(ClientNumber,Password,CommPass) VALUES "& _
"(stClientNumber,Pass,Comm)"

You need to put the values into the statement, not the variable names. Try this:
stSQL = "INSERT INTO Cust(ClientNumber,Password,CommPass) VALUES "& _
"('" & stClientNumber & "','" & Pass & "','" & Comm & "')"

Hope this helps!

[Catrina]

I cut and pasted your code, and it still will not work. I have checked and double checked my table name and fields, they are correct. I even changed all the field sizes to 6 and entered only 6 characters(data type is Text). Any other ideas?

It must be a problem with my database, the sample works fine with the sample database, but not mine.

Any thoughts would be appreciated. I'm beginning to think I will never grasp the concepts of database programming.

Catrina

[Catrina]

I forgot to mention that the sample was made for and uses an Access 97 database. My database was made using Access 2000. Is it possible that I need to reference something diffent than the sample does?

When working with ADO and an Access 2000 database, just what should be referenced? I have so many choices:
Microsoft Access 9.0 Object Library
Microsoft ADO Ext 2.1 for DDL & Security
Microsoft ActiveX Data Objects Recordset 2.1 Library
Microsoft ActiveX Data Objects 2.0 Library
Microsoft ActiveX Data Objects 2.1 Library
Microsoft ActiveX Data Objects 2.5 Library

etc, etc

The original sample used PROVIDER=Microsoft.Jet.OLEDB.3.51 which I changed to 4.0 because it didn't recongize my 2000 DB, and the references were: Microsoft ActiveX Data Objects Recordset 2.1 Library,
Microsoft ActiveX Data Objects 2.1 Library

I been trying to used different references(the lastest version), but probably digging myself into a deeper hole.


Thanks again

Catrina

[Catrina]

After trying the suggestions, I went back to the original program and started changing things one by one it the program no longer worked.

Apparently I can't name a field "Password". That is where my problem was. I used "Pass" instead, and it is working fine.

I was missing the apostrophes, but the original sample used a function to place them, and I didn't remove that this time.

Thanks you everyone's help, I'm sure I'll be back really soon as I haven't found a good, easy to understand book on ADO and database programming yet.

Catrina

[Johnny101]

Although this seems to have fixed your problem, it brings up another. I know for a fact that "Password" is a legal and valid column name. There must be something else that was causing this to fail. If password wasn't a valid column name then Access would've told you about it. not only that, if password wasn't a valid column name but it allowed you to create it anyway, how come your select statement works and returns the data you expect to your form? I realize this is waking a sleeping dog, but it just seems incredibly odd that changing the column name fixed it.

Maybe a little too paranoid,

John

John Pirkey
MCSD
http://www.ShallowWaterSystems.com
http://www.stlvbug.org