No momento, estou enfrentando um erro ao testar e depurar meu programa de aplicativo de console VB.NET que fiz círculos tentando resolver. Ao executar o código do programa, continuo recebendo o erro Incorrect Syntax Near 'C:'. Também tentei especificar um local de unidade diferente na tentativa de ver se recebo o mesmo erro, e recebo com a letra de unidade especificada.
Examinei a sintaxe do meu programa, verifiquei o código e também estudei os detalhes da pilha de erros e não consigo encontrar nenhuma resolução.
abaixo está meu código:
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
E abaixo os detalhes do erro são copiados:
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
Consigo gravar com êxito as informações do aplicativo de console em um arquivo XML. Eu executei manualmente uma consulta com o arquivo XML também em minha tabela de dados e ele lê e importa as informações perfeitamente para minha instância localhost do SQLServer a esse respeito. Acredito que o problema esteja no programa que lê o arquivo XML na tabela de dados.
Ainda não sou o mais versado em consultas SQL ou programação, então estou supondo que tenho uma sintaxe incorreta que estou perdendo.
Obrigado pela sua avaliação sobre este assunto.
Os créditos e agradecimentos também vão para Joel Coehoorn por otimizar o código de sua iteração anterior!