need help saving to database

[stateofidleness]

Hey guys,
I have an "Import" button that successfully reads in an excel spreadsheet to a bound datagridview with this code:

Private Sub importDBButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles importDBButton.Click
        OpenFileDialog1.InitialDirectory = ""
        OpenFileDialog1.Title = "Select the Excel File to import from"
        OpenFileDialog1.Filter = "Excel(*.xls)|*.xls"
        OpenFileDialog1.ShowDialog()
 
        If OpenFileDialog1.FileName <> "" Then
            Dim _filename As String = OpenFileDialog1.FileName
            Dim _conn As String
            _conn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _filename & ";" & "Extended Properties=Excel 8.0;"
            Dim _connection As OleDbConnection = New OleDbConnection(_conn)
            Dim da As OleDbDataAdapter = New OleDbDataAdapter()
            Dim _command As OleDbCommand = New OleDbCommand()
            _command.Connection = _connection
            _command.CommandText = "SELECT * FROM [Sheet1$]"
            da.SelectCommand = _command
            Try
                da.Fill(ds1, "sheet1")
                MessageBox.Show("Imported Successfully.")
                frmTest.device1DataScan.DataSource = ds1
                frmTest.device1DataScan.DataMember = "sheet1"
                frmTest.DVRSTableAdapter.Update(Me.ds1) 'I thought this would do the trick, but relaunching the program doesn't load the saved DB
            Catch e1 As Exception
                MessageBox.Show("Import Failed, incorrect Column names in the sheet!")
            End Try
        End If
    End Sub

This works great! What I would like to happen though, is for the new contents of the DGV to save to that table in the database, so that the next time the program loads, the user won't have to import again.

I tried Update().. Fill(), but to be honest, I don't know what needs to be "updated" or "filled" or how I should be saving the new contents to the database

Can someone straighten me out?

[jmcilhinney]

When you Fill a DataTable using a DataAdapter, all the DataRows in the table have their RowState set to Unchanged by default. What you need to do is set the AcceptChangesDuringFill property of your DataAdapter to False so that all the DataRows are flagged as Added, which means that they are new rows ready to be inserted into the database. You can then Update that DataTable using your TableAdapter.

Just note though, that you will need to Fill the same DataTable as your TableAdapter is supposed to save. That means that you can't Fill a table named "sheet1" in your DataSet. You have Fill ds1.DVRS, or whatever the table is called.

[stateofidleness]

ahh ok. guess that's where I'm kinda confused. that code posted I snagged online and it creates a completely different DA and DS. I was hoping I could leave it intact, and once the new ds is filled and I have bound it to the DGV, then I could somehow say.. "take whatever is now in the DGV and store it in my original table"
(DeviceDBDataSet.DVRS)

EDIT: I dont show that property on my TableAdapter or on my DataSet. Intellisense will show it for the da in the code above, but tells me "Property access must assign to the property or use its value"

frmTest.DVRSTableAdapter is the one i need to allow changes on i suppose but the property isn't listed for it

[stateofidleness]

*bumpity*

[stateofidleness]

Really can't figure this out.. been toying with it for the past 2 days and can't get it..

I have:
DVRSTableAdapter
DeviceDBDataSet
and a table called DVRS

the code above seems to create its own dataset and data adapter. I just need to be able to save what gets imported from the above code to my Table called DVRS and have it save to the database so they won't have to import on the next program launch.

Is there another way I could be doing this? I have a blank table, DVRS, in an access file. When the program first launches, I want them to import their stuff to my DataGridView. This works so far. Now I want them to be able to save the contents of the DataGridView to he DVRS table in the database. Since the DataGridView's binding source is the DVRS table, on the next launch, it should populate by itself the previous imported devices.

Can someone help me out here?

[jmcilhinney]

Really can't figure this out.. been toying with it for the past 2 days and can't get it..

I have:
DVRSTableAdapter
DeviceDBDataSet
and a table called DVRS

the code above seems to create its own dataset and data adapter. I just need to be able to save what gets imported from the above code to my Table called DVRS and have it save to the database so they won't have to import on the next program launch.

Is there another way I could be doing this? I have a blank table, DVRS, in an access file. When the program first launches, I want them to import their stuff to my DataGridView. This works so far. Now I want them to be able to save the contents of the DataGridView to he DVRS table in the database. Since the DataGridView's binding source is the DVRS table, on the next launch, it should populate by itself the previous imported devices.

Can someone help me out here?

I've already answered your question in post #2:

Just note though, that you will need to Fill the same DataTable as your TableAdapter is supposed to save. That means that you can't Fill a table named "sheet1" in your DataSet. You have Fill ds1.DVRS, or whatever the table is called.

[stateofidleness]

what is ds1.DVRS?

ds1 is the dataset created during the import. "DVRS" is an existing table in my database.
I've tried:
da.Fill(frmTest.DeviceDBDataSet.DVRS, "sheet1")

This works:
da.Fill(ds1, "sheet1")

but it is filling the wrong data adapter. I need it to go to DVRSTableAdapter, but the .Fill on DVRSTableAdapter won't work for me...

what should my fill statement be?

[stateofidleness]

Try
                da.Fill(ds1, "sheet1")
                MessageBox.Show("Imported Successfully.")
                frmTest.device1DataScan.DataSource = ds1
                frmTest.device1DataScan.DataMember = "sheet1"
            Catch e1 As Exception
                MessageBox.Show("Import Failed, incorrect Column names in the sheet!")
            End Try
 
            Try
                frmTest.DVRSTableAdapter.ClearBeforeFill = True
                frmTest.DVRSTableAdapter.Fill(ds1.Tables(0))
                frmTest.DVRSTableAdapter.Update(ds1)
                MsgBox("success!")
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try

This first Try block works fine. The second one fails with the exception "System.InvalidCastException: Unable to cast object of type 'System.Data.DataTable' to type 'DVRSDataTable'.