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.
5  1935 
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.
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
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
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...
--
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
This discussion thread is closed Replies have been disabled for this discussion. Similar topics
24 posts
views
Thread by Andrew Koenig |
last post: by
|
4 posts
views
Thread by nonzero |
last post: by
|
1 post
views
Thread by amitbadgi |
last post: by
|
2 posts
views
Thread by Roy Rodsson via .NET 247 |
last post: by
|
1 post
views
Thread by SealedClassSingleton |
last post: by
| | | | | | | | | | | | | | | | | | | | | | | | | | | |
