how to make a datagridview cellvaluechange 2 column which trigged loop-CodePudding

I am developing a sales order application. I am using a datagridview to fill the sales order.

the field in my datagridview are as per below

ItemCode - ItemDescription - qty - price

The description field is a combobox.

What I want is that when a user input an ItemCode, it automatically check my database and give me the Itemdescription

I also want user to be able to select an item from the ItemDescription which is a combobox, and it wil automatically update my Itemcode.

    Private Sub salesorder_dgv_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles salesorder_dgv.CellValueChanged
    If salesorder_dgv.Rows.Count > 0 Then
        If e.ColumnIndex = 0 Then
            Dim READER As SqlDataReader
            conn.Open()
            Dim query As String
            query = "select * from item where code = '" & salesorder_dgv.Rows(e.RowIndex).Cells(0).Value & "'"
            cmd = New SqlCommand(query, conn)
            READER = cmd.ExecuteReader
            If READER.Read Then
                salesorder_dgv.Rows(e.RowIndex).Cells(1).Value = READER.GetString(2)
            End If
            conn.Close()
        End If

        If e.ColumnIndex = 1 Then
            Dim READER As SqlDataReader
            conn.Open()
            Dim query As String
            query = "select * from item where description = '" & salesorder_dgv.Rows(e.RowIndex).Cells(1).Value & "'"
            cmd = New SqlCommand(query, conn)
            READER2 = cmd.ExecuteReader
            If READER.Read Then
                salesorder_dgv.Rows(e.RowIndex).Cells(0).Value = READER.GetString(1)
            End If
            conn.Close()
        End If
    End If
End Sub

Is there a way to make this code work? i am getting "The Connection was not closed"

CodePudding user response：

There's a lot wrong there so I'll first address what you have to clean it up, then address how you should be doing it.

As suggested in the comments, you should be creating all your ADO.NET objects where you need them, including the connection. You create, use and destroy in the narrowest scope possible. Also, if you only want data from a single column, don't use SELECT *. Retrieve only the column(s) you need. As you're only retrieving data from one column of one row, you should be using ExecuteScalar rather than ExecuteReader.

Next, you should acquaint yourself with the DRY principle, i.e. Don't Repeat Yourself. You have two blocks of code there that are almost identical so you should extract out the common parts and write that only once and pass in the different parts.

Finally, don't use string concatenation to insert values into SQL code. ALWAYS use parameters. It avoids a number of issues, most importantly SQL injection, which is quite possible in your case, as the user is entering free text. With all that in mind, the code you have would be refactored like so:

Private Sub salesorder_dgv_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles salesorder_dgv.CellValueChanged
    If salesorder_dgv.RowCount > 0 Then
        Dim sourceColumnIndex = e.ColumnIndex
        Dim targetColumnIndex As Integer
        Dim query As String

        Select Case sourceColumnIndex
            Case 0
                targetColumnIndex = 1
                query = "SELECT description FROM item WHERE code = @param"
            Case 1
                targetColumnIndex = 0
                query = "SELECT code FROM item WHERE description = @param"
            Case Else
                Return
        End Select

        Dim row = salesorder_dgv.Rows(e.RowIndex)
        Dim sourceValue = row.Cells(sourceColumnIndex).Value

        Using connection As New SqlConnection("connection string here"),
              command As New SqlCommand(query, connection)
            command.Parameters.AddWithValue("@param", sourceValue)
            connection.Open()

            row.Cells(targetColumnIndex).Value = command.ExecuteScalar()
        End Using
    End If
End Sub

Now to how you should have done it. If you're populating a combo box column with all the descriptions then you must be querying the database for them in the first place. What you should have done is retrieved both the descriptions and the codes in that initial query. That way, you never have to go back to the database. You can populate a DataTable with both the codes and the descriptions and then much of the work will be done for you.

For the example below, I started by setting up the form in the designer, which meant adding and configuring the appropriate columns in the grid and adding the BindingSource components. That also includes setting the DataPropertyName property of each grid column so it binds to the appropriate source column. I'm also manually populating the item data here but you would be getting that data from your database.

Private itemTable As New DataTable

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

Private Sub LoadItemData()
    With itemTable.Columns
        .Add("code", GetType(String))
        .Add("description", GetType(String))
    End With

    With itemTable.Rows
        .Add("123", "First Item")
        .Add("abc", "Second Item")
        .Add("789", "Third Item")
        .Add("xyz", "Fourth Item")
        .Add("01a", "Fifth Item")
    End With

    itemBindingSource.DataSource = itemTable

    With itemDescriptionColumn
        .DisplayMember = "Description"
        .ValueMember = "Description"
        .DataSource = itemBindingSource
    End With
End Sub

Private Sub LoadSaleData()
    Dim saleTable As New DataTable

    With saleTable.Columns
        .Add("ItemCode", GetType(String))
        .Add("ItemDescription", GetType(String))
        .Add("Quantity", GetType(Integer))
        .Add("Price", GetType(Decimal))
    End With

    saleBindingSource.DataSource = saleTable
    salesorder_dgv.DataSource = saleBindingSource
End Sub

Private Sub salesorder_dgv_CellValidating(sender As Object, e As DataGridViewCellValidatingEventArgs) Handles salesorder_dgv.CellValidating
    If e.RowIndex >= 0 AndAlso
       e.ColumnIndex = 0 AndAlso
       Not String.IsNullOrEmpty(e.FormattedValue) Then
        'Check that the code entered by the user exists.
        e.Cancel = (itemBindingSource.Find("code", e.FormattedValue) = -1)

        If e.Cancel Then
            MessageBox.Show("No such item")
        End If
    End If
End Sub

Private Sub salesorder_dgv_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles salesorder_dgv.CellValueChanged
    Dim rowIndex = e.RowIndex
    Dim sourceColumnIndex = e.ColumnIndex

    If rowIndex >= 0 And sourceColumnIndex >= 0 Then
        Dim sourceColumnName As String
        Dim targetColumnName As String
        Dim targetColumnIndex As Integer

        Select Case sourceColumnIndex
            Case 0
                sourceColumnName = "code"
                targetColumnName = "description"
                targetColumnIndex = 1
            Case 1
                sourceColumnName = "description"
                targetColumnName = "code"
                targetColumnIndex = 0
            Case Else
                Return
        End Select

        Dim itemRow = itemBindingSource(itemBindingSource.Find(sourceColumnName, salesorder_dgv(sourceColumnIndex, rowIndex).Value))
        Dim code = CStr(itemRow(targetColumnName))

        salesorder_dgv(targetColumnIndex, rowIndex).Value = code
    End If
End Sub

You start by populating the items and binding that data to the combo box column and then create an empty DataTable for the sales and bind that to the grid. The code checks that any manually entered codes actually do match items and it will set the description when a code is entered manually and the code when a description is selected from the list. It does this by referring back to the BindingSource containing the item data each time, so no extra queries. You might want to consider retrieving the price data for each item too, and calculating the price for that row based on that and the quantity.