query problems

[gemoon]

I am new to asp so please forgive me if the solution to this problem is obvious.

I have two asp pages, index.asp and detail.asp. index.asp queries an access database then writes text with a link to the second page. When the link is followed an id # is passed to the second page. detail.asp then queries the database using the id# and writes the results.

The first page is working correctly, but the second is giving me troubles. I think the problem is in how ive written the query. At first i was getting a type mismatch error. Now I am getting an error that says "Item cannot be found in the collection corresponding to the requested name or ordinal".

The code is as follows
From index.asp
<%
'Define variables and initialize database connection:
Dim adoCon 'Holds the Database Connection Object
Dim rsReadDetail 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query to query the database
Dim ID 'Variable to pass ItemID to next page
Set adoCon = Server.CreateObject("ADODB.Connection")'Create an ADO connection object
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("auction.mdb")'Set an active connection to the Connection object using a DSN-less connection
Set rsReadDetail = Server.CreateObject("ADODB.Recordset") 'Create an ADO recordset object

'strSQL variable with an SQL statement then open recordset: "other" items "rank=2"
strSQL = "SELECT qryDetail.ItemID, qryDetail.Item From qryDetail WHERE (((qryDetail.RankID)=2));"
rsReadDetail.Open strSQL, adoCon'Open the recordset with the SQL query

'Loop through recorset and display "minor" items
Do While not rsReadDetail.EOF

'display the current record in the recordset
Response.Write ("&#8226; ")
Response.Write ("<a href='detail.asp?ID=")
Response.Write (rsReadDetail("ItemID"))
Response.Write ("'>")
Response.Write (rsReadDetail("Item"))
Response.Write ("</a>")
Response.Write ("<p>")
rsReadDetail.MoveNext

Loop
'Close recordset and clear string
adoCon.close
Set rsReadDetail = Nothing
set adoCon = Nothing
%>

From detail.asp
<%
'Define variables and initialize database connection:
Dim adoCon 'Holds the Database Connection Object
Dim rsReadDetail 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query to query the database
Set adoCon = Server.CreateObject("ADODB.Connection")'Create an ADO connection object
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("auction.mdb")'Set active connection to Connection object using a DSN-less connection
Set rsReadDetail = Server.CreateObject("ADODB.Recordset")'Create an ADO recordset object

strSQL = "SELECT qryDetail.ItemID, qryDetail.Image, qryDetail.Item, qryDetail.Description, qryDetail.Value, qryDetail.CurrentBid, qryDetail.MinimumBid From qryDetail WHERE ItemID='"&Request.Querystring("ID")&"';"

'display the current record in the recordset
Response.Write ("<img src='images/")
Response.Write (rsReadDetail("Image"))
Response.Write ("' border='0' align='absbottom'>")
Response.Write ("<p>")
Response.Write (rsReadDetail("Item"))
Response.Write ("<br>")
Response.Write (rsReadDetail("Description"))
Response.Write ("<p>")
Response.Write("<strong>")
Response.Write (rsReadDetail("Value"))
Response.Write ("<Br>")
Response.Write ("Current Bid $")
Response.Write (rsReadDetail("CurrentBid"))
Response.Write ("<Br>")
Response.Write ("<a href='bid.asp'>Bid Now</a>")
Response.Write ("</Strong>")
Response.Write ("<hr width='100%' size='1' noshade color='#df8422'>")
Response.Write ("<p>")

'Close recordset and clear string
adoCon.close
Set rsReadDetail = Nothing
set adoCon = Nothing
%>


Thanks in advance for any advice.
Ed.

 

[DreXor]

i would assume the ITEMID is a numeric type? try removing the single quotes around the inserted value in your SQL statement if this is the case.

strSQL = "SELECT qryDetail.ItemID, qryDetail.Image, qryDetail.Item, qryDetail.Description, qryDetail.Value, qryDetail.CurrentBid, qryDetail.MinimumBid From qryDetail WHERE ItemID=[COLOR=black red]'[/color]"&Request.Querystring("ID")&"[COLOR=black red]'[/color];"

also in your second page, you never open the created recordset, you define the SQL but never execute it. ( see the first page sequence )

thirdly, to save typing and possible typo issues when working on code :

strSQL = "SELECT [COLOR=red black]qryDetail.[/color]ItemID, [COLOR=red black]qryDetail.[/color]Image, [COLOR=red black]qryDetail.[/color]Item, [COLOR=red black]qryDetail.[/color]Description, [COLOR=red black]qryDetail.[/color]Value, [COLOR=red black]qryDetail.[/color]CurrentBid, [COLOR=red black]qryDetail.[/color]MinimumBid From qryDetail WHERE ItemID='"&Request.Querystring("ID")&"';"

all the items in black can be removed, you're only selecting from one table, so you dont need to tell it table.field for every item you're wanting, if you were selecting from more than one table like a join or crosstab, then you would need those table.field flags for fields between the tables that might have the same name for solid distinctions

========
and
========

these two lines ....
Set rsReadDetail = Server.CreateObject("ADODB.Recordset")'Create an ADO recordset object
rsReadDetail.Open strSQL, adoCon'Open the recordset with the SQL query

can be scratched down to just :
Set rsReadDetail = adoCon.Execute(strSQL)

since you're just doing simple reads from the db

other than that, for just starting out, you pretty much got the hang of it, you're making some of the simple mistakes most of us make daily

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[Dashley]

Ed

Drex is correct. You have to open the recordset or you will get that error. Youll also get it if the RS is empty.
I test the rs state (1=Open 0=closed) beore using it.
Keeps you from getting a rs already open error. Here's a sample of another way to handel Rs's

Good luck to ya,


Dan


with rs1
if RS1.state=1 then rs1.close
.active connection= adoCon
.source= strSql
.Open
end with

While not rs1.eof

response.write rs1(My Item)

wend


Rs1.close
SetRS1=Nothing

 

[gemoon]

As I thought the answer was obvious. Thanks for the second and third set of eyes. ItemID is numeric so removing the single quotes fixed part of the problem. When I wrote the code, emulated what I saw on the help forums. What is the deal with quotes and &? Do single quotes designate text strings? Exicuting the recordset solved the second half of my problem. I think at one time i did have the recordset opening, but inadvertently deleted the text while trying to deal with the original type mismatch.

Does anyone recomend a good source for learning asp syntax, either online or book?

Thanks again for the help.
Ed.

 

[Genimuse]

Yes, in SQL (the language that your query is in), single-quotes (aka apostrophes) denote the beginning and ending of a string. Two single-quotes in a row denote an actual single-quote as part of a string, like:

SELECT * FROM Customers WHERE lname = 'O''Hara'

I learned from books, but the W3Schools is a good reference and tutorial:

http://www.w3schools.com

 

[Dashley]

Ed heres a little function you can use that will automatically double up your appostrophies for your sql.

As far as books go Cybex and O'Riely (publishers) usally put out pretty good books. On and offline I use MSDN (Microsoft.com). You can always use Tek-Tips. I've always had great help from folks here.

Dan



Function padQuotes( instring )

Dim bodybuild
Dim bodystring
Dim Length
Dim i

bodybuild = ""
bodystring = instring
Length = Len(bodystring)
For i = 1 to length
bodybuild = bodybuild & Mid(bodystring, i, 1)
If Mid(bodystring, i, 1) = Chr(39) Then
bodybuild = bodybuild & Mid(bodystring, i, 1)
End If
Next
bodystring = bodybuild
padQuotes = bodystring
End Function


BTW CHAR(39) is an apostrophie.

Dan

 

[Genimuse]

I recommend

Replace(instring, "'", "''")

instead of that function... it's much, much faster. If you want it as a function (to make the code cleaner) you could use:

Function padQuotes(instring)
    padQuotes= Replace(instring, "'", "''")
End Function

Either way it's way, way faster than calling Mid() 2 to 3 times for every character in the string, and concatenating a string length times.