VB.NET中SQL Update语句无法运行，寻求技术协助

Hey there, sorry to hear you’ve been stuck getting this UPDATE query working for days—let’s walk through the most likely issues and fixes to get this sorted.

Your Current Code #

Update Logic:

dbAccess.AddParameter("@flashcardID", flashcardUpdateIndex)
dbAccess.AddParameter("@flashcardFront", txtFlashcardFront.Text)
dbAccess.AddParameter("@flashcardBack", txtFlashcardBack.Text)
dbAccess.ExecuteQuery("UPDATE Questions SET Flashcard_Front=@flashcardFront WHERE Question_ID=@flashcardID;")

dbAccess Class Snippet:

Imports System.Data.OleDb
' Rest of your dbAccess class code here

Key Troubleshooting Steps #

• Fix Parameter Order (Critical for OleDb)
  OleDb doesn’t actually use named parameters—it matches parameters by the order they’re added to the command, not by their @name. Your SQL query references @flashcardFront first, then @flashcardID, but you’re adding @flashcardID first in your code. This means you’re passing the flashcard ID value to the Flashcard_Front field, and the front text to the Question_ID filter—no wonder it’s not updating anything!

  Fix the parameter order to match the SQL:

  ' Match the order of parameters in your UPDATE statement
  dbAccess.AddParameter("@flashcardFront", txtFlashcardFront.Text)
  dbAccess.AddParameter("@flashcardID", flashcardUpdateIndex)
  ' Remove the unused @flashcardBack parameter unless you meant to update that field too
  

• Check for Unused Parameters
  You’re adding @flashcardBack but it’s not included in your UPDATE statement. If you intended to update the Flashcard_Back field too, adjust your SQL:

  UPDATE Questions 
  SET Flashcard_Front=@flashcardFront, Flashcard_Back=@flashcardBack 
  WHERE Question_ID=@flashcardID;
  

  And make sure the parameter order matches this new SQL (Front first, Back second, ID third).

• Verify Your dbAccess Class Implementation
  Double-check that your AddParameter and ExecuteQuery methods are correctly handling OleDb commands. A proper implementation might look like this (adjust to match your class):

  Public Class dbAccess
      Private _parameters As New List(Of OleDbParameter)
  
      Public Sub AddParameter(ByVal paramName As String, ByVal paramValue As Object)
          _parameters.Add(New OleDbParameter(paramName, paramValue))
      End Sub
  
      Public Sub ExecuteQuery(ByVal sql As String)
          ' Replace with your actual connection string
          Dim connString As String = "YourOleDbConnectionStringHere"
          
          Using conn As New OleDbConnection(connString)
              conn.Open()
              Using cmd As New OleDbCommand(sql, conn)
                  ' Add all stored parameters to the command
                  cmd.Parameters.AddRange(_parameters.ToArray())
                  ' Execute the query and check the number of rows affected
                  Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
                  If rowsAffected = 0 Then
                      ' No rows were updated—this could mean your WHERE clause didn't match any records
                      Throw New Exception("No rows updated. Check if Question_ID exists in the database.")
                  End If
                  ' Clear parameters for next use
                  _parameters.Clear()
              End Using
          End Using
      End Sub
  End Class
  

• Catch and Inspect Exceptions
  If your code isn’t throwing errors, it’s hard to diagnose. Wrap your update logic in a try-catch block to see what’s going wrong:

  Try
      dbAccess.AddParameter("@flashcardFront", txtFlashcardFront.Text)
      dbAccess.AddParameter("@flashcardID", flashcardUpdateIndex)
      dbAccess.ExecuteQuery("UPDATE Questions SET Flashcard_Front=@flashcardFront WHERE Question_ID=@flashcardID;")
      MessageBox.Show("Update successful!")
  Catch ex As Exception
      MessageBox.Show($"Update failed: {ex.Message}{vbCrLf}{ex.StackTrace}")
  End Try
  

• Test the Query Directly in Your Database
  To rule out database-side issues, run a manual UPDATE query with hardcoded values (replace with your actual data):

  UPDATE Questions 
  SET Flashcard_Front='Test Front Text' 
  WHERE Question_ID=123; -- Replace 123 with a valid Question_ID from your table
  

  If this works, the problem is definitely in your code (most likely parameter order or dbAccess implementation).

• Check Data Type Mismatches
  Ensure flashcardUpdateIndex matches the data type of Question_ID (e.g., if Question_ID is an Integer, make sure flashcardUpdateIndex isn’t a String). Mismatched types can cause silent failures or errors.

内容的提问来源于stack exchange，提问作者Jamie Ford