Results 1 to 3 of 3

Thread: [RESOLVED] Gridview bound to excel not showing leading zero

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Nov 2007
    Posts
    180

    Resolved [RESOLVED] Gridview bound to excel not showing leading zero

    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

  2. #2

    Thread Starter
    Addicted Member
    Join Date
    Nov 2007
    Posts
    180

    Re: Gridview bound to excel not showing leading zero

    Bah i hate it when I figure this stuff out after i post to the forum.

    I found out I needed to add IMEX=1; to the connection string to make it read the columns as text.

  3. #3
    ASP.NET Moderator gep13's Avatar
    Join Date
    Nov 2004
    Location
    The Granite City
    Posts
    21,963

    Re: [RESOLVED] Gridview bound to excel not showing leading zero

    Hey,

    But that means you must be doing something right

    If you haven't done so already, bookmark connectionstrings.com. Your one stop shop for all things connection string related.

    Gary

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width