VB: How to bind a DataTable to a DataGridView?

Question

I know this is a basic question that has already been answered thousand times, but I can't make it work.

I am working in Visual Studio 2010 and have two forms in my Windows Application. In the first one (Main.vb), the user enters his inputs and the calculation takes place. In the second one (DataAnalysis.vb) the calculation results are displayed.

In Main.vb, I create the temp table that will contains all the intermediary calculation steps:

Dim tableTempJDL As DataTable = New DataTable("TempJDL")
Dim column As DataColumn

column = New DataColumn("ID", GetType(System.Int32))
tableTempJDL.Columns.Add(column)

column = New DataColumn("PthObjekt", GetType(System.Double))
tableTempJDL.Columns.Add(column)

'further columns are after created using the same method

Then, in DataAnalysis.vb, I try to display the DataTable tableTempJDL into the DataGridViewBerechnung:

Public bindingSourceBerechnung As New BindingSource()
Me.DataGridViewBerechnung.DataSource = Me.bindingSourceBerechnung

But then I don't understand how to fill the DataGridView...

Answer 1

Public Class Form1

Dim CON As New SqlConnection
Dim CMD As New SqlCommand
Dim dt As New DataTable

Public Sub DbConnect()
    If CON.State = ConnectionState.Open Then DbClose()
    CON.ConnectionString = "Data Source = yourservername;Initial Catalog=your database name;Integrated Security=True"
    CON.Open()
End Sub
Public Sub DbClose()
    CON.Close()
    CON.Dispose()
End Sub

Public Sub enabletext()
    TextBox1.Enabled = True
End Sub

Public Sub CLEARFEILDS()
    TextBox1.Clear()
    TextBox2.Clear()
    TextBox3.Clear()
    TextBox4.Clear()
    ComboBox1.DataSource = Nothing
    ComboBox1.SelectedIndex = 0
    DataGridView2.Rows.Clear()
End Sub

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    ComboBox1.Select()
    TextBox4.Enabled = False
    DataGridView1.Visible = False
    TextBox5.Visible = False
    'AUTOSEACH()

    DbConnect()
    Dim SELECTQUERY As String = "SELECT PROD_CODE FROM TBLPRODUCT"
    Dim MYCOMMAND As New SqlCommand(SELECTQUERY, CON)

    Dim MYREADER As SqlClient.SqlDataReader
    MYREADER = MYCOMMAND.ExecuteReader

    Prod_Code.Items.Clear()

    While MYREADER.Read
        Prod_Code.Items.Add(MYREADER("PROD_CODE"))
    End While
    DbClose()

End Sub

Public Function InvCodeGen(ByVal CurrCode As String) As String
    Dim RightSix As String = Microsoft.VisualBasic.Right(Trim(CurrCode), 3)
    Dim AddValue As Integer = Microsoft.VisualBasic.Val(RightSix) + 1
    Dim RetValue As String
    If Microsoft.VisualBasic.Len(AddValue.ToString) = 1 Then
        RetValue = "00" + AddValue.ToString
    ElseIf (Microsoft.VisualBasic.Len(AddValue.ToString) = 2) Then
        RetValue = "000" + AddValue.ToString
    Else
        RetValue = AddValue.ToString
    End If
    Return RetValue
End Function

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    CreateNewCode()
    TextBox1.Enabled = False
    DataGridView1.Visible = False
    TextBox5.Visible = False
    ComboBox1.Visible = True
    AddCustomer()
    'GridView2Load(GetDataGridView2())

End Sub

Private Sub CreateNewCode()
    Dim CurrCode As String = ""
    'Dim NewCode As String = "INV"
    Dim NewCode As Integer
    Dim mySelectQuery As String = "SELECT MAX(INV_NO) AS INVNO FROM TBLINV_HEADER"
    Dim myCommand As New SqlClient.SqlCommand(mySelectQuery, CON)
    myCommand.CommandType = CommandType.Text
    Dim myReader As SqlClient.SqlDataReader
    DbConnect()
    myReader = myCommand.ExecuteReader()
    Do While myReader.Read()
        If IsDBNull(myReader("INVNO")) Then
            myReader.Close()
            Dim myNewYearQuery As String = "SELECT MAX(INV_NO) AS INVNO FROM TBLINV_HEADER"
            Dim myNewYearCommand As New SqlClient.SqlCommand(myNewYearQuery, CON)
            myNewYearCommand.CommandType = CommandType.Text
            Dim myNewYearReader As SqlClient.SqlDataReader
            myNewYearReader = myNewYearCommand.ExecuteReader()
            Do While myNewYearReader.Read()
                CurrCode = Trim(myNewYearReader("INVNO").ToString)
            Loop
            myNewYearReader.Close()
            Exit Do
        Else
            CurrCode = Trim(myReader("INVNO").ToString)
            myReader.Close()
            Exit Do
        End If
    Loop
    DbClose()
    NewCode = Trim(InvCodeGen(CurrCode))
    TextBox1.Text = CInt(NewCode)
End Sub

Private Sub AddCustomer()
    Dim dsCusCode As New DataSet
    dsCusCode.Reset()
    ComboBox1.Items.Clear()
    Dim myCusCodeQuery As String = "SELECT CUST_CODE as Custcode FROM TBLCUSTOMER"
    Dim daCusCode As New SqlClient.SqlDataAdapter(myCusCodeQuery, CON)
    DbConnect()
    daCusCode.Fill(dsCusCode, "TBLCUSTOMER")
    DbClose()
    Dim x As Integer
    For x = 0 To dsCusCode.Tables(0).Rows.Count - 1
        ComboBox1.Items.Add(dsCusCode.Tables(0).Rows(x).Item("Custcode").ToString)
        'TextBox3.Text = dsCusCode.Tables(0).Rows(x).Item("custname").ToString
    Next
    ComboBox1.SelectedIndex = -1
    x = Nothing
    myCusCodeQuery = Nothing
    dsCusCode.Dispose()
    daCusCode.Dispose()
    dsCusCode = Nothing
    dsCusCode = Nothing

End Sub

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
    Dim Code As String

    Code = ComboBox1.Text

    Dim dsCode As New DataSet
    dsCode.Reset()
    Dim myCodeQuery As String = "SELECT cust_name as custname, cust_add1 as custadd FROM TBLCUSTOMER where Cust_Code = '" & Code & "'"
    Dim daCode As New SqlClient.SqlDataAdapter(myCodeQuery, CON)
    DbConnect()
    daCode.Fill(dsCode, "TBLCUSTOMER")
    DbClose()

    TextBox2.Text = dsCode.Tables(0).Rows(0).Item("custname").ToString
    TextBox3.Text = dsCode.Tables(0).Rows(0).Item("custadd").ToString


    myCodeQuery = Nothing
    dsCode.Dispose()
    daCode.Dispose()
    dsCode = Nothing
    dsCode = Nothing
End Sub
Private Sub Button8_Click(sender As Object, e As EventArgs) Handles Button8.Click
    For I As Integer = 0 To DataGridView2.Rows.Count - 1
        Dim MYQUERY As String = "SELECT PROD_DESC , PROD_PRICE FROM TBLPRODUCT WHERE PROD_CODE='" & DataGridView2.Rows(I).Cells(1).Value & "'"

        Dim MYCOMMAND As New SqlCommand(MYQUERY, CON)
        DbConnect()
        Dim MYREADER As SqlClient.SqlDataReader
        MYREADER = MYCOMMAND.ExecuteReader
        If MYREADER.Read() Then
            DataGridView2.Rows(I).Cells(2).Value = MYREADER("PROD_DESC")
            DataGridView2.Rows(I).Cells(3).Value = MYREADER("PROD_PRICE")
        End If
        DbClose()
    Next

End Sub
Private Sub DataGridView2_CellFormatting(sender As Object, e As DataGridViewCellFormattingEventArgs) Handles DataGridView2.CellFormatting
    DataGridView2.Rows(e.RowIndex).Cells(0).Value = CInt(e.RowIndex + 1)
End Sub
Private Sub DataGridView2_CellEndEdit(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView2.CellEndEdit
    For I As Integer = 0 To DataGridView2.Rows.Count - 1

        Dim QTY As Double = DataGridView2.Rows(I).Cells(4).Value
        Dim PRICE As Double = DataGridView2.Rows(I).Cells(3).Value

        Dim AMOUNT As Double = QTY * PRICE

        DataGridView2.Rows(I).Cells(5).Value = AMOUNT
    Next

    ' SUM OF AMOUNT TOTAL
    Dim COLSUM As Decimal = 0
    For Each ROW As DataGridViewRow In DataGridView2.Rows
        If Not IsDBNull(ROW.Cells(5).Value) Then
            COLSUM += ROW.Cells(5).Value
        End If
    Next
    TextBox4.Text = COLSUM
End Sub
'SAVE
Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click

    If MsgBox("Are you sure want to save this record?", vbYesNo + vbQuestion) = vbYes Then

        updateinvheader()
        updategridvalue()

        CLEARFEILDS()

    End If

End Sub

Private Sub updateinvheader()

    Dim insertquery1 As String = "insert into TBLINV_HEADER(inv_no,inv_date,inv_cust) values(@inv_no,@inv_date,@inv_cust)"

        Dim command As New SqlCommand(insertquery1, CON)

        command.Parameters.AddWithValue("@inv_no", TextBox1.Text)
        command.Parameters.AddWithValue("@inv_date", DateTime.Now)
        command.Parameters.AddWithValue("@inv_cust", ComboBox1.Text)

        DbConnect()
        command.ExecuteNonQuery()
        DbClose()

End Sub

Private Sub updategridvalue()
    Dim i As Integer

    Dim insertquery2 As String = "insert into TBLINV_detail(inv_lno,inv_no,inv_prod,inv_qty,inv_Price) values(@inv_lno,@inv_no,@inv_prod,@inv_qty,@inv_Price)"
    Dim command As New SqlCommand(insertquery2, CON)
    For i = 0 To DataGridView2.RowCount - 1

        If DataGridView2.Rows(i).Cells(1).Value = "" Then
            GoTo 100
        Else
            command.Parameters.AddWithValue("@inv_lno", DataGridView2.Rows(i).Cells(0).RowIndex + 1)

            'command.Parameters.AddWithValue("@inv_lno", DataGridView2.Rows(i).Cells(0).)
            command.Parameters.AddWithValue("@inv_no", TextBox1.Text)
            command.Parameters.AddWithValue("@inv_prod", DataGridView2.Rows(i).Cells(1).Value)
            command.Parameters.AddWithValue("@inv_qty", DataGridView2.Rows(i).Cells(4).Value)
            command.Parameters.AddWithValue("@inv_Price", DataGridView2.Rows(i).Cells(3).Value)
        End If

        DbConnect()
        command.ExecuteNonQuery()
        DbClose()

100: command.Parameters.Clear() Next MsgBox("data saved successfuly") End Sub

Private Sub Button9_Click(sender As Object, e As EventArgs) Handles Button9.Click
    DataGridView1.Visible = True
    TextBox5.Visible = True
    ComboBox1.Visible = False
    DbConnect()
    Dim MYQUERY As String = "Select * FROM  TBLCUSTOMER" &
                            " INNER Join TBLINV_HEADER ON TBLINV_HEADER.INV_CUST = TBLCUSTOMER.CUST_CODE" &
                            " WHERE TBLINV_HEADER.INV_NO ='" & TextBox1.Text & "'"

    Dim CMD As New SqlCommand(MYQUERY, CON)
    Dim DA As New SqlDataAdapter
    DA.SelectCommand = CMD

    Dim DT As New DataTable
    DA.Fill(DT)

    If DT.Rows.Count > 0 Then
        MsgBox(DT.Rows(0)(1).ToString())
        'ComboBox1.Text = DT.Rows(0)(1).ToString()
        ComboBox1.Visible = False
        TextBox5.Text = DT.Rows(0)(1).ToString()
        TextBox2.Text = DT.Rows(0)(2).ToString()
        TextBox3.Text = DT.Rows(0)(3).ToString()

    End If

    DbClose()

    DataGridView1.DataSource = GETPRODUCTDETAILS()

End Sub

Private Function GETPRODUCTDETAILS() As DataTable

    Dim PRODUCTDET As New DataTable

    DbConnect()
    Dim MYQUERY As String = " SELECT TBLINV_DETAIL.INV_LNO, TBLINV_DETAIL.INV_PROD, TBLPRODUCT.PROD_DESC, TBLINV_DETAIL.INV_PRICE, TBLINV_DETAIL.INV_QTY, (TBLINV_DETAIL.INV_QTY*TBLINV_DETAIL.INV_PRICE) AS AMOUNT  FROM  TBLINV_DETAIL " &
                            " INNER JOIN TBLPRODUCT On TBLPRODUCT.PROD_CODE = TBLINV_DETAIL.INV_PROD  " &
                            " WHERE TBLINV_DETAIL.INV_NO='" & TextBox1.Text & "'"

    Dim CMD As New SqlCommand(MYQUERY, CON)
    Dim READER As SqlDataReader = CMD.ExecuteReader()
    PRODUCTDET.Load(READER)


    DbClose()
    Return PRODUCTDET

End Function

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
    Dim DELETEQUERY As String = "DELETE FROM TBLINV_DETAIL WHERE  TBLINV_DETAIL.INV_NO= '" & TextBox1.Text & "'"
    Dim DELETEQUERY2 As String = "DELETE FROM TBLINV_HEADER WHERE TBLINV_HEADER.INV_NO= '" & TextBox1.Text & "'"

    Dim CMD As New SqlCommand(DELETEQUERY, CON)
    Dim CMD2 As New SqlCommand(DELETEQUERY2, CON)

    DbConnect()

    CMD.ExecuteNonQuery()
    CMD2.ExecuteNonQuery()
    DbClose()


    TextBox1.Clear()
    TextBox2.Clear()
    TextBox3.Clear()
    TextBox4.Clear()
    ComboBox1.DataSource = Nothing
    DataGridView1.DataSource = Nothing

    MsgBox("DATA DELETED SUCCESSFULLY")
End Sub

End Class

Answer 2

Simply, you can make your table as the datasource of bindingsource in following way:

 Me.bindingSourceBerechnung .DataSource = tableTempJDL

Later on, you can bind above binding source in your datagridview in following way:

 Me.DataGridViewBerechnung.DataSource = Me.bindingSourceBerechnung