SQL查询不更新数据 [英] Sql query not updating data

问题描述

我在VB.NET应用程序上有一个删除按钮，当用户点击时，应该将数据库中的布尔值更改为False。由于某种原因，这不起作用，我不知道为什么。这是代码：

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

实际的SQL在我为效率而设计的函数模块中执行。这是函数：

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

我尝试过：



我尝试将True / False更改为1/0和是/否。但是没有一个工作过。

What I have tried:

I have tried changing the True/False to 1/0 and Yes/No. None of which have worked though.

推荐答案

假设数据库中的值是布尔值而不是字符串，你应该使用 false 表示该值，而不是'False'。还要确保在WHERE子句中使用正确的值。



我认为解决这个问题的最佳方法是使用 OleDbParameter类（System.Data.OleDb） [ ^ ]。通过这种方式，您可以轻松确保使用正确的数据类型处理数据，并且您不会有SQL注入的风险，请参阅 SQL注入 - 维基百科 [ ^ ]

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[^]

@Wendelius我确实尝试过，我在表达式错误中得到数据类型不匹配。它看起来应该可以工作，但它不会

@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

这篇关于SQL查询不更新数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！