时间戳

首页 > 解决方案 > 如何从 TextBox 中搜索并从 SQL 数据库中填充 DataGridView 的特定列？

vb.net - 如何从 TextBox 中搜索并从 SQL 数据库中填充 DataGridView 的特定列？

问题描述

我正在尝试按 TextBox1 中提供的项目代码进行搜索。我在 DataGridView 的设计器中手动创建了标题。我的代码成功查询了数据库，但将附加列附加到查询返回的完整结果中。

我的代码：

Private Sub Button13_Click(sender As Object, e As EventArgs) Handles Button13.Click

    On Error Resume Next

    Using cn As New SqlConnection("server= PANKAJ\SQLEXPRESS; database = pankaj billing software; integrated security=true")
        Using cmd2 As New SqlCommand("select itemcode As 'Item Code', item,qty As Quantity, weight as Weight from stockdata Where itemcode = @itemcode;", cn)

            cmd2.Parameters.AddWithValue("@itemcode", TextBox1.Text)
            cn.Open()

            Dim dr As SqlDataReader = cmd2.ExecuteReader()
            dt.Load(dr)

            DataGridView1.DataSource = dt

            For Each row As DataGridViewRow In DataGridView1.Rows
                cmd2.Parameters.Add("@item", SqlDbType.VarChar)
                cmd2.Parameters.Add("@qty", SqlDbType.VarChar)
                cmd2.Parameters.Add("@weight", SqlDbType.VarChar)

                With cmd2
                    row.Cells(1).Value = .Parameters("@item").Value
                    row.Cells(2).Value = .Parameters("@qty").Value
                    row.Cells(2).Value = .Parameters("@weight").Value
                End With
                cmd2.ExecuteNonQuery()
            Next
        End Using
    End Using
End Sub

标签： vb.netdatagridviewsqldatareader

解决方案

很确定你正在尝试完成这样的事情。但正如其他人指出的那样，有更好的方法可以将数据实际绑定到网格。

    Try
        Using cn As New SqlConnection("server= PANKAJ\SQLEXPRESS; database = pankaj billing software; integrated security=true")
            Using cmd2 As New SqlCommand("select itemcode As 'Item Code', item,qty As Quantity, weight as Weight from stockdata Where itemcode = @itemcode;", cn)

                cmd2.Parameters.AddWithValue("@itemcode", TextBox1.Text)
                cn.Open()

                Dim dr As SqlDataReader = cmd2.ExecuteReader()

                'Read each line
                While dr.Read()

                    Using record As New DataGridViewRow

                        'Get the individual items you want to return
                        Dim item As New DataGridViewTextBoxCell With {.Value = dr.GetValue(dr.GetOrdinal("item"))}
                        Dim qty As New DataGridViewTextBoxCell With {.Value = dr.GetValue(dr.GetOrdinal("qty"))}
                        Dim weight As New DataGridViewTextBoxCell With {.Value = dr.GetValue(dr.GetOrdinal("weight"))}

                        'Add each of those cells to the row
                        record.Cells.Add(item)
                        record.Cells.Add(qty)
                        record.Cells.Add(weight)

                        'Add the entire row to the DataGridView
                        DataGridView1.Rows.Add(record)
                    End Using
                End While
            End Using
        End Using
    Catch ex As SqlException
        Console.WriteLine(ex)
    End Try

推荐阅读