Incorrect Syntax Near 'C:' Error on Microsoft VB Console Application

I am currently running into an error while testing and debugging my VB.NET Console Application program that I have ran circles around trying to resolve. When running the program code I keep receiving the Incorrect Syntax Near ‘C:’ error. I have also tried specifying a different drive location in an attempt to see if I receive the same error, and I do with the specified drive letter.

I have ran through the syntax on my program, checked the code and also studied the error stack details and cannot find any resolution.

below is my code:

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

And below the error details are copied:

  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

I am able to successfully write the information from the console application to an XML file. I have manually ran a query with the XML file as well into my datatable and it reads and imports the information just fine into my localhost SQLServer Instance in that regard. I believe the issue lies in the program reading the XML file into the datatable.

I am still not the most versed in SQL queries or programming, so I am guessing I potentially have incorrect syntax that I’m missing?

Thank you for your review regarding this matter.

Also credit and thanks goes to Joel Coehoorn for optimising the code from its previous iteration!

>Solution :

This error suggestion your SQL syntax is incorrect. You should check this, it looks like path is not surrounded by single quotes i.e.

(BULK " & path & ",

should be

(BULK '" & path & "',

You can check the final SQL command generated and test if you can run it against SQL server with a tool like SQL Management Studio. This way you can get the correct SQL command and ensure your application is using the same.

Leave a Reply