如何在datagridview中检查空白单元格 [英] How to check for blank cells in datagridview
问题描述
有人可以告诉我如何使用带有空白单元格的datagridview更新表吗?
我用datagridview创建了一个数据输入表单,在设计器中有四列。我想将一些列的单元格留空,并将空白单元格保存为表格中的零。如果没有空白单元格,我可以将datagridview内容保存到表格中。
Dim nonqueryCommand As SqlCommand = thisConnection.CreateCommand()
尝试
' 打开连接
thisConnection.Open()
Console.WriteLine( Connection Opened)
' 创建带有命名参数的INSERT语句
nonqueryCommand.CommandText = _
INSERT INTO Table3(Col1,Col2,col3,col4)VALUES(@ Col1,@ Col2 ,@ col3,@ col4)
nonqueryCommand.Prepare()
' 要插入的数据
For 每个行 As DataGridViewRow 在 DataGridView1.Rows
如果 不 row.IsNewRow 然后
nonqueryCommand.Parameters.Add( @ Col1,SqlDbType.VarChar, 50 )。Value = DBNull.Value
nonqueryCommand.Parameters.Add( @ Col2,SqlDbType.VarChar, 50 )。值= DBNull.Value
nonqueryCommand.Parameters.Add( @ Col3,SqlDbType.VarChar, 50 )。Value = DBNull.Value
nonqueryCommand.Parameters.Add(< span class =code-string> @ Col4,SqlDbType.VarChar, 50 )。Value = DBNull.Value
nonqueryCommand.Parameters( @ Col1)。Value = row.Cells( 1 )。Value.ToString()
nonqueryCommand.Parameters( @ Col2)。值= row.Cells( 2 ) .Value.ToString()
nonqueryCommand.Parameters( @ Col3)。Value = row.Cells( 3 )。Value.ToString()
nonqueryCommand.Parameters( < span class =code-string> @ Col4)。Value = row.Cells( 4 )。Value.ToString()
nonqueryCommand.ExecuteNonQuery()
结束 如果
下一步
Catch ex As SqlException
' 显示错误
控制台.WriteLine( 错误:& ex.ToString())
最后
' 关闭连接
thisConnection.Close()
Console.WriteLine( 连接已关闭)
结束 尝试
我的尝试:
我不知道这是否是检查空单元格以保存到表中的正确方法。当我在try和Catch之间放置代码时出现错误因为SqlException
此OleDbParameterCollection不包含ParameterName'@ Col1'的OleDbParameter
如果 row.Cells( 0 )。Value.ToString IsNot 没什么 那么
nonqueryCommand.Parameters( @ Col1)。值=行。单元格( 0 )。Value.ToString()
else
nonqueryCommand.Parameters ( @ Col1)。值= 0
end if
检查特定单元格值的代码是空的,保存为0是正确的
但你必须这样做运行Sql命令时代码中的一些小变化..
如果看到代码,则继续添加命令参数在循环内部,因为命令对象在循环外声明。应该避免它。
尝试在每个循环中添加整个代码。
对于每个事务,数据库连接应该已打开
和已关闭
,否则在大循环的情况下无法按预期工作。
尝试删除 prepare()
并检查它。
请阅读这个 [ ^ ]。
Could somebody tell me how to update table with datagridview with blank cell in it?
I have created a data entry form with datagridview with four columns in designer. I want to leave some cells of a column blank and save blank cells as zero in table. If there are no blank cells I can save datagridview content into table.
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 Table3 (Col1, Col2,col3,col4) VALUES (@Col1, @Col2,@col3,@col4)"
nonqueryCommand.Prepare()
' Data to be inserted
For Each row As DataGridViewRow In DataGridView1.Rows
If Not row.IsNewRow Then
nonqueryCommand.Parameters.Add("@Col1", SqlDbType.VarChar, 50).Value = DBNull.Value
nonqueryCommand.Parameters.Add("@Col2", SqlDbType.VarChar, 50).Value = DBNull.Value
nonqueryCommand.Parameters.Add("@Col3", SqlDbType.VarChar, 50).Value = DBNull.Value
nonqueryCommand.Parameters.Add("@Col4", SqlDbType.VarChar, 50).Value = DBNull.Value
nonqueryCommand.Parameters("@Col1").Value = row.Cells(1).Value.ToString()
nonqueryCommand.Parameters("@Col2").Value = row.Cells(2).Value.ToString()
nonqueryCommand.Parameters("@Col3").Value = row.Cells(3).Value.ToString()
nonqueryCommand.Parameters("@Col4").Value = row.Cells(4).Value.ToString()
nonqueryCommand.ExecuteNonQuery()
End If
Next
Catch ex As SqlException
' Display error
Console.WriteLine("Error: " & ex.ToString())
Finally
' Close Connection
thisConnection.Close()
Console.WriteLine("Connection Closed")
End Try
What I have tried:
I don’t know if this is correct way to check for empty cell in order to save into table. I get an error when I place the code between try and Catch ex As SqlException
An OleDbParameter with ParameterName '@Col1' is not contained by this 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
Hi Josef
Your code for checking the particular cell value is empty and save with 0 is correct
but you will have to do some little change in your code while running the Sql Command..
if you see the code, you are keep on adding the command parameters inside the loop since the command object is declared outside the loop. it should be avoided.
Try adding the entire code inside the for each loop.
For every transaction the data base connection should beopened
andclosed
, else it wont work as expected in case of large loop.
Try removingprepare()
and check it.
Please read this[^] before using Prepare().
这篇关于如何在datagridview中检查空白单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!