Solved

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

Posted on 2004-10-26
300 Views
Last Modified: 2012-05-05
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???
0
Question by:bunkers22
    10 Comments
     
    LVL 44

    Expert Comment

    by:aikimark
    if Recordset1__MMColParam2 contains a quote or apostrophe, it could mess up your SQL statement.
    0
     

    Author Comment

    by:bunkers22
    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
     
    LVL 44

    Assisted Solution

    by:aikimark
    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
     
    LVL 39

    Accepted Solution

    by:
    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
     

    Author Comment

    by:bunkers22
    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
     
    LVL 39

    Expert Comment

    by:stevbe
    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
     

    Author Comment

    by:bunkers22
    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
     
    LVL 39

    Expert Comment

    by:stevbe
    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
     
    LVL 44

    Expert Comment

    by:aikimark
    Try:
    LBound(MM_fields, 1) To UBound(MM_fields, 1)
    0
     

    Author Comment

    by:bunkers22
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Learn The Basics of Ethical Hacking & Pen Testing

    Computer and network security is one of the fastest growing and most essential industries in technology, meaning companies will pay big bucks for ethical hackers. This is the perfect course to leap into this lucrative career, learning how to use ethical hacking to reveal ...

    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    884 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now