无法将行添加到没有列的DataGridView控件中。必须先添加列。在vb.net中

Question

我在我的VB.NET代码中遇到了一个错误，上面写着：“没有行可以添加到没有列的DataGridView控件中。必须先添加列。”。当我尝试在DataGridView1上进行实时搜索时。请帮我。

这是遇到错误的DataGrid实时搜索的代码：

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

我会做一些不同的事情。

1. 您不应在班级级别声明您的连接，而应创建一个新的连接并在完成后立即处置。
2. 与datarow相同。
3. 我个人不想在搜索框中键入字符后立即看到结果。如果我有错别字，那么要删除并修复它，我需要等待错别字结果，然后查看固定结果。我们将使用计时器来创建此延迟。
4. 这将我带到了另一个问题，也就是说，您要在UI线程上击中数据库，因此将其添加到最后一个项目中，您的最终用户在等待每一个的结果时都会遇到a ui freeze 按键。我们将在UI上查询，并在完成后调用更新。
5. 不要将文本框文本直接放入查询中，否则您将被开放到SQL注入问题。我们将使用参数

（哦，我什至还没有提出您的问题！）

6. 您可以简单地将结果数据作为网格数据，而不是尝试添加行。您只能在网格具有预定义的行带有新行模板的预定义行时添加行。您可以做到这一点，但这将UI锁定到特定的设计中，因此，如果您以后要更改查询，也需要修复UI。太紧了。我们将使用类定义数据。如果您使用的ORM优于使用DataReader，但是您不这样做，那么您将获得这些。

让我们看看...

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

当您想点击数据库时，您将需要制作一个计时器。还记录此时文本框的当前状态。这是您在文本变化事件处理程序中所做的一切，

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

这是计时器回调，该计时器回调在完成后运行并调用UI更新

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

。