VB .NET SQL Delete query not working

Question

I am having a big program trying to delete a record from my SQL Servers Database. I originally created this code to work with Microsoft Access, however I have decided to convert it to use MS SQL 2008.

Now this is what I have:

Dim dbConnection As SqlConnection
Dim dbCommand As SqlCommand
Dim strSQL As String
Dim dgvSelectRow As Integer 'Represents the selected row in the DataGridView

Public Sub SQLConnect()
    dbConnection = New SqlConnection("Data Source=w7gbnewmfg2143\sqlexpress;Initial Catalog=master_plan;Integrated Security=True")
    dbConnection.Open()
End Sub

Public Sub SQLCommand()
    dbCommand = New SqlCommand(strSQL, dbConnection)
End Sub

Public Sub SQLDisconnect()
    dbConnection.Close()
End Sub

Private Sub btnRemove_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRemove.Click
    If dgvLine1.RowCount = 0 Then
        MsgBox("No Rows to Delete")
    Else

        dgvSelectRow = dgvLine1.CurrentRow.Index

        If MsgBox("Are you sure to remove this job?", MsgBoxStyle.Critical + MsgBoxStyle.YesNo, "Remove Job") = MsgBoxResult.Yes Then

            SQLConnect()
            strSQL = "DELETE FROM [LinePlan] WHERE DateTime = '" & dgvLine1.Item(8, dgvSelectRow).Value & "'" 'Removes selected row from DataGridView at position column 8
            SQLCommand()
            dbCommand.ExecuteNonQuery()
            SQLDisconnect()
            tmrUpdate.Enabled = True
            tmrUpdate.Start()
        Else

        End If
    End If
End Sub

Basically I am trying to delete a record from a selected row in a DataGridView. In the row it deletes the DateTime value at column at in my DGV. Now when I created this query in MS Access I had no problems at all, it worked perfectly. Now that I have migrated it to MS SQL, I am having all sorts of problems. I was getting alot of syntax errors which I have now resolved (i think). I have been using Catch ex As Exception to try and determine any errors. I have now cleared all errors. Now when I click the remove button, I receive no errors, however the record does not delete and it still remains in the database. The program does not crash or display any errors, and seems like it has done what it needs to do, but it does not delete the record. I have tried using different methods of connection strings but to no avail. I have tried it by not using the variable and just putting in the data manually and still no luck.

Am I missing something straight forward? Because I cannot figure out what the issue could be. It seems a very straight forward query and didnt think much could really go wrong, but how wrong am I :).

Any help or guidence would be greatly appreciated. Thanks for your time!

Regards, James

I think your sql command condition is not getting fulfill. like DateTime= 'dgvLine1.Item(8, gvSelectRow).Value' or might be possible that format of datetime is not matching your passing parameter — Deepak, Apr 12 '13 at 10:11

score 2 · Accepted Answer · edited May 23 '17 at 12:22

2

Try with a parametrized query

strSQL = "DELETE FROM [LinePlan] WHERE DateTime = @dtValue"
SQLCommand()
dbCommand.Parameters.AddWithValue("@dtValue", Convert.ToDateTime(dgvLine1.Item(8, dgvSelectRow).Value)
dbCommand.ExecuteNonQuery()

This should work better because you leave the job of passing a correct formatted datetime value to the database engine instead of trying to pass a string that you think is in the correct format for your database.

... and I have forgot something else? .... ah yes, kiss goodbye to Sql Injection

edited May 23 '17 at 12:22

Community

1
1

answered Apr 12 '13 at 10:06

Steve

213,761
22
232
286

Thank you so much that has sorted my problem, I really appreciate that thank you :) I will look in to parameters to boost my knowledge. Thanks again! – James Apr 12 '13 at 10:15

VB .NET SQL Delete query not working

1 Answers1