datatable error

[spooke2k]

struggling to conbine a excel read into a write to data table both work seperatly in demo form but wont work together

effectively the excel document reads fine and populates the table and datagridview and the columns in datagrid matches the spreadsheet fields exactly and the table im trying to send data too but when i try and import the data table to the actual access table doesnt write

   Dim table As New DataTable
        Dim adapter As New OleDbDataAdapter("SELECT * FROM dp2", "Provider=Microsoft.Jet.OLEDB.4.0; Data source ='" & dbpath1 & "'; Jet OLEDB:database password=123")
        Dim builder As New OleDbCommandBuilder(adapter)




        Dim ds1 As New DataSet
        Dim _filename As String = "C:\Users\Colin\Downloads\software\AccessConnection\DataImport.xls"
        Dim _conn As String


        Dim da As OleDbDataAdapter = New OleDbDataAdapter()
        Dim _command As OleDbCommand = New OleDbCommand()


        _conn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _filename & ";" & "Extended Properties=Excel 8.0;"
        Dim _connection As OleDbConnection = New OleDbConnection(_conn)


        _command.Connection = _connection
        _command.CommandText = "SELECT * FROM [Sheet1$]"
        da.SelectCommand = _command
        Try
            da.Fill(ds1, "sheet1")
            MessageBox.Show("The import is complete!")
            Me.DataGridView1.DataSource = ds1
            Me.DataGridView1.DataMember = "Sheet1"


            BindingSource1.DataSource = DataGridView1.DataSource
            DataGridView1.DataSource = Me.BindingSource1


            BindingSource1.EndEdit()
            adapter.Update(table)

Many thanks i appeciate the help and sorry troubke you

 

[jmcilhinney]

When you call Fill on a data adapter, it populates the DataTable and then calls AcceptChanges by default, which means that all rows have their RowState set to Unchanged. Such rows are ignored when you call Update. If you want a record to be inserted when you call Update then its RowState must be Added. To ensure that, set the AcceptChangesDuringFill property of the data adapter to False before calling Fill. AcceptChanges will not be called and the RowStates will remain Added, ready to be inserted.

 

[spooke2k]

Sorry but how would I set that I really appeciate help been out of programming a while

 

[jmcilhinney]

It's a property so you set it like you do any other property. If you don't know how to set properties then I suggest that you read a beginners tutorial and learn the basics. There's one in my signature below. You do know how to set properties though, because you're already doing it several times in the code you posted. I'm here to help people with programming problems, not teach them the fundamentals of programming.

 

[spooke2k]

I get jist of fundamentals And yes I know how to set property's. I was wondering whether it's declared to the component or via code easiest way.

i appeciate your help so apologies for offence

 

[jmcilhinney]

I get jist of fundamentals And yes I know how to set property's. I was wondering whether it's declared to the component or via code easiest way.

i appeciate your help so apologies for offence

That's not how your question came across so you should ensure that you actually ask the question that you want answered. The question isn't relevant anyway though. You're creating the data adapter in code so you have no choice but to set its properties in code.