syntax error while converting asp to asp.net

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.
 
G

Guest

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...
 
A

amitbadgi

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
 
G

Guest

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....
 
A

amitbadgi

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
 
G

Guest

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...
 

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

syntax error in update statement in vb.net 1
Javascript on ASP Survey Page 1
Help ASP; get last inserted value from one table, insert multiple rows in another table. 1
Microsoft VBScript & JScript Help 1
Abort Edit Issue 0
Error handling 0
LBound Who? 2
JScript Error 800a1391 'MM_NameOfPage_STRING' is undefined 1

Members online

Total: 34 (members: 1, guests: 33)
Robots: 450

Forum statistics

Threads
473,769
Messages
2,569,581
Members
45,056
Latest member
GlycogenSupporthealth

Latest Threads

Top