我目前在测试和调试 VB.NET 控制台应用程序时遇到错误，我已经绕了一圈试图解决该错误。运行程序代码时，我不断收到“C:”附近语法错误错误。我还尝试指定不同的驱动器位置，以查看是否收到相同的错误，并且我使用指定的驱动器号执行此操作。
我已经运行了程序的语法，检查了代码，还研究了错误堆栈详细信息，但找不到任何解决方案。
下面是我的代码：
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()
'Dim dt As New DataTable("DriveInformation")
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"))
' Get information and Write to Console.
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
' Put Information into DataTable
For Each d In allDrives
' The DriveNumber check was NOT NEEDED, added a bunch of extra code
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
' Write Information to XML file
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()
' Connection is closed/disposed here, *even if an exception is thrown*
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
我能够成功地将信息从控制台应用程序写入 XML 文件。我已经使用 XML 文件手动运行了一个查询，并且将其导入到我的数据表中，并且在这方面它可以很好地读取信息并将其导入到我的本地主机 SQLServer 实例中。我认为问题在于程序将 XML 文件读入数据表。
我仍然不是最精通 SQL 查询或编程，所以我猜测我可能缺少错误的语法？
感谢您对此事的评论。
还要感谢 Joel Coehoorn 优化了之前迭代的代码！