I am binding a gridview to an excel document.
One of the columns in the document is zip codes where some of the numbers have leading zero's, like 05301. When the gridview displays this column it does not show the value is there is a leading zero. I checked and there are no special characters hidden in the field.
I tried changing the column type to general, special (zip code), number, and text and got the same results.
Any ideas on what is going on? I know sometimes with excel and access it can be the connection string that goofs things up.
Here is the code I am using the query the excel document
Code:
Private Function GetExcelConnection() As OleDbConnection
Try
Dim objConn As OleDbConnection
'Grab the file name from its fully qualified path at client
Dim strSourceFile As String = "c:\document.xls"
' only the attched file name not its path
Dim strSourceFileName As String = System.IO.Path.GetFileName(strSourceFile)
Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strSourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"""
'Create connection object by using the preceding connection string.
objConn = New OleDbConnection(sConnectionString)
objConn.Open()
Return objConn
Catch ex As Exception
End Try
End Function
Code:
Dim objAdapter1 As OleDbDataAdapter
Dim objDataset As DataSet
Dim myData As OleDbDataAdapter
Using objConn As OleDbConnection = GetExcelConnection()
objAdapter1 = New OleDbDataAdapter
objDataset = New DataSet
myData = New OleDbDataAdapter("SELECT * FROM [Sheet1$]", objConn)
myData.Fill(objDataset)
objConn.Close()
End Using