Get ready for BLACK FRIDAY shopping starting in

Forums

This topic is locked

I NEED HELP!!! Inserting into SQL Error Messages

Posted 05 Oct 2001 17:55:04
1
has voted
05 Oct 2001 17:55:04 Glenn Campbell posted:
I need your help again! Here is the error message that I am getting.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '2'.

/OTMORDER/orderform.asp, line 90


Here is the code that I am using to insert into the database.

' *** Edit Operations: declare variables

MM_editAction = CStr(Request("URL")
If (Request.QueryString <> "" Then
MM_editAction = MM_editAction & "?" & Request.QueryString
End If

' boolean to abort record edit
MM_abortEdit = false

' query string to execute
MM_editQuery = ""
%>
<%
' *** Insert Record: set variables

If (CStr(Request("MM_insert") <> "" Then

MM_editConnection = MM_otmorder_STRING
MM_editTable = "dbo.otmorder"
MM_editRedirectUrl = ""
MM_fieldsStr = "lastname|value|firstname|value|middle|value|streetaddress|value|ssndob|value|operid|value|city|value|state|value|zip|value|homephone|value|busphone|value|otherphone|value|billingaddress|value|verifier|value|veroperid|value|orderdate|value|customeracctno|value|basicservice22|value|basicservice2|value|basicservice3|value|basicservice4|value|port1|value|port1phone|value|port2|value|port2phone|value|port3|value|port3phone|value|port4|value|port4phone|value|nonpublished1|value|nonpublished2|value|nonpublished3|value|nonpublished4|value|nonlisted1|value|nonlisted2|value|nonlisted3|value|nonlisted4|value|directorylisted1|value|directorylisted2|value|directorylisted3|value|directorylisted4|value|directorylistname|value|additionallist1|value|additionallist2|value|additionallist3|value|additionallist4|value|additionallistname|value|foreignlist1|value|foreignlist2|value|foreignlist3|value|foreignlist4|value|insidewire1|value|insidewire2|value|insidewire3|value|insidewire4|value|localtoll1|value|localtoll1carrier|value|localtoll2|value|localtoll2carrier|value|localtoll3|value|localtoll3carrier|value|localtoll4|value|localtoll4carrier|value|longdist1|value|longdist1carrier|value|longdist2|value|longdist2carrier|value|longdist3|value|longdist3carrier|value|longdist4|value|longdist4carrier|value|attldoption1|value|attldoption2|value|attldoption3|value|attldoption4|value|2featurepack1|value|2featurepack2|value|2featurepack3|value|2featurepack4|value|multifeaturepack1|value|multifeaturepack2|value|multifeaturepack3|value|multifeaturepack4|value|callwaiting1|value|callwaiting2|value|callwaiting3|value|callwaiting4|value|callerid1|value|callerid2|value|callerid3|value|callerid4|value|callerreturn1|value|callerreturn2|value|callerreturn3|value|callerreturn4|value|callforward1|value|callforward2|value|callforward3|value|callforward4|value|callforwardvar1|value|callforwardvar2|value|callforwardvar3|value|callforwardvar4|value|distinctivering1|value|distinctivering2|value|distinctivering3|value|distinctivering4|value|customring1|value|customring2|value|customring3|value|customring4|value|callforwardring1|value|callforwardring2|value|callforwardring3|value|callforwardring4|value|3way1|value|3way2|value|3way3|value|3way4|value|callscreening1|value|callscreening2|value|callscreening3|value|callscreening4|value|repeatdial1|value|repeatdial2|value|repeatdial3|value|repeatdial4|value|speeddial81|value|speeddial82|value|speeddial83|value|speeddial84|value|speeddial301|value|speeddial302|value|speeddial303|value|speeddial304|value|vmbasic1|value|vmbasic2|value|vmbasic3|value|vmbasic4|value|vmdelux1|value|vmdelux2|value|vmdelux3|value|vmdelux4|value|otherldopt1|value|otherldopt2|value|otherldopt3|value|otherldopt4|value|otherldopt1|value|otherldopt2|value|otherldopt3|value|otherldopt4|value|otherldopt1|value|otherldopt2|value|otherldopt3|value|otherldopt4|value|otherldopt1|value|otherldopt2|value|otherldopt3|value|otherldopt4|value|otherldopt1|value|otherldopt2|value|otherldopt3|value|otherldopt4|value|ldoptothercarrier|value|otherldopt1|value|otherldopt2|value|otherldopt3|value|otherldopt4|value|otherldopt1|value|otherldopt2|value|otherldopt3|value|otherldopt4|value|phonejacks|value|installdate|value|installtime|value|comments|value|customercallback|value"
MM_columnsStr = "lastname|',none,''|firstname|',none,''|middle|',none,''|streetaddress|',none,''|ssndob|',none,''|operid|',none,''|city|',none,''|state|',none,''|zip|',none,''|homephone|',none,''|busphone|',none,''|otherphone|',none,''|billingaddress|',none,''|verifier|',none,''|veroperid|',none,''|orderdate|',none,''|customeracctno|',none,''|basicservice1|none,'Y','N'|basicservice2|none,'Y','N'|basicservice3|none,'Y','N'|basicservice4|none,'Y','N'|port1|',none,''|port1phone|',none,''|port2|',none,''|port2phone|',none,''|port3|',none,''|port3phone|',none,''|port4|',none,''|port4phone|',none,''|nonpublished1|none,'Y','N'|nonpublished2|none,'Y','N'|nonpublished3|none,'Y','N'|nonpublished4|none,'Y','N'|nonlisted1|none,'Y','N'|nonlisted2|none,'Y','N'|nonlisted3|none,'Y','N'|nonlisted4|none,'Y','N'|directorylisted1|none,'Y','N'|directorylisted2|none,'Y','N'|directorylisted3|none,'Y','N'|directorylisted4|none,'Y','N'|directorylistname|',none,''|additionallist1|none,'Y','N'|additionallist2|none,'Y','N'|additionallist3|none,'Y','N'|additionallist4|none,'Y','N'|additionallistname|',none,''|foreignlist1|none,'Y','N'|foreignlist2|none,'Y','N'|foreignlist3|none,'Y','N'|foreignlist4|none,'Y','N'|insidewire1|none,'Y','N'|insidewire2|none,'Y','N'|insidewire3|none,'Y','N'|insidewire4|none,'Y','N'|localtoll1|none,'Y','N'|localtoll1carrier|',none,''|localtoll2|none,'Y','N'|localtoll2carrier|',none,''|localtoll3|none,'Y','N'|localtoll3carrier|',none,''|localtoll4|none,'Y','N'|localtoll4carrier|',none,''|longdist1|none,'Y','N'|longdist1carrier|',none,''|longdist2|none,'Y','N'|longdist2carrier|',none,''|longdist3|none,'Y','N'|longdist3carrier|',none,''|longdist4|none,'Y','N'|longdist4carrier|',none,''|attldoption1|',none,''|attldoption2|',none,''|attldoption3|',none,''|attldoption4|',none,''|2featurepack1|none,'Y','N'|2featurepack2|none,'Y','N'|2featurepack3|none,'Y','N'|2featurepack4|none,'Y','N'|multifeaturepack1|none,'Y','N'|multifeaturepack2|none,'Y','N'|multifeaturepack3|none,'Y','N'|multifeaturepack4|none,'Y','N'|callwaiting1|none,'Y','N'|callwaiting2|none,'Y','N'|callwaiting3|none,'Y','N'|callwaiting4|none,'Y','N'|callerid1|none,'Y','N'|callerid2|none,'Y','N'|callerid3|none,'Y','N'|callerid4|none,'Y','N'|callerreturn1|none,'Y','N'|callerreturn2|none,'Y','N'|callerreturn3|none,'Y','N'|callerreturn4|none,'Y','N'|callforward1|none,'Y','N'|callforward2|none,'Y','N'|callforward3|none,'Y','N'|callforward4|none,'Y','N'|callforwardvar1|none,'Y','N'|callforwardvar2|none,'Y','N'|callforwardvar3|none,'Y','N'|callforwardvar4|none,'Y','N'|distinctivering1|none,'Y','N'|distinctivering2|none,'Y','N'|distinctivering3|none,'Y','N'|distinctivering4|none,'Y','N'|customring1|none,'Y','N'|customring2|none,'Y','N'|customring3|none,'Y','N'|customring4|none,'Y','N'|callforwardring1|none,'Y','N'|callforwardring2|none,'Y','N'|callforwardring3|none,'Y','N'|callforwardring4|none,'Y','N'|3way1|none,'Y','N'|3way2|none,'Y','N'|3way3|none,'Y','N'|3way4|none,'Y','N'|callscreening1|none,'Y','N'|callscreening2|none,'Y','N'|callscreening3|none,'Y','N'|callscreening4|none,'Y','N'|repeatdial1|none,'Y','N'|repeatdial2|none,'Y','N'|repeatdial3|none,'Y','N'|repeatdial4|none,'Y','N'|speeddial81|none,'Y','N'|speeddial82|none,'Y','N'|speeddial83|none,'Y','N'|speeddial84|none,'Y','N'|speeddial301|none,'Y','N'|speeddial302|none,'Y','N'|speeddial303|none,'Y','N'|speeddial304|none,'Y','N'|vmbasic1|none,'Y','N'|vmbasic2|none,'Y','N'|vmbasic3|none,'Y','N'|vmbasic4|none,'Y','N'|vmdelux1|none,'Y','N'|vmdelux2|none,'Y','N'|vmdelux3|none,'Y','N'|vmdelux4|none,'Y','N'|otherldopt1|none,'Y','N'|otherldopt2|none,'Y','N'|otherldopt3|none,'Y','N'|otherldopt4|none,'Y','N'|otherldopt1|none,'Y','N'|otherldopt2|none,'Y','N'|otherldopt3|none,'Y','N'|otherldopt4|none,'Y','N'|otherldopt1|none,'Y','N'|otherldopt2|none,'Y','N'|otherldopt3|none,'Y','N'|otherldopt4|none,'Y','N'|otherldopt1|none,'Y','N'|otherldopt2|none,'Y','N'|otherldopt3|none,'Y','N'|otherldopt4|none,'Y','N'|otherldopt1|none,'Y','N'|otherldopt2|none,'Y','N'|otherldopt3|none,'Y','N'|otherldopt4|none,'Y','N'|ldoptothercarrier|',none,''|otherldopt1|none,'Y','N'|otherldopt2|none,'Y','N'|otherldopt3|none,'Y','N'|otherldopt4|none,'Y','N'|otherldopt1|none,'Y','N'|otherldopt2|none,'Y','N'|otherldopt3|none,'Y','N'|otherldopt4|none,'Y','N'|phonejacks|',none,''|installdate|',none,''|installtime|',none,''|comments|',none,''|customercallback|none,'Y','N'"

' create the MM_fields and MM_columns arrays
MM_fields = Split(MM_fieldsStr, "|"
MM_columns = Split(MM_columnsStr, "|"

' set the form values
For i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_fields(i+1) = CStr(Request.Form(MM_fields(i)))
Next

' append the query string to the redirect URL
If (MM_editRedirectUrl <> "" And Request.QueryString <> "" Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "" Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
End If
End If

End If
%>
<%
' *** Insert Record: construct a sql insert statement and execute it

If (CStr(Request("MM_insert") <> "" Then

' create the sql insert statement
MM_tableValues = ""
MM_dbValues = ""
For i = LBound(MM_fields) To UBound(MM_fields) Step 2
FormVal = MM_fields(i+1)
MM_typeArray = Split(MM_columns(i+1),","
Delim = MM_typeArray(0)
If (Delim = "none" Then Delim = ""
AltVal = MM_typeArray(1)
If (AltVal = "none" Then AltVal = ""
EmptyVal = MM_typeArray(2)
If (EmptyVal = "none" Then EmptyVal = ""
If (FormVal = "" Then
FormVal = EmptyVal
Else
If (AltVal <> "" Then
FormVal = AltVal
ElseIf (Delim = "'" Then ' escape quotes
FormVal = "'" & Replace(FormVal,"'","''" & "'"
Else
FormVal = Delim + FormVal + Delim
End If
End If
If (i <> LBound(MM_fields)) Then
MM_tableValues = MM_tableValues & ","
MM_dbValues = MM_dbValues & ","
End if
MM_tableValues = MM_tableValues & MM_columns(i)
MM_dbValues = MM_dbValues & FormVal
Next
MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & " values (" & MM_dbValues & ""

If (Not MM_abortEdit) Then
' execute the insert
Set MM_editCmd = Server.CreateObject("ADODB.Command"
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close

If (MM_editRedirectUrl <> "" Then
Response.Redirect(MM_editRedirectUrl)
End If
End If

End If
%>

Can ANYONE PLEASE HELP ME!! If I only insert the text fields everything works, but once I start inserting the check fields then I start getting errors.

- Glenn


Replies

Replied 05 Oct 2001 19:36:34
05 Oct 2001 19:36:34 Val Dubuis replied:
I think you have an error in the name of your form field (checkbox, I guess) which does not match table column name:
|basicservice22|value| versus |basicservice1|none,'Y','N'|
Might be a reason...

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
I need your help again! Here is the error message that I am getting.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '2'.

/OTMORDER/orderform.asp, line 90


Here is the code that I am using to insert into the database.

' *** Edit Operations: declare variables

MM_editAction = CStr(Request("URL")
If (Request.QueryString &lt;&gt; "" Then
MM_editAction = MM_editAction & "?" & Request.QueryString
End If

' boolean to abort record edit
MM_abortEdit = false

' query string to execute
MM_editQuery = ""
%&gt;
&lt;%
' *** Insert Record: set variables

If (CStr(Request("MM_insert") &lt;&gt; "" Then

MM_editConnection = MM_otmorder_STRING
MM_editTable = "dbo.otmorder"
MM_editRedirectUrl = ""
MM_fieldsStr = "lastname|value|firstname|value|middle|value|streetaddress|value|ssndob|value|operid|value|city|value|state|value|zip|value|homephone|value|busphone|value|otherphone|value|billingaddress|value|verifier|value|veroperid|value|orderdate|value|customeracctno|value|basicservice22|value|basicservice2|value|basicservice3|value|basicservice4|value|port1|value|port1phone|value|port2|value|port2phone|value|port3|value|port3phone|value|port4|value|port4phone|value|nonpublished1|value|nonpublished2|value|nonpublished3|value|nonpublished4|value|nonlisted1|value|nonlisted2|value|nonlisted3|value|nonlisted4|value|directorylisted1|value|directorylisted2|value|directorylisted3|value|directorylisted4|value|directorylistname|value|additionallist1|value|additionallist2|value|additionallist3|value|additionallist4|value|additionallistname|value|foreignlist1|value|foreignlist2|value|foreignlist3|value|foreignlist4|value|insidewire1|value|insidewire2|value|insidewire3|value|insidewire4|value|localtoll1|value|localtoll1carrier|value|localtoll2|value|localtoll2carrier|value|localtoll3|value|localtoll3carrier|value|localtoll4|value|localtoll4carrier|value|longdist1|value|longdist1carrier|value|longdist2|value|longdist2carrier|value|longdist3|value|longdist3carrier|value|longdist4|value|longdist4carrier|value|attldoption1|value|attldoption2|value|attldoption3|value|attldoption4|value|2featurepack1|value|2featurepack2|value|2featurepack3|value|2featurepack4|value|multifeaturepack1|value|multifeaturepack2|value|multifeaturepack3|value|multifeaturepack4|value|callwaiting1|value|callwaiting2|value|callwaiting3|value|callwaiting4|value|callerid1|value|callerid2|value|callerid3|value|callerid4|value|callerreturn1|value|callerreturn2|value|callerreturn3|value|callerreturn4|value|callforward1|value|callforward2|value|callforward3|value|callforward4|value|callforwardvar1|value|callforwardvar2|value|callforwardvar3|value|callforwardvar4|value|distinctivering1|value|distinctivering2|value|distinctivering3|value|distinctivering4|value|customring1|value|customring2|value|customring3|value|customring4|value|callforwardring1|value|callforwardring2|value|callforwardring3|value|callforwardring4|value|3way1|value|3way2|value|3way3|value|3way4|value|callscreening1|value|callscreening2|value|callscreening3|value|callscreening4|value|repeatdial1|value|repeatdial2|value|repeatdial3|value|repeatdial4|value|speeddial81|value|speeddial82|value|speeddial83|value|speeddial84|value|speeddial301|value|speeddial302|value|speeddial303|value|speeddial304|value|vmbasic1|value|vmbasic2|value|vmbasic3|value|vmbasic4|value|vmdelux1|value|vmdelux2|value|vmdelux3|value|vmdelux4|value|otherldopt1|value|otherldopt2|value|otherldopt3|value|otherldopt4|value|otherldopt1|value|otherldopt2|value|otherldopt3|value|otherldopt4|value|otherldopt1|value|otherldopt2|value|otherldopt3|value|otherldopt4|value|otherldopt1|value|otherldopt2|value|otherldopt3|value|otherldopt4|value|otherldopt1|value|otherldopt2|value|otherldopt3|value|otherldopt4|value|ldoptothercarrier|value|otherldopt1|value|otherldopt2|value|otherldopt3|value|otherldopt4|value|otherldopt1|value|otherldopt2|value|otherldopt3|value|otherldopt4|value|phonejacks|value|installdate|value|installtime|value|comments|value|customercallback|value"
MM_columnsStr = "lastname|',none,''|firstname|',none,''|middle|',none,''|streetaddress|',none,''|ssndob|',none,''|operid|',none,''|city|',none,''|state|',none,''|zip|',none,''|homephone|',none,''|busphone|',none,''|otherphone|',none,''|billingaddress|',none,''|verifier|',none,''|veroperid|',none,''|orderdate|',none,''|customeracctno|',none,''|basicservice1|none,'Y','N'|basicservice2|none,'Y','N'|basicservice3|none,'Y','N'|basicservice4|none,'Y','N'|port1|',none,''|port1phone|',none,''|port2|',none,''|port2phone|',none,''|port3|',none,''|port3phone|',none,''|port4|',none,''|port4phone|',none,''|nonpublished1|none,'Y','N'|nonpublished2|none,'Y','N'|nonpublished3|none,'Y','N'|nonpublished4|none,'Y','N'|nonlisted1|none,'Y','N'|nonlisted2|none,'Y','N'|nonlisted3|none,'Y','N'|nonlisted4|none,'Y','N'|directorylisted1|none,'Y','N'|directorylisted2|none,'Y','N'|directorylisted3|none,'Y','N'|directorylisted4|none,'Y','N'|directorylistname|',none,''|additionallist1|none,'Y','N'|additionallist2|none,'Y','N'|additionallist3|none,'Y','N'|additionallist4|none,'Y','N'|additionallistname|',none,''|foreignlist1|none,'Y','N'|foreignlist2|none,'Y','N'|foreignlist3|none,'Y','N'|foreignlist4|none,'Y','N'|insidewire1|none,'Y','N'|insidewire2|none,'Y','N'|insidewire3|none,'Y','N'|insidewire4|none,'Y','N'|localtoll1|none,'Y','N'|localtoll1carrier|',none,''|localtoll2|none,'Y','N'|localtoll2carrier|',none,''|localtoll3|none,'Y','N'|localtoll3carrier|',none,''|localtoll4|none,'Y','N'|localtoll4carrier|',none,''|longdist1|none,'Y','N'|longdist1carrier|',none,''|longdist2|none,'Y','N'|longdist2carrier|',none,''|longdist3|none,'Y','N'|longdist3carrier|',none,''|longdist4|none,'Y','N'|longdist4carrier|',none,''|attldoption1|',none,''|attldoption2|',none,''|attldoption3|',none,''|attldoption4|',none,''|2featurepack1|none,'Y','N'|2featurepack2|none,'Y','N'|2featurepack3|none,'Y','N'|2featurepack4|none,'Y','N'|multifeaturepack1|none,'Y','N'|multifeaturepack2|none,'Y','N'|multifeaturepack3|none,'Y','N'|multifeaturepack4|none,'Y','N'|callwaiting1|none,'Y','N'|callwaiting2|none,'Y','N'|callwaiting3|none,'Y','N'|callwaiting4|none,'Y','N'|callerid1|none,'Y','N'|callerid2|none,'Y','N'|callerid3|none,'Y','N'|callerid4|none,'Y','N'|callerreturn1|none,'Y','N'|callerreturn2|none,'Y','N'|callerreturn3|none,'Y','N'|callerreturn4|none,'Y','N'|callforward1|none,'Y','N'|callforward2|none,'Y','N'|callforward3|none,'Y','N'|callforward4|none,'Y','N'|callforwardvar1|none,'Y','N'|callforwardvar2|none,'Y','N'|callforwardvar3|none,'Y','N'|callforwardvar4|none,'Y','N'|distinctivering1|none,'Y','N'|distinctivering2|none,'Y','N'|distinctivering3|none,'Y','N'|distinctivering4|none,'Y','N'|customring1|none,'Y','N'|customring2|none,'Y','N'|customring3|none,'Y','N'|customring4|none,'Y','N'|callforwardring1|none,'Y','N'|callforwardring2|none,'Y','N'|callforwardring3|none,'Y','N'|callforwardring4|none,'Y','N'|3way1|none,'Y','N'|3way2|none,'Y','N'|3way3|none,'Y','N'|3way4|none,'Y','N'|callscreening1|none,'Y','N'|callscreening2|none,'Y','N'|callscreening3|none,'Y','N'|callscreening4|none,'Y','N'|repeatdial1|none,'Y','N'|repeatdial2|none,'Y','N'|repeatdial3|none,'Y','N'|repeatdial4|none,'Y','N'|speeddial81|none,'Y','N'|speeddial82|none,'Y','N'|speeddial83|none,'Y','N'|speeddial84|none,'Y','N'|speeddial301|none,'Y','N'|speeddial302|none,'Y','N'|speeddial303|none,'Y','N'|speeddial304|none,'Y','N'|vmbasic1|none,'Y','N'|vmbasic2|none,'Y','N'|vmbasic3|none,'Y','N'|vmbasic4|none,'Y','N'|vmdelux1|none,'Y','N'|vmdelux2|none,'Y','N'|vmdelux3|none,'Y','N'|vmdelux4|none,'Y','N'|otherldopt1|none,'Y','N'|otherldopt2|none,'Y','N'|otherldopt3|none,'Y','N'|otherldopt4|none,'Y','N'|otherldopt1|none,'Y','N'|otherldopt2|none,'Y','N'|otherldopt3|none,'Y','N'|otherldopt4|none,'Y','N'|otherldopt1|none,'Y','N'|otherldopt2|none,'Y','N'|otherldopt3|none,'Y','N'|otherldopt4|none,'Y','N'|otherldopt1|none,'Y','N'|otherldopt2|none,'Y','N'|otherldopt3|none,'Y','N'|otherldopt4|none,'Y','N'|otherldopt1|none,'Y','N'|otherldopt2|none,'Y','N'|otherldopt3|none,'Y','N'|otherldopt4|none,'Y','N'|ldoptothercarrier|',none,''|otherldopt1|none,'Y','N'|otherldopt2|none,'Y','N'|otherldopt3|none,'Y','N'|otherldopt4|none,'Y','N'|otherldopt1|none,'Y','N'|otherldopt2|none,'Y','N'|otherldopt3|none,'Y','N'|otherldopt4|none,'Y','N'|phonejacks|',none,''|installdate|',none,''|installtime|',none,''|comments|',none,''|customercallback|none,'Y','N'"

' create the MM_fields and MM_columns arrays
MM_fields = Split(MM_fieldsStr, "|"
MM_columns = Split(MM_columnsStr, "|"

' set the form values
For i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_fields(i+1) = CStr(Request.Form(MM_fields(i)))
Next

' append the query string to the redirect URL
If (MM_editRedirectUrl &lt;&gt; "" And Request.QueryString &lt;&gt; "" Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString &lt;&gt; "" Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
End If
End If

End If
%&gt;
&lt;%
' *** Insert Record: construct a sql insert statement and execute it

If (CStr(Request("MM_insert") &lt;&gt; "" Then

' create the sql insert statement
MM_tableValues = ""
MM_dbValues = ""
For i = LBound(MM_fields) To UBound(MM_fields) Step 2
FormVal = MM_fields(i+1)
MM_typeArray = Split(MM_columns(i+1),","
Delim = MM_typeArray(0)
If (Delim = "none" Then Delim = ""
AltVal = MM_typeArray(1)
If (AltVal = "none" Then AltVal = ""
EmptyVal = MM_typeArray(2)
If (EmptyVal = "none" Then EmptyVal = ""
If (FormVal = "" Then
FormVal = EmptyVal
Else
If (AltVal &lt;&gt; "" Then
FormVal = AltVal
ElseIf (Delim = "'" Then ' escape quotes
FormVal = "'" & Replace(FormVal,"'","''" & "'"
Else
FormVal = Delim + FormVal + Delim
End If
End If
If (i &lt;&gt; LBound(MM_fields)) Then
MM_tableValues = MM_tableValues & ","
MM_dbValues = MM_dbValues & ","
End if
MM_tableValues = MM_tableValues & MM_columns(i)
MM_dbValues = MM_dbValues & FormVal
Next
MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & " values (" & MM_dbValues & ""

If (Not MM_abortEdit) Then
' execute the insert
Set MM_editCmd = Server.CreateObject("ADODB.Command"
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close

If (MM_editRedirectUrl &lt;&gt; "" Then
Response.Redirect(MM_editRedirectUrl)
End If
End If

End If
%&gt;

Can ANYONE PLEASE HELP ME!! If I only insert the text fields everything works, but once I start inserting the check fields then I start getting errors.

- Glenn



<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Replied 12 Feb 2002 08:21:02
12 Feb 2002 08:21:02 Glenn Campbell replied:
I figured it out. Something VERY simple. When using the SQL database, the first character of the field name cannot be a number. DUH.. <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

Thanks anyway though.

- Glenn

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
I think you have an error in the name of your form field (checkbox, I guess) which does not match table column name:
|basicservice22|value| versus |basicservice1|none,'Y','N'|
Might be a reason...

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
I need your help again! Here is the error message that I am getting.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '2'.

/OTMORDER/orderform.asp, line 90


Here is the code that I am using to insert into the database.

' *** Edit Operations: declare variables

MM_editAction = CStr(Request("URL")
If (Request.QueryString &lt;&gt; "" Then
MM_editAction = MM_editAction & "?" & Request.QueryString
End If

' boolean to abort record edit
MM_abortEdit = false

' query string to execute
MM_editQuery = ""
%&gt;
&lt;%
' *** Insert Record: set variables

If (CStr(Request("MM_insert") &lt;&gt; "" Then

MM_editConnection = MM_otmorder_STRING
MM_editTable = "dbo.otmorder"
MM_editRedirectUrl = ""
MM_fieldsStr = "lastname|value|firstname|value|middle|value|streetaddress|value|ssndob|value|operid|value|city|value|state|value|zip|value|homephone|value|busphone|value|otherphone|value|billingaddress|value|verifier|value|veroperid|value|orderdate|value|customeracctno|value|basicservice22|value|basicservice2|value|basicservice3|value|basicservice4|value|port1|value|port1phone|value|port2|value|port2phone|value|port3|value|port3phone|value|port4|value|port4phone|value|nonpublished1|value|nonpublished2|value|nonpublished3|value|nonpublished4|value|nonlisted1|value|nonlisted2|value|nonlisted3|value|nonlisted4|value|directorylisted1|value|directorylisted2|value|directorylisted3|value|directorylisted4|value|directorylistname|value|additionallist1|value|additionallist2|value|additionallist3|value|additionallist4|value|additionallistname|value|foreignlist1|value|foreignlist2|value|foreignlist3|value|foreignlist4|value|insidewire1|value|insidewire2|value|insidewire3|value|insidewire4|value|localtoll1|value|localtoll1carrier|value|localtoll2|value|localtoll2carrier|value|localtoll3|value|localtoll3carrier|value|localtoll4|value|localtoll4carrier|value|longdist1|value|longdist1carrier|value|longdist2|value|longdist2carrier|value|longdist3|value|longdist3carrier|value|longdist4|value|longdist4carrier|value|attldoption1|value|attldoption2|value|attldoption3|value|attldoption4|value|2featurepack1|value|2featurepack2|value|2featurepack3|value|2featurepack4|value|multifeaturepack1|value|multifeaturepack2|value|multifeaturepack3|value|multifeaturepack4|value|callwaiting1|value|callwaiting2|value|callwaiting3|value|callwaiting4|value|callerid1|value|callerid2|value|callerid3|value|callerid4|value|callerreturn1|value|callerreturn2|value|callerreturn3|value|callerreturn4|value|callforward1|value|callforward2|value|callforward3|value|callforward4|value|callforwardvar1|value|callforwardvar2|value|callforwardvar3|value|callforwardvar4|value|distinctivering1|value|distinctivering2|value|distinctivering3|value|distinctivering4|value|customring1|value|customring2|value|customring3|value|customring4|value|callforwardring1|value|callforwardring2|value|callforwardring3|value|callforwardring4|value|3way1|value|3way2|value|3way3|value|3way4|value|callscreening1|value|callscreening2|value|callscreening3|value|callscreening4|value|repeatdial1|value|repeatdial2|value|repeatdial3|value|repeatdial4|value|speeddial81|value|speeddial82|value|speeddial83|value|speeddial84|value|speeddial301|value|speeddial302|value|speeddial303|value|speeddial304|value|vmbasic1|value|vmbasic2|value|vmbasic3|value|vmbasic4|value|vmdelux1|value|vmdelux2|value|vmdelux3|value|vmdelux4|value|otherldopt1|value|otherldopt2|value|otherldopt3|value|otherldopt4|value|otherldopt1|value|otherldopt2|value|otherldopt3|value|otherldopt4|value|otherldopt1|value|otherldopt2|value|otherldopt3|value|otherldopt4|value|otherldopt1|value|otherldopt2|value|otherldopt3|value|otherldopt4|value|otherldopt1|value|otherldopt2|value|otherldopt3|value|otherldopt4|value|ldoptothercarrier|value|otherldopt1|value|otherldopt2|value|otherldopt3|value|otherldopt4|value|otherldopt1|value|otherldopt2|value|otherldopt3|value|otherldopt4|value|phonejacks|value|installdate|value|installtime|value|comments|value|customercallback|value"
MM_columnsStr = "lastname|',none,''|firstname|',none,''|middle|',none,''|streetaddress|',none,''|ssndob|',none,''|operid|',none,''|city|',none,''|state|',none,''|zip|',none,''|homephone|',none,''|busphone|',none,''|otherphone|',none,''|billingaddress|',none,''|verifier|',none,''|veroperid|',none,''|orderdate|',none,''|customeracctno|',none,''|basicservice1|none,'Y','N'|basicservice2|none,'Y','N'|basicservice3|none,'Y','N'|basicservice4|none,'Y','N'|port1|',none,''|port1phone|',none,''|port2|',none,''|port2phone|',none,''|port3|',none,''|port3phone|',none,''|port4|',none,''|port4phone|',none,''|nonpublished1|none,'Y','N'|nonpublished2|none,'Y','N'|nonpublished3|none,'Y','N'|nonpublished4|none,'Y','N'|nonlisted1|none,'Y','N'|nonlisted2|none,'Y','N'|nonlisted3|none,'Y','N'|nonlisted4|none,'Y','N'|directorylisted1|none,'Y','N'|directorylisted2|none,'Y','N'|directorylisted3|none,'Y','N'|directorylisted4|none,'Y','N'|directorylistname|',none,''|additionallist1|none,'Y','N'|additionallist2|none,'Y','N'|additionallist3|none,'Y','N'|additionallist4|none,'Y','N'|additionallistname|',none,''|foreignlist1|none,'Y','N'|foreignlist2|none,'Y','N'|foreignlist3|none,'Y','N'|foreignlist4|none,'Y','N'|insidewire1|none,'Y','N'|insidewire2|none,'Y','N'|insidewire3|none,'Y','N'|insidewire4|none,'Y','N'|localtoll1|none,'Y','N'|localtoll1carrier|',none,''|localtoll2|none,'Y','N'|localtoll2carrier|',none,''|localtoll3|none,'Y','N'|localtoll3carrier|',none,''|localtoll4|none,'Y','N'|localtoll4carrier|',none,''|longdist1|none,'Y','N'|longdist1carrier|',none,''|longdist2|none,'Y','N'|longdist2carrier|',none,''|longdist3|none,'Y','N'|longdist3carrier|',none,''|longdist4|none,'Y','N'|longdist4carrier|',none,''|attldoption1|',none,''|attldoption2|',none,''|attldoption3|',none,''|attldoption4|',none,''|2featurepack1|none,'Y','N'|2featurepack2|none,'Y','N'|2featurepack3|none,'Y','N'|2featurepack4|none,'Y','N'|multifeaturepack1|none,'Y','N'|multifeaturepack2|none,'Y','N'|multifeaturepack3|none,'Y','N'|multifeaturepack4|none,'Y','N'|callwaiting1|none,'Y','N'|callwaiting2|none,'Y','N'|callwaiting3|none,'Y','N'|callwaiting4|none,'Y','N'|callerid1|none,'Y','N'|callerid2|none,'Y','N'|callerid3|none,'Y','N'|callerid4|none,'Y','N'|callerreturn1|none,'Y','N'|callerreturn2|none,'Y','N'|callerreturn3|none,'Y','N'|callerreturn4|none,'Y','N'|callforward1|none,'Y','N'|callforward2|none,'Y','N'|callforward3|none,'Y','N'|callforward4|none,'Y','N'|callforwardvar1|none,'Y','N'|callforwardvar2|none,'Y','N'|callforwardvar3|none,'Y','N'|callforwardvar4|none,'Y','N'|distinctivering1|none,'Y','N'|distinctivering2|none,'Y','N'|distinctivering3|none,'Y','N'|distinctivering4|none,'Y','N'|customring1|none,'Y','N'|customring2|none,'Y','N'|customring3|none,'Y','N'|customring4|none,'Y','N'|callforwardring1|none,'Y','N'|callforwardring2|none,'Y','N'|callforwardring3|none,'Y','N'|callforwardring4|none,'Y','N'|3way1|none,'Y','N'|3way2|none,'Y','N'|3way3|none,'Y','N'|3way4|none,'Y','N'|callscreening1|none,'Y','N'|callscreening2|none,'Y','N'|callscreening3|none,'Y','N'|callscreening4|none,'Y','N'|repeatdial1|none,'Y','N'|repeatdial2|none,'Y','N'|repeatdial3|none,'Y','N'|repeatdial4|none,'Y','N'|speeddial81|none,'Y','N'|speeddial82|none,'Y','N'|speeddial83|none,'Y','N'|speeddial84|none,'Y','N'|speeddial301|none,'Y','N'|speeddial302|none,'Y','N'|speeddial303|none,'Y','N'|speeddial304|none,'Y','N'|vmbasic1|none,'Y','N'|vmbasic2|none,'Y','N'|vmbasic3|none,'Y','N'|vmbasic4|none,'Y','N'|vmdelux1|none,'Y','N'|vmdelux2|none,'Y','N'|vmdelux3|none,'Y','N'|vmdelux4|none,'Y','N'|otherldopt1|none,'Y','N'|otherldopt2|none,'Y','N'|otherldopt3|none,'Y','N'|otherldopt4|none,'Y','N'|otherldopt1|none,'Y','N'|otherldopt2|none,'Y','N'|otherldopt3|none,'Y','N'|otherldopt4|none,'Y','N'|otherldopt1|none,'Y','N'|otherldopt2|none,'Y','N'|otherldopt3|none,'Y','N'|otherldopt4|none,'Y','N'|otherldopt1|none,'Y','N'|otherldopt2|none,'Y','N'|otherldopt3|none,'Y','N'|otherldopt4|none,'Y','N'|otherldopt1|none,'Y','N'|otherldopt2|none,'Y','N'|otherldopt3|none,'Y','N'|otherldopt4|none,'Y','N'|ldoptothercarrier|',none,''|otherldopt1|none,'Y','N'|otherldopt2|none,'Y','N'|otherldopt3|none,'Y','N'|otherldopt4|none,'Y','N'|otherldopt1|none,'Y','N'|otherldopt2|none,'Y','N'|otherldopt3|none,'Y','N'|otherldopt4|none,'Y','N'|phonejacks|',none,''|installdate|',none,''|installtime|',none,''|comments|',none,''|customercallback|none,'Y','N'"

' create the MM_fields and MM_columns arrays
MM_fields = Split(MM_fieldsStr, "|"
MM_columns = Split(MM_columnsStr, "|"

' set the form values
For i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_fields(i+1) = CStr(Request.Form(MM_fields(i)))
Next

' append the query string to the redirect URL
If (MM_editRedirectUrl &lt;&gt; "" And Request.QueryString &lt;&gt; "" Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString &lt;&gt; "" Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
End If
End If

End If
%&gt;
&lt;%
' *** Insert Record: construct a sql insert statement and execute it

If (CStr(Request("MM_insert") &lt;&gt; "" Then

' create the sql insert statement
MM_tableValues = ""
MM_dbValues = ""
For i = LBound(MM_fields) To UBound(MM_fields) Step 2
FormVal = MM_fields(i+1)
MM_typeArray = Split(MM_columns(i+1),","
Delim = MM_typeArray(0)
If (Delim = "none" Then Delim = ""
AltVal = MM_typeArray(1)
If (AltVal = "none" Then AltVal = ""
EmptyVal = MM_typeArray(2)
If (EmptyVal = "none" Then EmptyVal = ""
If (FormVal = "" Then
FormVal = EmptyVal
Else
If (AltVal &lt;&gt; "" Then
FormVal = AltVal
ElseIf (Delim = "'" Then ' escape quotes
FormVal = "'" & Replace(FormVal,"'","''" & "'"
Else
FormVal = Delim + FormVal + Delim
End If
End If
If (i &lt;&gt; LBound(MM_fields)) Then
MM_tableValues = MM_tableValues & ","
MM_dbValues = MM_dbValues & ","
End if
MM_tableValues = MM_tableValues & MM_columns(i)
MM_dbValues = MM_dbValues & FormVal
Next
MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & " values (" & MM_dbValues & ""

If (Not MM_abortEdit) Then
' execute the insert
Set MM_editCmd = Server.CreateObject("ADODB.Command"
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close

If (MM_editRedirectUrl &lt;&gt; "" Then
Response.Redirect(MM_editRedirectUrl)
End If
End If

End If
%&gt;

Can ANYONE PLEASE HELP ME!! If I only insert the text fields everything works, but once I start inserting the check fields then I start getting errors.

- Glenn



<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>


<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Reply to this topic