syntax error while converting to asp.net

[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|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,''"


' 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,''|departm­ent|none,none,NULL|email|',none,''|user_type|none,none,NULL|user_access|non­e,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.

 

[TechGnome]

It tells you right where the error is: "Syntax error in INSERT INTO statement."

Something isn't right with it.
Suggestion. After you have assembled it, try displaying the SQL (don't execute it, just display it). The error should become obvious as to what is wrong.

-tg