*** Using DataReaders to populate combo boxes - please help!!! ***

  • Thread starter Thread starter ricardo.dapaz
  • Start date Start date
R

ricardo.dapaz

Ok,

I am just learning ADO.NET with Visual Basic 2005 and have a form with
a whole bunch of combo boxes which I would like to populate from four
separate queries. I have been using the Wrox Expert One on One
Database Programming book as a reference along with the Beginner's
version. I would like to use the multipler results sets from a single
batch query in order to populate the four combo boxes.

I have adapted the following example from the book as shown below:

Private Sub listBuilder()
'Define and open the SqlConnection object
'Define the SqlCommand to return four resultsets
Dim strSQL As String = "SELECT [Client Name] FROM Clients"
strSQL += ";SELECT [Terms] from [Terms]"
strSQL += ";SELECT [Quote Number] from [Quotes]"
strSQL += ";SELECT [STATUS] from [Status]"
Dim cmdReader As SqlCommand = New SqlCommand(strSQL, con)
cmdReader.CommandType = CommandType.Text
'Define, create, and traverse the SqlDataReader
'Close the connection when closing the SqlDataReader
Dim sdrReader As SqlDataReader = _
cmdReader.ExecuteReader(CommandBehavior.CloseConnection)
'sdrReader = cmdReader.ExecuteReader
With sdrReader
If .HasRows Then
While .Read
'Fill the client combo box
cboClientName.Items.Add(.Item(0).ToString)
End While
While .NextResult
'Process additional resultsets
While .Read
'Fill the terms combo box
cboTerms.Items.Add(.Item(0).ToString)
End While
End While
While .NextResult
While .Read
'Fill the Quote number combo box
cboQuoteNumber.Items.Add(.Item(0).ToString)
End While
End While
While .NextResult
While .Read
'Fill the Status combo box
cboStatus.Items.Add(.Item(0).ToString)
End While
End While
End If
'Close the SqlDataReader and SqlConnection
.Close()
End With
End Sub

This is behaving quite erratically though, working as it should for the
first instance but getting it wrong for the section of the code inside
the while.next result. I have tried modifying this so that there is a
single while .next result outer loop but it does exactly the same
thing, i.e. the second combo box gets all of the results of the
queries.

Any suggestions would be greatly appreciated. Oh, almost forgot, I am
developing under Visual Studio 2005 with an MSDE instance.

Regards,

Ric
 
Ric,

Replace each of the While .NextResult loops with .NextResult

Remember to also get rid of each corresponding End While.

You don't need any of those While .NextResult loops given how the rest of
your code is structured.

Kerry Moorman


[email protected] said:
Ok,

I am just learning ADO.NET with Visual Basic 2005 and have a form with
a whole bunch of combo boxes which I would like to populate from four
separate queries. I have been using the Wrox Expert One on One
Database Programming book as a reference along with the Beginner's
version. I would like to use the multipler results sets from a single
batch query in order to populate the four combo boxes.

I have adapted the following example from the book as shown below:

Private Sub listBuilder()
'Define and open the SqlConnection object
'Define the SqlCommand to return four resultsets
Dim strSQL As String = "SELECT [Client Name] FROM Clients"
strSQL += ";SELECT [Terms] from [Terms]"
strSQL += ";SELECT [Quote Number] from [Quotes]"
strSQL += ";SELECT [STATUS] from [Status]"
Dim cmdReader As SqlCommand = New SqlCommand(strSQL, con)
cmdReader.CommandType = CommandType.Text
'Define, create, and traverse the SqlDataReader
'Close the connection when closing the SqlDataReader
Dim sdrReader As SqlDataReader = _
cmdReader.ExecuteReader(CommandBehavior.CloseConnection)
'sdrReader = cmdReader.ExecuteReader
With sdrReader
If .HasRows Then
While .Read
'Fill the client combo box
cboClientName.Items.Add(.Item(0).ToString)
End While
While .NextResult
'Process additional resultsets
While .Read
'Fill the terms combo box
cboTerms.Items.Add(.Item(0).ToString)
End While
End While
While .NextResult
While .Read
'Fill the Quote number combo box
cboQuoteNumber.Items.Add(.Item(0).ToString)
End While
End While
While .NextResult
While .Read
'Fill the Status combo box
cboStatus.Items.Add(.Item(0).ToString)
End While
End While
End If
'Close the SqlDataReader and SqlConnection
.Close()
End With
End Sub

This is behaving quite erratically though, working as it should for the
first instance but getting it wrong for the section of the code inside
the while.next result. I have tried modifying this so that there is a
single while .next result outer loop but it does exactly the same
thing, i.e. the second combo box gets all of the results of the
queries.

Any suggestions would be greatly appreciated. Oh, almost forgot, I am
developing under Visual Studio 2005 with an MSDE instance.

Regards,

Ric
Click to expand...
 
Hi Kerry,

Thanks for your prompt response. I shall give it a try and will let you
know how I go...

Regards,

Ric
Kerry said:
Ric,

Replace each of the While .NextResult loops with .NextResult

Remember to also get rid of each corresponding End While.

You don't need any of those While .NextResult loops given how the rest of
your code is structured.

Kerry Moorman


[email protected] said:
Ok,

I am just learning ADO.NET with Visual Basic 2005 and have a form with
a whole bunch of combo boxes which I would like to populate from four
separate queries. I have been using the Wrox Expert One on One
Database Programming book as a reference along with the Beginner's
version. I would like to use the multipler results sets from a single
batch query in order to populate the four combo boxes.

I have adapted the following example from the book as shown below:

Private Sub listBuilder()
'Define and open the SqlConnection object
'Define the SqlCommand to return four resultsets
Dim strSQL As String = "SELECT [Client Name] FROM Clients"
strSQL += ";SELECT [Terms] from [Terms]"
strSQL += ";SELECT [Quote Number] from [Quotes]"
strSQL += ";SELECT [STATUS] from [Status]"
Dim cmdReader As SqlCommand = New SqlCommand(strSQL, con)
cmdReader.CommandType = CommandType.Text
'Define, create, and traverse the SqlDataReader
'Close the connection when closing the SqlDataReader
Dim sdrReader As SqlDataReader = _
cmdReader.ExecuteReader(CommandBehavior.CloseConnection)
'sdrReader = cmdReader.ExecuteReader
With sdrReader
If .HasRows Then
While .Read
'Fill the client combo box
cboClientName.Items.Add(.Item(0).ToString)
End While
While .NextResult
'Process additional resultsets
While .Read
'Fill the terms combo box
cboTerms.Items.Add(.Item(0).ToString)
End While
End While
While .NextResult
While .Read
'Fill the Quote number combo box
cboQuoteNumber.Items.Add(.Item(0).ToString)
End While
End While
While .NextResult
While .Read
'Fill the Status combo box
cboStatus.Items.Add(.Item(0).ToString)
End While
End While
End If
'Close the SqlDataReader and SqlConnection
.Close()
End With
End Sub

This is behaving quite erratically though, working as it should for the
first instance but getting it wrong for the section of the code inside
the while.next result. I have tried modifying this so that there is a
single while .next result outer loop but it does exactly the same
thing, i.e. the second combo box gets all of the results of the
queries.

Any suggestions would be greatly appreciated. Oh, almost forgot, I am
developing under Visual Studio 2005 with an MSDE instance.

Regards,

Ric
Click to expand...
Click to expand...
 
Back
Top