I am testing some bit of code that I am implementing to pull from an XML file and place into a database. I have successfully written to the XML file without issue. The only issue that is now presenting itself is uploading the information successfully from the XML file to the SQL Server database table.
Running the code as a query successfully places the XML information into the datatable.
However when implemented into code I cannot get it to function.
I know to open the connection, but I am unfamiliar with the correct command to insert the data from the XML file to the DataTable in the specified SQL Server database.
What would be the correct command that I am missing? Or is there something else I'm missing?
If more information is needed upon request I can list the contents of the entire code.
Every other facet of the code is operating as should at this point and even generates the XML file to import into the database without issue. I only have an issue of properly importing the XML information into the datatable thorough the connection set up within the code.
Thank you for your time and review in this matter.
Full code listed here:
Imports System.Data
Imports System.Data.SqlClient
Module Module1
Public Sub Main()
Dim dt As New DataTable("DriveInformation")
Dim Column_1st As New DataColumn("Date")
Dim Column_2nd As New DataColumn("Server")
Dim Column_3rd As New DataColumn("Drive")
Dim Column_4th As New DataColumn("TotalSpace")
Dim Column_5th As New DataColumn("UsedSpace")
Dim Column_6th As New DataColumn("RemainingSpace")
Dim Column_7th As New DataColumn("GBorTBDrive")
Dim Column_8th As New DataColumn("DriveActiveStatus")
dt.Columns.Add(Column_1st)
dt.Columns.Add(Column_2nd)
dt.Columns.Add(Column_3rd)
dt.Columns.Add(Column_4th)
dt.Columns.Add(Column_5th)
dt.Columns.Add(Column_6th)
dt.Columns.Add(Column_7th)
dt.Columns.Add(Column_8th)
' Get information and Write to Console.
Console.WriteLine("Server Name: {0}", System.Net.Dns.GetHostName)
Console.WriteLine("Date: {0}", DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt"))
Console.WriteLine("")
Dim DriveNumber As Integer = 0
Dim allDrives() As IO.DriveInfo = IO.DriveInfo.GetDrives()
Dim d As IO.DriveInfo
For Each d In allDrives
Dim DriveName As String = d.Name
DriveNumber = DriveNumber + 1
Console.WriteLine(DriveName)
Console.WriteLine(" Drive type: {0}", d.DriveType)
If d.IsReady = True And d.TotalSize < 1099511627776 Then
Console.WriteLine(" Volume label: {0}", d.VolumeLabel)
Console.WriteLine(" File system: {0}", d.DriveFormat)
Console.WriteLine(
" Total size of drive: {0, 15} GB ",
FormatNumber(d.TotalSize / 1024 / 1024 / 1024))
ElseIf d.IsReady = True And d.TotalSize >= 1099511627776 Then
Console.WriteLine(" Volume label: {0}", d.VolumeLabel)
Console.WriteLine(" File system: {0}", d.DriveFormat)
Console.WriteLine(
" Total size of drive: {0, 15} TB ",
FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024))
End If
If d.IsReady = True And d.TotalSize - d.TotalFreeSpace < 1099511627776 Then
Console.WriteLine(
" Total used space: {0, 15} GB",
FormatNumber(d.TotalSize / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024))
ElseIf d.IsReady = True And d.TotalSize - d.TotalFreeSpace >= 1099511627776 Then
Console.WriteLine(
" Total used space: {0, 15} TB",
FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024))
End If
If d.IsReady = True And d.TotalFreeSpace < 1099511627776 Then
Console.WriteLine(
" Total available space: {0, 15} GB",
FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024))
Console.WriteLine("")
ElseIf d.IsReady = True And d.TotalFreeSpace >= 1099511627776 Then
Console.WriteLine(
" Total available space: {0, 15} TB",
FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024))
Console.WriteLine("")
End If
Next
' Put Information into DataTable
For Each d In allDrives
Dim DriveName As String = d.Name
If DriveNumber = 1 And d.TotalSize < 1099511627776 Then
Dim drow1 As DataRow = dt.NewRow
drow1("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
drow1("Server") = System.Net.Dns.GetHostName
drow1("Drive") = DriveName.Remove(1, 2)
drow1("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024)
drow1("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024)
drow1("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024)
drow1("GBorTBDrive") = "GB"
drow1("DriveActiveStatus") = d.IsReady
dt.Rows.Add(drow1)
ElseIf DriveNumber = 1 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace >= 1099511627776 And d.TotalFreeSpace >= 1099511627776 Then
Dim drow1 As DataRow = dt.NewRow
drow1("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
drow1("Server") = System.Net.Dns.GetHostName
drow1("Drive") = DriveName.Remove(1, 2)
drow1("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024)
drow1("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
drow1("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
drow1("GBorTBDrive") = "TB"
drow1("DriveActiveStatus") = d.IsReady
dt.Rows.Add(drow1)
ElseIf DriveNumber = 1 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace >= 1099511627776 And d.TotalFreeSpace < 1099511627776 Then
Dim drow1 As DataRow = dt.NewRow
drow1("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
drow1("Server") = System.Net.Dns.GetHostName
drow1("Drive") = DriveName.Remove(1, 2)
drow1("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024,)
drow1("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
drow1("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024)
drow1("GBorTBDrive") = "TB"
drow1("DriveActiveStatus") = d.IsReady
dt.Rows.Add(drow1)
ElseIf DriveNumber = 1 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace < 1099511627776 And d.TotalFreeSpace >= 1099511627776 Then
Dim drow1 As DataRow = dt.NewRow
drow1("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
drow1("Server") = System.Net.Dns.GetHostName
drow1("Drive") = DriveName.Remove(1, 2)
drow1("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024,)
drow1("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024)
drow1("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
drow1("GBorTBDrive") = "TB"
drow1("DriveActiveStatus") = d.IsReady
dt.Rows.Add(drow1)
ElseIf DriveNumber = 1 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace < 1099511627776 And d.TotalFreeSpace < 1099511627776 Then
Dim drow1 As DataRow = dt.NewRow
drow1("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
drow1("Server") = System.Net.Dns.GetHostName
drow1("Drive") = DriveName.Remove(1, 2)
drow1("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024,)
drow1("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024)
drow1("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024)
drow1("GBorTBDrive") = "TB"
drow1("DriveActiveStatus") = d.IsReady
dt.Rows.Add(drow1)
End If
If DriveNumber = 2 And d.TotalSize < 1099511627776 Then
Dim drow2 As DataRow = dt.NewRow
drow2("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
drow2("Server") = System.Net.Dns.GetHostName
drow2("Drive") = DriveName.Remove(1, 2)
drow2("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024)
drow2("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024)
drow2("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024)
drow2("GBorTBDrive") = "GB"
drow2("DriveActiveStatus") = d.IsReady
dt.Rows.Add(drow2)
ElseIf DriveNumber = 2 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace >= 1099511627776 And d.TotalFreeSpace >= 1099511627776 Then
Dim drow2 As DataRow = dt.NewRow
drow2("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
drow2("Server") = System.Net.Dns.GetHostName
drow2("Drive") = DriveName.Remove(1, 2)
drow2("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024)
drow2("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
drow2("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
drow2("GBorTBDrive") = "TB"
drow2("DriveActiveStatus") = d.IsReady
dt.Rows.Add(drow2)
ElseIf DriveNumber = 2 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace >= 1099511627776 And d.TotalFreeSpace < 1099511627776 Then
Dim drow2 As DataRow = dt.NewRow
drow2("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
drow2("Server") = System.Net.Dns.GetHostName
drow2("Drive") = DriveName.Remove(1, 2)
drow2("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024)
drow2("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
drow2("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024)
drow2("GBorTBDrive") = "TB"
drow2("DriveActiveStatus") = d.IsReady
dt.Rows.Add(drow2)
ElseIf DriveNumber = 2 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace < 1099511627776 And d.TotalFreeSpace >= 1099511627776 Then
Dim drow2 As DataRow = dt.NewRow
drow2("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
drow2("Server") = System.Net.Dns.GetHostName
drow2("Drive") = DriveName.Remove(1, 2)
drow2("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024)
drow2("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024)
drow2("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
drow2("GBorTBDrive") = "TB"
drow2("DriveActiveStatus") = d.IsReady
dt.Rows.Add(drow2)
ElseIf DriveNumber = 2 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace < 1099511627776 And d.TotalFreeSpace < 1099511627776 Then
Dim drow2 As DataRow = dt.NewRow
drow2("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
drow2("Server") = System.Net.Dns.GetHostName
drow2("Drive") = DriveName.Remove(1, 2)
drow2("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024,)
drow2("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024)
drow2("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024)
drow2("GBorTBDrive") = "TB"
drow2("DriveActiveStatus") = d.IsReady
dt.Rows.Add(drow2)
End If
If DriveNumber = 3 And d.TotalSize < 1099511627776 Then
Dim drow3 As DataRow = dt.NewRow
drow3("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
drow3("Server") = System.Net.Dns.GetHostName
drow3("Drive") = DriveName.Remove(1, 2)
drow3("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024)
drow3("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024,)
drow3("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024,)
drow3("GBorTBDrive") = "GB"
drow3("DriveActiveStatus") = d.IsReady
dt.Rows.Add(drow3)
ElseIf DriveNumber = 3 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace >= 1099511627776 And d.TotalFreeSpace >= 1099511627776 Then
Dim drow3 As DataRow = dt.NewRow
drow3("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
drow3("Server") = System.Net.Dns.GetHostName
drow3("Drive") = DriveName.Remove(1, 2)
drow3("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024)
drow3("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
drow3("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
drow3("GBorTBDrive") = "TB"
drow3("DriveActiveStatus") = d.IsReady
dt.Rows.Add(drow3)
ElseIf DriveNumber = 3 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace >= 1099511627776 And d.TotalFreeSpace < 1099511627776 Then
Dim drow3 As DataRow = dt.NewRow
drow3("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
drow3("Server") = System.Net.Dns.GetHostName
drow3("Drive") = DriveName.Remove(1, 2)
drow3("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024)
drow3("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
drow3("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024)
drow3("GBorTBDrive") = "TB"
drow3("DriveActiveStatus") = d.IsReady
dt.Rows.Add(drow3)
ElseIf DriveNumber = 3 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace < 1099511627776 And d.TotalFreeSpace >= 1099511627776 Then
Dim drow3 As DataRow = dt.NewRow
drow3("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
drow3("Server") = System.Net.Dns.GetHostName
drow3("Drive") = DriveName.Remove(1, 2)
drow3("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024)
drow3("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024)
drow3("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
drow3("GBorTBDrive") = "TB"
drow3("DriveActiveStatus") = d.IsReady
dt.Rows.Add(drow3)
ElseIf DriveNumber = 3 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace < 1099511627776 And d.TotalFreeSpace < 1099511627776 Then
Dim drow3 As DataRow = dt.NewRow
drow3("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
drow3("Server") = System.Net.Dns.GetHostName
drow3("Drive") = DriveName.Remove(1, 2)
drow3("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024,)
drow3("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024)
drow3("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024)
drow3("GBorTBDrive") = "TB"
drow3("DriveActiveStatus") = d.IsReady
dt.Rows.Add(drow3)
End If
If DriveNumber = 4 And d.TotalSize < 1099511627776 Then
Dim drow4 As DataRow = dt.NewRow
drow4("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
drow4("Server") = System.Net.Dns.GetHostName
drow4("Drive") = DriveName.Remove(1, 2)
drow4("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024)
drow4("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024)
drow4("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024)
drow4("GBorTBDrive") = "GB"
drow4("DriveActiveStatus") = d.IsReady
dt.Rows.Add(drow4)
ElseIf DriveNumber = 4 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace >= 1099511627776 And d.TotalFreeSpace >= 1099511627776 Then
Dim drow4 As DataRow = dt.NewRow
drow4("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
drow4("Server") = System.Net.Dns.GetHostName
drow4("Drive") = DriveName.Remove(1, 2)
drow4("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024,)
drow4("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
drow4("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
drow4("GBorTBDrive") = "TB"
drow4("DriveActiveStatus") = d.IsReady
dt.Rows.Add(drow4)
ElseIf DriveNumber = 4 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace >= 1099511627776 And d.TotalFreeSpace < 1099511627776 Then
Dim drow4 As DataRow = dt.NewRow
drow4("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
drow4("Server") = System.Net.Dns.GetHostName
drow4("Drive") = DriveName.Remove(1, 2)
drow4("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024,)
drow4("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
drow4("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024)
drow4("GBorTBDrive") = "TB"
drow4("DriveActiveStatus") = d.IsReady
dt.Rows.Add(drow4)
ElseIf DriveNumber = 4 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace < 1099511627776 And d.TotalFreeSpace >= 1099511627776 Then
Dim drow4 As DataRow = dt.NewRow
drow4("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
drow4("Server") = System.Net.Dns.GetHostName
drow4("Drive") = DriveName.Remove(1, 2)
drow4("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024,)
drow4("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024)
drow4("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
drow4("GBorTBDrive") = "TB"
drow4("DriveActiveStatus") = d.IsReady
dt.Rows.Add(drow4)
ElseIf DriveNumber = 4 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace < 1099511627776 And d.TotalFreeSpace < 1099511627776 Then
Dim drow4 As DataRow = dt.NewRow
drow4("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
drow4("Server") = System.Net.Dns.GetHostName
drow4("Drive") = DriveName.Remove(1, 2)
drow4("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024,)
drow4("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024)
drow4("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024)
drow4("GBorTBDrive") = "TB"
drow4("DriveActiveStatus") = d.IsReady
dt.Rows.Add(drow4)
End If
If DriveNumber = 5 And d.TotalSize < 1099511627776 Then
Dim drow5 As DataRow = dt.NewRow
drow5("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
drow5("Server") = System.Net.Dns.GetHostName
drow5("Drive") = DriveName.Remove(1, 2)
drow5("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024)
drow5("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024)
drow5("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024)
drow5("GBorTBDrive") = "GB"
drow5("DriveActiveStatus") = d.IsReady
dt.Rows.Add(drow5)
ElseIf DriveNumber = 5 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace >= 1099511627776 And d.TotalFreeSpace >= 1099511627776 Then
Dim drow5 As DataRow = dt.NewRow
drow5("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
drow5("Server") = System.Net.Dns.GetHostName
drow5("Drive") = DriveName.Remove(1, 2)
drow5("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024,)
drow5("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
drow5("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
drow5("GBorTBDrive") = "TB"
drow5("DriveActiveStatus") = d.IsReady
dt.Rows.Add(drow5)
ElseIf DriveNumber = 5 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace >= 1099511627776 And d.TotalFreeSpace < 1099511627776 Then
Dim drow5 As DataRow = dt.NewRow
drow5("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
drow5("Server") = System.Net.Dns.GetHostName
drow5("Drive") = DriveName.Remove(1, 2)
drow5("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024)
drow5("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
drow5("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024)
drow5("GBorTBDrive") = "TB"
drow5("DriveActiveStatus") = d.IsReady
dt.Rows.Add(drow5)
ElseIf DriveNumber = 5 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace < 1099511627776 And d.TotalFreeSpace >= 1099511627776 Then
Dim drow5 As DataRow = dt.NewRow
drow5("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
drow5("Server") = System.Net.Dns.GetHostName
drow5("Drive") = DriveName.Remove(1, 2)
drow5("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024,)
drow5("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024)
drow5("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
drow5("GBorTBDrive") = "TB"
drow5("DriveActiveStatus") = d.IsReady
dt.Rows.Add(drow5)
ElseIf DriveNumber = 5 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace < 1099511627776 And d.TotalFreeSpace < 1099511627776 Then
Dim drow5 As DataRow = dt.NewRow
drow5("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
drow5("Server") = System.Net.Dns.GetHostName
drow5("Drive") = DriveName.Remove(1, 2)
drow5("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024,)
drow5("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024)
drow5("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024)
drow5("GBorTBDrive") = "TB"
drow5("DriveActiveStatus") = d.IsReady
dt.Rows.Add(drow5)
End If
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) & "ss" & Space(1) & "tt")
Dim path As String = "C:\temp"
path = IO.Path.Combine(path, "ServerStorageC7L6M72" & Space(1) & day & Space(1) & ".xml")
dt.WriteXml(path)
End Sub
Public Sub UploadXML(sender As Object, e As EventArgs)
Dim day As String = DateTime.Now.ToString("yyyy MM dd h mm ss tt")
Dim path As String = IO.Path.Combine("C:\temp", "ServerStorageC7L6M72 " & day & " .xml")
' Please tell me you're not really using the sa account for this. That's REALLY BAD!
Dim ServerCheckConnectString = "Persist Security Info=False;User ID=sa;Password=*****;Initial Catalog=ServerDriveStorageChecks;Data Source=localhost;"
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);"
Using cn As New SqlConnection(ServerCheckConnectString),
cmd As New SqlCommand(SQL, cn)
cn.Open()
cmd.ExecuteNonQuery()
End Using ' Connection is closed/disposed here, *even if an exception is thrown*
End Sub
End Module
The VB.Net SQL string includes an extra comma at the end of the first line, between the initial SELECT and INSERT commands, that is not part of the original SQL and should not be there, causing SQL Server to see this invalid expression:
SELECT * FROM DriveInformation, INSERT INTO ...
Remove the "," from that section. But also it doesn't seem you are using this SELECT statement at all, and can remove it entirely. If you are using it, and we just don't see that portion of the code, at least put it after the INSERT command, so the new rows are included with the results.
There are other improvements we can make, as well:
Public Sub UploadXML(sender As Object, e As EventArgs)
Dim day As String = DateTime.Now.ToString("yyyy MM dd h mm ss tt")
Dim path As String = IO.Path.Combine("C:\temp", "ServerStorageC7L6M72 " & day & " .xml")
' Please tell me you're not really using the sa account for this. That's REALLY BAD!
Dim ServerCheckConnectString As String = "Persist Security Info=False;User ID=sa;Password=********;Initial Catalog=ServerDriveStorageChecks;Data Source=localhost;"
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);"
Using cn As New SqlConnection(ServerCheckConnectString), _
cmd As New SqlCommand(SQL, cn)
cn.Open()
cmd.ExecuteNonQuery()
End Using ' Connection is closed/disposed here, *even if an exception is thrown*
End Sub
One particular pet peeve of mine is this:
Dim cn As New SqlConnection
' ...
cn = New SqlConnection(ServerCheckConnectString)
... where you declare a variable with New, so it also allocates the object, and then the first reference to the variable again assigns a new reference to it. This wastefully allocates an extra object from memory, only to immediately discard it. While it's possible the compiler might detect this and optimize the allocation away, it's often a sign the author lacks a basic understanding of what the code is doing.
Update:
Having read through the code, I believe this issue is the current time changes between creating the xml file and loading it in the server, so the file name (down to the second) no longer exists. To fix this, you can either save the file name somewhere in memory at the point when you write it to disk, or ask the system for the names of the xml files in your target path.
While I was there, I took a few minutes to consolidate all the IF/Else checks in the final loop down to a SINGLE SET, reducing the code considerably:
Public GBTBBreak As Integer = 1099511627776
Public GBDivisor As Double = 1024 * 1024 * 1024 * 1.0
Public TBDivisor As Double = GBDivisor * 1024
Public Function FormatForGBTB(input As Integer) 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)
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") = Host
row("Drive") = d.Name.Remove(1, 2)
' There was an issue with the original where the units were
' only preserved in the GBorTBDrive column, which was only
' based on the TotalSpace column, but the UsedSpace and
' Remaining space columns would still scale without showing
' which units they used. Since we're storing the unit
' separately, I simplified to always scale with the
' GBorTBColumn. Otherwise, information was lost about
' the actual amount of used vs free space. As an alternative,
' you could include the units with each column again,
' as was done in the previous loop.
row("TotalSpace") = (d.TotalSize / divisor).ToString()
row("UsedSpace") = ((d.TotalSize-d.TotalFreeSpace) / divisor).ToString()
row("RemainingSpace") = (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 MM dd h mm ss tt")
Dim path As String = IO.Path.Combine("C:\temp", "ServerStorageC7L6M72 " & day & " .xml")
dt.WriteXml(path)
End Sub