[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 724
  • Last Modified:

Connection String Blues

I get the following respone when I try to insert a record into my database.
Connection String Undefined. I am using DWMX2004, and the code is below.
Thanks in advance for your help.
-----------------------------------------------------------------------------------------
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<% Option Explicit %>
<!--#include virtual="/adovbs.inc"-->

<!--#include file="Connections/webconn.asp" -->
<%
' *** 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

  MM_editConnection = MM_webconn_STRING  '****Here lies the problem****'
  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,NULL|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.ActiveConnection.Close

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

End If
%>
---------------------------------------------------------------
webconn.asp (below)
----------------------------
<%
' FileName="Connection_ado_conn_string.htm"
' Type="ADO"
' DesigntimeType="ADO"
' HTTP="true"
' Catalog=""
' Schema=""
Dim MM_webconn_STRING
MM_webconn_STRING = ConnectionString="Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\Accounts\montanah\wwwRoot\RI7data.mdb"
%>
0
bunkers22
Asked:
bunkers22
  • 19
  • 8
  • 7
  • +2
6 Solutions
 
Steve BinkCommented:
Yup, that definitly is the problem:

 MM_editConnection = MM_webconn_STRING  '****Here lies the problem****'

If you are using MM_editConnection as a connection object, you need to create it as such, exactly the way you created your command object:

Set MM_editConnection = Server.CreateObject("ADODB.Connection")

You will need to set its properties before it can be used.  Remember, the connection string is not the connection itself.  It only defines how the connection is generated.
0
 
flavoCommented:
0
 
bunkers22Author Commented:
I used your suggestion and I received the following;
Requested operation requires an OLE DB Session object, which is not supported by the current provider.

My service provider suggested I use the following dsnless connection;
<%

'''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Access Database connection string
' Change this to the actual location of your mdb file
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim DBPath,objRS,objConn
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 Products WHERE ProductID = " + Replace(Recordset1__MMColParam, "'", "''") + "" 
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()
%>

Any ideas on how I can make this work together??Thanks
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Steve BinkCommented:
Your provider knows what it is talking about.  :)  Essentially, you are replacing this line:

MM_editConnection = MM_webconn_STRING  '****Here lies the problem****'

with these lines:

'''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Access Database connection string
' Change this to the actual location of your mdb file
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim DBPath,objRS,objConn
DBPath="/RI7data.mdb"

Set objConn=Server.CreateObject("ADODB.Connection")
objConn.ConnectionString="Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.mappath("/RI7data.mdb)
objConn.Open

To use this connection object anywhere else in code, you just assign it to the .ActiveConnection property of the object that must use it.  The rest of the code from your provider shows an example of this using a recordset.  The key line you are looking for is:

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = objConn

This means that you have declared a variable called Recordset1 as an ADODB.Recordset, hosted on the server-side.  A recordset needs to know what database to use, and how to get to it, so you tell it to use the connection object created previously.  Understand?
0
 
bunkers22Author Commented:
I think I am starting to see the the light,
I made the following changes;
------------------------------------------------
 Set MM_editConnection = Server.CreateObject("ADODB.Connection")
 Dim DBPath,objRS,objConn
DBPath="/RI7data.mdb"

Set objConn=Server.CreateObject("ADODB.Connection")
objConn.ConnectionString="Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.mappath(DBPath)
objConn.Open
Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = objConn
Recordset1.Source = "SELECT * FROM ri7Table WHERE ContactID = " + Replace(Recordset1__MMColParam, "'", "''") + "" 
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

----------------------------------------------------------------------
I get back the following;
Microsoft VBScript runtime error '800a01f4'

Variable is undefined: 'Recordset1'
0
 
Arthur_WoodCommented:
Dim DBPath,objRS,objConn
Dim Recordset1   'add this line - you must declare the variable before you can use it

DBPath="/RI7data.mdb"

Set objConn=Server.CreateObject("ADODB.Connection")
objConn.ConnectionString="Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.mappath(DBPath)
objConn.Open
Set Recordset1 = Server.CreateObject("ADODB.Recordset")

AW
0
 
bunkers22Author Commented:
Thanks I declared both Recordset1 and Recordset1_MMColParam as follows;
---------------------------------
 Set MM_editConnection = Server.CreateObject("ADODB.Connection")
 Dim DBPath,objRS,objConn
 Dim Recordset1  
 Dim Recordset1__MMColParam
DBPath="/RI7data.mdb"

Set objConn=Server.CreateObject("ADODB.Connection")
objConn.ConnectionString="Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.mappath(DBPath)
objConn.Open
Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = objConn
Recordset1.Source = "SELECT * FROM ri7Table WHERE ContactID = " + Replace(Recordset1__MMColParam, "'", "''") + "" 
Recordset1.CursorType = 0

---------------------------------
and I get the following error message;

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

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'ContactID ='.



What do I do now??

0
 
YSkellingCommented:
if contactID is string, the single quote it like this :
Recordset1.Source = "SELECT * FROM ri7Table WHERE ContactID = '" + Replace(Recordset1__MMColParam, "'", "''") + "'"


Where do you put data in this variable : Recordset1__MMColParam ?


 
0
 
bunkers22Author Commented:
I am very new to the world of databases, for that matter programming in general, so please bear with me,

ContactID is the first column of my database. Sorry about declaring Recordset1_MMColParam a variable.
0
 
Steve BinkCommented:
Ok, the problem now lies in your definition of the SQL statement you are trying to execute.  I have a couple questions:

1) What data type is ContactID?  Text or numeric?
2) Where do you store the ContactID value for the query?  Do you have a variable for it, is it hard-coded, is it passed from the previous page, etc...

Also, for string concatenation, use the "&" operator instead of "+".  The plus sign indicates a math operation.  At the very least, your line should be changed to:

Recordset1.Source = "SELECT * FROM ri7Table WHERE ContactID = '" & Replace(Recordset1__MMColParam, "'", "''") & "'"
0
 
Arthur_WoodCommented:
what are you expecting to be the VALUE of the variable

Recordset1__MMColParam
??

In you code, you have declared this variable, but never assigned any value to it, so it starts out with nothing ( a zero-length string), and your SQL then becomes:

Recordset1.Source = "SELECT * FROM ri7Table WHERE ContactID = "

which does in fact create an error, since ContactID must, at the very least, be equal to SOMETHING (even a zero-length string)

what value of ClientID are you trying to use to restring the SQL, and where is that value coming from?

AW

0
 
bunkers22Author Commented:
Thanks Arthur

I am trying to get the results of a form (form2--In the opening code), from http://montanahandcrafters.com/Request_Information, where ContactID is a hidden field, intended to be
used as a unique customer id. Are you saying that I should set Recordset1__MMColParam=0???
0
 
Steve BinkCommented:
No, you should do this:

Recordset1__MMColParam = Request.Form("ContactID")

Assuming your hidden field is named ContactID.
0
 
bunkers22Author Commented:
OK, Just to be on the same page, my code now looks like this;

<%@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__MMColParam
Recordset1__MMColParam = Request.Form("ContactID")
DBPath="/RI7data.mdb"
Set objConn=Server.CreateObject("ADODB.Connection")
objConn.ConnectionString="Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.mappath(DBPath)
objConn.Open
Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = objConn
Recordset1.Source = "SELECT * FROM ri7Table WHERE ContactID = " + Replace(Recordset1__MMColParam, "'", "''") + "" 
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,NULL,''|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.ActiveConnection.Close

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

End If
%>

But I still cannot make it work, I still receive the following error;

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

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'ContactID ='.

Why???????????
0
 
bunkers22Author Commented:
Sorry I did not see the comment;

Comment from routinet  feedback
Date: 10/20/2004 08:16AM PDT
 Comment  


Ok, the problem now lies in your definition of the SQL statement you are trying to execute.  I have a couple questions:

1) What data type is ContactID?  Text or numeric?
2) Where do you store the ContactID value for the query?  Do you have a variable for it, is it hard-coded, is it passed from the previous page, etc...

Also, for string concatenation, use the "&" operator instead of "+".  The plus sign indicates a math operation.  At the very least, your line should be changed to:

Recordset1.Source = "SELECT * FROM ri7Table WHERE ContactID = '" & Replace(Recordset1__MMColParam, "'", "''") & "'"

To answer your questions;
1.) ContactID is set to number in the database
2.) I am assuming that ContactID is passed when the client clicks "Send" on http://montanahandcrafters.com/Request_Information.asp ---But I really do not know.

I will try changing  Recordset1.Source as you suggested and let you know the outcome...Thanks for your help...

 




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

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

0
 
bunkers22Author Commented:
I received yet another error message;

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

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression

0
 
YSkellingCommented:
If it is a number remove the single quotes...

 ... WHERE ContactID = " & Replace(Recordset1__MMColParam, "'", "''")
0
 
Steve BinkCommented:
Is it the same line generating the error?  Since your ContactID field is numeric, your SELECT statement should look like this:

Recordset1.Source = "SELECT * FROM ri7Table WHERE ContactID = " & Recordset1__MMColParam

You do not need to use the Replace() function, since your ContactID SHOULD be all numeric.  If you are getting non-numeric text from the Request.Form("ContactID") reference,
then your problem exists on the previous page, which passes that value.

If you receive another error, try to pinpoint which line is causing it.  The error page that is produced should include a line number to give you an idea of where to start looking.
0
 
bunkers22Author Commented:
Set MM_editConnection = Server.CreateObject("ADODB.Connection")
 Dim DBPath,objRS,objConn
 Dim Recordset1  
Dim Recordset1__MMColParam
Recordset1__MMColParam = Request.Form("ContactID")
DBPath="/RI7data.mdb"
Set objConn=Server.CreateObject("ADODB.Connection")
objConn.ConnectionString="Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.mappath(DBPath)
objConn.Open
Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = objConn
Recordset1.Source = "SELECT * FROM ri7Table WHERE ContactID = " & Recordset1__MMColParam
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()                             'This is the line receiving the error
0
 
bunkers22Author Commented:
Anybody out there???
0
 
YSkellingCommented:
type this line before Recordset1.Open()

debug.print Recordset1.Source

What does it print ?
0
 
bunkers22Author Commented:
Microsoft VBScript runtime error '800a01b6'

Object doesn't support this property or method: 'debug.print'

/TMPxazyj5wfy7.asp, line 67


Where line 67 is

debug.print Recordset1.Source
0
 
YSkellingCommented:
what is in Recordset1__MMColParam ?  What is the value in it ?
0
 
bunkers22Author Commented:
I do not know, maybe It would help to understand that I used a Record Insert Wizard from DWMX2004,
I did not establish variables such as Recordset1_MMColParam, the wizard did.  I was able to fill a database using a DSN connection,using http://localhost/Request_Information.asp.
But after I uploaded the data (RI7data.mdb) to my  ISP they sent me a dsnless connnection code,

Anyways I Don't Know what exactly  is in Recordset1__MMColParam .....
0
 
bunkers22Author Commented:
Does everyone realize that I am trying to gather customer information and input that information from a form@ http://montanahandcrafters.com/Request_Information.asp, which is "form2" in opening code.
Plus ContactID is a hidden field with no assigned value, wheras all other values are to be inserted into database when
client clicks "Send"???
0
 
YSkellingCommented:
In this form, contactid is set to "", so if you gather it, it will be "", thus making this sql statement invalid :

SELECT * FROM ri7Table WHERE ContactID =

Can you use other information on the form, laike a combination of first and lastname plus emailaddress ?

Then do you the select like this :

Dim Recordset1__MMColParam1
Dim Recordset2__MMColParam2
Dim Recordset3__MMColParam3
Recordset1__MMColParam1 = Request.Form("FirstName")
Recordset1__MMColParam2 = Request.Form("LastName")
Recordset1__MMColParam3 = Request.Form("EmailAddress")


...
Recordset1.Source = "SELECT * FROM ri7Table WHERE FirstName= '" & Recordset1__MMColParam1 & "' and LastName = '" & Recordset1__MMColParam2  & "' and EmailAddress ='"  & Recordset1__MMColParam3 & "'"


Maybe some fine tunig for the replace of double quotes like there was in the initial code...

0
 
Steve BinkCommented:
I know exactly what you're trying to do, bunkers22.  A user will hit your web site, fill out an info form, and hit submit.  The form directs them to another ASP (the one we're working on) to handle their input and, I imagine, return some information to them.  I did not know you used a wizard to generate the page...that explains some of those variable names.  :)

First, try removing the () from Recordset1.Open().  They are unnecessary, and may be confusing the parser.  VB/VBScript/ASP does not require the placeholders.  Also, try commenting out the three lines between the .Source and the .Open.  Let's concentrate on opening the recordset before we worry about properties that may or may not affect the execution.  (.CursorType, .CursorLocation, .LockType)

At this point I've got to stop and ask how familiar you are with debugging ASP.  YSkelling's question about Recordset1__MMColParam was also my next...we need to know what it is in that variable before we can continue.
0
 
bunkers22Author Commented:
Sorry for the delay-- I was detained.

I removed the () from Recordset1.Open and now I get

ADODB.Command error '800a0e7d'

Requested operation requires an OLE DB Session object, which is not supported by the current provider.

/TMP14ubi5wkwu.asp, line 147 , Where line 147 is

MM_editCmd.ActiveConnection = MM_editConnection

I am not familiar at all with debugging asp, in fact this is the first time I've tried it.  How do I find out what is in the variable  Recordset1__MMColParam ??

0
 
bunkers22Author Commented:
Here is the latest version of the code,

<%


' *** 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 ContactID
Dim Recordset1__MMColParam
Recordset1__MMColParam = Request.Form("ContactID")
ContactID=Request.Form("ContactID")
Dim Recordset1__MMColParam1
Dim Recordset1__MMColParam2
Dim Recordset1__MMColParam3
Recordset1__MMColParam1 = Request.Form("FirstName")
Recordset1__MMColParam2 = Request.Form("LastName")
Recordset1__MMColParam3 = Request.Form("EmailAddress")

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 FirstName= '" & Recordset1__MMColParam1 & "' and LastName = '" & Recordset1__MMColParam2  & "' and EmailAddress ='"  & Recordset1__MMColParam3 & "'"


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

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

End If
%>
0
 
YSkellingCommented:
Now your query seems to work, you need a session, maybe someone else can help here, i am more familiar with vb and ado than with sessions...

Good luck ;-)
0
 
Steve BinkCommented:
Your first post said the error was on line 1.  The code you posted has line 2 marked.  I don't see anything wrong with line 1, so I'm going to recommend you change line 2:

       Set MM_editCmd = Server.CreateObject("ADODB.Command")
1.    MM_editCmd.ActiveConnection = MM_editConnection
       MM_editCmd.CommandText = MM_editQuery
       MM_editCmd.Execute
2.    MM_editCmd.ActiveConnection.Close  '************THIS IS LINE 147
2a.  MM_editCmd.Close                            '<------------------------------------THIS IS THE REPLACEMENT LINE 2

Debugging ASP without a development environment (like VBA in Access) is kind of a pain, but it is possible.  At the very beginning of the code, you would put this line:

On Error Resume Next

Then you would sprinkle something like this throughout the code at the points you want to check a variable:

Response.Write "MyVar equals: " & TheVariableIWantToWatch
OR
%>MyVar equals: <%= TheVariableIWantToWatch %><%

The first line will make any run-time errors non-fatal (it will not stop syntax errors), and either of the second lines will write the value of the variables to the
generated HTML.  The problem with this method is it requires a certain amount of intuition...the page may not be functioning correctly, and you have to take
that into account as you go through the code.  The code MUST execute entirely through the page, or you'll never see the actual response...you'll just see the
same error page you always do.  Most of the time I end up removing the later portions of the code, which is sort of like 'breaking' the program in the middle.
0
 
bunkers22Author Commented:
Microsoft VBScript compilation error '800a03ea' (Latest Error Code)

I Have added the following, could anyone let me know if I am on the right track,

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
0
 
bunkers22Author Commented:
Forgot to point out the offending line,


Microsoft VBScript compilation error '800a03ea'

Syntax error

/TMPuq47n608kw.asp, line 100

Recordset1.Source = "SELECT * FROM ri7Table WHERE ContactID= '" & Recordset1__MMColParam1 & "' and FirstName = '" & Recordset1__MMColParam2 & "' and LastName ='"  & Recordset1__MMColParam3 &
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------^
Thanks for any advice.............
0
 
YSkellingCommented:
Contact ID does not need single quites as it is an integer
0
 
bunkers22Author Commented:
Microsoft VBScript compilation error '800a03ea'

Syntax error

/TMP7653p61zd5.asp, line 100

Recordset1.Source = "SELECT * FROM ri7Table Where ContactID= " & Recordset1__MMColParam1 & " and FirstName = '" & Recordset1__MMColParam2 & "' and LastName ='"  & Recordset1__MMColParam3 &                                 '************etc,etc...
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------^
I took of the single quotes as suggested from YSkelling, and received the above error. Any other ideas would be greatly appreciated. Or should I start from scratch with a
new approach? Please advise...
0
 
YSkellingCommented:
Sorry can't help you anymore. This is too tough to debug from distance...

Good luck.

Yan.
0
 
bunkers22Author Commented:
Just wanted to thank everyone for their help ---I was not able to get the original code to work,
but I learned enough along the way to approach it from a different direction and was finally successful.

I appreciate all your efforts, points were awarded based on success rate of idea, ====Take Care,,,,
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 19
  • 8
  • 7
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now