473,382 Members | 1,533 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,382 software developers and data experts.

syntax error while converting asp to asp.net

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
5 2111
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

24
by: Andrew Koenig | last post by:
PEP 315 suggests that a statement such as do: x = foo() while x != 0: bar(x) be equivalent to while True:
4
by: nonzero | last post by:
I was hoping someone could explain the Syntax of the second argument of the bind() function. bind(sockfd, (struct sockaddr *)&my_addr, sizeof(struct sockaddr)); .....................(struct...
1
by: amitbadgi | last post by:
I am gettign this error while converting an asp application to asp.net , its a query syntax error, Exception Details: System.Runtime.InteropServices.COMException: Syntax error in query expression...
2
by: Roy Rodsson via .NET 247 | last post by:
Hi all! I am using a stored procedure in SQL2000 for retrieving fileinformations from a db. the table as an uniqueidentifier for the file information. The stored procedure has a variable...
1
by: SealedClassSingleton | last post by:
Hi, When calling a stored procedure on a SQL 2005 DB with the managed SQL ADOprovider I got the following exception: Syntax error converting character string to uniqueidentifier when I use...
1
by: ronca | last post by:
Error Syntax error converting the varchar value $568.25 to a column of data type Statement I'm using Select Top 1000 * From mass.dbo.dbo_MASS where _amount > 5000 Want only row with _amount...
36
by: rhys | last post by:
My Gurus and Angels -- Please pardon this old-school programmer, only recently enlightened to open-source, having been trapped in the convenience of proprietary lingos for way too long. My...
0
by: goal2007 | last post by:
Ok I am tying to convert access syntax to Sql syntax to put it in a stored procedure or view.. Here is the part that I need to convert: SELECT .proj_name, .task_name, .Employee, ...
1
by: SnehaAgrawal | last post by:
Hi I get the error "Syntax error converting datetime from character string "for the following sql statement in Stored proc. SET @dynamicSQL =N'SELECT CUSTID,SHCodeLink FROM AccountMaster where...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.