Solved

Connection String Blues

Posted on 2004-10-19
37
709 Views
Last Modified: 2008-01-09
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
Comment
Question by:bunkers22
  • 19
  • 8
  • 7
  • +2
37 Comments
 
LVL 50

Assisted Solution

by:Steve Bink
Steve Bink earned 275 total points
ID: 12351992
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
 
LVL 34

Expert Comment

by:flavo
ID: 12352016
0
 

Author Comment

by:bunkers22
ID: 12352165
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
 
LVL 50

Assisted Solution

by:Steve Bink
Steve Bink earned 275 total points
ID: 12352237
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
 

Author Comment

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

Assisted Solution

by:Arthur_Wood
Arthur_Wood earned 50 total points
ID: 12352539
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
 

Author Comment

by:bunkers22
ID: 12352616
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
 
LVL 2

Expert Comment

by:YSkelling
ID: 12353256
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
 

Author Comment

by:bunkers22
ID: 12358376
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
 
LVL 50

Expert Comment

by:Steve Bink
ID: 12359207
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 12359733
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
 

Author Comment

by:bunkers22
ID: 12360021
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
 
LVL 50

Expert Comment

by:Steve Bink
ID: 12360036
No, you should do this:

Recordset1__MMColParam = Request.Form("ContactID")

Assuming your hidden field is named ContactID.
0
 

Author Comment

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

Author Comment

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

Author Comment

by:bunkers22
ID: 12361300
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
 
LVL 2

Expert Comment

by:YSkelling
ID: 12361690
If it is a number remove the single quotes...

 ... WHERE ContactID = " & Replace(Recordset1__MMColParam, "'", "''")
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 12361726
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

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

Author Comment

by:bunkers22
ID: 12362577
Anybody out there???
0
 
LVL 2

Expert Comment

by:YSkelling
ID: 12362665
type this line before Recordset1.Open()

debug.print Recordset1.Source

What does it print ?
0
 

Author Comment

by:bunkers22
ID: 12362715
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
 
LVL 2

Expert Comment

by:YSkelling
ID: 12362828
what is in Recordset1__MMColParam ?  What is the value in it ?
0
 

Author Comment

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

Author Comment

by:bunkers22
ID: 12363124
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
 
LVL 2

Accepted Solution

by:
YSkelling earned 175 total points
ID: 12363249
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
 
LVL 50

Assisted Solution

by:Steve Bink
Steve Bink earned 275 total points
ID: 12363272
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
 

Author Comment

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

Author Comment

by:bunkers22
ID: 12363891
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
 
LVL 2

Expert Comment

by:YSkelling
ID: 12364956
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
 
LVL 50

Assisted Solution

by:Steve Bink
Steve Bink earned 275 total points
ID: 12371725
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
 

Author Comment

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

Author Comment

by:bunkers22
ID: 12385252
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
 
LVL 2

Expert Comment

by:YSkelling
ID: 12386706
Contact ID does not need single quites as it is an integer
0
 

Author Comment

by:bunkers22
ID: 12389790
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
 
LVL 2

Expert Comment

by:YSkelling
ID: 12391093
Sorry can't help you anymore. This is too tough to debug from distance...

Good luck.

Yan.
0
 

Author Comment

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
identify all unused queries, forms and reports 10 29
Access MDB/PDF 21 32
Code editor Problem 8 16
Error in query 3 0
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
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…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

11 Experts available now in Live!

Get 1:1 Help Now