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

Hi Guys,

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

Code:

---

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

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

vb.net Code:

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

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:

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:

Code:

---

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

---

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.

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

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)

thanks again Deepak. Worked great:)

Don't forget to rate my post(s) ;)

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

Quote:

---

Originally Posted by 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

---

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.

Code:

---

      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.

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

Code:

---

cmd.executereader

---

directly into the table, it worked.


Thanks for your help