Datareader HasRows but don't read back or forward

In my previous post I submit this 'little' annoyance I have with Datareader.

Unfortunatly Matt and Dave your answer is wrong, I already tested with.Hasrows before, I should mention it in my post.

Hasrows is a valid test but don't read any data.

So if I write

If dtr.HasRows then

Poll_Question= dtr("Question")

End If

'-- Now bind to the radiobutton list
MyPoll.Datasource =dtr
MyPoll.Databind

I will still have an error.

The solution proposed by David is probably a good one.

If you are using stored procedures (which you should), then you could have the Question be an OUTPUT parameter. That way you don't have the question unnecessarily repeated in your resultset.

If you don't choose to go that path, then you could manually create the Items instead of setting the DataSource.

if (dtr.Read())
{
Poll_Question.Text = dtr["Question"].ToString();
MyPoll.Items.Add(...);
while (dtr.Read())
{
MyPoll.Items.Add(...);
}
}
dtr.Close();

And yes David I use SP all the time, but I was just thinking that for this small control, I could avoid them.

On the second part of your answer, you still miss the first record because of the dtr.Read.

How much I regret the good ASP Recordset.movenext and .moveprevious, .movelast, .movefirst !

Why this is not implemented in .Net ?

As I say before maybe the lack of a Datasource for some web controls like Label is the source of the problem.

Something I would like to investigate is that the Label control has a Databinding event, so maybe something to find there, who knows.

 

10 Comments

  • You have the ability to move forward and back...its called a DataTable :). Different tools have different options. You can also check DataTable.Rows.Count.



    HTH,

    -Scott

  • Paschal: Does this work?



    If dtr.HasRows then

    dtr.Read

    Poll_Question= dtr("Question")

    '-- Now bind to the radiobutton list

    MyPoll.Datasource =dtr

    MyPoll.Databind

    End If

  • Thanks Scott but I know that ;-)



    The question was more about Datareader. For a small data binding, a Datatable is bit too much.

    My point is that it's bit silly to have to read the data twice to bind to the different controls ;-)

    If I had Movefirst for example, I could avoid closing the reader.

    Datareader suppose to be the modern version of the Recordset object isn't it ?

  • Phil technicall yes, but you lost the first record after binding the Poll_Question control !

    So the MyPoll radiobuttonlist control miss one record

  • No, the DataReader is not the modern version of the Recordset. Its single goal is to get you the data ask quick as possible. End of story. Remember, you are connected to the DB while the datareader is open. The goals should be get in, get out..move on.



    Having said that, what about this:



    string pollQuestion = null;

    while(reader.Read())

    {

    if(pollQuestion == null)

    {

    pollQuestion = (string)reader["X"];

    }

    control.Items.Add(Something);

    }



    //also remember, if pollQuestion is still here null, then no data was found in the first place



    or, return two results:

    while(reader.Read())

    {

    //Get Question

    break; //should only have 1 result anyway

    }

    reader.NextResult();

    control.Datasource = reader;

    control.DataBind();

  • Here's a nice script I use to convert a datareader to a datatable in similar cases. Definitely some overhead, but nice at times...



    Public Shared Function GetTable(ByVal reader As SqlDataReader) As DataTable



    Dim table As DataTable = reader.GetSchemaTable()

    Dim dt As DataTable = New System.Data.DataTable()

    Dim dc As DataColumn

    Dim row As DataRow

    Dim al As System.Collections.ArrayList = New System.Collections.ArrayList()

    Dim i As Integer



    For i = 0 To table.Rows.Count - 1

    dc = new DataColumn()



    If Not(dt.Columns.Contains(String.Format(table.Rows(i)("ColumnName")))) Then

    dc.ColumnName = table.Rows(i)("ColumnName").ToString()

    dc.Unique = Convert.ToBoolean(table.Rows(i)("IsUnique"))

    dc.AllowDBNull = Convert.ToBoolean(table.Rows(i)("AllowDBNull"))

    dc.ReadOnly = Convert.ToBoolean(table.Rows(i)("IsReadOnly"))

    al.Add(dc.ColumnName)

    dt.Columns.Add(dc)

    End If

    Next



    While (reader.Read())

    row = dt.NewRow()



    For i = 0 To al.Count - 1

    row((String.Format(al(i)))) = reader(String.Format(al(i)))

    Next



    dt.Rows.Add(row)

    End While



    Return dt



    End Function

  • I didn't read what you were trying to do all that closely, or I wouldn't've suggested that HasRows would work. OTOH, I'd've put the questions and answers in different tables to start with.

  • You wrote: "On the second part of your answer, you still miss the first record because of the dtr.Read."



    Actually, you don't miss the first record. That's because the code manually adds each item to the list instead setting the DataSource and using DataBind(). The line of code just before the while loop adds the first row to the list.

  • So many feedbacks ! Thanks to everybody.



    I am going to do some testing now with all the ideas.

    Russ, I use already this solution for some more complex things like the Quiz tool, but for this 'little' code it's bit too much ;-)

    Scott I got the point but I will try the idea.

    Joseph no I don't miss ADO, I just find stupid that we can't move forward and backward with a datareader easily (without a datatable), but just for the fun I will try to implement ADO via COM interop ;-)

  • Why would you write custom code to put a datareader into a datatable when the DbDataAdapter already does this for you?

Comments have been disabled for this content.