VB.NET控制台程序报错：'C:'附近语法不正确的解决求助

API 探路者

2026-7-12

问题：VB.NET控制台程序执行SQL导入时提示“Incorrect syntax near 'C:'”

调试VB.NET控制台应用时反复遇到“Incorrect syntax near 'C:'”错误，更换盘符后仍出现。已检查语法、代码和错误堆栈，未解决。能成功将控制台信息写入XML，手动执行XML导入SQL的查询可正常导入本地SQL Server，但程序执行失败，怀疑SQL语法问题。

程序代码

Imports System.Data
Imports System.Data.SqlClient

Public Module Module1

Public GBTBBreak As Single = 1099511627776
Public GBDivisor As Single = 1024 * 1024 * 1024 * 1.0
Public TBDivisor As Single = GBDivisor * 1024

Dim dt As New DataTable("DriveInformation")

Public Function FormatForGBTB(input As Single) As String
    If input < GBTBBreak Then
        Return String.Format("{0,15} {1}", input / GBDivisor, "GB")
    Else
        Return String.Format("{0,15} {1}", input / TBDivisor, "TB")
    End If
End Function

Public Sub Main()
    dt.Columns.Add(New DataColumn("Date"))
    dt.Columns.Add(New DataColumn("Server"))
    dt.Columns.Add(New DataColumn("Drive"))
    dt.Columns.Add(New DataColumn("TotalSpace"))
    dt.Columns.Add(New DataColumn("UsedSpace"))
    dt.Columns.Add(New DataColumn("RemainingSpace"))
    dt.Columns.Add(New DataColumn("GBorTBDrive"))
    dt.Columns.Add(New DataColumn("DriveActiveStatus"))

    ' 获取信息并输出到控制台
    Dim Host As String = System.Net.Dns.GetHostName
    Console.WriteLine("Server Name: {0}", Host)
    Console.WriteLine("Date: {0}{1}", DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt"), vbCrLf)

    Dim allDrives() As IO.DriveInfo = IO.DriveInfo.GetDrives()

    For Each d As IO.DriveInfo In allDrives.Where(Function(dr) dr.IsReady)
        Console.WriteLine(d.Name.Remove(2))
        Console.WriteLine("  Drive type: {0}", d.DriveType)
        Console.WriteLine("  Volume label: {0}", d.VolumeLabel)
        Console.WriteLine("  File system: {0}", d.DriveFormat)
        Console.WriteLine("  Total size of drive:   {0}", FormatForGBTB(d.TotalSize))
        Console.WriteLine("  Total used space:      {0}", FormatForGBTB(d.TotalSize - d.TotalFreeSpace))
        Console.WriteLine("  Total available space: {0}", FormatForGBTB(d.TotalFreeSpace))
    Next

    ' 将信息存入DataTable
    For Each d In allDrives
        Dim divisor As Double = If(d.TotalSize < GBTBBreak, GBDivisor, TBDivisor)
        Dim row As DataRow = dt.NewRow

        row("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
        row("Server") = System.Net.Dns.GetHostName
        row("Drive") = d.Name.Remove(1, 2)
        row("TotalSpace") = FormatNumber(d.TotalSize / divisor).ToString()
        row("UsedSpace") = FormatNumber((d.TotalSize - d.TotalFreeSpace) / divisor).ToString()
        row("RemainingSpace") = FormatNumber(d.TotalFreeSpace / divisor).ToString()
        row("GBorTBDrive") = If(d.TotalSize < GBTBBreak, "GB", "TB")
        row("DriveActiveStatus") = d.IsReady
        dt.Rows.Add(row)
    Next

    ' 将信息写入XML文件
    Dim day As String = DateTime.Now.ToString("yyyy" & Space(1) & "MM" & Space(1) & "dd" & Space(1) & "h" & Space(1) & "mm" & Space(1) & "tt")
    Dim path As String = Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
    path = IO.Path.Combine(path, "ServerStorageC7L6M72" & Space(1) & day & Space(1) & ".xml")

    dt.WriteXml(path)
    ReadXmlContents()
End Sub

Public Sub ReadXmlContents()
    Dim day As String = DateTime.Now.ToString("yyyy" & Space(1) & "MM" & Space(1) & "dd" & Space(1) & "h" & Space(1) & "mm" & Space(1) & "tt")
    Dim path As String = Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
    path = IO.Path.Combine(path, "ServerStorageC7L6M72" & Space(1) & day & Space(1) & ".xml")

    dt.ReadXml(path)

    Dim ServerCheckConnectString = "Data Source=localhost;Initial Catalog=ServerDriveStorageChecks;Persist Security Info=True;User ID=test;Password=*****"

    Dim SQL As String =
        "INSERT INTO DriveInformation(Date, Server, Drive, TotalSpace, UsedSpace, RemainingSpace, GBorTBDrive, DriveActiveStatus) " &
     "Select MY_XML.DriveInformation.query('Date').value('.', 'DATETIME') " &
     ",MY_XML.DriveInformation.query('Server').value('.', 'VARCHAR(15)') " &
     ",MY_XML.DriveInformation.query('Drive').value('.', 'VARCHAR(1)') " &
     ",MY_XML.DriveInformation.query('TotalSpace').value('.', 'NUMERIC(10,2)') " &
     ",MY_XML.DriveInformation.query('UsedSpace').value('.', 'NUMERIC(10,2)') " &
     ",MY_XML.DriveInformation.query('RemainingSpace').value('.', 'NUMERIC(10,2)') " &
     ",MY_XML.DriveInformation.query('GBorTBDrive').value('.', 'VARCHAR(2)') " &
     ",MY_XML.DriveInformation.query('DriveActiveStatus').value('.', 'VARCHAR(6)') " &
     "FROM ( " &
     "SELECT CAST(MY_XML AS xml) " &
     "FROM OPENROWSET (BULK " & path & ", SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML) CROSS APPLY MY_XML.nodes('DocumentElement/DriveInformation') AS MY_XML (DriveInformation) "

    Dim cn As SqlConnection
    Dim cmd As SqlCommand

    cn = New SqlConnection(ServerCheckConnectString)
    cmd = New SqlCommand(SQL, cn)

    cn.Open()
    cmd.ExecuteNonQuery()
End Sub
End Module

错误详情

System.Data.SqlClient.SqlException
HResult=0x80131904
Message=Incorrect syntax near 'C:'.
Source=Core .Net SqlClient Data Provider
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean 
breakConnection, Action`1 wrapCloseInAction) in 
/_/src/System.Data.SqlClient/src/System/Data/SqlClient/SqlConnection.cs:line 1352
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject 
stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) in 
/_/src/System.Data.SqlClient/src/System/Data/SqlClient/TdsParser.cs:line 1140
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand 
cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, 
TdsParserStateObject stateObj, Boolean& dataReady) in 
/_/src/System.Data.SqlClient/src/System/Data/SqlClient/TdsParser.cs:line 2172
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean 
async, Int32 timeout, Boolean asyncWrite) in 
/_/src/System.Data.SqlClient/src/System/Data/SqlClient/SqlCommand.cs:line 2421
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 
completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName) 
in /_/src/System.Data.SqlClient/src/System/Data/SqlClient/SqlCommand.cs:line 1176
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() in 
/_/src/System.Data.SqlClient/src/System/Data/SqlClient/SqlCommand.cs:line 874
at HDD_Drive_Information_Console_App.Module1.ReadXmlContents() in C:\Users\daniel 
presnell\source\repos\HDD Drive Information Console App Rev 6  06-26-2023\HDD Drive 
Information Console App\Program.vb:line 155
at HDD_Drive_Information_Console_App.Module1.Main() in C:\Users\daniel 
presnell\source\repos\HDD Drive Information Console App Rev 6  06-26-2023\HDD Drive 
Information Console App\Program.vb:line 113

解决方案

问题根源

路径未正确转义与包裹：OPENROWSET的BULK参数要求路径必须用单引号包裹，且Windows路径中的反斜杠\是SQL的转义字符，需要替换为\\，否则SQL会将C:解析为语法错误。
保留关键字冲突：Date是SQL Server的保留关键字，插入时未用方括号[Date]包裹，导致语法歧义。

修改后的SQL语句

将ReadXmlContents方法中的SQL字符串修改为：

Dim SQL As String =
    "INSERT INTO DriveInformation([Date], Server, Drive, TotalSpace, UsedSpace, RemainingSpace, GBorTBDrive, DriveActiveStatus) " &
 "Select MY_XML.DriveInformation.query('Date').value('.', 'DATETIME') " &
 ",MY_XML.DriveInformation.query('Server').value('.', 'VARCHAR(15)') " &
 ",MY_XML.DriveInformation.query('Drive').value('.', 'VARCHAR(1)') " &
 ",MY_XML.DriveInformation.query('TotalSpace').value('.', 'NUMERIC(10,2)') " &
 ",MY_XML.DriveInformation.query('UsedSpace').value('.', 'NUMERIC(10,2)') " &
 ",MY_XML.DriveInformation.query('RemainingSpace').value('.', 'NUMERIC(10,2)') " &
 ",MY_XML.DriveInformation.query('GBorTBDrive').value('.', 'VARCHAR(2)') " &
 ",MY_XML.DriveInformation.query('DriveActiveStatus').value('.', 'VARCHAR(6)') " &
 "FROM ( " &
 "SELECT CAST(MY_XML AS xml) " &
 "FROM OPENROWSET (BULK N'" & path.Replace("\", "\\") & "', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML) CROSS APPLY MY_XML.nodes('DocumentElement/DriveInformation') AS MY_XML (DriveInformation) "

更优方案：使用SqlBulkCopy直接导入DataTable

既然已经将数据存入DataTable，无需先写入XML再读入解析，直接用SqlBulkCopy导入数据库更高效，还能避免路径相关的SQL语法问题：

Public Sub ReadXmlContents()
    Dim day As String = DateTime.Now.ToString("yyyy MM dd h mm tt")
    Dim path As String = Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
    path = IO.Path.Combine(path, $"ServerStorageC7L6M72 {day}.xml")

    dt.ReadXml(path)

    Dim ServerCheckConnectString = "Data Source=localhost;Initial Catalog=ServerDriveStorageChecks;Persist Security Info=True;User ID=test;Password=*****"

    Using cn As New SqlConnection(ServerCheckConnectString)
        cn.Open()
        Using bulkCopy As New SqlBulkCopy(cn)
            bulkCopy.DestinationTableName = "DriveInformation"
            ' 列名映射（若DataTable与数据库列名完全一致可省略）
            bulkCopy.ColumnMappings.Add("Date", "[Date]")
            bulkCopy.ColumnMappings.Add("Server", "Server")
            bulkCopy.ColumnMappings.Add("Drive", "Drive")
            bulkCopy.ColumnMappings.Add("TotalSpace", "TotalSpace")
            bulkCopy.ColumnMappings.Add("UsedSpace", "UsedSpace")
            bulkCopy.ColumnMappings.Add("RemainingSpace", "RemainingSpace")
            bulkCopy.ColumnMappings.Add("GBorTBDrive", "GBorTBDrive")
            bulkCopy.ColumnMappings.Add("DriveActiveStatus", "DriveActiveStatus")
            bulkCopy.WriteToServer(dt)
        End Using
    End Using
End Sub

另外，感谢Joel Coehoorn对代码进行了优化！

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

VB.NET控制台程序报错：'C:'附近语法不正确的解决求助

程序代码 #

错误详情 #

解决方案 #

问题根源 #

修改后的SQL语句 #