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
