 | Syntax Error when try to insert record |  |
|
musicalbell
|
|
 Posted: Fri Mar 14, 2008 12:21 pm |
|
 |
 |
|
|
I'm a Dreamweaver user and know nothing about programming or coding. I followed a tutorial and set up an .asp page, where the records in a form will be inserted into my .mdb database.
When I tried to submit the form, I got an error message from my web server (Baby ASP Web Server):
Script error detected at line 127.
Source line: MM_editCmd.Execute
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
|
This is my .asp file:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/connSongofsoul.asp" -->
<%
' *** Edit Operations: declare variables
Dim MM_editAction
Dim MM_abortEdit
Dim MM_editQuery
Dim MM_editCmd
Dim MM_editConnection
Dim MM_editTable
Dim MM_editRedirectUrl
Dim MM_editColumn
Dim MM_recordId
Dim MM_fieldsStr
Dim MM_columnsStr
Dim MM_fields
Dim MM_columns
Dim MM_typeArray
Dim MM_formVal
Dim MM_delim
Dim MM_altVal
Dim MM_emptyVal
Dim MM_i
MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
MM_editAction = MM_editAction & "?" & Request.QueryString
End If
' boolean to abort record edit
MM_abortEdit = false
' query string to execute
MM_editQuery = ""
%>
<%
' *** Insert Record: set variables
If (CStr(Request("MM_insert")) = "admin_life_addblog") Then
MM_editConnection = MM_connSongofsoul_STRING
MM_editTable = "Blogs"
MM_editRedirectUrl = "life.asp"
MM_fieldsStr = "Title|value|Date|value|Time|value|BlogEntry|value"
MM_columnsStr = "Title|',none,''|Date|',none,NULL|Time|',none,NULL|BlogEntry|',none,''"
' create the MM_fields and MM_columns arrays
MM_fields = Split(MM_fieldsStr, "|")
MM_columns = Split(MM_columnsStr, "|")
' set the form values
For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))
Next
' append the query string to the redirect URL
If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
End If
End If
End If
%>
<%
' *** Insert Record: construct a sql insert statement and execute it
Dim MM_tableValues
Dim MM_dbValues
If (CStr(Request("MM_insert")) <> "") Then
' create the sql insert statement
MM_tableValues = ""
MM_dbValues = ""
For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_formVal = MM_fields(MM_i+1)
MM_typeArray = Split(MM_columns(MM_i+1),",")
MM_delim = MM_typeArray(0)
If (MM_delim = "none") Then MM_delim = ""
MM_altVal = MM_typeArray(1)
If (MM_altVal = "none") Then MM_altVal = ""
MM_emptyVal = MM_typeArray(2)
If (MM_emptyVal = "none") Then MM_emptyVal = ""
If (MM_formVal = "") Then
MM_formVal = MM_emptyVal
Else
If (MM_altVal <> "") Then
MM_formVal = MM_altVal
ElseIf (MM_delim = "'") Then ' escape quotes
MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
Else
MM_formVal = MM_delim + MM_formVal + MM_delim
End If
End If
If (MM_i <> LBound(MM_fields)) Then
MM_tableValues = MM_tableValues & ","
MM_dbValues = MM_dbValues & ","
End If
MM_tableValues = MM_tableValues & MM_columns(MM_i)
MM_dbValues = MM_dbValues & MM_formVal
Next
MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ")"
If (Not MM_abortEdit) Then
' execute the insert
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close
If (MM_editRedirectUrl <> "") Then
Response.Redirect(MM_editRedirectUrl)
End If
End If
End If
%>
|
I'm using a Custom Connection String and this is the code:
<%
' FileName="Connection_ado_conn_string.htm"
' Type="ADO"
' DesigntimeType="ADO"
' HTTP="false"
' Catalog=""
' Schema=""
Dim MM_connSongofsoul_STRING
MM_connSongofsoul_STRING = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\Documents and Settings\windows\My Documents\localhost\songofsoul.mdb"
%>
|
Can someone please explain to me and help solving this problem? Thanks a lot! |
|
|
 |
| | |
|
dmalone
HostMySite Tech
|
|
| Posted: Thu Mar 20, 2008 1:48 pm |
|
|
|
|
|
This issue is a little confusing because it looks like it is building the insert based on the variables and fields that are present. The first step is to get a look at the exact SQL syntax that is being built.
I think the easiest way to do this would be to write it out with a response.write so you can see the syntax. In your code directly above this line:
MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ")"
|
add this:
| Response.write "insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ")" |
That should write to your screen the syntax being built for the insert statement. Once you have that, you can compare the syntax to the correct syntax. If you are not familiar with the syntax, w3schools.com is always a good place to start:
http://w3schools.com/sql/sql_insert.asp
Once you have that, you can go backwards through the code to see why it was malformed. That is the fun part 
Let me know how it goes!
Regards,
Dave Malone |
|
|
|
| | |
|
musicalbell
|
|
| Posted: Thu Mar 20, 2008 2:51 pm |
|
|
|
|
|
Thanks a lot Dave.
I don't really know how to deal with codes. After changing the statement that you taught, I got this:
Script error detected at line 116.
Source line: MM_editCmd.Execute
Description: Command text was not set for the command object.
|
What does this mean? Can you help me out with this please? |
|
|
|
dmalone
HostMySite Tech
|
|
| Posted: Thu Mar 20, 2008 3:16 pm |
|
|
|
|
|
Can you repost the code with the changes you made? Thanks!
-Dave
|
|
|
|
| | |
|
musicalbell
|
|
| Posted: Fri Mar 21, 2008 4:11 am |
|
|
|
|
|
Oh no! Sorry I've made a mistake! So I changed the code again:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/connSongofsoul.asp" -->
<%
' *** Edit Operations: declare variables
Dim MM_editAction
Dim MM_abortEdit
Dim MM_editQuery
Dim MM_editCmd
Dim MM_editConnection
Dim MM_editTable
Dim MM_editRedirectUrl
Dim MM_editColumn
Dim MM_recordId
Dim MM_fieldsStr
Dim MM_columnsStr
Dim MM_fields
Dim MM_columns
Dim MM_typeArray
Dim MM_formVal
Dim MM_delim
Dim MM_altVal
Dim MM_emptyVal
Dim MM_i
MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
MM_editAction = MM_editAction & "?" & Request.QueryString
End If
' boolean to abort record edit
MM_abortEdit = false
' query string to execute
MM_editQuery = ""
%>
<%
' *** Insert Record: set variables
If (CStr(Request("MM_insert")) = "admin_life_addblog") Then
MM_editConnection = MM_connSongofsoul_STRING
MM_editTable = "Blogs"
MM_editRedirectUrl = "life.asp"
MM_fieldsStr = "Title|value|Date|value|Time|value|BlogEntry|value"
MM_columnsStr = "Title|',none,''|Date|',none,NULL|Time|',none,NULL|BlogEntry|',none,''"
' create the MM_fields and MM_columns arrays
MM_fields = Split(MM_fieldsStr, "|")
MM_columns = Split(MM_columnsStr, "|")
' set the form values
For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))
Next
' append the query string to the redirect URL
If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
End If
End If
End If
%>
<%
' *** Insert Record: construct a sql insert statement and execute it
Dim MM_tableValues
Dim MM_dbValues
If (CStr(Request("MM_insert")) <> "") Then
' create the sql insert statement
MM_tableValues = ""
MM_dbValues = ""
For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_formVal = MM_fields(MM_i+1)
MM_typeArray = Split(MM_columns(MM_i+1),",")
MM_delim = MM_typeArray(0)
If (MM_delim = "none") Then MM_delim = ""
MM_altVal = MM_typeArray(1)
If (MM_altVal = "none") Then MM_altVal = ""
MM_emptyVal = MM_typeArray(2)
If (MM_emptyVal = "none") Then MM_emptyVal = ""
If (MM_formVal = "") Then
MM_formVal = MM_emptyVal
Else
If (MM_altVal <> "") Then
MM_formVal = MM_altVal
ElseIf (MM_delim = "'") Then ' escape quotes
MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
Else
MM_formVal = MM_delim + MM_formVal + MM_delim
End If
End If
If (MM_i <> LBound(MM_fields)) Then
MM_tableValues = MM_tableValues & ","
MM_dbValues = MM_dbValues & ","
End If
MM_tableValues = MM_tableValues & MM_columns(MM_i)
MM_dbValues = MM_dbValues & MM_formVal
Next
Response.Write "insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ")"
MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ")"
If (Not MM_abortEdit) Then
' execute the insert
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close
If (MM_editRedirectUrl <> "") Then
Response.Redirect(MM_editRedirectUrl)
End If
End If
End If
%>
<html>
<head>
<title>Song of Soul - Life_Admin Add Blog</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
@import url("font.css");
-->
</style>
</head>
<body bgcolor="#333333" text="#CCCCCC" link="#FF00FF" vlink="#FF00FF" alink="#FF0000">
<div align="center"><span class="Font"><font color="#FF0000" face="ChopinScript"><img src="bigtitledeco.jpg" width="150" height="38">
Song of Soul - <a href="life.asp">Life</a><img src="bigtitledeco.jpg" width="150" height="38"></font></span><br>
<font color="#FFFFFF" size="+3" face="ChopinScript"> </font>
<p><font color="#FFFFFF" size="+3" face="ChopinScript">= Exposure of the Inner
Me =</font></p>
<p><font color="#FFFFFF" size="+1">+ Add Blog Entry+</font></p>
<form action="<%=MM_editAction%>" method="POST" name="admin_life_addblog" id="admin_life_addblog">
<table width="70%" border="1">
<tr>
<td><table width="95%" border="0" align="center">
<tr>
<td width="18%">Title:</td>
<td width="82%"><input name="Title" type="text" id="Title"></td>
</tr>
<tr>
<td>Date/Time:</td>
<td><input name="Date" type="text" id="Date"> <input name="Time" type="text" id="Time"></td>
</tr>
<tr>
<td colspan="2">Entry:</td>
</tr>
<tr>
<td colspan="2"><textarea name="BlogEntry" cols="100" rows="10" id="BlogEntry"></textarea></td>
</tr>
</table></td>
</tr>
<tr>
<td><div align="left">
<input name="postButton" type="submit" id="postButton" value="Post">
<input name="blankButton" type="reset" id="blankButton" value="Blank">
</div></td>
</tr>
</table>
<input type="hidden" name="MM_insert" value="admin_life_addblog">
</form>
<p><font color="#FFFFFF"></font></p>
<p><font color="#FFFFFF"></font></p>
<p> </p>
</div>
</body>
</html>
|
And now I got this error again after attempting to insert the record into my database:
Script error detected at line 117.
Source line: MM_editCmd.Execute
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
|
|
|
|
|
| | |
|
dmalone
HostMySite Tech
|
|
| Posted: Fri Mar 21, 2008 1:28 pm |
|
|
|
|
|
Until we can see exactly what the insert statement looks like, we can't make any progress. Take out the change that I recommended earlier and try this:
1) Under this line | MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ")" |
add | response.write MM_editQuery |
2) Then, comment out the part where it executes the insert. You can do that by adding a ' character to the start of each line. It should look something like this when you are done
'If (Not MM_abortEdit) Then
' execute the insert
'Set MM_editCmd = Server.CreateObject("ADODB.Command")
' MM_editCmd.ActiveConnection = MM_editConnection
' MM_editCmd.CommandText = MM_editQuery
'MM_editCmd.Execute
'MM_editCmd.ActiveConnection.Close
'If (MM_editRedirectUrl <> "") Then
' Response.Redirect(MM_editRedirectUrl)
'End If
' End If |
After you are done that, run it again and it should output the syntax it is using. If it does, let me know what it looks like. If not, you will need to go to the person that wrote the script to see if they can help.
Let me know how it goes!
-Dave |
|
|
|
| | |
|
musicalbell
|
|
| Posted: Sat Mar 29, 2008 1:31 pm |
|
|
|
|
|
Well, I've found that the cause of the problem already. I've used "date" as one of my column name in my database. MS Access doesn't allow that because it is a "reserved word".
|
|
|
|
dmalone
HostMySite Tech
|
|
| Posted: Mon Mar 31, 2008 1:14 pm |
|
|
|
|
|
Ahh that would do it. Glad to know you got it sorted out!
-Dave
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
All times are GMT
Page 1 of 1
|
|
|
|
|
