Help Insert into Excel database..

[edgarbenilde]

Hi! someone help me please..

1.) error on saving the data into excel sheet when the program is execute nonquery the bold line below

        Try
            Dim MyConnection As System.Data.OleDb.OleDbConnection
            Dim myCommand As New System.Data.OleDb.OleDbCommand
            Dim sql As String

            MyConnection = New System.Data.OleDb.OleDbConnection _
            ("provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + _
            "'c:\LocalSystem\SMRPCustomer.xls';Extended Properties=Excel 8.0;HDR=YES;""")

            MyConnection.Open()
            myCommand.Connection = MyConnection
            sql = "Insert into [Customer$] (CustID, Cuscode, Cusname, Cusaddress, Tel, Fax, Tscode, Group, Status, Tradecode, Trade, Class, Sync, Employees ) values('" & Me.txtCustID.Text & "','" & Me.txtCustcode.Text & "', '" & Me.txtCusname.Text & "','" & Me.txtAddress.Text & "', '" & Me.txtTel.Text & "', '" & Me.txtfax.Text & "', '" & mdiTSMobile.lblUsername.Text & "', '" & mdiTSMobile.lblgroup.Text & "', '" & Me.cboStatus.Text & "', '" & Me.txtTradecode.Text & "', '" & Me.txtTrade.Text & "', '" & Me.txtClass.Text & "', '" & Me.txtSync.Text & "', '" & Me.txtEmployee.Text & "')"
            myCommand.CommandText = sql
   myCommand.ExecuteNonQuery()
            MyConnection.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        MsgBox("Row Added ")

'===============================================
2.) I have tried another way still got error when updating the database see the bold line below..

   Public connstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=c:\LocalSystem\SMRPCustomer.xls;Extended Properties=""Excel 8.0;HDR=YES;"""
    Public pram As OleDbParameter
    Public dr As DataRow

    Public olecon As OleDbConnection
    Public olecomm As OleDbCommand
    Public olecomm1 As OleDbCommand
    Public oleadpt As OleDbDataAdapter
    Public ds As DataSet

        If MsgBox("Are you sure to add this data", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
            Try
                olecon = New OleDbConnection
                olecon.ConnectionString = connstring
                olecomm = New OleDbCommand
                olecomm.CommandText = "Select CustID, Cuscode, Cusname, Cusaddress, Tel, Fax, Tscode, Group, Status, Tradecode, Trade, Class, Sync, Employees from [Customer$]"
                olecomm.Connection = olecon
                olecomm1 = New OleDbCommand
                olecomm1.CommandText = "Insert into [Customer$] " & _
                            "( CustID, Cuscode, Cusname, Cusaddress, Tel, Fax, Tscode, Group, Status, Tradecode, Trade, Class, Sync, Employees) values (@CustID, @Cuscode, @Cusname, @Cusaddress, @Tel, @Fax, @Tscode, @Group, @Status, @Tradecode, @Trade, @Class, @Sync, @Employees)"
                olecomm1.Connection = olecon
                pram = olecomm1.Parameters.Add("@CustID", OleDbType.VarChar)
                pram.SourceColumn = "CustID"
                pram = olecomm1.Parameters.Add("@Cuscode", OleDbType.VarChar)
                pram.SourceColumn = "Cuscode"
                pram = olecomm1.Parameters.Add("@Cusname", OleDbType.VarChar)
                pram.SourceColumn = "Cusname"
                pram = olecomm1.Parameters.Add("@Cusaddress", OleDbType.VarChar)
                pram.SourceColumn = "Cusaddress"
                pram = olecomm1.Parameters.Add("@Tel", OleDbType.VarChar)
                pram.SourceColumn = "Tel"
                pram = olecomm1.Parameters.Add("@Fax", OleDbType.VarChar)
                pram.SourceColumn = "Fax"
                pram = olecomm1.Parameters.Add("@Tscode", OleDbType.VarChar)
                pram.SourceColumn = "Tscode"
                pram = olecomm1.Parameters.Add("@Group", OleDbType.VarChar)
                pram.SourceColumn = "Group"
                pram = olecomm1.Parameters.Add("@Status", OleDbType.VarChar)
                pram.SourceColumn = "Status"
                pram = olecomm1.Parameters.Add("@Tradecode", OleDbType.VarChar)
                pram.SourceColumn = "Tradecode"
                pram = olecomm1.Parameters.Add("@Trade", OleDbType.VarChar)
                pram.SourceColumn = "Trade"
                pram = olecomm1.Parameters.Add("@Class", OleDbType.VarChar)
                pram.SourceColumn = "Class"
                pram = olecomm1.Parameters.Add("@Sync", OleDbType.VarChar)
                pram.SourceColumn = "Sync"
                pram = olecomm1.Parameters.Add("@Employees", OleDbType.VarChar)
                pram.SourceColumn = "Employees"
                oleadpt = New OleDbDataAdapter(olecomm)
                ds = New DataSet
                olecon.Open()
                oleadpt.Fill(ds, "Customer")
                If IsNothing(ds) = False Then
                    dr = ds.Tables(0).NewRow
                    dr("CustID") = Me.txtCustID.Text
                    dr("Cuscode") = Me.txtCustcode.Text
                    dr("Cusname") = Me.txtCusname.Text
                    dr("Cusaddress") = Me.txtAddress.Text
                    dr("Tel") = Me.txtTel.Text
                    dr("Fax") = Me.txtfax.Text
                    dr("Tscode") = mdiTSMobile.lblUsername.Text
                    dr("Group") = mdiTSMobile.lblgroup.Text
                    dr("Status") = Me.cboStatus.Text
                    dr("Tradecode") = Me.txtTradecode.Text
                    dr("Trade") = Me.txtTrade.Text
                    dr("Class") = Me.txtClass.Text
                    dr("Sync") = Me.txtSync.Text
                    dr("Employees") = Me.txtEmployee.Text

                    ds.Tables(0).Rows.Add(dr)
                    oleadpt = New OleDbDataAdapter
                    oleadpt.InsertCommand = olecomm1
                    Dim i As Integer = oleadpt.Update(ds, "Customer")
                    MessageBox.Show(i & " row added")
                   Me.Close()
                End If
            Catch ex As Exception
                MessageBox.Show(ex.Message)
                MessageBox.Show(ex.ToString)
            Finally
                olecon = Nothing
                olecomm = Nothing
                oleadpt = Nothing
                ds = Nothing
                dr = Nothing
                pram = Nothing
            End Try
        End If

Please help me really appreciate your help...

[toecutter]

I have to ask what version?

I see "Excel 8.0" in your code, is that like 2000 or prior?

[edgarbenilde]

it's 2003 version and i used vs.net 2005

[toecutter]

Do you have the Office XP Primary Interop Assemblies installed?

http://support.microsoft.com/kb/897646

[edgarbenilde]

yes i have already

[edgarbenilde]

it's the Microsoft Excel 11.0 library right..?

[toecutter]

mmm

I am running Office 2002 which comes up as Office10 in C:\Program Files\Microsoft Office\Office10\EXCEL.EXE and you have Excel 8.0 in your code and say you are using excel 2003.

[edgarbenilde]

yes 2003.. so, what it should be..?

[toecutter]

I am not sure but if you have "Data Source=c:\LocalSystem\SMRPCustomer.xls;Extended Properties=""Excel 8.0;HDR=YES;" in your code i think it refers to a earlier version than 2003.

[edgarbenilde]

it's 2003 this is the version i i have

C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE
"Data Source=c:\LocalSystem\SMRPCustomer.xls;Extended Properties=""Excel 8.0;HDR=YES;"

so if it is version 2003 what should be in the Extended Properties=?

[toecutter]

i am NO expert but i think it should be Extended Properties=""Excel 11.0

you can only try...

[edgarbenilde]

it's not working i have tried it already..

any thanks for help,
can anybody help me solve my problem, please..?

[edgarbenilde]

any help please..?