need help saving to database
Hey guys,
I have an "Import" button that successfully reads in an excel spreadsheet to a bound datagridview with this code:
vb 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?
Re: need help saving to database
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.
Re: need help saving to database
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
Re: need help saving to database
Re: need help saving to database
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?
Re: need help saving to database
Quote:
Originally Posted by
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?
I've already answered your question in post #2:
Quote:
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.
Re: need help saving to database
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?
Re: need help saving to database
vb Code:
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'.