NextResult 1

[eramgarden]

I have a sql that returns 3 rows and want to assign each row to a Label...

I tried using "Nextresult" but I get this error:
Invalid attempt to read when no data is present.

---
This is what I have:

Dim cnn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnString"))
Dim cmd As SqlCommand = cnn.CreateCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "Select label from lablem where original_label = 'Caller' ;" & _
"Select label from labelm where original_label = 'Company' ;" & _
"Select label from labelm where original_label = 'Incident' ; "


cnn.Open()

Dim dr As SqlDataReader = cmd.ExecuteReader()

If dr.Read() Then
btnContact.Text = dr.GetString(0)
dr.NextResult()
btnSite.Text = dr.GetString(0)
dr.NextResult()
btnIncident.Text = dr.GetString(0)

End If

dr.Close()
cnn.Close()

any ideas on how to do this?

 

[VBRookie]

I believe that you only need to use nextResult if you are returning more than one recordset. It sounds as if you are simply returning one recordset with three rows.

If that is the case all you need to do is loop through the recordset to get to each of the three rows.

I'm not very good with VB.NET but with C# it would be something like:

while (dr.Read ())
{
     lbl1.Text = dr ["readerValueOne"].ToString ();
}

Hope this helps ...

- VB Rookie

 

[eramgarden]

ah..

then how can I assign each row to a different label?..


while (dr.Read ())
{
lbl1.Text = dr ["readerValueOne"].ToString ();
lbl2.Text = ??
lbl3.Text = ??
}

 

[VBRookie]

hmmm ...

How about this:

int i = 0;
while (dr.Read ())
{
     i++
     Label lbl = (Label) Page.FindControl ("lbl" + i)
     
     if (lbl != nulll)
     {
         lbl.Text = dr ["readerValueOne"].ToString (); 
     }
}

- VB Rookie

 

[snowmaster]

Hi there,

Actually, you need to use NextResult for every result set you request after the first one.

The fact that you use the GetXXX is good. These methods return typed information which do not require further conversion and thus are the ones to use if you absolutely must use a DataReader.

Here is the code which solves your problem:

Dim dr As SqlDataReader = cmd.ExecuteReader()

If dr.Read() Then
btnContact.Text = dr.GetString(0)
dr.NextResult() 
If dr.Read() Then
btnSite.Text = dr.GetString(0)

dr.NextResult() 
If dr.Read() Then
btnIncident.Text = dr.GetString(0)

If you are going to use the DataReader often, use it in conjunction with the GetOrdinal() method which extracts the ordinal value for a particular column. That way you can use the GetXXX methods without worrying that your code will break. It also avoids the slow dr["xxx"] version.

Hope this helps!