locked
Excel connection and update RRS feed

  • Question

  • User740415740 posted

     i am doing a excel upload to the database in my application, and i dont seems to be able to make it work, there's this error at the open conneciton part, "Could not find installable ISAM.": can anyone help me with this? thanks!

     

    Imports System.Data
    Imports System.Data.SqlClient
    Imports System.Data.OleDb
    
    Partial Class ExcelUpdate
        Inherits System.Web.UI.Page
        Protected Sub transferbtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles transferbtn.Click
            Dim TotalRows As Integer
            Dim TotalRows2 As Integer
    
            Dim dSetSource As New DataSet
            Dim dAdapterSource As OleDbDataAdapter
            Dim selSource As String
    
            Dim dSetDestination As New DataSet
            Dim dAdapterDestination As SqlDataAdapter
            Dim selDestination As String
            'Dim UpdDestination As String
            'Dim InsDestination As String
    
            Dim sourceConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Data Source=D:\Inetpub\wwwroot\WebSite_AMS1\excelfile\uploadupdate.xls;Extended Properties = Excel 8.0; HDR=YES;"
            '"Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=D:\Inetpub\wwwroot\WebSite_AMS1\excelfile\uploadupdate.xls;" & "Extended Properties=Excel 8.0;HDR=YES;"
    
            Dim sourceConnection As OleDbConnection = New OleDbConnection(sourceConnectionString)
            sourceConnection.Open()
            selSource = "SELECT * FROM [inventory$];"
            'ibdwvc_invid, cntry, mach_typ, mdl, mach_ser_no, first_instld_date, warr_end_date, ma_start_date, ma_end_date, ma_no, maint_typ, cust_name, invty_sys
            dAdapterSource = New OleDbDataAdapter(selSource, sourceConnection)
            dAdapterSource.Fill(dSetSource, "inventory$")
    
            TotalRows = dSetSource.Tables("inventory$").Rows.Count
    
            Dim destinationConnectionString As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\AMS.mdf;Integrated Security=True;User Instance=True"
            Dim destinationConnection As SqlConnection = New SqlConnection(destinationConnectionString)
    
            selDestination = "SELECT [idinventoryid], [ibdwvc_invid], [ams_cust_name], [cust_no], [country], [cust_name], [mtype], [mdl], [serial], [tom], [inv_sts], [inst_date], [wexp_date], [ma_start], [ma_end], [cont_id], [site], [esa_eligible], [mc_eligible], [pm_eligible], [call_home], [modem], [vpn_support], [vpn_setup], [ch_remarks], [inv_remarks], [obsolete] FROM [inventory]"
            dAdapterDestination = New SqlDataAdapter(selDestination, destinationConnection)
            dAdapterDestination.Fill(dSetDestination, "inventory")
    
            TotalRows2 = dSetDestination.Tables("inventory").Rows.Count
    
            Dim Counter As Integer
            Dim Counter2 As Integer
            Dim dsNewRowDestination As DataRow
            'Dim cBuilder As New SqlCommandBuilder(dAdapterDestination)
    
            Dim invid As String
            Dim country As String
            Dim mactyp As String
            Dim mdl As String
            Dim mserial As String
            Dim insdate As String
            Dim warrdate As String
            Dim masdate As String
            Dim maedate As String
            Dim maint As String
            Dim cusna As String
            Dim mano As String
            Dim invty As String
    
            For Counter = 0 To TotalRows - 1
    
                'TotalRows = Label2.Text
                'read
                invid = dSetSource.Tables("inventory$").Rows(Counter).Item("ibdwvc_invid")
                country = dSetSource.Tables("inventory$").Rows(Counter).Item("cntry")
                mactyp = dSetSource.Tables("inventory$").Rows(Counter).Item("mach_typ")
                mdl = dSetSource.Tables("inventory$").Rows(Counter).Item("mdl")
                mserial = dSetSource.Tables("inventory$").Rows(Counter).Item("mach_ser_no")
                insdate = dSetSource.Tables("inventory$").Rows(Counter).Item("first_instld_date")
                warrdate = dSetSource.Tables("inventory$").Rows(Counter).Item("warr_end_date")
                masdate = dSetSource.Tables("inventory$").Rows(Counter).Item("ma_start_date")
                maedate = dSetSource.Tables("inventory$").Rows(Counter).Item("ma_end_date")
                maint = dSetSource.Tables("inventory$").Rows(Counter).Item("maint_typ")
                cusna = dSetSource.Tables("inventory$").Rows(Counter).Item("cust_name")
                mano = dSetSource.Tables("inventory$").Rows(Counter).Item("ma_no")
                invty = dSetSource.Tables("inventory$").Rows(Counter).Item("invty_sys")
    
    
                'insert
                dsNewRowDestination = dSetDestination.Tables("inventory").NewRow()
                dsNewRowDestination.Item("ibdwvc_invid") = invid
                dsNewRowDestination.Item("country") = country
                dsNewRowDestination.Item("mtype") = mactyp
                dsNewRowDestination.Item("mdl") = mdl
                dsNewRowDestination.Item("serial") = mserial
                dsNewRowDestination.Item("inst_date") = insdate
                dsNewRowDestination.Item("wexp_date") = warrdate
                dsNewRowDestination.Item("ma_start") = masdate
                dsNewRowDestination.Item("ma_end") = maedate
                dsNewRowDestination.Item("tom") = maint
                dsNewRowDestination.Item("cust_name") = cusna
                dsNewRowDestination.Item("cont_id") = mano
                dsNewRowDestination.Item("inv_sts") = invty
    
                dSetDestination.Tables("inventory").Rows.Add(dsNewRowDestination)
                dAdapterDestination.Update(dSetDestination, "inventory")
    
            Next Counter
            Label1.Text = "Database Inserted"
            
        End Sub
      
    Wednesday, November 19, 2008 2:23 PM

Answers

  • User-1136466523 posted

    Hi,

    <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>

    This error will also be generated when the syntax of the connection string is incorrect. This commonly occurs when using multiple Extended Properties parameters. Try the following samples with Microsoft.Jet.OLEDB.4.0

    <o:p> </o:p>

    ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

    "Data Source=D:\Inetpub\wwwroot\WebSite_AMS1\excelfile\uploadupdate.xls;Extended Properties=""Excel 8.0;HDR=NO;IMEX=1"""

    <o:p> </o:p>

    Thanks.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 25, 2008 1:04 AM

All replies

  • User1485238302 posted

    First things first. Your sourceConnectionString is a bit messed up. You have Data Source value defined multiple times.

    Wednesday, November 19, 2008 2:39 PM
  • User740415740 posted
    its not working either... i even try this "

    provider=Microsoft.ACE.OLEDB.12.0;Data source=D:\Inetpub\wwwroot\WebSite_AMS1\excelfile\uploadupdate.xls;Extended Properties=Excel 12.0;HDR=YES;IMEX=1"""

     

    Thursday, November 20, 2008 12:17 AM
  • User-1136466523 posted

    Hi,

    <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>

    This error will also be generated when the syntax of the connection string is incorrect. This commonly occurs when using multiple Extended Properties parameters. Try the following samples with Microsoft.Jet.OLEDB.4.0

    <o:p> </o:p>

    ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

    "Data Source=D:\Inetpub\wwwroot\WebSite_AMS1\excelfile\uploadupdate.xls;Extended Properties=""Excel 8.0;HDR=NO;IMEX=1"""

    <o:p> </o:p>

    Thanks.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 25, 2008 1:04 AM