Microsoft VBScript compilation error '800a03ea' ---I need help

Microsoft VBScript compilation error '800a03ea'

Syntax error

/TMP1zg1r67jg4.asp, line 98

Recordset1.Source = "SELECT * FROM ri7Table Where ContactID= " & Recordset1__MMColParam1 & " and FirstName = '" & Recordset1__MMColParam2 & "' and LastName ='"  & Recordset1__MMColParam3 &
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------^
Here is the code,

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<% Option Explicit %>
<!--#include virtual="/adovbs.inc"-->
<%
' *** Edit Operations: declare variables

Dim MM_editAction
Dim MM_abortEdit
Dim MM_editQuery
Dim MM_editCmd

Dim MM_editConnection
Dim MM_editTable
Dim MM_editRedirectUrl
Dim MM_editColumn
Dim MM_recordId

Dim MM_fieldsStr
Dim MM_columnsStr
Dim MM_fields
Dim MM_columns
Dim MM_typeArray
Dim MM_formVal
Dim MM_delim
Dim MM_altVal
Dim MM_emptyVal
Dim MM_i




MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
  MM_editAction = MM_editAction & "?" & Server.HTMLEncode(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")) = "form2") Then

 Set MM_editConnection = Server.CreateObject("ADODB.Connection")
 Dim DBPath,objRS,objConn
 Dim Recordset1  
Dim Recordset1__MMColParam1
Dim Recordset1__MMColParam2
Dim Recordset1__MMColParam3
Dim Recordset1__MMColParam4
Dim Recordset1__MMColParam5
Dim Recordset1__MMColParam6
Dim Recordset1__MMColParam7
Dim Recordset1__MMColParam8
Dim Recordset1__MMColParam9
Dim Recordset1__MMColParam10
Dim Recordset1__MMColParam11
Dim Recordset1__MMColParam12
Dim Recordset1__MMColParam13
Dim Recordset1__MMColParam14
Dim Recordset1__MMColParam15
Dim Recordset1__MMColParam16
Dim Recordset1__MMColParam17
Dim Recordset1__MMColParam18
Recordset1__MMColParam1 = Request.Form("ContactID")
Recordset1__MMColParam2 = Request.Form("FirstName")
Recordset1__MMColParam3 = Request.Form("LastName")
Recordset1__MMColParam4 = Request.Form("StreetAddress")
Recordset1__MMColParam5 = Request.Form("City")
Recordset1__MMColParam6 = Request.Form("State")
Recordset1__MMColParam7 = Request.Form("ZipCode")
Recordset1__MMColParam8 = Request.Form("PhoneNumber")
Recordset1__MMColParam9 = Request.Form("FaxNumber")
Recordset1__MMColParam10 = Request.Form("EmailAddress")
Recordset1__MMColParam11 = Request.Form("PSD")
Recordset1__MMColParam12 = Request.Form("ASF")
Recordset1__MMColParam13 = Request.Form("NOF")
Recordset1__MMColParam14 = Request.Form("LHS")
Recordset1__MMColParam15 = Request.Form("PS")
Recordset1__MMColParam16 = Request.Form("HCWH")
Recordset1__MMColParam17 = Request.Form("CP")
Recordset1__MMColParam18 = Request.Form("QOC")


DBPath="RI7data.mdb"
Set objConn=Server.CreateObject("ADODB.Connection")
objConn.ConnectionString="Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.mappath("/RI7data.mdb")
objConn.Open
Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = objConn
Recordset1.Source = "SELECT * FROM ri7Table Where ContactID= " & Recordset1__MMColParam1 & " and FirstName = '" & Recordset1__MMColParam2 & "' and LastName ='"  & Recordset1__MMColParam3 & 
"' and StreetAddress ='" & Recordset1__MMColParam4 & "' and City ='" & Recordset1__MMColParam5 & "' and State='" & Recordset1__MMColParam6 & "' and ZipCode= '" & Recordset1__MMColParam7 & "' and PhoneNumber ='"  & Recordset1__MMColParam8 & "' and FaxNumber ='" & Recordset1__MMColParam9 & "' and EmailAddress ='" & Recordset1__MMColParam10 & 
"' and PSD= '" & Recordset1__MMColParam11 & "' and ASF ='"  & Recordset1__MMColParam12 & "' and NOF ='" & Recordset1__MMColParam13 & "' and LHS ='" & Recordset1__MMColParam14 & 
"' and PS = '" & Recordset1__MMColParam15 & "' and HCWH ='" & Recordset1__MMColParam16 & "' and CP ='" & Recordset1__MMColParam17 & "' and QOC ='" & Recordset1__MMColParam18 & "'"



Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open


  MM_editTable = "ri7Table"
  MM_editRedirectUrl = "/Thank_You.htm"
  MM_fieldsStr  = "ContactID|value|FirstName|value|LastName|value|StreetAddress|value|City|value|State|value|ZipCode|value|PhoneNumber|value|FaxNumber|value|EmailAddress|value|PSD|value|ASF|value|NOF|value|LHS|value|PS|value|HCWH|value|CP|value|QOC|value"
  MM_columnsStr = "ContactID|,none,none,none,none,|FirstName|',none,''|LastName|',none,''|StreetAddress|',none,''|City|',none,''|State|',none,''|ZipCode|',none,''|PhoneNumber|',none,''|FaxNumber|',none,''|EmailAddress|',none,''|PSD|',none,''|ASF|',none,''|NOF|',none,''|LHS|',none,''|PS|',none,''|HCWH|',none,''|CP|',none,''|QOC|',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 <> "") 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

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 & ")"

  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.Close  '************THIS IS LINE 147

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

End If
%>

I am trying to insert records into a Access database using a web form--Any ideas???
bunkers22Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
if Recordset1__MMColParam2 contains a quote or apostrophe, it could mess up your SQL statement.
0
bunkers22Author Commented:
I have not taken the time to validate the form on the client side, but the database is empty and waiting to be filled from the form...
0
aikimarkCommented:
Then you might only need:
Recordset1.Source = "SELECT * FROM ri7Table"

=================
ALWAYS VALIDATE YOUR DATA PRIOR TO USING IT!!!!

=================
Comment your statement that is giving you problems and see if the INSERT
statement is working.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

stevbeCommented:
you were missing a line continuation character ...

Recordset1.Source = "SELECT * FROM ri7Table Where ContactID= " & Recordset1__MMColParam1 & " and FirstName = '" & Recordset1__MMColParam2 & "' and LastName ='" & Recordset1__MMColParam3 & _
"' and StreetAddress ='" & Recordset1__MMColParam4 & "' and City ='" & Recordset1__MMColParam5 & "' and State='" & Recordset1__MMColParam6 & "' and ZipCode= '" & Recordset1__MMColParam7 & "' and PhoneNumber ='" & Recordset1__MMColParam8 & "' and FaxNumber ='" & Recordset1__MMColParam9 & "' and EmailAddress ='" & Recordset1__MMColParam10 & _
"' and PSD= '" & Recordset1__MMColParam11 & "' and ASF ='" & Recordset1__MMColParam12 & "' and NOF ='" & Recordset1__MMColParam13 & "' and LHS ='" & Recordset1__MMColParam14 & _
"' and PS = '" & Recordset1__MMColParam15 & "' and HCWH ='" & Recordset1__MMColParam16 & "' and CP ='" & Recordset1__MMColParam17 & "' and QOC ='" & Recordset1__MMColParam18 & "'"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bunkers22Author Commented:
hey stevebe,

I made some progress, I took your suggestion and was able to move to a new error

Microsoft VBScript runtime error '800a000d'

Type mismatch: 'LBound'

/Request_Information.asp, line 147

where line 147 is-------------
For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2

0
stevbeCommented:
not sure ... you could try hardcoding the bound values

  ' set the form values
  For MM_i = 0 To 35 Step 2
    MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))
  Next

Steve
0
bunkers22Author Commented:
Ok , I now get the following, I have added the snippet, to be sure that I placed suggestion in proper line
Microsoft VBScript runtime error '800a000d'

Type mismatch

/Request_Information.asp, line 149

'Where line 149 is indicated

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

  ' create the sql insert statement
  MM_tableValues = ""
  MM_dbValues = ""
  ' set the form values
  For MM_i = 0 To 35 Step 2
    MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))   '***************This is line 149
  Next


  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 & ")"

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

    If (MM_editRedirectUrl <> "") Then
      Response.Redirect(MM_editRedirectUrl)
    End If
  End If
0
stevbeCommented:
I think you need to enclose the field name in quotes ...

MM_fields(MM_i + 1) = Request.Form('" & MM_fields(MM_i) & "'")

how would you type it out if it was harcoded ?

 Request.Form("MyField")

or

 Request.Form(MyField)

Steve
0
aikimarkCommented:
Try:
LBound(MM_fields, 1) To UBound(MM_fields, 1)
0
bunkers22Author Commented:
Just a quick note=== Thanks for your help everyone.  I never was able to use the above code to insert into my database, but the suggestions
were helpful when I tried a different approach.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.