Reuse of DGV doesn't fill the grid

[RafStorr]

I have an invoice form using a data grid (DGVInvoice). When I start to enter a description, this shows another grid in the same Form (DGVMatches) that shows all matches from the Invoice/Description field in the database. Clicking any of the matches updates the DGVInvoice description field.

This works perfectly on Line 1 of the invoice, but when I enter a description on subsequent lines, no matches are displayed in DGVMatches (although the DGVMatches header shows the search term correctly).

Code in DGVInvoice to call DGVMatches:

Private Sub ShowMatches()

      ' @@ Fetch Descriptions from LineEntry table
      Dim pSQL As String = "SELECT DISTINCT [Desc] FROM LineEntry WHERE [Desc] LIKE '" & mCellContent & "%' ORDER BY [Desc] "
      Dim m_Datatable As New DataTable

      Using cnn As New OleDbConnection(gs_Connection)
          cnn.Open()
          Using dad As New OleDbDataAdapter(pSQL, cnn)
              dad.Fill(m_Datatable)
              dad.Dispose()
          End Using
          cnn.Close()
      End Using

      GbMatching.Text = "Matching entries for '" & mCellContent & "'"

      If m_Datatable.Rows.Count = 0 Then  ' ** No data
          GbMatching.Visible = False
      Else
          With DgvMatches
              .DataSource = m_Datatable
              GbMatching.Visible = True
              .Columns(0).Width = 360
              .Columns(0).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft   ' @@ Aligns cells
              .Refresh()
          End With
          m_Datatable = Nothing
      End If

    End Sub

Code in DGVMatches to fill the DGVInvoice Description field:

Private Sub DgvMatches_Click(sender As Object, e As EventArgs) Handles DgvMatches.Click
      With DgvInvoice
          .CurrentCell.Value = DgvMatches.CurrentCell.Value         
          DgvInvoice.BeginEdit(False)
      End With

      GbMatching.Visible = False
      mMatchFound = True
      DgvMatches.Dispose()

  End Sub

Can anyone help with this?

[jdelano]

Have you stepped through the code with debugging? Maybe run the query manually to ensure it should be returning records?

[RafStorr]

If I select from the DGVMatches grid, the following line fails to display the matching data but as I said, the Matches header does show the search term.
If I don't select from the Matches grid, the following line does show all matches.
I think that the problem is in the ShowMatches routine, because of the correct header, and when debugging, the Row.Count is more than zero. Nothing is showing any errors.

I'm stuck because as the code operates correctly on the first row that it's used, the code would seem to be correct. The only thing I can think of is that I haven't closed the connection or DGVMatches correctly so conditions are somehow different on the second iteration, but it all looks ok to me.

Puzzled of Cambridge!

[wes4dbt]

I don't see where "ShowMatches" is called from. You need to include that.

Also, always wrap the code in code tags. Just click "#" and insert the code. That way it retains the formatting and easier to read.

[RafStorr]

Oops! Thanks for the tip about the # wrapper.

I should have mentioned that DGVMatches is inside a group box called GbMatching.
GbMatching.text inside ShowMatches always shows the search string, so the query must be working.

Thanks for your help.

   Private Sub DgvInvoice_EditingControlShowing(sender As Object, e As DataGridViewEditingControlShowingEventArgs) Handles DgvInvoice.EditingControlShowing
       EditingTextBox = TryCast(e.Control, TextBox)
   End Sub
   
   Private Sub Editingtextbox_KeyDown(sender As Object, e As KeyEventArgs) Handles EditingTextBox.KeyDown
       ' @@ Handles call to ShowMatches 
       If EditingTextBox IsNot Nothing Then
           If DgvInvoice.CurrentCell.ColumnIndex = 4 Then  ' @@ Description cell for invoice line
               mCellContent += (e.KeyCode).ToString
 
               ' @@ Show Autofill matches
               If Len(mCellContent) > 0 Then
                   GbMatching.Visible = True
                   ShowMatches()
               Else
                   GbMatching.Visible = False
               End If
           End If
       End If
   End Sub

[jmcilhinney]

Oops! Thanks for the tip about the # wrapper.

Except you got it wrong. They didn't say to type that character. They said to click it, i.e. click the toolbar button with that symbol on it. Surely you've used text editors with toolbars for formatting before. I fixed it for you this time.

[RafStorr]

Thank you - no I haven't seen this before, but then I've never posted for help before either.

[wes4dbt]

You use mCellcontent as your search parameter, I don't know what's in the Desc field but this seems strange,

mCellContent += (e.KeyCode).ToString

You shouldn't concatenate strings like that, it should be mCellContent &= (e.KeyCode).ToString.

Unless mCellContent isn't a string but if that's true then this is wrong, " [Desc] LIKE '" & mCellContent & "%' "

mCellContent will just get larger and larger. Maybe that's correct, I don't know. I'd suggest putting a breakpoint here "If Len(mCellContent) > 0 Then" and check the value of mCellContent.

[RafStorr]

Problem resolved. I had a line in other code that stopped this code from working properly.
All the code listed in this thread works correctly.

Sorry if I caused some head scratching...

[RafStorr]

Thanks to wes4dbt - I have corrected my concatenation. (Yes, mCellContent is a string).