PDA

View Full Version : Syntax error in INSERT INTO statement.

BarrMan
04-01-2005, 01:40 PM
<%@ Language=VBScript %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>
<%
'On Error Resume Next

Function strFormat(str)
If IsEmpty(str) or IsNull(str) Then Exit Function
Str = Replace(str,"<","&lt;")
Str = Replace(str,">","&gt;")
Str = Replace(str,"'","'")
Str = Replace(str,"""","&quot;")
Str = Replace(str,chr(13),"<br>")
strFormat = str
End Function

Dim Conn
Dim Rs
Dim SQL
Dim StrConn

StrConn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.mappath("db/db.mdb") & ";"

Set Conn = Server.CreateObject("ADODB.connection")
Conn.Open StrConn

SQL = "INSERT INTO msgs (from, to, message, date) "
SQL = SQL & " VALUES('" & Session("user_name")
SQL = SQL & "','" & strFormat(Request.Form("to"))
SQL = SQL & "','" & strFormat(Request.Form("message"))
SQL = SQL & "'," & Date() & ")"

Conn.Execute SQL

'Set Rs = Server.CreateObject("ADODB.recordset")
'Rs.Open SQL, Conn

'Rs.Close
'Set Rs = Nothing

Conn.Close
Set Conn = Nothing

'If Err <> 0 Then
'Response.Write Err.Description
'Else
'Response.Write "ההודעה נשלחה בהצלחה!"
'End If
%>
</BODY>
</HTML>
Roelf
04-01-2005, 02:58 PM
before you execute the sql, print it, analyze it or post the printed sql here and we will have a look at it.
fractalvibes
04-01-2005, 04:15 PM
Yes, do this:


SQL = "INSERT INTO msgs (from, to, message, date) "
SQL = SQL & " VALUES('" & Session("user_name")
SQL = SQL & "','" & strFormat(Request.Form("to"))
SQL = SQL & "','" & strFormat(Request.Form("message"))
SQL = SQL & "'," & Date() & ")"

response.write SQL
response.end


Chances are you are either missing some values, or you have failed
to quote date to be inserted as CHAR() data.

fv
BarrMan
04-01-2005, 06:32 PM
INSERT INTO msgs ([from], [to], [message], [date]) VALUES('BarrMan','to user','
message',01/04/2005)
tboss132
04-04-2005, 09:20 AM
What kind of date format have you given the date field in your access table? Looks like that's where the problem would be.
BarrMan
04-05-2005, 01:10 PM
From = string
To = string
Message = string
Date = date

These are the types.
But now I get this error:
"Operation must use an updateable query."
fractalvibes
04-05-2005, 05:05 PM
If this is an Access database, aren't dates delimited with # signs? Think this is a strange quirk of Access...


fv
tboss132
04-05-2005, 05:34 PM
Try this, if it works, then the problem is with your date formats. You'll be able to narrow down your troubleshooting.
<%@ Language=VBScript %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>
<%
'On Error Resume Next

Function strFormat(str)
If IsEmpty(str) or IsNull(str) Then Exit Function
Str = Replace(str,"<","&lt;")
Str = Replace(str,">","&gt;")
Str = Replace(str,"'","'")
Str = Replace(str,"""","&quot;")
Str = Replace(str,chr(13),"<br>")
strFormat = str
End Function

Dim Conn
Dim Rs
Dim SQL
Dim StrConn

StrConn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.mappath("db/db.mdb") & ";"

Set Conn = Server.CreateObject("ADODB.connection")
Conn.Open StrConn

SQL = "INSERT INTO msgs (from, to, message) " 'Remove date
SQL = SQL & " VALUES('" & Session("user_name")
SQL = SQL & "','" & strFormat(Request.Form("to"))
SQL = SQL & "','" & strFormat(Request.Form("message"))
' last line removed

Conn.Execute SQL

'Set Rs = Server.CreateObject("ADODB.recordset")
'Rs.Open SQL, Conn

'Rs.Close
'Set Rs = Nothing

Conn.Close
Set Conn = Nothing

'If Err <> 0 Then
'Response.Write Err.Description
'Else
'Response.Write "ההודעה נשלחה בהצלחה!"
'End If
%>
</BODY>
</HTML>
glenngv
04-06-2005, 06:06 AM
If this is an Access database, aren't dates delimited with # signs? Think this is a strange quirk of Access...


fv
That's correct. So the sql should be:

SQL = "INSERT INTO msgs (from, to, message, date) "
SQL = SQL & " VALUES('" & Session("user_name")
SQL = SQL & "','" & strFormat(Request.Form("to"))
SQL = SQL & "','" & strFormat(Request.Form("message"))
SQL = SQL & "',#" & Date() & "#)"