Click here to Skip to main content
15,889,200 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
C#
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
Posted
Comments
Richmond Boateng 3-Feb-16 14:16pm    
Sorry it is
If row.Cells(3).Value = True Then

1 solution

Looks to me like you have a mistake in this line:
VB
If Convert.ToBoolean(d.Cells(3).Value) = True Then

I assume you meant to write this?:
VB
If Convert.ToBoolean(row.Cells(3).Value) = True Then


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:
VB
If CBool(row.Cells(3).Value) = True Then

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):
VB
' 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


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.)
 
Share this answer
 
Comments
Richmond Boateng 3-Feb-16 15:20pm    
Tank you very much for your tutorials
Sascha Lefèvre 3-Feb-16 15:32pm    
You're welcome! :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900