Fill DataGridView from second datagridview with sql statment

Question

Im working with Sql localdb and i want to get data from current row from datagridview to another one this my code

     Private Sub FrmSales_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            Dim dadasales As New SqlDataAdapter
            Dim dtsales As New DataTable
            dtsales.Clear()
            dadasales = New SqlDataAdapter("Select ItemsInfo.Category From ItemsInfo Group By ItemsInfo.Category Order By ItemsInfo.Category", con)
            dadasales.Fill(dtsales)
            DGVCategory.DataSource = dtsales
    
    
    
        End Sub
    
        Private Sub lbHome_Click(sender As Object, e As EventArgs) Handles lbHome.Click
            frmHome.Show()
            Me.Close()
        End Sub
    
        Private Sub DGVCategory_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DGVCategory.CellClick
    
            Dim pos = DGVCategory.CurrentRow.Index
            Dim category As String = DGVCategory.Rows(pos).Cells(0).Value
            Dim daItem As New SqlDataAdapter
            Dim dtItem As New DataTable
    
            daItem = New SqlDataAdapter("Select ItemsInfo.Item, ItemsInfo.Price From ItemsInfo Where ItemsInfo.Category = '" & category & "'   Order By ItemsInfo.Category", con)
    
            daItem.Fill(dtItem)
            DgvItems.DataSource = dtItem

the datagridview get the data from the database but the second datagridview get empty data i dont know why ?

            Dim pos = DGVCategory.CurrentRow.Index
            Dim category As String = DGVCategory.Rows(pos).Cells(0).Value
            Dim daItem As New SqlDataAdapter
            Dim dtItem As New DataTable
    
            daItem = New SqlDataAdapter("Select ItemsInfo.Item, ItemsInfo.Price From ItemsInfo Where ItemsInfo.Category = '" & category & "'   Order By ItemsInfo.Category", con)
    
            daItem.Fill(dtItem)
            DgvItems.DataSource = dtItem

I think the problem was on Sql statement but i dont know how To fix it

Well, first thing I would suggest to you, use params for your queries. Just makes the whole thing more pleasant to debug. I'm not sure exactly what the difference between the DGVCategory_CellClick and your second snippet but appear identical, Assuming 2nd snippet is the 2nd query your mentioned, are you sure the value of pos is correct and hasn't been reset somewhere? What debugging have you done? Is the 2nd query returning data at all? — Hursey
– Hursey, Commented Apr 30, 2021 at 21:43
Yeah, understand the dvg isn’t showing the data, have you inspected the results of the query via debugging to ensure the expected data is being returned — Hursey
– Hursey, Commented May 1, 2021 at 8:28
Yes the data return the problem i guess here Dim pos = DGVCategory.CurrentRow.Index Dim category As String = DGVCategory.Rows(pos).Cells(0).Value — Chaker
– Chaker, Commented May 1, 2021 at 9:01

Mary · Accepted Answer · 2021-05-02 03:35:35Z

I separated the database code from the user interface code. This makes it easier to maintain.

If you are going to let your DataAdapter fall out of scope, there isn't much point in using one. Just load a Datatable with a reader.

dtsales.Clear()

It is a bit silly to clear a DataTable that you just created on the previous line.

Connections, Commands, and DataReaders need to be disposed. Using...End Using blocks do this even if there is an error. This is why connections need to be declared in the method where they are used, not on the form level.

Repeating the table name with each field in the CommandText is not necessary when you are only dealing with a single table.

There is no reason for a Group By clause. I believe what you want is Distinct. This will give you a single instance of each category.

Private ConStr As String = "Your connection string"

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Try
        DGVCategory.DataSource = GetSalesData()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

Private Function GetSalesData() As DataTable
    Dim dtsales As New DataTable
    Using con As New SqlConnection(ConStr),
            cmd As New SqlCommand("Select Distinct Category From ItemsInfo Order By Category;", con)
        con.Open()
        Using reader = cmd.ExecuteReader
            dtsales.Load(reader)
        End Using
    End Using
    Return dtsales
End Function

Private Sub DGVCategory_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DGVCategory.CellClick
    Dim pos = DGVCategory.CurrentRow.Index
    Dim category As String = DGVCategory.Rows(pos).Cells(0).Value.ToString
    Debug.Print(category) 'Check if you are passing what you expect
    Try
        DgvItems.DataSource = GetCategoryData(category)
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

Private Function GetCategoryData(category As String) As DataTable
    Dim dtItem As New DataTable
    Using con As New SqlConnection(ConStr),
            cmd As New SqlCommand("Select Item, Price From ItemsInfo Where Category = @Category Order By Category", con)
        cmd.Parameters.Add("@Category", SqlDbType.VarChar).Value = category
        con.Open()
        Using reader = cmd.ExecuteReader
            dtItem.Load(reader)
        End Using
    End Using
    Return dtItem
End Function

You may want to use a ListBox or ComboBox to display the categories. Try to choose the appropriate control for each user interaction.

One more thing. Turn on Option Strict for this and all you code.

The same problem the first datagridview for category get data but the second one empty just column names
Its ok i was wrong with SqlDbType.VarChar i change it to SqlDbType.NVarChar and all is good Thank you very much for your help

Collectives™ on Stack Overflow

Fill DataGridView from second datagridview with sql statment

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related