By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,701 Members | 1,970 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,701 IT Pros & Developers. It's quick & easy.

syntax error while converting asp to asp.net

P: n/a
Hi guys, I am getting the following error in teh insert statement , I
am converting this asp application to asp.net, here is teh error,

Exception Details: System.Runtime.InteropServices.COMException: Syntax
error in INSERT INTO statement.

Source Error:
Line 118: MM_editCmd.ActiveConnection = MM_editConnection
Line 119: MM_editCmd.CommandText = MM_editQuery
Line 120: MM_editCmd.Execute
Line 121: MM_editCmd.ActiveConnection.Close
Line 122:
Source File: C:\Documents and
Settings\amit\Desktop\WebSite1\add_user.aspx Line: 120

Stack Trace:
[COMException (0x80040e14): Syntax error in INSERT INTO statement.]

Microsoft.VisualBasic.CompilerServices.LateBinding .InternalLateCall(Object
o, Type objType, String name, Object[] args, String[] paramnames,
Boolean[] CopyBack, Boolean IgnoreReturn) +776

Microsoft.VisualBasic.CompilerServices.NewLateBind ing.LateCall(Object
Instance, Type Type, String MemberName, Object[] Arguments, String[]
ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean
IgnoreReturn) +194083
ASP.add_user_aspx.__Render__control1(HtmlTextWrite r __w, Control
parameterContainer) in C:\Documents and
Settings\amit\Desktop\WebSite1\add_user.aspx:120
System.Web.UI.Control.RenderChildrenInternal(HtmlT extWriter writer,
ICollection children) +98
System.Web.UI.Control.RenderChildren(HtmlTextWrite r writer) +20
System.Web.UI.Page.Render(HtmlTextWriter writer) +27
System.Web.UI.Control.RenderControlInternal(HtmlTe xtWriter writer,
ControlAdapter adapter) +53
System.Web.UI.Control.RenderControl(HtmlTextWriter writer,
ControlAdapter adapter) +280
System.Web.UI.Control.RenderControl(HtmlTextWriter writer) +24
System.Web.UI.Page.ProcessRequestMain(Boolean
includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
+8878
Now here is the code for teh above error,
<%
' *** Insert Record: set variables

If (CStr(Request("MM_insert")) = "form1") Then

MM_editConnection = MM_rs_PAVE_CC_1_STRING
MM_editTable = "tblUsers"
MM_editRedirectUrl = "list_users.aspx"
MM_fieldsStr =
"firstname|value|lastname|value|userid|value|passw ord|value|position_name|value|building|value|offic e_number|value|department|value|email|value|user_t ype|value|user_access|value|viewallcase|value|acti ve|value"
MM_columnsStr =
"firstname|',none,''|lastname|',none,''|userid|',n one,''|password|',none,''|position_name|',none,''| building|',none,''|office_number|',none,''|departm ent|none,none,NULL|email|',none,''|user_type|none, none,NULL|user_access|none,none,NULL|viewallcase|' ,none,''|active|',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("values") <> "")
Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And
Request.QueryString("values") <> "") Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" &
Request.QueryString("values")
Else
MM_editRedirectUrl = MM_editRedirectUrl & "&" &
Request.QueryString("values")
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 & ")"

I think teh error is coming from this line
MM_columnsStr =
"firstname|',none,''|lastname|',none,''|userid|',n one,''|password|',none,''|position_name|',none,''| building|',none,''|office_number|',none,''|departm ent|none,none,NULL|email|',none,''|user_type|none, none,NULL|user_access|none,none,NULL|viewallcase|' ,none,''|active|',none,''"

Coz when I replace the columnstr, fieldstr and teh table name to
something else, its working, I am not sure whats wrong. plase I need
help. thankyou.

Nov 19 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
just give us the text for the INSERT statement once...

--
Curt Christianson
site: http://www.darkfalz.com
blog: http://blog.darkfalz.com

"am*******@gmail.com" wrote:
Hi guys, I am getting the following error in teh insert statement , I
am converting this asp application to asp.net, here is teh error,

Exception Details: System.Runtime.InteropServices.COMException: Syntax
error in INSERT INTO statement.

Source Error:
Line 118: MM_editCmd.ActiveConnection = MM_editConnection
Line 119: MM_editCmd.CommandText = MM_editQuery
Line 120: MM_editCmd.Execute
Line 121: MM_editCmd.ActiveConnection.Close
Line 122:
Source File: C:\Documents and
Settings\amit\Desktop\WebSite1\add_user.aspx Line: 120

Stack Trace:
[COMException (0x80040e14): Syntax error in INSERT INTO statement.]

Microsoft.VisualBasic.CompilerServices.LateBinding .InternalLateCall(Object
o, Type objType, String name, Object[] args, String[] paramnames,
Boolean[] CopyBack, Boolean IgnoreReturn) +776

Microsoft.VisualBasic.CompilerServices.NewLateBind ing.LateCall(Object
Instance, Type Type, String MemberName, Object[] Arguments, String[]
ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean
IgnoreReturn) +194083
ASP.add_user_aspx.__Render__control1(HtmlTextWrite r __w, Control
parameterContainer) in C:\Documents and
Settings\amit\Desktop\WebSite1\add_user.aspx:120
System.Web.UI.Control.RenderChildrenInternal(HtmlT extWriter writer,
ICollection children) +98
System.Web.UI.Control.RenderChildren(HtmlTextWrite r writer) +20
System.Web.UI.Page.Render(HtmlTextWriter writer) +27
System.Web.UI.Control.RenderControlInternal(HtmlTe xtWriter writer,
ControlAdapter adapter) +53
System.Web.UI.Control.RenderControl(HtmlTextWriter writer,
ControlAdapter adapter) +280
System.Web.UI.Control.RenderControl(HtmlTextWriter writer) +24
System.Web.UI.Page.ProcessRequestMain(Boolean
includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
+8878
Now here is the code for teh above error,
<%
' *** Insert Record: set variables

If (CStr(Request("MM_insert")) = "form1") Then

MM_editConnection = MM_rs_PAVE_CC_1_STRING
MM_editTable = "tblUsers"
MM_editRedirectUrl = "list_users.aspx"
MM_fieldsStr =
"firstname|value|lastname|value|userid|value|passw ord|value|position_name|value|building|value|offic e_number|value|department|value|email|value|user_t ype|value|user_access|value|viewallcase|value|acti ve|value"
MM_columnsStr =
"firstname|',none,''|lastname|',none,''|userid|',n one,''|password|',none,''|position_name|',none,''| building|',none,''|office_number|',none,''|departm ent|none,none,NULL|email|',none,''|user_type|none, none,NULL|user_access|none,none,NULL|viewallcase|' ,none,''|active|',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("values") <> "")
Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And
Request.QueryString("values") <> "") Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" &
Request.QueryString("values")
Else
MM_editRedirectUrl = MM_editRedirectUrl & "&" &
Request.QueryString("values")
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 & ")"

I think teh error is coming from this line
MM_columnsStr =
"firstname|',none,''|lastname|',none,''|userid|',n one,''|password|',none,''|position_name|',none,''| building|',none,''|office_number|',none,''|departm ent|none,none,NULL|email|',none,''|user_type|none, none,NULL|user_access|none,none,NULL|viewallcase|' ,none,''|active|',none,''"

Coz when I replace the columnstr, fieldstr and teh table name to
something else, its working, I am not sure whats wrong. plase I need
help. thankyou.

Nov 19 '05 #2

P: n/a
hey curt, he is using the following as teh insert statemnt,
MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues
& ") values (" & MM_dbValues & ")"

And he is getting the values from the code above. Now when i replace
the following with a different table and values, its working,

MM_editTable = "tblUsers"
MM_editRedirectUrl = "list_users.aspx"
MM_fieldsStr =
"firstname|value|lastname|value|userid|value|passw ord|value|position_name|v*alue|building|value|offi ce_number|value|department|value|email|value|user_ t*ype|value|user_access|value|viewallcase|value|ac tive|value"

MM_columnsStr =
"firstname|',none,''|lastname|',none,''|userid|',n one,''|password|',none,''*|position_name|',none,'' |building|',none,''|office_number|',none,''|depart m*ent|none,none,NULL|email|',none,''|user_type|non e,none,NULL|user_access|non*e,none,NULL|viewallcas e|',none,''|active|',none,''"
But when I use the above its giving me a syntax error in the insert
into statemtn, I am totally new to this area, hence need help. thanks
once again

Nov 19 '05 #3

P: n/a
I see where it's being done... just want to see the actual rendered result of
MM_editQuery
Copy/Paste it into query analyser... see what it says....

--
Curt Christianson
site: http://www.darkfalz.com
blog: http://blog.darkfalz.com

"am*******@gmail.com" wrote:
hey curt, he is using the following as teh insert statemnt,
MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues
& ") values (" & MM_dbValues & ")"

And he is getting the values from the code above. Now when i replace
the following with a different table and values, its working,

MM_editTable = "tblUsers"
MM_editRedirectUrl = "list_users.aspx"
MM_fieldsStr =
"firstname|value|lastname|value|userid|value|passw ord|value|position_name|vĀ*alue|building|value|off ice_number|value|department|value|email|value|user _tĀ*ype|value|user_access|value|viewallcase|value| active|value"

MM_columnsStr =
"firstname|',none,''|lastname|',none,''|userid|',n one,''|password|',none,''Ā*|position_name|',none,' '|building|',none,''|office_number|',none,''|depar tmĀ*ent|none,none,NULL|email|',none,''|user_type|n one,none,NULL|user_access|nonĀ*e,none,NULL|viewall case|',none,''|active|',none,''"
But when I use the above its giving me a syntax error in the insert
into statemtn, I am totally new to this area, hence need help. thanks
once again

Nov 19 '05 #4

P: n/a
Hey Thanks for your reply, I am using the visual web developer with an
access database, teh query analyzer that ur mentioning is usually when
u use sql server i guess, i am not sure and probably wrong, could
please elaborate. thankyou

Nov 19 '05 #5

P: n/a
That's fine... Mostly its so that we can see the rendered sql string...
It's 10 times easier to see what's wrong when you have:
"insert into myTable...." rather then:
"insert into " & somevar & " .." & somethingElse & "..."
You get the idea...

--
Curt Christianson
site: http://www.darkfalz.com
blog: http://blog.darkfalz.com

"am*******@gmail.com" wrote:
Hey Thanks for your reply, I am using the visual web developer with an
access database, teh query analyzer that ur mentioning is usually when
u use sql server i guess, i am not sure and probably wrong, could
please elaborate. thankyou

Nov 19 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.