Click here to Skip to main content
15,895,746 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi, I have a delete button on a VB.NET app which when the user clicks, is supposed to change a boolean in a database to "False". This doesn;t work for some reason and I don't know why. This is the code:

If DataGridViewCustomers.CurrentRow.Cells(1).Value <> Nothing Then


           If CStr(DataGridViewCustomers.CurrentRow.Cells(10).Value.ToString) = "True" Then
               If MsgBox("Delete " + CStr(DataGridViewCustomers.CurrentRow.Cells(1).Value.ToString) + " from customers?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
                   SqlQuery = "UPDATE tblCustomers SET Active = '" & False & "' WHERE CustomerID = '" & DataGridViewCustomers.CurrentRow.Cells(0).Value.ToString & "'"
                   CustomerTable = db.updateData(SqlQuery)
                   DataGridViewCustomers.DataSource = CustomerTable

               End If
           Else
               MsgBox("Customer has already been deleted.")
           End If

       Else
           MsgBox("Please select a customer to delete.")
       End If




The actual SQL gets executed in a module of functions which I have made for efficiency. This is the function:

Public Function updateData(ByVal SqlQuery As String)
        If conn.State = ConnectionState.Closed Then
            conn.Open()
        End If
        Dim sqlCommand As New OleDbCommand
        With sqlCommand
            .CommandText = SqlQuery
            .Connection = conn
            .ExecuteNonQuery()
        End With

    End Function


What I have tried:

I have tried changing the True/False to 1/0 and Yes/No. None of which have worked though.
Posted
Updated 22-Apr-18 8:52am

Taken that the value in the database is boolean, not a string, you should use false for the value, not 'False'. Also ensure that you're using a correct value in the WHERE clause.

I think tThe best way to deal with this would be to use OleDbParameter Class (System.Data.OleDb)[^]. This way you would easily ensure that data is handled with correct data types and you wouldn't have the risk of SQL injection, see SQL injection - Wikipedia[^]
 
Share this answer
 
Comments
Member 13779854 22-Apr-18 10:53am    
@Wendelius Yes the value in the database is a boolean. When I replace False with false it automatically changes it to False. I'm quite sure that the correct value is used in the Where clause as well. I do intend to use parameters later in the program though.
Wendelius 22-Apr-18 12:39pm    
Not sure if I understand your reply, but did you try a statement like following

SqlQuery = "UPDATE tblCustomers SET Active = False WHERE CustomerID = '" & DataGridViewCustomers.CurrentRow.Cells(0).Value.ToString & "'"
Maciej Los 23-Apr-18 2:13am    
No, not like this!
SqlQuery = "UPDATE tblCustomers SET Active = False WHERE CustomerID = @CustId"
YourCommad.Parameters.AddWithValues("@CustId", DataGridViewCustomers.CurrentRow.Cells(0).Value)
Maciej Los 23-Apr-18 2:11am    
5ed!
@Wendelius I did try that and I get a data type mismatch in the expression error. It looks like it should work though but it doesn't
 
Share this answer
 
Comments
Maciej Los 23-Apr-18 2:10am    
This is not an answer. Please, delete it to avoid down-voting!

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