[RESOLVED] [2008] How to populate datagridview using a datareader

[Nitesh]

Hi Guys,

This is some of my code. But how do I populate a datagridview using the datareader.

Dim strSQL As String = "SELECT peopleid,firstnames,surname FROM people;"
            Dim cmd As New MySqlCommand(strSQL, con)
            'cmd.Parameters.AddWithValue("Parameter1", strName)

            Dim reader As MySqlDataReader = cmd.ExecuteReader

and how can I handle null field values.Please help

[Deepak Sakpal]

You cannot directly bind DataReader to DataGridView. So you need to do it the other way like following:

Dim table As New DataTable()
table.Load(DataReader)
DatagridView1.DataSource=table

[Nitesh]

Perfect. Thanks Deepak . Please check my code. Is there a better way of achieving this and if so please show me.

this is my form code:

Imports MySql.Data.MySqlClient
Public Class Form1
    Dim myConnStr As String
    Private Sub GetPupilInfo()
        Dim table As New DataTable
        Using con As New MySqlConnection(myConnStr)
            con.Open()

            Dim strSQL As String = "SELECT peopleid,firstnames,surname FROM people;"
            Dim cmd As New MySqlCommand(strSQL, con)
            'cmd.Parameters.AddWithValue("Parameter1", strName)

            Dim reader As MySqlDataReader = cmd.ExecuteReader
            table.Load(reader)
            With DataGridView1

                .DataSource = table

            End With

            reader.Close()
            con.Close()
            cmd.Dispose()
        End Using
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        myConnStr = GetConnection()

        GetPupilInfo()

    End Sub
End Class

and I have this in a module:

Module moddatabase
    Public Function GetConnection()
        Dim strServer As String = "localhost"
        Dim intPort As Integer = 3309
        Dim strDatabase As String = "clifton"
        Dim strUser As String = "root"
        Dim strPassword As String = ""

        Dim strConn As String = String.Format("Server={0};Port={1};Database={2};Uid={3};Pwd={4};", _
            strServer, intPort, strDatabase, strUser, strPassword)

        GetConnection = strConn

    End Function
End Module

[Deepak Sakpal]

First of all i suggest u to store the connection string in app.config file. So in future if server changes then u don't need to recompile the exe. What u have to do is just to change the values in config file.

[Nitesh]

sory for my lack of knowledge. But how do I do that?

[Deepak Sakpal]

Go to Settings tab of project properties window and create an entry for 'ConnectionString'. Save and close the project properties window and then use it something like this:

cn.Open(My.Settings.ConnectionString, My.Settings.Username, My.Settings.Password)

[Nitesh]

thanks again Deepak. Worked great

[Deepak Sakpal]

Don't forget to rate my post(s)

[Linus_VB2008]

Hey guys, i know this topic is quite old but my question just fits in here perfectly:

I followed the instructions exactly but unfortunately, even if the Datareader contains 2 Datasets, only the last one is being displayed in my datagridviewer.

Could you please help me, getting all Datasets from the sql datareader displayed.

Thanks in advance

[kareninstructor]

Hey guys, i know this topic is quite old but my question just fits in here perfectly:

I followed the instructions exactly but unfortunately, even if the Datareader contains 2 Datasets, only the last one is being displayed in my datagridviewer.

Could you please help me, getting all Datasets from the sql datareader displayed.

Thanks in advance

Your best option is to write a SQL statement with joins. The following example (VS2008 or higher) requires the NorthWind database. There are three tables involved which will load all rows meeting the WHERE condition into a DataGridView.

      Dim cn As New SqlClient.SqlConnection
      cn.ConnectionString = YourConnectionString

      Dim SQL = _
      <SQL>
         SELECT     
            Employees.LastName, 
            Employees.FirstName, 
            Employees.Title, 
            Territories.TerritoryDescription
         FROM         
            Employees INNER JOIN
               EmployeeTerritories ON 
                  Employees.EmployeeID = EmployeeTerritories.EmployeeID 
            INNER JOIN Territories ON 
                  EmployeeTerritories.TerritoryID = Territories.TerritoryID
         WHERE 
            Employees.Title = 'Sales Manager'
         ORDER BY Employees.LastName
      </SQL>.Value

      Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(SQL, cn)

      cn.Open()

      Dim dt As New DataTable
      dt.Load(cmd.ExecuteReader())
      dgvSample.DataSource = dt
      cn.Close()

I will say that using two SELECT statements do not seem to work as you want which is why only the first table is returned. Perhaps others will have another idea but the above is how I would do this.

[Linus_VB2008]

Thank you very much, i just couldn`t get it work.
My mistake was to make the detour with the sqldatareader.

As soon as i loaded the

cmd.executereader

directly into the table, it worked.


Thanks for your help