Using OdbcDataAdapter in vb2005  
Author Message
Dave987654321





PostPosted: Visual Basic Express Edition, Using OdbcDataAdapter in vb2005 Top

Two questions;

The following code works to import an excell file into a datagridview in VB2005 express.

ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=c:\test.xls;"
Dim Con As New OdbcConnection(ConnectionString)
Dim Adapter As New OdbcDataAdapter("SELECT * FROM [test$]", Con)
Dim Ds As New DataSet

Try
Con.Open()
Adapter.Fill(Ds, "Sheet1")
Catch Err As Exception
MessageBox.Show(Err.ToString())
Finally
Con.Close()
End Try

Datagridview1.DataSource = Ds.Tables("Sheet1")

1. I was looking for a way to find the names of the data sheets in the file (sheet1,sheet2,etc) and put them in a listbox so the user could select the one to load. But haven't been able to come up with anything.

2. Also, I did find a blurb form microsoft about OdbcDataAdapter not being supported in .NET2....Am I doing this wrong and if so what should I be using
Thanks
Dave


Visual Studio Express Editions10  
 
 
ahmedilyas





PostPosted: Visual Basic Express Edition, Using OdbcDataAdapter in vb2005 Top

.NET 2.0 does have odbc.

To get the worksheets using odbc, I guess you can use the GetOleDbSchema() to get the schema and go through each row using the "TABLE_NAME" column to get the worksheet name.

Example (untested)

Dim theDataTable as new DataTable()

theDataTable = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, nothing);

for each currentRow as DataRow in theDataTable

MessageBox.Show(currentRow("TABLE_NAME").ToString())

next

does this help



 
 
Spooner





PostPosted: Visual Basic Express Edition, Using OdbcDataAdapter in vb2005 Top

I'm trying to use the code posted above to import a excel spreadsheet but the two lines highlighted below show as being Type Not Defined.

When I set up the datagrid on the Design screen and try to configure the data connection I can only see Microsoft Access or Microsoft SQL Server Database File types but not Excel.

Any help would be gratefully appreciated

ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=c:\test.xls;"
Dim Con As New OdbcConnection(ConnectionString)
Dim Adapter As New OdbcDataAdapter("SELECT * FROM [test$]", Con)
Dim Ds As New DataSet

Try
Con.Open()
Adapter.Fill(Ds, "Sheet1")
Catch Err As Exception
MessageBox.Show(Err.ToString())
Finally
Con.Close()
End Try

Datagridview1.DataSource = Ds.Tables("Sheet1")