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

Posted on 2012-08-22
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?
Question by:sqlagent007
    LVL 1

    Author Comment

    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
            dReader = SqlCmd.ExecuteReader
            lbSpResults.DataSource = dReader

    Open in new window

    LVL 2

    Expert Comment

    create a datagridview, a sqlclient.sqldataadapter and a datatable.

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

    Accepted Solution

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

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Training Course: Java/J2EE and SOA

    This course will cover both core and advanced Java concepts like Database connectivity, Threads, Exception Handling, Collections, JSP, Servlets, XMLHandling, and more. You'll also learn various Java frameworks like Hibernate and Spring.

    In my previous article ( we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
    Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now