ADODB.Field (0x80020009) error driving me crazy 2

[cr84net2]

I am using sql server express and have two Select Statements on the same asp page. I have used the same statements on another page to gather similar data without a hitch. The only real difference I can see is that I am using a parameter in the Where clause of each that is fed by Request.Querystring on this page but with Request.Form on the other.

I keep getting this empty recordset error:

Error Type:
ADODB.Field (0x80020009)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

I have Response.Write the sql and it looks fine, i used it in the query analyzer and I get the proper results.

There are only a few records in each table so I exported teh data from each as csv and lined up my select statement under each column name to assure they were identical. Then deleted records (from the csv) that didn't match the criteria and the only ones left are the records that I expect to be in the recordset.

I have changed the way I apply the parameter several times with no luck.

Oh, I also check for the end of the recordset but it never seems to get that far.

First Select:

'zid=Request.Querystring("ID")

Set Lcrs = Server.CreateObject("ADODB.RecordSet")

LSql="Select [ID], [teamname], [leaguedesc] FROM [leagueteam] Where [ID]='" & Request.Querystring("ID") & "'"


 If Lcrs.EOF Then
 Response.Write "Sorry, either your league has been deleted or it was never created."

There is an end if later.

I also tried

Set Lcrs= conn.Execute(LSql)

And continue to get the same error.

I have also tried zid=CLng(Request.Querystring("ID"))
without any luck.

Any help would be appreciated.

 

[worldwise]

I see a few things off the bat:

1) If ID is a numeric field, then you do not need to surround it with single quotes.

2) I would do a Trim() on the request.querysting. Some browsers add white space before or after which could throw things off. So. Trim(Request.Querystring("ID"))

3) I don't see you actually opening a connection object or setting the recordset. I'm assuming you're doing this but just not writing it in the code above?

4) As a safety precaution against SQL injection, I would alter the part of the code where you get the ID from the querystring. I would do this:
Replace(Trim(Request.Querystring("ID")),"'","")

 

[cr84net2]

Thank you for your help, i tried your suggestions a couple of ways but still got the same error. I'll answer your questions...

1. Yes it is numeric. I have tried it with and without the single quotes without any luck.

2. I did try the Trim(Request.Querystring("ID") before but I had not tryed the replace. I have implemented the replace now both with the variable zid=Replace(Trim(Request.Querystring("ID")),"'","")

and then my select statement like this...

LSql="Select [ID], [teamname], [leaguedesc] FROM dbo.leagueteam Where [ID]=" & CLng(zid)

and like this

LSql="Select [ID], [teamname], [leaguedesc] FROM dbo.leagueteam Where [ID]=" & zid

Without any luck..I still get the same error

Oh, and 3. Yes I am opening the connection object above.

Any other ideas would be greatly appreciated, thanks for your help.

 

[worldwise]

First try hardcoding the SQL string. As in, don't take in a variable from the querysting but instead type in an ID that you know will give you back results.

If that doesnt work, post the entire relevant code and tell me which line is causing the error. i suspect you arent setting the recordset correctly or something.

 

[cr84net2]

I have tried hardcoding the variable to no avail...same error. Here is the code...

The EW_DB_CONNECTION_STRING is established in an include like this:

EW_DB_CONNECTION_STRING = "Provider=SQLOLEDB;Persist Security Info=False;Data Source=(local)\SQLEXPRESS;Initial Catalog=xxxxxxx;User Id=xxxxxxx;Password=xxxxxxxxx"

<%
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open EW_DB_CONNECTION_STRING
%>

<%
'Check for user level and call function
'------------------------------------
If Request.QueryString("key") = 2 Then
Call lcreate()
End If 
%>

<%
 'League Manager process
 '----------------------------------------
If Request.Querystring("mb")<> "" Then
 Response.Write "Your League action has been processed."
 End If
 %>



<%
'===============================
Function lcreate()

zid=Replace(Trim(Request.Querystring("ID")),"'","")

Set Lcrs = Server.CreateObject("ADODB.RecordSet")

LSql="Select [ID],[teamname], [leaguedesc] FROM [leagueteam] Where [ID]=" & CDBL(zid)
'Response.Write LSql & "<br>"
'Response.End 

Lcrs.Open LSql, conn, 3, 2, 1


If Lcrs.EOF Then 
Response.Write "Cannot find matching record."
Else



%>
<div align="center">
  <table border="2" width="75%" id="table1" bordercolor="#000000">
    <tr>
      <td width="33%">
      <p align="center"><b>League Name</b></td>
      <td width="50%" >
      <p align="center"><b>League Description</b></td>
      <td width="17%">
      <p align="center"><b>ID</b></td>
    </tr>
    <tr>
      <td width="33%" >
      <p align="center"><%=Lcrs("teamname")%></td>
      <td width="50%" >
      <p align="center"><%=Lcrs("leaguedesc")%></td>
      <td width="17%">
      <p align="center"><%=Lcrs("ID")%></td>
    </tr>
  </table>
</div>

<br>
<div align="center">
<table border="1" width="75%"  bordercolor="#000080">
   <td width="50%">
      <p align="center"><b>League Players</b></td>
      <td width="20%">
      <p align="center"><b>Approved</b></td>
      <td width="20%">
      <p align="center"><b>Approve/Deny</b></td>
    </tr>
  
    
 <% 
 End If  
 Set Rrs = Server.CreateObject("ADODB.RecordSet")

qSql="Select [leagueplayer], [ltID], [playerID], [approved] From [leagueplayers] where [ltID]=" & CLng(zid)

'Response.Write qSql & "<br>"
'Response.End 
Rrs.Open qSql, conn, 3, 2, 1



 
 If Rrs.EOF Then
 Response.Write "There are no players listed for this league at this time"
 Else
 %>
  <form name="updateleague" method="Post" action="jleague.asp" >
 <%
While Not Rrs.EOF
 
 %>
 <tr>
      <td class=""centerTxt"" width="70%"><%=Rrs("leagueplayer")%></td>
      
      <%If Rrs("approved")= True Then
      Response.Write "<td class=""centerTxt"" width=""30%"">Yes</td>"
      Else If Rrs("approved")= False Then
      Response.Write "<td class=""centerNTxt"" width=""30%"">No</td>"
      Else
      Response.Write "<td class=""centerPTxt"" width=""30%"">Pending</td>"
      End If
    
      %>
      <td class=""centerTxt"" width="70%"><SELECT NAME="action">
	                                       <OPTION VALUE="0" SELECTED>Select One</OPTION>
	                                       <OPTION VALUE="1">Approve</OPTION>
	                                       <OPTION VALUE="2">Deny</OPTION>
	                                    </SELECT>
	               </td>
    </tr>
     
 <% 
 End If
 
 Rrs.MoveNext 
	  Wend
	%>
  </table>
</div>
<br>
<div align="center">
  <table border="0" width="75%" >
    <tr>
      <td class=""centerTxt"" >
      Please Approve or Deny only one player at a time, in this way we can 
      be certain that your league players will be properly processed. </font></td>
    </tr>
  </table>
</div>

<br>
<%
 End If
%>
<div align="center">
  <table border="0" width="75%">
    <tr>
      <td colspan="3">
     
      <input type=hidden name="ID"  value="<%=zid%>" >
      <input type=hidden name="playerID"  value="<%=Rrs("playerID")%>" >
        </td>
    </tr>
    <tr>
      <td align="center"><INPUT TYPE=submit value="Update League" NAME=Submit style="font-family: sans-serif; font-size: 10pt; border: 1px ridge #000080; padding-left: 4px; padding-right: 4px; padding-top: 1px; padding-bottom: 1px; background-color: #FFFFFF"> </td>
    </tr>
  </table>
</div>
<%


Lcrs.Close
Set Lcrs = Nothing
Rrs.Close
Set Rrs = Nothing

End Function
'------------------------


'close the db  connections  
'-------------------------   
'End If
conn.Close
Set conn = Nothing
%>

The query string passes pagename.asp?ID=4&key=2

The ID is an example but the key in this case is static.

Thanks again, I really appreciate your help

 

[cr84net2]

In case anybody is still looking at this post, I have taken the bottom portion or the second recordset call out of the function to test it and the top Select statement is working fine. So the problem is with the bottom Select statement. I do have a question regarding the second statement. The approved field is a bit field being used like a boolean field so it is sometimes blank. Of the records I currently have in the db 2 are True the other two show nothing, could this be seen as null values and throw this error?? I wouldn't think it would since the same select statement works on another page. I thought anyone looking at this should know.. Thanks again for your help.

 

[gmmastros]

well... that makes sense.

The problem is that NULL values often times cause problems with ASP code. You could modify your query so that NULL defaults to true or false. You would do that like this...

qSql="Select [leagueplayer], [ltID], [playerID], [!]Coalesce([/!][approved][!], 0) as approved[/!] From [leagueplayers] where [ltID]=" & CLng(zid)

Coalesce will swap out null values for you. If the value of approved is true, you'll get true. If the value is false, you'll get false. If the value is NULL, then Coalesce will return the value in the second parameter.

In the example I show above, NULL values will return as False. If you prefer to have them be true, then replace the 0 with a 1.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cr84net2]

Thank you George, I really appreciate your help.

What I am doing here is setting up a league owner so he can see who has attempted to join his league. Of course this field is null until the league owner approves or denies someone who is trying to join. If he approves then the field displays as "Yes", if he denys the person entrance to his league it shows "No" but if the league owner hasn't acted yet (the field would be null) it displays as "Pending".

This is the code where I determine what to display:

<%If Rrs("approved")= True Then
      Response.Write "<td class=""centerTxt"" width=""30%"">Yes</td>"
      Else If Rrs("approved")= False Then
      Response.Write "<td class=""centerNTxt"" width=""30%"">No</td>"
      Else
      Response.Write "<td class=""centerPTxt"" width=""30%"">Pending</td>"
      End If
    
      %>

Since it is for display only could a null field be associated with something other than true or false so I can use this for the display. I am not sure how to go about this. In other words could this be done:

Coalesce([approved], 2) as approved

I see the logic behind 0 and 1 and I am probably hoping for a little to much luck here.

Oh, also what does this do if the field is true and we use 0, do I understand that it will only affect null values?

I hope this makes sense. Thanks again

 

[cr84net2]

arrrgggghhhh...... I tried the statement provided by George and received the same error. Then I removed 'approved' from the statment and it still gave me the same error. So, either the approved field isn't the problem or it isn't the only problem...This is driving me nuts!!!

 

[gmmastros]

Let me try to explain this so it makes sense.

With SQL Server, a bit column can only contain 1 of 3 values.
1 = true
0 = false
NULL = unknown

Coalesce will only affect null values.

If you use Coalesce(Approved, 2), the data type returned in the sql query will change from a bit (think boolean) to an integer data type.

So, you could use....

Coalesce(Approved, 2) As Approved

In your query, but then you would have to change your ASP code. You will no longer be able to compare the data as though it were boolean. But... You will be able to treat it as an integer. Like this...

<%
  If Rrs("approved")= [!]1[/!] Then
     Response.Write "<td class=""centerTxt"" width=""30%"">Yes</td>"
  Else If Rrs("approved") = [!]0[/!] Then
     Response.Write "<td class=""centerNTxt"" width=""30%"">No</td>"
  Else
     Response.Write "<td class=""centerPTxt"" width=""30%"">Pending</td>"
  End If
%>

Alternatively, you could remove the Coalesce from the sql query and then check for null values, like this...

<%
  If IsNull(Rrs("approved")) Then
    Response.Write "<td class=""centerPTxt"" width=""30%"">Pending</td>"
  Else
    If Rrs("approved") Then
      Response.Write "<td class=""centerTxt"" width=""30%"">Yes</td>"
    Else 
      Response.Write "<td class=""centerNTxt"" width=""30%"">No</td>"
    End If
  End If
%>

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cr84net2]

Thanks George, that makes perfect sense. I have now found that it is the playerID field that is causing the problem. It is an int (max 4bytes) field.

I don't understand why this is causing the problem. ( I removed it and the error disappeared) Every field has a value so nulls aren't a problem. Is there something obvious I am missing here.

 

[cr84net2]

Finally....I changed playerID to CAST(playerID As Int) and it seems to be working now. George I really appreciate your help. I learned about Coalesce and more about null values.

 

[cr84net2]

Can someone remind me how i give points here??

 

[gbaughma]

cr84net2:

Click on the "Thank user for this valuable post"



Just my 2¢
-Cole's Law: Shredded cabbage

--Greg

http://parallel.tzo.com

 

[cr84net2]

I thought I was done with this problem but evidently not....

After Casting playerID to int I cannot retreive it as Rrs("playerID")

I get the Ordinal cannot be found error

Any clues how to retrieve it once it has been cast as int??

Any help would be appreciated.

 

[gmmastros]

You need to alias the column. like this...

[tt][blue]CAST(playerID As Int) [!] As playerID[/!][/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cr84net]

Thanks again George..my brain is turning to mush...