sql-server - 从 DataGridView 获取当前单元格值

Question

我正在尝试获取current cell valueDataGridView。此当前单元格值为product name. 当用户键入the product name时，我想自己填充Unit。所以我需要获取当前的单元格值，以便将其传递给数据库并获取产品的预期单位。我使用一个变量 ( Product) 来存储当前单元格值，并且每当我调试该变量包含的代码时nothing。

我将我的代码放在下面让您了​​解

Private Sub dataGridView2_CellValidating(ByVal sender As Object, ByVal e As DataGridViewCellValidatingEventArgs) Handles DataGridView2.CellValidating
    If e.ColumnIndex = 1 Then

        Dim product As String = DataGridView2.CurrentCell.Value
        Dim rowind As Integer = DataGridView2.CurrentCell.RowIndex
        Dim columnind As Integer = DataGridView2.CurrentCell.ColumnIndex
        Using ConnectionString As New SqlConnection("data source=ADMIN-PC\SQLEXPRESS; database=billdev;Trusted_Connection=yes;")
            Dim strSQL As String = ("SELECT Unit from b_productMst where productname =" + product)
            Dim SQLcommand As New SqlCommand(strSQL, ConnectionString)
            ConnectionString.Open()
            Dim reader As SqlDataReader
            reader = SQLcommand.ExecuteReader()
            DataGridView2.Rows(rowind).Cells(columnind) = reader.Item(0)
        End Using
        End If
    End Sub

我什至尝试使用

 product = DataGridView2.Rows(rowind).Cells(columnind).Value

但没有用。

我在上面提到了我的目标。请让我知道是否有任何其他方法可以实现这一目标。

标签： sql-servervb.netdatagridview

Answer 1

不要使用类的名称作为变量的名称。如，不要Dim SqlCommand As New SqlCommand。

命令和阅读器都需要使用块。（他们公开 .Dispose 方法）。该命令包含在与连接相同的块中。注意第一行结尾的逗号。

始终使用参数来避免 Sql 注入。我不得不猜测数据类型和字段大小。检查您的数据库以获取正确的值并相应地调整代码。

DataReader 在第一条记录之前启动。您需要调用 .Read 移动到第一条记录。

Private Sub dataGridView1_CellValidating(ByVal sender As Object, ByVal e As DataGridViewCellValidatingEventArgs) Handles DataGridView1.CellValidating
    If e.ColumnIndex = 1 Then
        Dim product As String = DataGridView1.CurrentCell.Value.ToString 'Value is an Object
        Dim rowind As Integer = DataGridView1.CurrentCell.RowIndex
        Dim columnind As Integer = 1 'The If block code wouldn't be executing unless the ColumnIndex = 1
        Using Con As New SqlConnection("data source=ADMIN-PC\SQLEXPRESS; database=billdev;Trusted_Connection=yes;"),
            cmd As New SqlCommand("SELECT Unit from b_productMst where productname = @Product;", Con)
            cmd.Parameters.Add("@Product", SqlDbType.VarChar, 100).Value = product
            Con.Open()
            Using reader = cmd.ExecuteReader()
                reader.Read()
                DataGridView1.Rows(rowind).Cells(columnind + 1).Value = reader.Item(0).ToString
            End Using
        End Using
    End If
End Sub

除非 b_productMst 表很大，否则我更愿意看到在 Form.Load 中下载并作为 DataTable 或 Dictionary 存储在内存中的所有数据。这将避免对数据库的重复命中。

Private dt As New DataTable

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Using Con As New SqlConnection("data source=ADMIN-PC\SQLEXPRESS; database=billdev;Trusted_Connection=yes;"),
            cmd As New SqlCommand("SELECT productname, Unit from b_productMst; ", Con)
        Con.Open()
        dt.Load(cmd.ExecuteReader)
    End Using
End Sub

Private Sub dataGridView1_CellValidating(ByVal sender As Object, ByVal e As DataGridViewCellValidatingEventArgs) Handles DataGridView1.CellValidating
    If e.ColumnIndex = 1 Then
        Dim product As String = DataGridView1.CurrentCell.Value.ToString 'Value is an Object
        Dim rowind As Integer = DataGridView1.CurrentCell.RowIndex
        Dim columnind As Integer = 1 'The If block code wouldn't be executing unless the ColumnIndex = 1
        DataGridView1.Rows(rowind).Cells(columnind + 1).Value = dt.Select("productname = product")(0)(2)
    End If
End Sub