Syntax error in INSERT INTO - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Syntax error in INSERT INTO

Author

Topic

jarv
Posting Yak Master

131 Posts

Posted - 2007-07-21 : 09:01:06

[code]sSQL = "INSERT INTO Date (rsMessage,eventDate,eventTime,rsPubber,PubID)"
sSQL = sSQL & " VALUES ('" & request.form("rsMessage") & "'," & request.form("eventDate") & "," & request.form("eventTime") & "," & session("UserID") & "," & request.form("PubID") & " ) "[/code]

sSQL brings back:

INSERT INTO Date (rsMessage,eventDate,eventTime,rsPubber,PubID) VALUES ('to drink',21/07/2007,07:15,60,376 )

why is there a syntax error?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-21 : 09:17:53

You need single quotes around the dates too.

Peter Larsson
Helsingborg, Sweden

jarv
Posting Yak Master

131 Posts

Posted - 2007-07-21 : 09:24:41

i have been told using Access databases I need to wrap # around dates, i tried that, it didn't work, i'll try '

jarv
Posting Yak Master

131 Posts

Posted - 2007-07-21 : 09:30:18

hmm didn't work?!

if I don't write out the sSQL I get:

POST Data:
rsMessage=to+drink&eventDate=21%2F07%2F2007&eventTime=07%3A15&rsPubber=60&PubID=376&Submit=Add+to+mypubspace&MM_insert=thispub

is this right? it looks it!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-21 : 09:41:31

MS Access queries should be posted in proper forum.

sSQL = "INSERT INTO Date (rsMessage,eventDate,eventTime,rsPubber,PubID)"
sSQL = sSQL & " VALUES ('" & request.form("rsMessage") & "',#" & request.form("eventDate") & "#,#" & request.form("eventTime") & "#," & session("UserID") & "," & request.form("PubID") & " ) "

Peter Larsson
Helsingborg, Sweden

jarv
Posting Yak Master

131 Posts

Posted - 2007-07-21 : 09:58:23

i tried that, it didn't work?!

sSQL brings back:

INSERT INTO Date (rsMessage,eventDate,eventTime,rsPubber,PubID) VALUES ('to drink with friends',#21/07/2007#,#07:15#,60,384 )

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-21 : 10:02:11

What is the error message you receive?

Peter Larsson
Helsingborg, Sweden

jarv
Posting Yak Master

131 Posts

Posted - 2007-07-21 : 10:04:13

Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.
/pubspace/add_date.asp, line 43

line 43 executes the sSQL statement!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-21 : 10:09:36

If using MS Access, strings are to be quotes with ", not '.

sSQL = "INSERT INTO Date (rsMessage,eventDate,eventTime,rsPubber,PubID)"
sSQL = sSQL & " VALUES (" & char(34) & request.form("rsMessage") & char(34) & ",#" & request.form("eventDate") & "#,#" & request.form("eventTime") & "#," & session("UserID") & "," & request.form("PubID") & " ) "

Peter Larsson
Helsingborg, Sweden

jarv
Posting Yak Master

131 Posts

Posted - 2007-07-21 : 10:13:50

i have never used char before?!
I used it and now i get:

Type mismatch: 'char'
/pubspace/add_date.asp, line 41

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-21 : 10:14:49

Maybe it's called CHR (not CHAR) in Access?

Peter Larsson
Helsingborg, Sweden

jarv
Posting Yak Master

131 Posts

Posted - 2007-07-21 : 10:24:36

i changed it in DreamWeaver, it went from black to purple. still doesn't work though?! still syntax error?!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-21 : 10:26:55

And these are the actual and real column names in table Date?
rsMessage, eventDate, eventTime, rsPubber and PubID

Peter Larsson
Helsingborg, Sweden

jarv
Posting Yak Master

131 Posts

Posted - 2007-07-21 : 10:30:09

Yes!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-21 : 10:33:15

Your SQL string should look like this

INSERT INTO Date (rsMessage,eventDate,eventTime,rsPubber,PubID) VALUES ("to drink with friends", #21/07/2007#, #07:15#, 60, 384)

Please note that no single quotes are used, only double quotes (shift 2, ASCII 34).

Peter Larsson
Helsingborg, Sweden

jarv
Posting Yak Master

131 Posts

Posted - 2007-07-21 : 10:53:47

this is what i get now:

INSERT INTO Date (rsMessage,eventDate,eventTime,rsPubber,PubID) VALUES ("to drink with friends",#21/07/2007#,#07:15#,60,373 )

jarv
Posting Yak Master

131 Posts

Posted - 2007-07-21 : 11:56:40

i have changed my code to this:

Dim rsMessage   :  rsMessage = request.Form("rsMessage")
Dim eventDate   :  eventDate = request.Form("eventDate")
Dim eventTime   :  eventTime = request.form("eventTime")
Dim rsPubber   :  rsPubber = session("UserID")
Dim PubID	  :  PubID = request.Form("PubID")

sSQL = "INSERT INTO Date (rsMessage,eventDate,eventTime,rsPubber,PubID)"
sSQL = sSQL & " VALUES ('" & rsMessage & "'," & eventDate & "," & eventTime & "," & rsPubber & ", " & pubID & ")"

still doesn't work!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-21 : 14:42:55

Now you have single quotes again!

Peter Larsson
Helsingborg, Sweden

jarv
Posting Yak Master

131 Posts

Posted - 2007-07-21 : 14:56:10

I have sorted it out in another forum thanks! someone told me i cannot use the table name Date (reserved word) it has to be in []

jarv
Posting Yak Master

131 Posts

Posted - 2007-07-21 : 14:56:36