syntax error while converting asp to asp.net

  • Thread starter Thread starter amitbadgi
  • Start date Start date
A

amitbadgi

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.NewLateBinding.LateCall(Object
Instance, Type Type, String MemberName, Object[] Arguments, String[]
ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean
IgnoreReturn) +194083
ASP.add_user_aspx.__Render__control1(HtmlTextWriter __w, Control
parameterContainer) in C:\Documents and
Settings\amit\Desktop\WebSite1\add_user.aspx:120
System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer,
ICollection children) +98
System.Web.UI.Control.RenderChildren(HtmlTextWriter writer) +20
System.Web.UI.Page.Render(HtmlTextWriter writer) +27
System.Web.UI.Control.RenderControlInternal(HtmlTextWriter 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|password|value|position_name|value|building|value|office_number|value|department|value|email|value|user_type|value|user_access|value|viewallcase|value|active|value"
MM_columnsStr =
"firstname|',none,''|lastname|',none,''|userid|',none,''|password|',none,''|position_name|',none,''|building|',none,''|office_number|',none,''|department|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|',none,''|password|',none,''|position_name|',none,''|building|',none,''|office_number|',none,''|department|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.
 
just give us the text for the INSERT statement once...

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



[email protected] said:
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.NewLateBinding.LateCall(Object
Instance, Type Type, String MemberName, Object[] Arguments, String[]
ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean
IgnoreReturn) +194083
ASP.add_user_aspx.__Render__control1(HtmlTextWriter __w, Control
parameterContainer) in C:\Documents and
Settings\amit\Desktop\WebSite1\add_user.aspx:120
System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer,
ICollection children) +98
System.Web.UI.Control.RenderChildren(HtmlTextWriter writer) +20
System.Web.UI.Page.Render(HtmlTextWriter writer) +27
System.Web.UI.Control.RenderControlInternal(HtmlTextWriter 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|password|value|position_name|value|building|value|office_number|value|department|value|email|value|user_type|value|user_access|value|viewallcase|value|active|value"
MM_columnsStr =
"firstname|',none,''|lastname|',none,''|userid|',none,''|password|',none,''|position_name|',none,''|building|',none,''|office_number|',none,''|department|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|',none,''|password|',none,''|position_name|',none,''|building|',none,''|office_number|',none,''|department|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.
Click to expand...
 
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|password|value|position_name|v­alue|building|value|office_number|value|department|value|email|value|user_t­ype|value|user_access|value|viewallcase|value|active|value"

MM_columnsStr =
"firstname|',none,''|lastname|',none,''|userid|',none,''|password|',none,''­|position_name|',none,''|building|',none,''|office_number|',none,''|departm­ent|none,none,NULL|email|',none,''|user_type|none,none,NULL|user_access|non­e,none,NULL|viewallcase|',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
 
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....
 
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
 
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...
 
Back
Top