April 16, 2019 at 8:28 pm
I have the followimg cpde in a VB.Net Windows Forms App:
I have the followimg cpde in a VB.Net Windows Forms App:Private Sub ImportButton_Click(sender As Object, e As EventArgs) Handles ImportButton.Click
REM Dim ds As New DataSet("PARTS")
Dim da As OleDbDataAdapter
Dim conn As OleDbConnection = Nothing
Dim sheet As String, path As String
Dim xl As New DataTable("PARTS")
Dim column As New DataColumn
column.DataType = Type.GetType("System.Int32")
column.ColumnName = "EXCEL_ROW"
xl.Columns.Add(column)
column = New DataColumn
column.DataType = Type.GetType("System.String")
column.ColumnName = "ID"
xl.Columns.Add(column)
column = New DataColumn
column.DataType = Type.GetType("System.DateTime")
column.ColumnName = "DATE"
xl.Columns.Add(column)
Dim I As Integer
path = FileNameTextBox.Text.ToString
Try
conn = New OleDbConnection(
"Provider=Microsoft.ACE.OLEDB.12.0; " &
"Data Source=""" & path & """; " &
"Extended Properties=Excel 12.0 Xml;HDR=YES;")
''get sheet name
sheet = GetSheetName(path)
da = New OleDbDataAdapter("SELECT EXCEL_ROW, ID, [DATE] FROM [" & sheet & "]", conn)
conn.Open()
da.Fill(xl)
Catch ex As Exception
Debug.Print(ex.Message)
MessagesTextBox.AppendText(ex.Message & vbCrLf)
Finally
If conn.State = ConnectionState.Open Then
conn.Close()
End If
End Try
I = 2
For Each row In xl.Rows
row = New String() {row("EXCEL ROW").ToString, row("ID").ToString, row("DATE").ToString}
DGV.Rows.Add(row)
I = I + 1
Next
xl = Nothing
conn = Nothing
End Sub
Function GetSheetName(ByVal path As String) As String
Dim dt As New DataTable
Dim conn As OleDbConnection
Dim sSheetName As String = ""
Dim strConn As String
Try
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" &
"Data Source=" & path & ";" &
"Extended Properties=""Excel 12.0 Xml;"""
conn = New OleDbConnection(strConn)
conn.Open()
dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
sSheetName = dt.Rows(0).Item("Table_Name")
Catch ex As Exception
Debug.Print(ex.Message)
MessagesTextBox.AppendText(ex.Message & vbCrLf)
End Try
GetSheetName = sSheetName
End Function
When I've taken care of using a file dialog to populate the text box with the Excel file name, I then click on my IMPORT button, and this code runs, and then fails with the "Could not find installable ISAM." error when it runs the conn.Open statement. Any ideas? I've tried forcing the connection string to have double quotes around the filename, as it does have spaces in the path portion. I've look online a lot and this error only seems to appear relating to MS Access. Hoping someeone out there has done this before and is familiar.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 16, 2019 at 8:53 pm
Not sure about VB. From SQL I use syntax like below and it works fine. Provided, of course, that ACE driver is actually installed on the SQL Server, no one keeps Excel file open and Sheet1 exists.
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\Excel File.xls',
'select * from [Sheet1$]')--Vadim R.
April 17, 2019 at 1:54 pm
Thanks for that. Unfortunately, I can't use T-SQL for this, as the file needs to go on the user's machine as opposed to on the server. We need this as uncomplicated as I can make it, given that I already have to secure the database at a fairly high level.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 17, 2019 at 2:54 pm
Found the OLEDB answer on StackOverflow. Turns out the Extended Properties portion of the connection string needed to be enclosed in quotes. Of course, that only got me past the problem with getting the data. Now I've got a weird problem with filling the DataGridView control. I have another app in which I create a New String() = {"value1", "value2", "value3"} and that new string variable is named newrow, and that is how I then use DataGridView1.Add(newrow).
But now, for some reason, using the same technique in this new app, my first column gets the value "System.String[]" and the other 2 columns get nothing. No idea why. Something I'm doing must be messing this up.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 17, 2019 at 4:21 pm
So here's the latest version of the code:
Private Sub ImportButton_Click(sender As Object, e As EventArgs) Handles ImportButton.Click
Dim da As OleDbDataAdapter
Dim conn As OleDbConnection = Nothing
Dim sheet As String, path As String
Dim xl As New DataTable("PARTS")
Dim column As New DataColumn
column.DataType = Type.GetType("System.Int32")
column.ColumnName = "EXCEL_ROW"
xl.Columns.Add(column)
column = New DataColumn
column.DataType = Type.GetType("System.String")
column.ColumnName = "ID"
xl.Columns.Add(column)
column = New DataColumn
column.DataType = Type.GetType("System.DateTime")
column.ColumnName = "COUNT_DATE"
xl.Columns.Add(column)
path = FileNameTextBox.Text.ToString
Try
conn = New OleDbConnection(
"Provider=Microsoft.ACE.OLEDB.12.0; " &
"Data Source=""" & path & """; " &
"Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=2;""")
''get sheet name
sheet = GetSheetName(path)
da = New OleDbDataAdapter("SELECT EXCEL_ROW, ID, COUNT_DATE FROM [" & sheet & "]", conn)
conn.Open()
da.Fill(xl)
Catch ex As Exception
Debug.Print(ex.Message)
MessagesTextBox.AppendText(ex.Message & vbCrLf)
Finally
If conn.State = ConnectionState.Open Then
conn.Close()
End If
End Try
Dim newrow
Dim ExcelRow As String, ID As String, TheDate As String
For Each row In xl.Rows
ExcelRow = row("EXCEL_ROW").ToString
ID = row("ID").ToString
TheDate = row("COUNT_DATE").ToString
TheDate = VisualBasic.Left(TheDate, 10)
newrow = New String() {ExcelRow, ID, TheDate}
DGV.Rows.Add(newrow)
Next
xl = Nothing
conn = Nothing
End Sub
Function GetSheetName(ByVal path As String) As String
Dim dt As New DataTable
Dim conn As OleDbConnection
Dim sSheetName As String = ""
Dim strConn As String
Try
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" &
"Data Source=" & path & ";" &
"Extended Properties=""Excel 12.0 Xml;"""
conn = New OleDbConnection(strConn)
conn.Open()
dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
sSheetName = dt.Rows(0).Item("Table_Name")
Catch ex As Exception
Debug.Print(ex.Message)
MessagesTextBox.AppendText(ex.Message & vbCrLf)
End Try
GetSheetName = sSheetName
End Function
And it populates only the first column of the DataGridView named DGV with "System.String[]". I've tried changing the data types in the data table to all be strings, and still couldn't make things work. At one point I even had a failure because of the way I named a column in the SQL query. Just hoping someone will see the code and recognize why a DataGridView control would react that way. The DGV control has exactly 3 columns. Wondering if some property needs to be changed or something like that? Am I constructing the newrow variable incorrectly, perhaps? Any ideas at all?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 17, 2019 at 5:42 pm
You probably will have better luck on some VB.Net forum.
--Vadim R.
April 17, 2019 at 5:42 pm
Finally fixed the problem. Had to Dim newrow As String() first. That solved the problem. All I've got left to do now is to fix the column alignment in the second column and then figure out how to reference the values in the datagridview cells.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply