Click here to Skip to main content
15,910,411 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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


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