SQL query gone wrong :(

heres my query:

myconn.Execute ("INSERT INTO Member(Title, Forename, Surname)
VALUES(strTitle, strForename, strSurname,)")

i cant see whats wrong with it, but it shows up red in vb and does not execute properly. Any ideas?

... Please dont send me an ADO tut ;).

thanks,

Sam

First thing that comes to mind is the additional comma...


myconn.Execute ("INSERT INTO Member(Title, Forename, Surname)
VALUES(strTitle, strForename, strSurname,)")

Well, for one thing, there's a comma after strSurname...

HTH

strTitle etc seem to be parameters, so:



"INSERT INTO Member(Title, Forename, Surname) VALUES( '" & strTitle & "', '" & strForename & "' , '" & strSurname & "')"

Ok ive tried that last line of code:


"INSERT INTO Member(Title, Forename, Surname) VALUES( '" & strTitle & "', '" & strForename & "' , '" & strSurname & "')"


but now im getting syntax error?

Thanks,

Sam

Try:



Dim strSQL as string

strSQL = "INSERT INTO Member(Title, Forename, Surname) " & _
"VALUES( '" & strTitle & "', '" & strForename & "' , '" & strSurname & "')"

MyConn.Execute strSQL

Granty, I've been staring at our sqls and could not find a difference.
(may be I'm tired and don't see it..)
Anyway another suggestion, Title is a reserved word thus you have to
use


...[Title]...

Yeah Shurik, I just figured he might be using the .execute("blah blah") and therefore have extra brackets....

i have used this code :


Dim myconn As ADODB.Connection
Dim myRecSet As ADODB.Recordset
Dim strTitle, strForename, strSurname, strAddress1, strAddress2, strAddress3, intTelephone, strPostcode

strTitle = cmbTitle.Text
strForename = txtForename.Text
strSurname = txtSurname.Text
strAddress1 = txtAddress1.Text
strAddress2 = txtaddress2.Text
strAddress3 = txtAddress3.Text

Set myconn = New ADODB.Connection
myconn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\db2.mdb;"
myconn.Open

Dim strSQL As String
strSQL = "INSERT INTO Member([Title], Forename, Surname)" & _
"VALUES( '" & strTitle & "', '" & strForename & "' , '" & strSurname & "')"

myconn.Execute strSQL

myconn.Close


This is the code that you supplied. Now i am getting a different error.

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.
Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

Runtime error: '-2147467259(80004005)'

any ideas?

im puzzled.

thanks, Sam.

That's just what it says. You table has unique index(es) which doesn't not allow for the duplication of the data.

cheers guys.

i went back to the database and found that the datatype was number, so i changed it to autonumber.

thanks again,

sam