如何修复System.Data.SqlClient.SqlException:关键字'file'附近语法错误

System.Data.SqlClient.SqlException: '关键字 'file' 附近有语法错误。'

每当点击窗体上的List_Patient_Button按钮时都会触发这个错误，相关代码如下：

Imports System.Data.SqlClient
Imports System.Xml

Public Class Patient_File_Form
    Dim sqlConn As SqlConnection
    Dim sqlCmd As SqlCommand
    Dim sqlAdp As SqlDataAdapter
    Private Sub Patient_File_Form_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim connStr As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|Patient File.mdf;Integrated Security=True"
        sqlConn = New SqlConnection(connStr)
        sqlConn.Open()
    End Sub

    Private Sub Patient_File_Form_FormClosing(sender As Object, e As FormClosingEventArgs) Handles MyBase.FormClosing
        sqlConn.Close()
    End Sub

    Private Sub List_Patient_Button_Click(sender As Object, e As EventArgs) Handles List_Patient_Button.Click
        sqlCmd = New SqlCommand("SELECT Nom, Prénom FROM file ORDER BY Nom", sqlConn)
        Dim adapter As SqlDataAdapter = New SqlDataAdapter(sqlCmd)
        Dim ds As DataSet = New DataSet
        adapter.Fill(ds, "allFiles")
        Dim dt As DataTable = ds.Tables("allFiles")
        If (dt.Rows.Count = 0) Then
            MsgBox("No patients found", MsgBoxStyle.Exclamation, " No Data")
        Else
            PatientListBox.Items.Clear()

            For Each dr As DataRow In dt.Rows
                PatientListBox.Items.Add(dr("Nom") & " . " & dr("Prénom"))
            Next
        End If
    End Sub
End Class

完整错误信息：

System.Data.SqlClient.SqlException
  HResult=0x80131904
  Message=关键字 'file' 附近有语法错误。
  Source=.Net SqlClient Data Provider
  调用堆栈:
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
   at WindowsApp1.Patient_File_Form.List_Patient_Button_Click(Object sender, EventArgs e) in C:\Users\hp\source\repos\WindowsApp1\Form1.vb:line 22
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ButtonBase.WndProc(Message& m)
   at System.Windows.Forms.Button.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
   at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
   at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
   at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
   at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
   at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
   at WindowsApp1.My.MyApplication.Main(String[] Args) in :line 83

解决方案 #

问题根源是file是SQL Server的保留关键字，不能直接作为表名使用，有两种修复方式：

1. 给表名加方括号转义
   修改SQL语句，将file改为[file]：

   sqlCmd = New SqlCommand("SELECT Nom, Prénom FROM [file] ORDER BY Nom", sqlConn)
   

2. 修改数据库表名
   把数据库中的表名改为非保留关键字（比如PatientFiles），同时更新SQL语句中的表名：

   sqlCmd = New SqlCommand("SELECT Nom, Prénom FROM PatientFiles ORDER BY Nom", sqlConn)
   

额外优化建议 #

为避免资源泄漏，建议使用Using语句自动管理数据库连接和数据适配器的生命周期，修改后的代码示例：

Imports System.Data.SqlClient
Imports System.Xml

Public Class Patient_File_Form
    Private Sub Patient_File_Form_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ' 连接无需提前打开，SqlDataAdapter会自动处理
    End Sub

    Private Sub List_Patient_Button_Click(sender As Object, e As EventArgs) Handles List_Patient_Button.Click
        Dim connStr As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|Patient File.mdf;Integrated Security=True"
        
        ' Using语句自动释放连接资源
        Using sqlConn As New SqlConnection(connStr)
            Using sqlCmd As New SqlCommand("SELECT Nom, Prénom FROM [file] ORDER BY Nom", sqlConn)
                Using adapter As New SqlDataAdapter(sqlCmd)
                    Dim ds As New DataSet()
                    adapter.Fill(ds, "allFiles")
                    Dim dt As DataTable = ds.Tables("allFiles")
                    
                    If dt.Rows.Count = 0 Then
                        MsgBox("未找到患者信息", MsgBoxStyle.Exclamation, "无数据")
                    Else
                        PatientListBox.Items.Clear()
                        For Each dr As DataRow In dt.Rows
                            PatientListBox.Items.Add($"{dr("Nom")} . {dr("Prénom")}")
                        Next
                    End If
                End Using
            End Using
        End Using
    End Sub
End Class

这样可以避免手动管理连接开闭时可能出现的异常，代码更健壮。

内容的提问来源于stack exchange，提问作者Simo Mohamed