none
May i ask how i can use code call out my data table and post on data grid view RRS feed

  • Question

  • how i can write the code let my datagridview (dgvdata) can show the data table when i run the code. in current project status is when i run the project the datagridview (dgvdata) will no show the database .but when i search the data on database the database still can show...can anyone pls help take a look...thanks a lot.

    Imports System.Data.OleDb
    Public Class Form2
        Dim cnn As New OleDb.OleDbConnection
        Private dbDataSet As New DataTable
        Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            cnn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0; Data Source=mydataX.mdb;"
        End Sub
        Private Sub RefreshData()
            If Not cnn.State = ConnectionState.Open Then
                'open connection
                cnn.Open()
            End If
            Dim da As New OleDb.OleDbDataAdapter("SELECT cid, cbcid as [ID], " & _
                                                 "CheckerName as [CheckerName], Quantity as [StockQuantity],Unit as [Unit], Date as [Date], CompanyName as [CompanyName], CheckerDetail as [CheckerDetail]" & _
                                                 " FROM checker ORDER BY cid", cnn)
            Dim dt As New DataTable
            'fill data to datatable
            da.Fill(dt)
            Me.dgvData.DataSource = dt
            Me.dgvData.Columns("cid").Visible = False
            'close connection
            cnn.Close()
        End Sub

        Private Sub loadTransactions(ByVal transactionid As Integer)
            If Not cnn.State = ConnectionState.Open Then
                'open connection
                cnn.Open()
            End If
            Dim da As New OleDb.OleDbDataAdapter("SELECT s.cid as [ID], c.CheckerName as item_name, " & _
                                                "s.CheckerName as [CheckerName],s.ShipQuantity as [StockQuantity], s.[Date] as [Date], s.CompanyName as [CompanyName], s.CheckerDetail as [CheckerDetail]" & _
                                                "FROM stock s left join checker c on c.cid = s.checkercid where s.checkercid = " & transactionid & " ORDER BY s.cid", cnn)
            Dim dt As New DataTable
            da.Fill(dt)
            Me.DataGridView1.DataSource = dt
            Me.DataGridView1.Columns("item_name").HeaderText = "CheckerName"
            'close connection
            cnn.Close()
        End Sub

        Private Sub txtbarcode_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtbarcode.TextChanged
            cnn.Open()
            Dim dt As New DataTable
            Dim ds As New DataSet
            ds.Tables.Add(dt)
            Dim da As New OleDbDataAdapter

            da = New OleDbDataAdapter("SELECT * FROM checker WHERE cbcid like '%" & txtbarcode.Text & "%'", cnn)

            da.Fill(dt)

            dgvData.DataSource = dt.DefaultView
            cnn.Close()
        End Sub

        Private Sub dgvData_CellEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvData.CellEnter
            If dgvData.SelectedCells.Count > 0 Then
                loadTransactions(dgvData("cid", dgvData.CurrentCell.RowIndex).Value)
            End If
        End Sub

        Private Sub btnclose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnclose.Click
            Me.Close()
        End Sub

        Private Sub dgvData_CellFormatting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles dgvData.CellFormatting
            Dim dgv As DataGridView = Me.dgvData
            For i As Integer = 0 To dgv.Rows.Count - 1
                For ColNo As Integer = 1 To 7
                    If Not dgv.Rows(i).Cells(ColNo).Value Is DBNull.Value Then
                        dgv.Rows(i).Cells(ColNo).Style.BackColor = Color.LightCyan
                    End If
                Next
            Next

            For i As Integer = 0 To Me.dgvData.Rows.Count - 1
                If Not IsDBNull(Me.dgvData.Rows(i).Cells(3).Value) Then
                    If Me.dgvData.Rows(i).Cells(3).Value = "0" Then
                        Me.dgvData.Rows(i).DefaultCellStyle.ForeColor = Color.Red
                    End If
                Else

                End If

            Next
        End Sub
    End Class

    Tuesday, July 2, 2019 7:37 AM

Answers

  • Hi,

    Do you want to fill the datagridview? I think your methods are ok, but you didn't call them.

      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            cnn.ConnectionString = "Data Source = (localdb)\MSSQLLocalDB; Integrated Security = True ;AttachDbFileName= C:\Users\alexl2\Desktop\DataBase\Alex\alex.mdf"
            RefreshData()
            'loadTransactions(1)
        End Sub

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by christing Tuesday, July 2, 2019 8:51 AM
    Tuesday, July 2, 2019 8:24 AM
  • Hi,
    in your code are many problems:

    1. please, set OPTION STRICT ON for explicit type conversions.

    2. use "Using" for disposable types like OleDbDataAdapter

    3. use parameter in SQL statements to prevent SQL Injection

    4. you don't need connection.open if you use the DataAdapter

    5. your method "RefreshData" never be called

    Your code can look like this:

    Imports System.Data.OleDb
    
    Public Class Form29
    
      ' simulate Designer
      Private WithEvents dgvData As New DataGridView With {.Dock = DockStyle.Top}
      Private DataGridView1 As New DataGridView With {.Dock = DockStyle.Fill}
      Private WithEvents txtbarcode As New TextBox With {.Dock = DockStyle.Top}
      Private WithEvents btnclose As New Button With {.Dock = DockStyle.Top, .Text = "Close Form"}
    
      Dim cnn As New OleDb.OleDbConnection
      Private dbDataSet As New DataTable
    
      Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ' simulate Designer
        Me.Controls.AddRange(New Control() {DataGridView1, dgvData, txtbarcode, btnclose})
        '
        cnn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0; Data Source=mydataX.mdb;"
        RefreshData()
      End Sub
      Private Sub RefreshData()
        Using da As New OleDb.OleDbDataAdapter("SELECT cid, cbcid as [ID], " &
                                                 "CheckerName as [CheckerName], Quantity as [StockQuantity],Unit as [Unit], Date as [Date], CompanyName as [CompanyName], CheckerDetail as [CheckerDetail]" &
                                                 " FROM checker ORDER BY cid", cnn)
          Dim dt As New DataTable
          'fill data to datatable
          da.Fill(dt)
          Me.dgvData.DataSource = dt
          Me.dgvData.Columns("cid").Visible = False
        End Using
      End Sub
      Private Sub loadTransactions(ByVal transactionid As Integer)
        Using da As New OleDb.OleDbDataAdapter("SELECT s.cid as [ID], c.CheckerName as item_name, " &
                                                "s.CheckerName as [CheckerName],s.ShipQuantity as [StockQuantity], s.[Date] as [Date], s.CompanyName as [CompanyName], s.CheckerDetail as [CheckerDetail]" &
                                                "FROM stock s left join checker c on c.cid = s.checkercid where s.checkercid = @transactionid ORDER BY s.cid", cnn)
          da.SelectCommand.Parameters.AddWithValue("@transactionid", transactionid)
          Dim dt As New DataTable
          da.Fill(dt)
          Me.DataGridView1.DataSource = dt
          Me.DataGridView1.Columns("item_name").HeaderText = "CheckerName"
        End Using
      End Sub
      Private Sub txtbarcode_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtbarcode.TextChanged
        Using da As New OleDbDataAdapter("SELECT * FROM checker WHERE cbcid like @txtbarcode", cnn)
          da.SelectCommand.Parameters.AddWithValue(" @txtbarcode", $"%{txtbarcode.Text}%")
          Dim dt As New DataTable
          da.Fill(dt)
          dgvData.DataSource = dt.DefaultView
        End Using
      End Sub
    
      Private Sub dgvData_CellEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvData.CellEnter
        If dgvData.SelectedCells.Count > 0 Then
          loadTransactions(CInt(dgvData("cid", dgvData.CurrentCell.RowIndex).Value))
        End If
      End Sub
    
      Private Sub btnclose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnclose.Click
        Me.Close()
      End Sub
    
      Private Sub dgvData_CellFormatting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles dgvData.CellFormatting
        Dim dgv As DataGridView = Me.dgvData
        For i As Integer = 0 To dgv.Rows.Count - 1
          For ColNo As Integer = 1 To 7
            If Not dgv.Rows(i).Cells(ColNo).Value Is DBNull.Value Then
              dgv.Rows(i).Cells(ColNo).Style.BackColor = Color.LightCyan
            End If
          Next
        Next
    
        For i As Integer = 0 To Me.dgvData.Rows.Count - 1
          If Not IsDBNull(Me.dgvData.Rows(i).Cells(3).Value) AndAlso Me.dgvData.Rows(i).Cells(3).Value.ToString = "0" Then
            Me.dgvData.Rows(i).DefaultCellStyle.ForeColor = Color.Red
          End If
        Next
      End Sub
    End Class


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks


    Tuesday, July 2, 2019 8:41 AM

All replies

  • Hi,

    Do you want to fill the datagridview? I think your methods are ok, but you didn't call them.

      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            cnn.ConnectionString = "Data Source = (localdb)\MSSQLLocalDB; Integrated Security = True ;AttachDbFileName= C:\Users\alexl2\Desktop\DataBase\Alex\alex.mdf"
            RefreshData()
            'loadTransactions(1)
        End Sub

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by christing Tuesday, July 2, 2019 8:51 AM
    Tuesday, July 2, 2019 8:24 AM
  • Hi,
    in your code are many problems:

    1. please, set OPTION STRICT ON for explicit type conversions.

    2. use "Using" for disposable types like OleDbDataAdapter

    3. use parameter in SQL statements to prevent SQL Injection

    4. you don't need connection.open if you use the DataAdapter

    5. your method "RefreshData" never be called

    Your code can look like this:

    Imports System.Data.OleDb
    
    Public Class Form29
    
      ' simulate Designer
      Private WithEvents dgvData As New DataGridView With {.Dock = DockStyle.Top}
      Private DataGridView1 As New DataGridView With {.Dock = DockStyle.Fill}
      Private WithEvents txtbarcode As New TextBox With {.Dock = DockStyle.Top}
      Private WithEvents btnclose As New Button With {.Dock = DockStyle.Top, .Text = "Close Form"}
    
      Dim cnn As New OleDb.OleDbConnection
      Private dbDataSet As New DataTable
    
      Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ' simulate Designer
        Me.Controls.AddRange(New Control() {DataGridView1, dgvData, txtbarcode, btnclose})
        '
        cnn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0; Data Source=mydataX.mdb;"
        RefreshData()
      End Sub
      Private Sub RefreshData()
        Using da As New OleDb.OleDbDataAdapter("SELECT cid, cbcid as [ID], " &
                                                 "CheckerName as [CheckerName], Quantity as [StockQuantity],Unit as [Unit], Date as [Date], CompanyName as [CompanyName], CheckerDetail as [CheckerDetail]" &
                                                 " FROM checker ORDER BY cid", cnn)
          Dim dt As New DataTable
          'fill data to datatable
          da.Fill(dt)
          Me.dgvData.DataSource = dt
          Me.dgvData.Columns("cid").Visible = False
        End Using
      End Sub
      Private Sub loadTransactions(ByVal transactionid As Integer)
        Using da As New OleDb.OleDbDataAdapter("SELECT s.cid as [ID], c.CheckerName as item_name, " &
                                                "s.CheckerName as [CheckerName],s.ShipQuantity as [StockQuantity], s.[Date] as [Date], s.CompanyName as [CompanyName], s.CheckerDetail as [CheckerDetail]" &
                                                "FROM stock s left join checker c on c.cid = s.checkercid where s.checkercid = @transactionid ORDER BY s.cid", cnn)
          da.SelectCommand.Parameters.AddWithValue("@transactionid", transactionid)
          Dim dt As New DataTable
          da.Fill(dt)
          Me.DataGridView1.DataSource = dt
          Me.DataGridView1.Columns("item_name").HeaderText = "CheckerName"
        End Using
      End Sub
      Private Sub txtbarcode_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtbarcode.TextChanged
        Using da As New OleDbDataAdapter("SELECT * FROM checker WHERE cbcid like @txtbarcode", cnn)
          da.SelectCommand.Parameters.AddWithValue(" @txtbarcode", $"%{txtbarcode.Text}%")
          Dim dt As New DataTable
          da.Fill(dt)
          dgvData.DataSource = dt.DefaultView
        End Using
      End Sub
    
      Private Sub dgvData_CellEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvData.CellEnter
        If dgvData.SelectedCells.Count > 0 Then
          loadTransactions(CInt(dgvData("cid", dgvData.CurrentCell.RowIndex).Value))
        End If
      End Sub
    
      Private Sub btnclose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnclose.Click
        Me.Close()
      End Sub
    
      Private Sub dgvData_CellFormatting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles dgvData.CellFormatting
        Dim dgv As DataGridView = Me.dgvData
        For i As Integer = 0 To dgv.Rows.Count - 1
          For ColNo As Integer = 1 To 7
            If Not dgv.Rows(i).Cells(ColNo).Value Is DBNull.Value Then
              dgv.Rows(i).Cells(ColNo).Style.BackColor = Color.LightCyan
            End If
          Next
        Next
    
        For i As Integer = 0 To Me.dgvData.Rows.Count - 1
          If Not IsDBNull(Me.dgvData.Rows(i).Cells(3).Value) AndAlso Me.dgvData.Rows(i).Cells(3).Value.ToString = "0" Then
            Me.dgvData.Rows(i).DefaultCellStyle.ForeColor = Color.Red
          End If
        Next
      End Sub
    End Class


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks


    Tuesday, July 2, 2019 8:41 AM
  • Alex,

    thanks your answer.but i face other issue is column name cbcid cannot be found parameter name: column name.

    Private Sub dgvData_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvData.CellContentClick
            Try
                If e.RowIndex >= 0 Then
                    Dim row As DataGridViewRow
                    row = Me.dgvData.Rows(e.RowIndex)
                    'textbox detecd db show on textbox
                    txtbarcode.Text = row.Cells("cbcid").Value.ToString
                End If

            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub

    this coding is after i click database cbcid, cbcid data will show on my textbox.


    Tuesday, July 2, 2019 8:55 AM
  • Alex,

    thanks your answer.but i face other issue is column name cbcid cannot be found parameter name: column name.

    Private Sub dgvData_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvData.CellContentClick
            Try
                If e.RowIndex >= 0 Then
                    Dim row As DataGridViewRow
                    row = Me.dgvData.Rows(e.RowIndex)
                    'textbox detecd db show on textbox
                    txtbarcode.Text = row.Cells("cbcid").Value.ToString
                End If

            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub

    this coding is after i click database cbcid, cbcid data will show on my textbox.


    Hi,

    This error indicates that there is no column named cbcid in DataGridview. Please note that there are no spaces in the column name in Datagridview?

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 2, 2019 9:06 AM
  • Thanks a lot.:)
    Tuesday, July 2, 2019 9:24 AM