Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-10-26
10
Medium Priority
?
320 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
Comment
Question by:bunkers22
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
10 Comments
 
LVL 46

Expert Comment

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

Author Comment

by:bunkers22
ID: 12415610
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 46

Assisted Solution

by:aikimark
aikimark earned 300 total points
ID: 12415919
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 39

Accepted Solution

by:
stevbe earned 1200 total points
ID: 12421551
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
ID: 12422220
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
ID: 12424752
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
ID: 12424854
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
ID: 12425035
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 46

Expert Comment

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

Author Comment

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

618 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