none
Trying to read the contents of an excel sheet and populate it to a Gridview RRS feed

  • Question

  • Hi All,

    I am new to VB.net. I have a requirement to read the excel sheet contents(with first row as header) and to populate it in a Datagrid. so as to manipulate the excel sheet data. But i get an error saying "Could not find installable ISAM". after a big struggle i came to know that the culprit is the "connection string". I tried with various connection strings and now i get the error " Format of the Initialization string does not conform to specification starting at index 121".
    i have no idea about the error.. could some one help me please...

    my code is


    Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnSubmit.Click

    Dim Extension As String
    Dim Filename As String

    If (BtnUploadFile.HasFile) Then

    Try

    Filename = Server.HtmlEncode(BtnUploadFile.FileName)
    Extension = System.IO.Path.GetExtension(Filename)

    If (Extension.Equals(".xls")) = False Then
    MsgBox("Please select an Excel File", MsgBoxStyle.OkOnly)
    Else

    BtnUploadFile.SaveAs(Server.MapPath("~/" + Filename))
    LblUpload.Text = "File has been uploaded Successfully "
    LblUpload.Visible = True

    'Define Connection String
    Dim MyConnection As String
    MyConnection = "Provider=Microsoft.Jet.OLEDB.4.0; datasource=" & Server.MapPath("~/" + Filename) & ";Extended Properties=""Excel 8.0; HDR=Yes; IMEX=1"""""

    'MyConnection = "Provider=Microsoft.Jet.OLEDB.4.0; datasource=" & Server.MapPath("~/" + Filename) & ";Integrated Security=True;Extended Properties=""Text;HDR=Yes;IMEX=2"""""

    'MyConnection = "Provider=Microsoft.Jet.OLEDB.4.0; datasource=" & Server.MapPath("~/" + Filename) & ";Integrated Security=True; Extended Properties = ""Text;HDR=YES;FMT=Delimited(,);"""

    'Open connection to an Excel File

    Dim ObjDataAdapter = New System.Data.OleDb.OleDbDataAdapter
    Dim odbConn As System.Data.OleDb.OleDbConnection
    Dim dbCommand As System.Data.OleDb.OleDbCommand

    'MyConnection = "Provider=Microsoft.Jet.OLEDB.4.0; datasource=" & Server.MapPath("~/" + Filename) & ";Extended Properties=""Text;HDR=Yes;IMEX=1"""""

    'MyConnection = "provider=Microsoft.Jet.OLEDB.4.0; datasource=" & Server.MapPath("~/" + Filename) & ";Username=uid;password=pwd;Extended Properties=""Text;HDR=Yes;IMEX=1"""""

    odbConn = New OleDbConnection(MyConnection)
    odbConn.Open()
    dbCommand = New OleDbCommand("select * from [inhouse$]", odbConn)
    ObjDataAdapter.SelectCommand = dbCommand

    'Fill contents in Gridview
    Dim DS As New System.Data.DataSet
    Dim DT As New System.Data.DataTable
    ObjDataAdapter.Fill(DS)
    DT = DS.Tables("0")
    Dim count As Integer
    count = DT.Rows.Count
    DataGridView.DataSource = DT
    DataGridView.DataBind()

    odbConn.Close()

    End If

    Catch ex1 As Exception
    MsgBox("Error Occured : " & ex1.Message, MsgBoxStyle.OkOnly)
    End Try

    Else
    MsgBox("Please select a File to Upload", MsgBoxStyle.OkOnly)
    End If

    End Sub

    I am struck in this for two days.. can some one help me

    Tuesday, April 20, 2010 2:14 PM

Answers

  • The error indicates there is a problem with your connection string. First "Datasource" should be "Data Source". Try fixing that first.

    I would also remove the Integrated Security argument since it is not supported by the Jet OLEDB Provider.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by meenakshi68 Wednesday, April 21, 2010 6:39 AM
    Tuesday, April 20, 2010 5:27 PM

All replies

  • Hi,

    don't use ado.net for Excel. .NET has an own provider for Office.

    The magic class is: Excel interop

    With the excel interop you can do all (really all) actions as directly in excel.

    Some codelines you can read in an another thread like here .

     

    Regards

    martinwy

    Tuesday, April 20, 2010 5:01 PM
  • The error indicates there is a problem with your connection string. First "Datasource" should be "Data Source". Try fixing that first.

    I would also remove the Integrated Security argument since it is not supported by the Jet OLEDB Provider.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by meenakshi68 Wednesday, April 21, 2010 6:39 AM
    Tuesday, April 20, 2010 5:27 PM
  • Hi Paul,

    Thank you.. it started working when i made the change mentioned by you. Also there was an extra " (double quotes) at the end of the connection string. When i remove that too.. it started working..

    Thanks a lot for the Answer

    Wednesday, April 21, 2010 6:41 AM