Problem with DataReader

Results 1 to 3 of 3

Thread: Problem with DataReader

  1. #1
    Join Date
    Dec 1969

    Default Problem with DataReader

    Thanks again for your help with my earlier problems. So far, I&#039;ve been able to use your advice & fix them all. Now, I&#039;m trying to read an Access database by virtue of the DataReader. (According to what I&#039;ve read, that should be the fastest way to get just one record. Is this correct?)<BR><BR>My SELECT statement looks good to me. The .HasRows indicates True, & the .FieldCount shows the correct number. But when I try to access the data I&#039;ve read, I&#039;m getting an error: "No data exists for the row/column." I get the error when I&#039;m trying to access the data in the individual fields, such as "tbxServiceType.Text = dtrCompatibility ("service_type")" or "If dtrCompatibility (intField) = -100 Then". The record that I&#039;m trying to read is actually on the database. When I enter bogus data for the parameters (so that it shouldn&#039;t find a match), it doesn&#039;t find it (as it should be). In that case the .HasRows is false as it should be. My code is below. Could you, please, tell me what I&#039;m doing wrong?<BR><BR>&#039;Verify logon & retrieve service type & last trait completed<BR>&#039;Return True/False indicating whether the registration record was found<BR>Function VerifyLogon() as Boolean<BR> Dim conCompatibility as System.Data.OleDb.OleDbConnection, _<BR> dtrCompatibility as System.Data.OleDb.OleDbDataReader, _<BR> strSQL as String, _<BR> strConnection as String, _<BR> intField as Integer, _<BR> blnFound as Boolean<BR><BR> strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; " _<BR> & "Data Source=C:My ASPNet Web SiteCompatibility.mdb"<BR> conCompatibility = New System.Data.OleDb.OleDbConnection (strConnection)<BR> conCompatibility.Open()<BR> strSQL = "SELECT service_type, score_1, score_2, score_3, score_4, score_5," _<BR> & " score_6, score_7, score_8, score_9, score_10, score_11, score_12" _<BR> & " FROM tbl_registered_users WHERE (email=""" & tbxEmail.Text _<BR> & """ AND password=""" & tbxPassword.Text & """ AND name_first=""" _<BR> & tbxFirstName.Text & """)"<BR> dtrCompatibility = New System.Data.OleDb.OleDbCommand _<BR> (strSQL, conCompatibility).ExecuteReader()<BR> &#039;Record found?<BR> If dtrCompatibility.HasRows Then<BR> &#039;Yes<BR> tbxServiceType.Text = dtrCompatibility ("service_type")<BR> tbxLastTrait.Text = "12"<BR> &#039;Find last trait completed<BR> For intField = 1 To dtrCompatibility.FieldCount - 1<BR><BR> If dtrCompatibility (intField) = -100 Then<BR> tbxLastTrait.Text = CStr(intField - 1)<BR> Exit For<BR> End If<BR> Next intField<BR> End If<BR> &#039;Return True or False to indicate whether record was found<BR> blnFound = dtrCompatibility.HasRows<BR> dtrCompatibility.Close()<BR> conCompatibility.Close()<BR> Return blnFound<BR>End Function<BR>

  2. #2
    Join Date
    Dec 1969

    Default Not quite...

    When you get a DataReader, it is positioned *BEFORE* the first row of data.<BR><BR>The common way of reading all rows from a datareader is:<BR><BR>Do While dr.Read<BR> ... process current row ...<BR>Loop<BR><BR>See? You have to do the READ call to get to the first record.<BR><BR>Might I suggest that you *ALWAYS* compile with<BR> OPTION STRICT ON<BR>? It will help you avoid coding errors that *look* right but which blow up at runtime.<BR><BR>I do not believe, for example, that this line is legal:<BR> tbxServiceType.Text = dtrCompatibility ("service_type")<BR><BR>I believe you have to code that at<BR> dtrCompatibility.GetString( dtrCompatibility.GetOrdinal("service_type") )<BR><BR>Although, since your SQL query is right up above this code, I don&#039;t see why you couldn&#039;t do<BR> dtrCompatibility.GetString( 0 )<BR><BR>Similarly, I don&#039;t think your<BR> dtrCompatibility(intField) <BR>is correct. I think it should be<BR> dtrCompatibility.GetInt32(intField)<BR><BR>Again, using OPTION STRICT ON would tell you about this kind of stuff before you run into run-time bugs.<BR><BR><BR>

  3. #3
    Join Date
    Dec 1969

    Default and if you are expecting just one row

    then add the line of code<BR><BR><BR>[code language="VB.NET"]If dtrCompatibility.HasRows Then <BR> dtrCompatibility.Read()<BR> &#039;Yes <BR> tbxServiceType.Text = dtrCompatibility ("service_type") <BR> tbxLastTrait.Text = "12"<BR> ....[/code]<BR><BR>don&#039;t have to if or loop the .Read() line, that method moves the pointed to the first (or next) record and returns True or False if it got a row back or not, and you are not required to do anything with that boolean

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts