如何根据datagridview复选框从数据库中删除值 [英] How to delete values from database based on datagridview checkbox

问题描述

btndelete_Click()
for each row as DatagridViewRows In DGV.Rows
 If Convert.ToBoolean(d.Cells(3).Value) = True Then
 Try
sql = "Delete from Student Where ID = '" & row.Cells(0).Value & "'"
connect()
cmd = New OledbCommand (sql,conn)
cmd.ExecuteNonQuery()

Catch ex As Exception
End Try
Next
End If
End Sub


I want to delete values in database based on checked cells
in DatagridView. When i run the code and i don’t even check the cell it
will still delete all.

Please help me

推荐答案

在我这看来你错了：

Looks to me like you have a mistake in this line:

If Convert.ToBoolean(d.Cells(3).Value) = True Then

我假设你打算写这个？：

I assume you meant to write this?:

If Convert.ToBoolean(row.Cells(3).Value) = True Then

除此之外：



1如果你把Booleans放到你的DataGridView的第3列，那么当你想要阅读它时，仍然有Booleans。所以你不需要转换它们 - 只需转换它们：

Apart from that:

1) If you've put Booleans into column 3 of your DataGridView then there are still Booleans in there when you want to read it. So you don't need to convert them - just cast them:

If CBool(row.Cells(3).Value) = True Then

这样可以防止意外转换输入错误，从而避免可能很难找到错误。因为Convert几乎可以将任何东西转换成其他东西。



2）不要使用字符串连接来构建SQL语句。使用 SqlParameters [ ^ ]而不是。使您免于潜在的SQL注入，并使您的代码更具可读性（特别是对于较大的SQL语句）。



5）使用数据库对象（Connection，Command，使用 -blocks在中的Transaction，DataReader等）并在本地声明它们以便尽快释放资源。



4）使用交易 [ ^ ]。通过事务，您可以让多个SQL语句成功或失败。如果由于某种原因，你的第一个删除语句在这里成功，然后下一个失败你有一个不一致的数据库。



5）不要吞下例外。含义：不要抓住它们然后不采取任何措施。至少要通知用户。



这就是我推荐它的方法（简短一点，如下所述）：



This will prevent unintended conversions from typing mistakes and thus save you from potentially hard to find bugs. Because Convert will convert almost anything to anything else.

2) Don't use string concatenation to build SQL-statements. Use SqlParameters[^] instead. Saves you from potential SQL-injection and makes your code more readable (especially for larger SQL-statements).

5) Use your database-objects (Connection, Command, Transaction, DataReader etc) in using-blocks and declare them only locally in order to release the resources as soon as possible.

4) Use Transactions[^]. With transactions you can let several SQL-statements succeed or fail as a whole. If for whatever reason the first of your delete-statements here succeed and then a following one fails you have an inconsistent database.

5) Don't swallow Exceptions. Meaning: Don't catch them and then do nothing about it. At least notify the user.

This is how I would recommend it (short one point, mentioned below):

' have the following declaration somewhere central instead:
Dim connectionString As String = "your connection string"

Try
	Using con As New SqlConnection(connectionString)
		Using trans As SqlTransaction = con.BeginTransaction(IsolationLevel.Serializable)
			Using cmd As New SqlCommand("", con, trans)

				Dim sb As New StringBuilder()
				Dim parameterIndex As Integer = 0

				For Each row As DataGridViewRow In DGV.Rows
					If CBool(row.Cells(3).Value) = True Then
						Dim parameterName As String = "@p" + parameterIndex
						sb.AppendLine([String].Format("DELETE FROM Student WHERE ID = {0};", parameterName))
						cmd.Parameters.AddWithValue(parameterName, row.Cells(0).Value)
						parameterIndex += 1
					End If
				Next

				cmd.CommandText = sb.ToString() ' execute all as a batch
				cmd.ExecuteNonQuery()

				trans.Commit() ' all succeed or all fail

			End Using
		End Using
	End Using
Catch ex As SqlException
    ' automatic transaction-rollback
	MessageBox.Show("A database error occurred and the attempted deletion of records failed as a whole. Error Detail: " + ex.Message)
End Try

更好的方法是使用 DbProviderFactory [ ^ ]创建数据库供应商不可知的基本类型DbConnection，DbCommand等，而不是这里使用的特定于SQL Server的版本。因为那样你就很容易了（或更容易）时间调整你的代码以使用不同的数据库系统。



（我使用代码转换器将此代码从我的C＃-draft转换为VB。 NET - 没有测试运行它。）

Even better would be to use a DbProviderFactory[^] to create the database-vendor-agnostic basetypes DbConnection, DbCommand etc. instead of the SQL-Server-specific versions used here. Because then you have an easy (or easier) time adapting your code to work with a different database system.

(I used a code converter to convert this code from my C#-draft to VB.NET - didn't test-run it.)

这篇关于如何根据datagridview复选框从数据库中删除值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！