如何将datagridview空单元格保存到数据库表

Question

有人可以告诉我如何更新带有空单元格的datagridview表吗？ 我已经在设计器中创建了一个带有两列的datagridview数据输入表单。我想留下一列空白的一些单元格，并将表格中的空白单元格保存为零。如果没有空白单元格，我可以节省datagridview的内容转换成表格

Dim thisConnection As New SqlConnection() 
Dim nonqueryCommand As SqlCommand = thisConnection.CreateCommand() 

Try 
    ' Open Connection 
    thisConnection.Open() 
    Console.WriteLine("Connection Opened") 

    ' Create INSERT statement with named parameters 
    nonqueryCommand.CommandText = _ 
     "INSERT INTO myTable (Col1, Col2) VALUES (@Col1, @Col2)" 
    ' Add Parameters to Command Parameters collection 
    nonqueryCommand.Parameters.Add("@Col1", SqlDbType.VarChar, 50) 
    nonqueryCommand.Parameters.Add("@Col2", SqlDbType.VarChar, 50) 

    ' Prepare command for repeated execution 
    nonqueryCommand.Prepare() 

    ' Data to be inserted 
    For Each row As DataGridViewRow In DataGridView1.Rows 
    If Not row.IsNewRow Then 
     nonqueryCommand.Parameters("@Col1").Value = row.Cells(0).Value.ToString 
     nonqueryCommand.Parameters("@Col2").Value = row.Cells(1).Value.ToString 
    End If 
    Next 

    nonqueryCommand.ExecuteNonQuery() 

Catch ex As SqlException 
    ' Display error 
    Console.WriteLine("Error: " & ex.ToString()) 
Finally 
    ' Close Connection 
    thisConnection.Close() 
    Console.WriteLine("Connection Closed") 

End Try 

我不知道这是为了保存到表以检查空单元格正确的方法。我得到一个错误，当我把try和catch前作为SQLEXCEPTION 与参数名称的OleDbParameter“@ Col1中”之间的代码不受此OleDbParameterCollection

If row.Cells(0).Value.ToString IsNot Nothing Then 
    nonqueryCommand.Parameters("@Col1").Value = row.Cells(0).Value.ToString() 
else 
    nonqueryCommand.Parameters("@Col1").Value = "0" 
end if 

Answer 1

你做太多与你插入包含。您只需调用Parameters.AddWithValue即可，但此处的关键是将您的对象值转换/转换为DB匹配的数据类型。假设两个值都是varchar并且可以为空。见代码注释

nonqueryCommand.CommandText = _ 
    "INSERT INTO myTable (Col1, Col2) VALUES (@Col1, @Col2)" 

' Prepare Parameters with correct Data Type 

nonqueryCommand.Parameters.AddWithValue("@Col1", String.Empty) 
nonqueryCommand.Parameters.AddWithValue("@Col2", String.Empty) 

For Each row As DataGridViewRow In DataGridView1.Rows 
    If Not row.IsNewRow Then 
     nonqueryCommand.Parameters(0).Value = 
      If(row.Cells(0).Value Is Nothing, DbNull.Value, row.Cells(0).Value.ToString()) 
     nonqueryCommand.Parameters(1).Value = 
      If(row.Cells(1).Value Is Nothing, DbNull.Value, row.Cells(0).Value.ToString()) 

     ' Here Execute your query for each row 
     If nonqueryCommand.ExecuteNonQuery() > 0 Then 
      Debug.WriteLine("Inserted Values") 
     End If 
    End If 
Next 

这应该是你所需要的。注 - 只有当您有输出参数时，才需要设置参数的大小。在输入时，大小会在您添加值时自动设置。但请记住在检索字符串时要设置大小来自参数的数据。