如何在Visual Basic 2008中使用SQL一次更新表中的多个字段?

Question

这是我当前的代码行，但它似乎不起作用。

Str = " UPDATE tblTest SET SET = FALSE WHERE SetTest = TRUE"

代码的目的是使用布尔值将SetTest中的所有值设置为false。我不确定这是否正确，因为我的数据库没有更新。我用的是visual basic 2008。

这是我从

开始的代码

Private Sub btnSetTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSetTest.Click
    Dim Str As String
    Str = "SELECT * FROM tblTest WHERE SetTest = True"
    dtasetSpellingBee.Tables("tblTest").Rows(CurrentRowNo)("SetTest") = "TRUE"
    dtaadpTest.Update(dtasetSpellingBee, "tblTest")
    'Record has been saved so make add record false now
    AddRecord = False
    'Update the Student detail table in the database
    MessageBox.Show("The update has been saved to disk", "Save Updates", MessageBoxButtons.OK, MessageBoxIcon.Information)
End Sub

Private Sub frmStudent_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    conDatabase()
    ' Create an SqlCommand object to use the DataAdapter for the Account table to retrieve its records from the database into the DataSet
    dtaadpTest.SelectCommand = New OleDbCommand
    ' Assign the Connection object to the DataAdapter so it knows what database to retrieve the records from
    dtaadpTest.SelectCommand.Connection = conSpellingBee
    ' Create a query which will retrieve no records from the Account table but will
    ' add a table to the DataSet with the same fields
    dtaadpTest.SelectCommand.CommandText = "SELECT * FROM tblTest WHERE TestID = 0"
    ' Execute the SqlCommand to fill a table called tblTest in the DataSet
    dtaadpTest.Fill(dtasetSpellingBee, "tblTest")
End Sub

Answer 1

这取决于你的SQL方言(你不说)。

假设您使用MS SQL Server, SetTest将是bit类型，其值为1和0。所以这个查询应该是:

UPDATE tablTest SET SetTest = 0 WHERE SetTest = 1