asp_databases thread: Syntax Error in INSERT INTO statement
Message #1 by "serhat" <bahceli@h...> on Sun, 20 May 2001 07:45:20
|
|
Hi everyone,
I have a syntax error in my INSERT INTO statement. Can someone help me?
Here is my code:
sSQL="INSERT INTO custinfo1(Name, LastName, Middleinitial, Title,
Organization, City, State, Postalcode, Country, Workphone, Homephone,
Email, Age, Height, Weight, Haircolor, Eyecolor, Hobbies, Event)"
sSQL=sSQL + "VALUES("
sSQL=sSQL + "'" + sName + "'"
sSQL=sSQL + ",'" + sLastName + "'"
sSQL=sSQL + ",'" + sMiddleinitial + "'"
sSQL=sSQL + ",'" + sTitle + "'"
sSQL=sSQL + ",'" + sOrganization + "'"
sSQL=sSQL + ",'" + sCity + "'"
sSQL=sSQL + ",'" + sState + "'"
sSQL=sSQL + "," + Postalcode
sSQL=sSQL + ",'" + sCountry + "'"
sSQL=sSQL + "," + Workphone
sSQL=sSQL + "," + Homephone
sSQL=sSQL + ",'" + sEmail + "'"
sSQL=sSQL + "," + Age
sSQL=sSQL + "," + Height
sSQL=sSQL + "," + Weight
sSQL=sSQL + ",'" + sHaircolor + "'"
sSQL=sSQL + ",'" + sEyecolor + "'"
sSQL=sSQL + ",'" + sHobbies + "'"
sSQL=sSQL + ",'" + sEvent + "'"
sSQL=sSQL + ")"
Response.Write "<br><b>SQL Command: </b>" + sSQL
Set rs = Server.CreateObject("ADODB.Recordset")
If Err.description <> "" Then
Response.Write "<br>CreateObject Recordset: " + Err.description
End If
Const adOpenDynamic = 2
Const adLockPessimistic = 2
rs.Open sSQL, cn, adOpenDynamic, adLockPessimistic
If Err.description <> "" Then Response.Write "<br>rs.Open: " +
Err.description
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub
Message #2 by "Ken Schaefer" <ken@a...> on Mon, 21 May 2001 12:16:47 +1000
|
|
http://www.adopenstatic.com/faq/80040e14.asp
Also, don't use + as a string concantenator in VBscript. Use & instead.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: "serhat" <bahceli@h...>
To: "ASP Databases" <asp_databases@p...>
Sent: Sunday, May 20, 2001 7:45 AM
Subject: [asp_databases] Syntax Error in INSERT INTO statement
: Hi everyone,
: I have a syntax error in my INSERT INTO statement. Can someone help me?
: Here is my code:
: sSQL="INSERT INTO custinfo1(Name, LastName, Middleinitial, Title,
: Organization, City, State, Postalcode, Country, Workphone, Homephone,
: Email, Age, Height, Weight, Haircolor, Eyecolor, Hobbies, Event)"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #3 by "serhat" <bahceli@h...> on Sun, 20 May 2001 23:04:10
|
|
I finally found what the problem was. i defined a number as a string.
Hi everyone,
> I have a syntax error in my INSERT INTO statement. Can someone help me?
> Here is my code:
> sSQL="INSERT INTO custinfo1(Name, LastName, Middleinitial, Title,
> Organization, City, State, Postalcode, Country, Workphone, Homephone,
> Email, Age, Height, Weight, Haircolor, Eyecolor, Hobbies, Event)"
> sSQL=sSQL + "VALUES("
> sSQL=sSQL + "'" + sName + "'"
> sSQL=sSQL + ",'" + sLastName + "'"
> sSQL=sSQL + ",'" + sMiddleinitial + "'"
> sSQL=sSQL + ",'" + sTitle + "'"
> sSQL=sSQL + ",'" + sOrganization + "'"
> sSQL=sSQL + ",'" + sCity + "'"
> sSQL=sSQL + ",'" + sState + "'"
> sSQL=sSQL + "," + Postalcode
> sSQL=sSQL + ",'" + sCountry + "'"
> sSQL=sSQL + "," + Workphone
> sSQL=sSQL + "," + Homephone
> sSQL=sSQL + ",'" + sEmail + "'"
> sSQL=sSQL + "," + Age
> sSQL=sSQL + "," + Height
> sSQL=sSQL + "," + Weight
> sSQL=sSQL + ",'" + sHaircolor + "'"
> sSQL=sSQL + ",'" + sEyecolor + "'"
> sSQL=sSQL + ",'" + sHobbies + "'"
> sSQL=sSQL + ",'" + sEvent + "'"
> sSQL=sSQL + ")"
> Response.Write "<br><b>SQL Command: </b>" + sSQL
> Set rs = Server.CreateObject("ADODB.Recordset")
> If Err.description <> "" Then
> Response.Write "<br>CreateObject Recordset: " + Err.description
> End If
> Const adOpenDynamic = 2
> Const adLockPessimistic = 2
> rs.Open sSQL, cn, adOpenDynamic, adLockPessimistic
> If Err.description <> "" Then Response.Write "<br>rs.Open: " +
> Err.description
> rs.Close
> cn.Close
> Set rs = Nothing
> Set cn = Nothing
> End Sub
Message #4 by "Dallas Martin" <dmartin@z...> on Sun, 20 May 2001 09:31:00 -0400
|
|
First, replace all the "+" with "&".
Next, you don't need a recordset for an INSERT,
simply:
Connection.Execute(sSQL).
INSERTS don't return a recordset
Additionally, during your development, response.write the
SQL string to the screen, followed by a response.end.
Scrape the string from the string and open the Query Designer
and try to execute the SQL statement directly in the QD.
Dallas
----- Original Message -----
From: "serhat" <bahceli@h...>
To: "ASP Databases" <asp_databases@p...>
Sent: Sunday, May 20, 2001 7:45 AM
Subject: [asp_databases] Syntax Error in INSERT INTO statement
> Hi everyone,
> I have a syntax error in my INSERT INTO statement. Can someone help me?
> Here is my code:
> sSQL="INSERT INTO custinfo1(Name, LastName, Middleinitial, Title,
> Organization, City, State, Postalcode, Country, Workphone, Homephone,
> Email, Age, Height, Weight, Haircolor, Eyecolor, Hobbies, Event)"
> sSQL=sSQL + "VALUES("
> sSQL=sSQL + "'" + sName + "'"
> sSQL=sSQL + ",'" + sLastName + "'"
> sSQL=sSQL + ",'" + sMiddleinitial + "'"
> sSQL=sSQL + ",'" + sTitle + "'"
> sSQL=sSQL + ",'" + sOrganization + "'"
> sSQL=sSQL + ",'" + sCity + "'"
> sSQL=sSQL + ",'" + sState + "'"
> sSQL=sSQL + "," + Postalcode
> sSQL=sSQL + ",'" + sCountry + "'"
> sSQL=sSQL + "," + Workphone
> sSQL=sSQL + "," + Homephone
> sSQL=sSQL + ",'" + sEmail + "'"
> sSQL=sSQL + "," + Age
> sSQL=sSQL + "," + Height
> sSQL=sSQL + "," + Weight
> sSQL=sSQL + ",'" + sHaircolor + "'"
> sSQL=sSQL + ",'" + sEyecolor + "'"
> sSQL=sSQL + ",'" + sHobbies + "'"
> sSQL=sSQL + ",'" + sEvent + "'"
> sSQL=sSQL + ")"
> Response.Write "<br><b>SQL Command: </b>" + sSQL
> Set rs = Server.CreateObject("ADODB.Recordset")
> If Err.description <> "" Then
> Response.Write "<br>CreateObject Recordset: " + Err.description
> End If
> Const adOpenDynamic = 2
> Const adLockPessimistic = 2
> rs.Open sSQL, cn, adOpenDynamic, adLockPessimistic
> If Err.description <> "" Then Response.Write "<br>rs.Open: " +
> Err.description
> rs.Close
> cn.Close
> Set rs = Nothing
> Set cn = Nothing
> End Sub
>
Message #5 by "Russ Harding" <rharding@s...> on Sun, 20 May 2001 08:30:25 -0600
|
|
Try sSQL="INSERT INTO custinfo1 Values (Name, LastName, Middleinitial,
Title, ........... )
That should work for you. Looks like your just missing the values
statement.
Russell Harding
InterACT Web Witness
TransACT
MediaDB
Office: xxx-xxx-xxxx
24 Hour Support: xxx-xxx-xxxx x5
Mobile: xxx-xxx-xxxx
Fax: xxx-xxx-xxxx
IWWAdmin@s...
rharding@s...
--- How do I set a laser printer to stun? ---
If you reply to this E-mail please Cc IWWAdmin@s...
If you do not and I go off duty your E-mail might be missed.
----- Original Message -----
From: "serhat" <bahceli@h...>
To: "ASP Databases" <asp_databases@p...>
Sent: Sunday, May 20, 2001 07:45
Subject: [asp_databases] Syntax Error in INSERT INTO statement
> Hi everyone,
> I have a syntax error in my INSERT INTO statement. Can someone help me?
> Here is my code:
> sSQL="INSERT INTO custinfo1(Name, LastName, Middleinitial, Title,
> Organization, City, State, Postalcode, Country, Workphone, Homephone,
> Email, Age, Height, Weight, Haircolor, Eyecolor, Hobbies, Event)"
> sSQL=sSQL + "VALUES("
> sSQL=sSQL + "'" + sName + "'"
> sSQL=sSQL + ",'" + sLastName + "'"
> sSQL=sSQL + ",'" + sMiddleinitial + "'"
> sSQL=sSQL + ",'" + sTitle + "'"
> sSQL=sSQL + ",'" + sOrganization + "'"
> sSQL=sSQL + ",'" + sCity + "'"
> sSQL=sSQL + ",'" + sState + "'"
> sSQL=sSQL + "," + Postalcode
> sSQL=sSQL + ",'" + sCountry + "'"
> sSQL=sSQL + "," + Workphone
> sSQL=sSQL + "," + Homephone
> sSQL=sSQL + ",'" + sEmail + "'"
> sSQL=sSQL + "," + Age
> sSQL=sSQL + "," + Height
> sSQL=sSQL + "," + Weight
> sSQL=sSQL + ",'" + sHaircolor + "'"
> sSQL=sSQL + ",'" + sEyecolor + "'"
> sSQL=sSQL + ",'" + sHobbies + "'"
> sSQL=sSQL + ",'" + sEvent + "'"
> sSQL=sSQL + ")"
> Response.Write "<br><b>SQL Command: </b>" + sSQL
> Set rs = Server.CreateObject("ADODB.Recordset")
> If Err.description <> "" Then
> Response.Write "<br>CreateObject Recordset: " + Err.description
> End If
> Const adOpenDynamic = 2
> Const adLockPessimistic = 2
> rs.Open sSQL, cn, adOpenDynamic, adLockPessimistic
> If Err.description <> "" Then Response.Write "<br>rs.Open: " +
> Err.description
> rs.Close
> cn.Close
> Set rs = Nothing
> Set cn = Nothing
> End Sub
>
Message #6 by "Daniel O'Dorisio" <dodorisio@h...> on Sun, 20 May 2001 21:59:22 -0400
|
|
i think one of your problems is the +.. in vb that should be a &
also i dont see where you open the connection object.. maybe i am just so
bugg eyed that i missed it.. but also since you are using the insert
statement you dont need a rs.. you can just do connection.execute.
daniel
Daniel O'Dorisio
dodorisio@h...
xxx-xxx-xxxx
-----Original Message-----
From: serhat [mailto:bahceli@h...]
Sent: Sunday, May 20, 2001 7:45 AM
To: ASP Databases
Subject: [asp_databases] Syntax Error in INSERT INTO statement
Hi everyone,
I have a syntax error in my INSERT INTO statement. Can someone help me?
Here is my code:
sSQL="INSERT INTO custinfo1(Name, LastName, Middleinitial, Title,
Organization, City, State, Postalcode, Country, Workphone, Homephone,
Email, Age, Height, Weight, Haircolor, Eyecolor, Hobbies, Event)"
sSQL=sSQL + "VALUES("
sSQL=sSQL + "'" + sName + "'"
sSQL=sSQL + ",'" + sLastName + "'"
sSQL=sSQL + ",'" + sMiddleinitial + "'"
sSQL=sSQL + ",'" + sTitle + "'"
sSQL=sSQL + ",'" + sOrganization + "'"
sSQL=sSQL + ",'" + sCity + "'"
sSQL=sSQL + ",'" + sState + "'"
sSQL=sSQL + "," + Postalcode
sSQL=sSQL + ",'" + sCountry + "'"
sSQL=sSQL + "," + Workphone
sSQL=sSQL + "," + Homephone
sSQL=sSQL + ",'" + sEmail + "'"
sSQL=sSQL + "," + Age
sSQL=sSQL + "," + Height
sSQL=sSQL + "," + Weight
sSQL=sSQL + ",'" + sHaircolor + "'"
sSQL=sSQL + ",'" + sEyecolor + "'"
sSQL=sSQL + ",'" + sHobbies + "'"
sSQL=sSQL + ",'" + sEvent + "'"
sSQL=sSQL + ")"
Response.Write "<br><b>SQL Command: </b>" + sSQL
Set rs = Server.CreateObject("ADODB.Recordset")
If Err.description <> "" Then
Response.Write "<br>CreateObject Recordset: " + Err.description
End If
Const adOpenDynamic = 2
Const adLockPessimistic = 2
rs.Open sSQL, cn, adOpenDynamic, adLockPessimistic
If Err.description <> "" Then Response.Write "<br>rs.Open: " +
Err.description
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub
Message #7 by "Blake, Shane" <Shane.Blake@p...> on Mon, 21 May 2001 16:08:46 -0400
|
|
you don't need a recordset to do an insert
replace this code :
Set rs = Server.CreateObject("ADODB.Recordset")
If Err.description <> "" Then
Response.Write "<br>CreateObject Recordset: " + Err.description
End If
Const adOpenDynamic = 2
Const adLockPessimistic = 2
rs.Open sSQL, cn, adOpenDynamic, adLockPessimistic
If Err.description <> "" Then Response.Write "<br>rs.Open: " +
Err.description
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
with this :
cn.Execute sSQL
If Err.description <> "" Then Response.Write "<br>cn.Execute [" & sSQL & "]
: " & Err.description
cn.Close
Set cn = Nothing
shane
|
View Cart
