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

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
 
G

Guest

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...
 
R

ric_deez

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Populate Combo Boxes using Form_Current 1
Combo Box II 2
using datasets vs. datareaders 2
Populate Combo Boxes using Form_Current Options 3
sqldatareader padding varchar fields with extra white space 3
Converting VB to C# 4
Having trouble with combo boxes. 11
Populate a combo box 4

Top