Classic ASP Database error

Question

Well I am querying my DB, a table called bookBilling, to get a value under the column of billingID.

In my first query I get the customer ID from a table based on what value the cookie holds. In my second query I take that custID value and am looking to get the billingID associated with it.

query = "SELECT custID FROM bookSession WHERE session='"&theCookie&"'"
'--Connect to DB'
 Set objConn = ConnectDB()
'--Query to DB'
 Set objRS = objConn.Execute(query)
 custID = objRS.Fields("custID")

  query = "SELECT billingID FROM bookBilling WHERE custID="&custID&""
  objConn.Execute(query)

Here is where it becomes an issue. I tried using this to obtain the value:

billingID = objRS.Fields("billingID")

When that didn't work I printed the query to my browser to make sure it worked and it came back good, I checked it in SQL Server Management Studio to make sure. After that I decided to play with it a bit and found that using "0" as the reference works since it is the first item in my table.

billingID = objRS.Fields(0)

So that worked, I know my table column header is correct I have looked at it and used it in other queries before. Does anyone know why this might happen? Is it a result of not clearing my connection or closing it afterward?

Thanks

Answer 1

Running two separate queries is slow anyway. It's almost always faster to combine them into one statement:

SELECT billingID
FROM bookBilling bb
INNER JOIN bookSession bs ON bs.custID=bb.custID
WHERE bs.session= @theCookie

Also: cookies are just text files, and anyone can edit a text file. If you substitute a cookie value directly in your query like that there is the potential for sql injection. It's not the normal attack vector, but it's still possible.

As to your specific error, you execute the 2nd query directly from the connection rather than opening it in the record set:

objConn.Execute(query)

I'm surprised you get anything at all, and I expect the value you're seeing in objRS.Fields(0) is probably just the custID from the previous query. But that all becomes moot if you consolidate the queries like I recommended.

Answer 2

Hey, is it a typo that you don't have this?

query = "SELECT billingID FROM bookBilling WHERE custID="&custID&""

objRS = objConn.Execute(query)

To reload the recordset with the data and definition of the second query.....

Just a thought, try Setting/Instantiating the ObjRS to a Recordset first, then apply the query.execute to it after, rather than the initial all in one with the first CustId query

Answer 3

query = "SELECT billingID FROM bookBilling WHERE custID="&custID&""
objConn.Execute(query)

You're not opening a resordset for that second query. And, not sure if that has any influence at all, but I never name my queries identical. To be sure I guess.

Edit, well that's kind of what the people above me said, I'm a Slowpoke.

Answer 4

Damn, yes it was a typo. I'm not sure I understand why it worked using the column number still but not the column name even though I didn't open the recordset.

@Joel Coehoorn Thanks for the info on inner joining the queries. My teacher has been telling us to do that but I have not really understood it enough to do it until I saw that.

For my table I can't really use it though because there isn't necessarily a billingID upon registration so I would run into some "EOF's" occasionally.

Thanks everyone!