Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

how to I display the values in SqlDataReader back to the user?

Posted on 2012-08-22
4
Medium Priority
?
471 Views
Last Modified: 2012-08-22
I am working on a simple windows form and I have a stored proc that will fetch anywhere from 1 - 20 rows. I populate SQL data reader with the results. How do I display these results back to a user on the windows form?
0
Comment
Question by:sqlagent007
  • 2
4 Comments
 
LVL 1

Author Comment

by:sqlagent007
ID: 38322891
Here is the code I am using:

        Dim SqlCmd As New SqlCommand
        Dim dReader As SqlDataReader
        SqlCmd.CommandText = "getAllNames"
        SqlCmd.CommandType = CommandType.StoredProcedure
        SqlCmd.Connection = myConn


        myConn.Open()

        dReader = SqlCmd.ExecuteReader
        lbSpResults.DataSource = dReader

        myConn.Close()

Open in new window

0
 
LVL 2

Expert Comment

by:Baleboos
ID: 38322975
create a datagridview, a sqlclient.sqldataadapter and a datatable.

the adapter fills the datatable, the datatable serves as the datasource for the dgv.
0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 2000 total points
ID: 38323132
You do not "populate" a DataReader. You use it to read the result of a query, one line at a time. Each time you move to a new line, you lose the last one. So it is up to you to something with the data.

Think of it as a sequential read in a file. If you do not record the data someway, it is lost everytime you read a new line.

In the following code, I loop through the records to build a collection that I then bind to a ComboBox for display purpose:

 		Dim materials as New MaterialCollection

		'Builds a collection of material names
		Dim data As Common.DbDataReader

		data = WoodworkData.GetMaterialNames(materialType)

		While data.Read
			Dim Material As New Material()
			With Material
				.MaterialNameID = data.GetInt32(0)
				.Name = data.GetString(1)
				.TypeID = DirectCast(data.GetInt32(2), TypesOfMaterial)
				.Type = data.GetString(3)
			End With	'Material

			materials .Add(Material)

		End While

		data.Close()

		cboMaterials.DataSource = materials
		cboMaterials.DisplayMember = "Name"
		cboMaterials.ValueMember = MaterialNameID

	End Sub

Open in new window


GetMaterialNames is a function that creates the DataReader by preparing the query that is used to retrieve the data, and returning the DataReader, ready to read its first line.

I then loop through the lines returned by the query (While data.Read). Each time I call Read, a line is fetched from the result of the query.

On each line, I create a new object (a custom object called Material) and fill in its properties by getting the data in each column of the line held by the DataReader. The different Get... methods retrieve the value of the column at the specified index.

Once the object is created, I add it to the collection (Me.Add(Material)).

I then loop to the next record.

At the end of the routine, I have a collection filled in with the data, the DataReader is not needed anymore (data.Close()). I can use that collection any way I want to display the data.

You do not need to have a collection. You can do anything you want with the data in the loop. You could read one line, display its values on the screen, and wait until the user requests the next record before calling Read again. Some will use that data to fill a grid.

If you have only one record to fetch, forget the loop and simply call Read once.
0
 
LVL 1

Author Closing Comment

by:sqlagent007
ID: 38323143
Thanks!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question