SQL query gone wrong :(

sambase
10-14-2004, 08:55 AM
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

Cerryl
10-14-2004, 09:01 AM
First thing that comes to mind is the additional comma...


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

Syko10-96
10-14-2004, 09:02 AM
Well, for one thing, there's a comma after strSurname...

HTH

Shurik12
10-14-2004, 09:06 AM
strTitle etc seem to be parameters, so:



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

sambase
10-14-2004, 09:17 AM
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

Granty
10-14-2004, 09:33 AM
Try:



Dim strSQL as string

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

MyConn.Execute strSQL

Shurik12
10-14-2004, 09:37 AM
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]...

Granty
10-14-2004, 09:41 AM
Yeah Shurik, I just figured he might be using the .execute("blah blah") and therefore have extra brackets....

sambase
10-14-2004, 10:09 AM
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.

Shurik12
10-14-2004, 10:11 AM
That's just what it says. You table has unique index(es) which doesn't not allow for the duplication of the data.

sambase
10-14-2004, 10:29 AM
cheers guys.

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

thanks again,

sam

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum