No row can be added to a DataGridView control that does not have columns. Columns must be added first. in VB.net

Question

I have been encountering an error in my VB.Net code that says "No row can be added to a DataGridView control that does not have columns. Columns must be added first.". When I was trying to code a live search on a datagridview1. Please help me.

Here is the code for the datagrid live search that encounters an error:

Private Sub txt_search_TextChanged(sender As Object, e As EventArgs) Handles txt_search.TextChanged
    DataGridView1.Rows.Clear()
    '' Searching via room number or category id, ie room type
    Try
        conn.Open()
        Dim cmd As New MySqlCommand("SELECT r.id, r.room_no, c.name, r.description, r.price FROM categories c JOIN rooms r ON c.id = r.category_id WHERE room_no LIKE '%" & txt_search.Text & "%' or name LIKE '%" & txt_search.Text & "%'", conn)
        dr = cmd.ExecuteReader
        While dr.Read
            DataGridView1.Rows.Add(dr.Item("ID"), dr.Item("room_no"), dr.Item("name"), dr.Item("description"), dr.Item("price"))
        End While
        dr.Dispose()
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
    conn.Close()
End Sub

Answer 1

I would do a few things differently.

1. You should not declare your connection at the class level, instead create a new connection and dispose immediately when done.
2. Same with the DataRow.
3. I personally wouldn't like to see results immediately after typing a character in a searchbox. If I have a typo, then want to erase it and fix it, I need to wait for the typo result, then see the fixed result. We'll use a timer to create this delay.
4. Which brings me to the other issue, which is that you are hitting your database on the UI thread, so add that to the last item, your end user is experiencing a UI freeze while waiting for results with each keypress. We will query off the UI and invoke the update back when done.
5. Don't put textbox Text directly into a query or you will be opened up to sql injection issues. We will use parameters

(Oh, and I haven't even arrived at your question yet!)

6. You can simply databind the result to the grid instead of trying to add rows. You can only add rows if the grid has predefined rows with a template for new rows. You can do that but this locks your UI into a particular design so if you want to change the query later, you will also need to fix the UI. Too tightly coupled. We'll use a class to define the data. You will get these if you use an ORM which is vastly superior to using a DataReader, but you don't so I made one.

Let's see...

Private Class Result
    Public Sub New(iD As Integer, room_no As String, name As String, description As String, price As Decimal)
        Me.ID = iD
        Me.Room_no = room_no
        Me.Name = name
        Me.Description = description
        Me.Price = price
    End Sub
    ' Update data types according to your actual database types
    Public Property ID As Integer
    Public Property Room_no As String
    Public Property Name As String
    Public Property Description As String
    Public Property Price As Decimal
End Class

You will want to make a timer to tick when you want to hit the database. Also record the current state of the textbox at this time. This is all you do in the TextChanged event handler

Private queryTimer As New System.Threading.Timer(AddressOf queryCallback)
Private queryTerm As String

Private Sub txt_search_TextChanged(sender As Object, e As EventArgs) Handles txt_search.TextChanged
    queryTerm = txt_search.Text.Trim() ' do you want to trim whitespace?
    ' Execute search 500ms later.
    ' If multiple keystrokes are pressed quickly,
    ' previous timer schedules are cancelled and only the last happens.
    queryTimer.Change(500, -1)
End Sub

This is the timer callback, which runs off the UI and invokes the UI update when it's done

Private Sub queryCallback(state As Object)
    ' This routine is called on a background thread, off the UI
    Dim results As New List(Of Result)()
    Using conn As New MySqlConnection("connection string")
        conn.Open()
        ' build command with parameter
        Using cmd As New MySqlCommand(
$"SELECT r.id, r.room_no, c.name, r.description, r.price 
FROM categories c 
JOIN rooms r ON c.id = r.category_id 
WHERE room_no LIKE '%@search%' 
or name LIKE '%@search%'", conn)
            cmd.Parameters.AddWithValue("@search", queryTerm)
            Using dr = cmd.ExecuteReader()
                While dr.Read()
                    ' Update conversions according to actual data types
                    ' here as well as in the class I provided.
                    results.Add(New Result(
                                Convert.ToInt32(dr("id")),
                                dr("room_no").ToString(),
                                dr("name").ToString(),
                                dr("description").ToString(),
                                Convert.ToDecimal(dr("price"))))
                End While
            End Using
        End Using
    End Using
    ' Can't update the UI from a background thread, so this call is invoked
    DataGridView1.Invoke(
        Sub()
            DataGridView1.DataSource = Nothing
            DataGridView1.DataSource = results
        End Sub)
End Sub

Additional information is in remarks in the code provided