Error on the Example shown on the book

tarzannn · January 15th, 2006, 02:08 AM

Hi, there?
I'm trying to run the example code from the wrox book called, "Beginning ASP databases".

In chapter 5, there is an example code as follows and just try to copy and to paste running this code I got into error message.

The error message is

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
ODBC driver does not support the requested properties.

From the sql text line, SQLtxt = "Select * FROM Items WHERE ItemVendor = " & vVendor & ";"
if I delete vVendor the error message disappear.
Is that because vVendor set up as QueryString value??? How do I make this code working?

<html>
<head>
<title>Chapter_05_MoreRS_Exercise_3_Vendor_Items_R esponse</title>
</head>
<body>
<h2>Chapter 05 MoreRS Exercise 3</h2>
<h3>Exercise #3 Vendor Items Response</h3>
<%
  dim vVendor
  vVendor = Request.QueryString("lstVendors")

  dimDim oRSi
  set oRSi=server.CreateObject("ADODB.Recordset")
  SQLtxt = "Select * FROM Items WHERE ItemVendor = " & vVendor & ";"
  oRSi.Open sqltxt, "DSN=clothier"
  oRSi.movef.MoveFirst

  Response.Write "<TABLE BORDER=1><TR>"
  ' Header row
  For Each oHeader in oRSi.Fields
    Response.Write "<th>" & oHeader.name & "</th>"
  Next
  Response.Write "</TR><TR><TD>"
  ' Data rows
  Response.Write oRSi.GetString(,,"</td><td>","</TD></TR><TR><TD>")
  Response.Write "</TD></TR></Table>"
  oRSi.Close
  Set oRSi=nothing
%>
</body>
</html>

Imar · January 15th, 2006, 04:19 AM

Quote:

quote:Is that because vVendor set up as QueryString value???

Yeah, it looks like that's the problem, so vVendor never gets a value.

Maybe you're not supposed to request this page directly? Instead, look for a previous page with a form and at least a drop-down list (or other HTML form control) called lstVendors that submits to this page...

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

tarzannn · January 16th, 2006, 12:36 AM

Thanks for your advice, Imar.
I did use Drop-down list and actually I'm trying to run the code from Excercise.
But I'm getting the error message and any other advice??

Imar · January 16th, 2006, 03:16 AM

Hi again,

In that case, can you post the complete code for both pages?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

tarzannn · January 16th, 2006, 03:36 AM

Thanks for so much for your help again.

Here is the code where it calls the data from DB for the select option.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="949"%>

<HTML>
<Head>
<TITLE> </TITLE>
</Head>
<body>
<%
    Dim objRS
    Set objRS = Server.CreateObject ("ADODB.Recordset")

    sqlstr = "SELECT xxx from xx ORDER BY xx ASC"

    objRS.Open sqlstr, dbCon, 1

    objRS.MoveFirst
%>
<form method="get" action="xxxxx.asp">
<p><Select Name="DealerCode" Size="6">
<%
    Do While NOT objRS.EOF
        Response.Write "<Option Value='"& objRS("xxx") & "'>"
        Response.Write objRS("xxxx") & "</Option>"
        objRS.MoveNext
    Loop

    objRS.Close
    Set objRS = Nothing
%>
</SELECT> </P>
<p><INPUT TYPE="submit"> </P>
</form>
</body>
</html>

And the xxx.asp is as follows,

<%@LANGUAGE="VBSCRIPT" CODEPAGE="949"%>

<HTML>
<HEAD>
<TITLE>:::</TITLE>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>
<%
    Dim sDealer
    sDealer = Request.QueryString ("DealerCode")

    sqlstr = "SELECT x_xxfrom xxx WHERE xxxxxx= '" & sDealer & "';"

    rs.Open sqlstr, dbCon, 3, 3
    rs.MoveNext

    if rs.EOF = false then

%>

<%
else
    Response.Write "<TABLE BORDER=1><TR><TD ALIGN=CENTER>"
    Response.Write rs.GetString(,,"</TD><TD ALIGN=CENTER>","</TD></TR><TR><TD ALIGN=CENTER>","-unknown-")
    Response.Write "</TD></TR></TABLE>"

    rs.Close
    set rs = nothing

End if

%>
</BODY>
</HTML>

Imar · January 16th, 2006, 03:16 PM

Hmm can't see any obvious error...

On the first page, do you see items in the drop-down list before you click the submit button?

To diagnose the second page, make the following change and post the output to this thread:

sqlstr = "SELECT Dealer_Code from tblDealerInfo WHERE Dealer_Code = '" & sDealer & "';"

   Response.Write("Sql is " & sqlstr)
   Response.End()
   rs.Open sqlstr, dbCon, 3, 3
   rs.MoveNext

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

tarzannn · January 16th, 2006, 09:05 PM

Hello, Imar, yes, I am able to read the data for the select option.

Okay, it displays.. "Sql is SELECT xxxx from xxxx WHERE xxxx = 'xxxx';" but it doesn't the display the result of the select query.
How come it doesn't display the query table?

Imar · January 17th, 2006, 03:49 AM

Right, I thought it was still displaying an error like "ODBC driver does not support the requested properties" so I was looking for a reason for that error.

Apparently, something changed somehow for the better, without you telling us.

Anyway, look at this:

rs.Open sqlstr, dbCon, 3, 3
rs.MoveNext

if rs.EOF = false then

You select one record, then do a MoveNext (effectively changing the recordset's pointer to the second record) and then check for EOF. Since there was only one record, EOF will now be True and the record won't be displayed.

Whenever you open a recordset in ADO, it's ready for use; there is no need to use MoveFirst or MoveNext first. So the fix is:

rs.Open sqlstr, dbCon, 3, 3
<s> rs.MoveNext</s>
if rs.EOF = false then

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

tarzannn · January 17th, 2006, 10:52 PM

Hello, Imar?
The problem got solved and You're the man!!
Thank you so much,
Best Regards,
Tarzannn.