Classic ASP Database Error
Well, I am accessing my DB, bookBilling table, to get the value under the billingID column.
In my first request, I am getting the client id from the table based on what the cookie has value. In my second request I am taking this custID value and want to get the associated billingID.
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)
This is where it gets into trouble. I tried to use this to get the value:
billingID = objRS.Fields("billingID")
If that didn't work, I printed the request in my browser to make sure it works and it returned well, I checked it in SQL Server Management Studio to make sure. After that I decided to play around with it a bit and found that using "0" as this link works as it is the first item in my table.
billingID = objRS.Fields(0)
So it worked, I know the table column header is correct, I looked at it and used it in other queries before. Does anyone know why this might be happening? Is this the result of not clearing my connection or closing it after?
thank
source to share
Hey, is that a typo, why don't you have that?
query = "SELECT billingID FROM bookBilling WHERE custID =" & custID & ""
objRS = objConn.Execute (query)
To reload recordset with data and definition of second query .....
Just think, first try to set / initialize ObjRS on Recordset and then apply the .execute request to it, not the initial all in one with the first CustId request
source to share
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 only text files and anyone can edit the text file. If you substitute the cookie value directly into your query, then there is potential for SQL injection. This is not a common attack vector, but it is still possible.
As for your specific error, you are executing the second query directly from the connection, rather than opening it on a recordset:
objConn.Execute(query)
I'm surprised you got anything at all, and I expect that the value you see in objRS.Fields(0)
might just be custID
from a previous request. But all of this becomes controversial if you consolidate queries as I recommended.
source to share
query = "SELECT billingID FROM bookBilling WHERE custID="&custID&""
objConn.Execute(query)
You are not opening a request for a second request. And, not sure if this has any impact at all, but I never call my queries the same. Of course I think.
Edit, ok that what the people above me said I am Slowpoke.
source to share
Hell yes, that was a typo. I'm not sure I understand why it worked using the column number but not the column name, although I didn't open the recordset.
@Joel Coehoorn Thanks for the info on internal request join. My teacher told us about it, but I didn't understand it enough until I saw it.
For my spreadsheet, I cannot use it, although due to the fact that the billing ID is not required during registration, I sometimes came across some "EOF".
Thanks everyone!
source to share