Recordset Error WHERE IDNmbr = " & SurveyID

First web page saves the data to the table LOTADATA, just fine.
I pass SurveyID from first to second page.
I want to use that ID to show the field Score on the second web page, but get an error using this code.
**THANK you for any help**JOHN

SurveyID = Request("SurveyID")

<%
Dim LOTADATA
Dim LOTADATA_numRows

Set LOTADATA = Server.CreateObject("ADODB.Recordset")
LOTADATA.ActiveConnection = MM_ConnectOpt_STRING
LOTADATA.Source = "SELECT Score  FROM LOTADATA  WHERE IDNmbr = " & SurveyID
LOTADATA.CursorType = 0
LOTADATA.CursorLocation = 2
LOTADATA.LockType = 1
LOTADATA.Open()

LOTADATA_numRows = 0
%>

<p align="center">Your score was <%=(LOTADATA.Fields.Item("Score").Value)%>.</p>
obernaiAsked:
Who is Participating?
 
Irwin SantosConnect With a Mentor Computer Integration SpecialistCommented:
"SurveyID = Request("SurveyID")"  <<<<<<<<<<<<<<<<<< SYNTAX IS NOT CORRECT!!!!!  >>>>>>>>>>>>>>>>>>>>>

SurveyID = Request.QueryString("SurveyID")

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

Tells me so.  that you want to put a value in your query...however due to the incorrect syntax and subsequent value in SurveyID...that cannot be done.
0
 
Irwin SantosComputer Integration SpecialistCommented:
"SELECT Score  FROM LOTADATA  WHERE IDNmbr = " & SurveyID

-----------------------
use this
-----------------------
SELECT Score, IDNmbr FROM LOTADATA WHERE IDNmbr = '" & SurveyID & "'"
0
 
Irwin SantosComputer Integration SpecialistCommented:
Add these 2 items to your code at the very beginning...(1st & 2nd line)

<% @ LANGUAGE = VBSCRIPT%>
<% Option Explicit %>

Then in Internet Explorer, go to TOOLS-INTERNET OPTIONS - ADVANCE tab, then locate "SHOW FRIENDLY HTTP ERROR MESSAGES", UNCHECK that box.

Post back any errors (exact errors)
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
obernaiAuthor Commented:
Here is the error I am getting:

Microsoft VBScript runtime error '800a01f4'

Variable is undefined: 'SurveyID'

/lot_finish.asp, line 10
0
 
obernaiAuthor Commented:
I changed the code to this:

<%@LANGUAGE="VBSCRIPT"%>
<% Option Explicit %>
<!--#include file="Connections/ConnectOpt.asp" -->

<%
Dim LOTADATA
Dim LOTADATA_numRows
Dim SurveyID
SurveyID = Request("SurveyID")
Set LOTADATA = Server.CreateObject("ADODB.Recordset")
LOTADATA.ActiveConnection = MM_ConnectOpt_STRING
LOTADATA.Source = "SELECT Score, IDNmbr FROM LOTADATA  WHERE IDNmbr = '" & SurveyID & "'"
LOTADATA.CursorType = 0
LOTADATA.CursorLocation = 2
LOTADATA.LockType = 1
LOTADATA.Open()

LOTADATA_numRows = 0

Here is the error I get now:

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

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

/lot_finish.asp, line 18
0
 
Irwin SantosComputer Integration SpecialistCommented:
ok.. what is the datatype for IDNmbr?

also

SurveyID=Request("SurveyID") is NOT correct in syntax.. if you are calling from a form, thenuse

Request.Form("SurveyID"), if from a hyperlink

Request.Query("SurveyID")

0
 
obernaiAuthor Commented:
IDNmbr is Numeric
0
 
Irwin SantosComputer Integration SpecialistCommented:
did you apply my recent comment?
0
 
obernaiAuthor Commented:
Yes, no difference.
0
 
Irwin SantosComputer Integration SpecialistCommented:
what does your code look like now?


instead of
SurveyID = Request("SurveyID")

put

SurveyID = 1

(or some other valid value in your table)
0
 
obernaiAuthor Commented:
The value of SurveyID is a numeric value.

The code
SurveyID = Request("SurveyID")
is just calling for the value from the previous form page so that all the records in the DB tables are linkable via the IDNmbr table field.

I have a table called IDCreator where it has one numeric field that is increased by one after each user completes the first page of the survey.  This number is passed to each page (as 'SurveyID' and entered into each table as IDNmbr).


So on the previous page, the code for this process is this:

    If (MM_editRedirectUrl <> "") Then
       If (MM_editRedirectUrl <> "") Then
      MM_editRedirectUrl = MM_editRedirectUrl & "?SurveyID="& SurveyID &"" 
                         'the ID is passed to Next Page as query string
      Response.Redirect(MM_editRedirectUrl)
    End If

I have been using this method for several years without a problem.  So I can have multiple users completing the survey at the same time.

I have never tried to load a field specific to the user's IDNmbr though....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.