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...