Syntax error in insertinto statement

S

Sanju

Hai,to every one

here is my question

I have designed the coding to collect the feedback report from the user,
which includes Name of the user, staffno and comments.But when I click on
submit button I am getting the following error

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.
/feedbackform/formreport.asp, line 32


<html>
<% @LANGUAGE="VBScript" %>
<body>
<%
' Declaring variables
Dim username, userstaffno, usercomments, data_source,
con, sql_insert

' A Function to check if some field entered by user is
empty
Function ChkString(string)
If string = "" Then string = " "
ChkString = Replace(string, "'", "''")
End Function

' Receiving values from Form
username = ChkString(Request.Form("username"))
userstaffno = ChkString(Request.Form("userstaffno"))
usercomments = ChkString(Request.Form("usercomments"))


data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=" &_
Server.MapPath("formreport.mdb")

sql_insert = "insert into formreport
(username,userstaffno,usercomments)"
sql_insert = sql_insert & " values (' " &_
Request.Form("username") & " ' , ' " & _
Request.Form("userstaffno") & " ' , ' " &_
Request.Form("usercomments") & " ' , " & " )"

' Creating Connection Object and opening the database
Set con = Server.CreateObject("ADODB.Connection")
con.Open data_source
con.Execute sql_insert

' Done. Close the connection
con.Close
Set con = Nothing
Set sql_insert = Nothing
Response.Write "All records were successfully entered
into the database."
%>
</body>
</html>
 
W

Wolfgang Kais

Hello "Sanju".

Sanju said:
Hai,to every one

here is my question

I have designed the coding to collect the feedback report from the
user, which includes Name of the user, staffno and comments. But
when I click on submit button I am getting the following error

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.
/feedbackform/formreport.asp, line 32 [...]

sql_insert = _
"insert into formreport (username,userstaffno,usercomments)"
sql_insert = sql_insert & " values (' " &_
Request.Form("username") & " ' , ' " & _
Request.Form("userstaffno") & " ' , ' " &_
Request.Form("usercomments") & " ' , " & " )"
Click to expand...
[...]

Are you sure to add these blanks? There are too many commas.
How about this:

sql_insert = _
"insert into formreport (username,userstaffno,usercomments)"
sql_insert = sql_insert & " values ('" & _
Request.Form("username") & "','" & _
Request.Form("userstaffno") & "','" & _
Request.Form("usercomments") & "')"

Just a thought: if any of these fields contained a single ' in the
text (usercomments: this is Angie's brother), you will receive an other
syntax error. To avoid this, try
sql_insert = _
"insert into formreport (username,userstaffno,usercomments)"
sql_insert = sql_insert & " values ('" & _
Replace(Request.Form("username"),"'","''") & "','" & _
Replace(Request.Form("userstaffno"),"'","''") & "','" & _
Replace(Request.Form("usercomments"),"'","''") & "')"
 
S

Sanju

Wolfgang Kais,
Thanks for your suggestion.When I try to run the program and I am getting
the following error message
Error Type:
Microsoft JET Database Engine (0x80004005)
Operation must use an updateable query.
/feedbackform/formreport.asp, line 36





<html>
<% @LANGUAGE="VBScript" %>
<body>
<%
' Declaring variables
Dim username, userstaffno, usercomments, data_source,
con, sql_insert

' A Function to check if some field entered by user is
empty
Function ChkString(string)
If string = "" Then string = " "
ChkString = Replace(string, "'", "''")
End Function

' Receiving values from Form
username = ChkString(Request.Form("usernme"))
userstaffno = ChkString(Request.Form("userstffno"))
usercomments = ChkString(Request.Form("usercommnts"))


data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=" &_
Server.MapPath("formreport.mdb")

sql_insert = _
"insert into formreport (username,userstaffno,usercomments)"
sql_insert = sql_insert & " values ('" & _
Replace(Request.Form("username"),"'","''") & "','" & _
Replace(Request.Form("userstaffno"),"'","''") & "','" & _
Replace(Request.Form("usercomments"),"'","''") & "')"




' Creating Connection Object and opening the database
Set con = Server.CreateObject("ADODB.Connection")
con.Open data_source
con.Execute sql_insert

' Done. Close the connection
con.Close
Set con = Nothing
Set sql_insert = Nothing
Response.Write "All records were successfully entered
into the database."
%>
</body>
</html>
please help me,waitingfor your reply




Wolfgang Kais said:
Hello "Sanju".

Sanju said:
Hai,to every one

here is my question

I have designed the coding to collect the feedback report from the
user, which includes Name of the user, staffno and comments. But
when I click on submit button I am getting the following error

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.
/feedbackform/formreport.asp, line 32 [...]

sql_insert = _
"insert into formreport (username,userstaffno,usercomments)"
sql_insert = sql_insert & " values (' " &_
Request.Form("username") & " ' , ' " & _
Request.Form("userstaffno") & " ' , ' " &_
Request.Form("usercomments") & " ' , " & " )"
Click to expand...
[...]

Are you sure to add these blanks? There are too many commas.
How about this:

sql_insert = _
"insert into formreport (username,userstaffno,usercomments)"
sql_insert = sql_insert & " values ('" & _
Request.Form("username") & "','" & _
Request.Form("userstaffno") & "','" & _
Request.Form("usercomments") & "')"

Just a thought: if any of these fields contained a single ' in the
text (usercomments: this is Angie's brother), you will receive an other
syntax error. To avoid this, try
sql_insert = _
"insert into formreport (username,userstaffno,usercomments)"
sql_insert = sql_insert & " values ('" & _
Replace(Request.Form("username"),"'","''") & "','" & _
Replace(Request.Form("userstaffno"),"'","''") & "','" & _
Replace(Request.Form("usercomments"),"'","''") & "')"
Click to expand...
 
W

Wolfgang Kais

Hallo Sanju.

Sanju said:
Thanks for your suggestion.When I try to run the program and I am
getting the following error message Error Type:
Microsoft JET Database Engine (0x80004005)
Operation must use an updateable query.
/feedbackform/formreport.asp, line 36
Click to expand...

As we can see: formreport is the name of your page and the name of
the database. But is it the name of a table or query?
If it was a query, is it possible to insert a new record manually,
fillig the fields username, staffno and usercomments?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query error 1
Syntax error in Insert Into statement 1
feedback database 1
simple database error 1
Feedback database 1
Microsoft Jet Database Engine (0*80040E014) ERROR 2
Error with SQL query included in .asp page. 1
Please help : System.Data.OleDb.OleDbException: Syntax error in IN 2

Top