Updating Database

[smithbr]

I have a couple of questions about a project I am working on involving an access database and a vb.net windows form.

I have an Access Database that is used to keep track of brokers commissions. On a form, I display the line items from a particular invoice (based on the invoice number typed into the textbox) in a datagrid. Since I'm pretty new to VB Net, I would like to find out if it is possible to add new tasks, change existing tasks, or delete existing tasks using just the datagrid. It looks like I can type in the datagrid, to add a new task. It also looks like I can change existing tasks information, but I don't know how to identify the changes so I can update the database. For adds - I have 4 textboxes and two comboboxes that I want to use to add new records. How do I get the textboxes and comboboxes to add to the datagrid, so it can be inserted into the database?
For updates or deletes, how can I identify which line on the datagrid I have updated or marked to be deleted? After I have updated the data grid, how do I take all the data in the datagrid and send it to a new table in the same access database(not the same table is was populated with)?

Also, I have two radio buttons above the datagrid that is check yes or no depending on if teh customer took the discount. If the yes radio button is checked I want to total amount for each line item to be lowered by a % that is displayed in a label. (I could also have the discount percent be displayed in the datagrid but don't know how to make the datagrid know that if the radio button "YES" is selected to use the amount in in the discount column in the total calculation.

Thanks, Brent

 

[RiverGuy]

For the first part of your question, it gets more complicated than binding data from designer. I know you are creating your dataset in code, and binding it to the grid from your previous posts.

The quick and dirty way would be create a new dataset when the save button is clicked. You would get the changes from your current dataset, like:

Dim dsNew as DataSet = dsOld.GetChanges()

You could then iterate through the rows of the new dataset and either append them or update them in your database.

But this doesn't work if you want people to change what the grid displays and be able to save all changes after they have changed what they want it to display.

For this I would recommend you use a related tables approach. Look up the article in help titled Creating Master-Details Lists with the Windows Forms DataGrid Control. That should give you some ideas.

 

[RiverGuy]

I hit submit too early. With this second approcah, what you're doing is bringing over all of the data from the database and putting both tables in one dataset. You do this from the designer, so it will generate update, insert, ect commands for you. When you want to save the data, you call the update method of the dataadapter.

 

[smithbr]

Riverguy,
I have set up a master-edtail relationship (programmatically) but still do not know how to update the dataset to the database. I think I have figured out how to update the dataset but the database is not affected by the changes.
Brent

 

[RiverGuy]

Call the Update Method of your DataAdapters:

SqlDataAdapter2.Update(DataSet, "TableName")
DataSet.AcceptChanges()

Do it for both tables.

 

[smithbr]

RiverGuy,
After a weekend full of research on VB.Net I think I have found out what my problem is but am not sure exactly how to solve it. Because I have set up my oledbconnection and oledbdataadapter programmatically I do not have the paramaters defined in the code. The problem is, when I set up these at design time, I can not get the datagrid to fill. I think what I need to do is some how define the paramaters of the dataadapter in the code. I was wondering if I could just create a dataadapter at design time, and copy its parameters to the dataadapter i created programmatically.

I am sorry to be asking almost the same qusetion over and over again, I am working at an internship at a small company and there is no support team here for me( I am the only one here that knows even the slightest bit about databases and programming). I am completely on my own and have very little programming experience, if it was not for these forums I would be completely lost.

 

[younguser]

Download 101 samples from microsoft. There is a project "data entry form".

 

[RiverGuy]

Your DataAdapter should have command objects, for Select, Insert, Update, Delete.

Those command objects would have parameters if you defined them.

To bring over your data, you would need to set the values of the parameters:

Command1.Parameters("@ID") = SomeValue
DataAdapter1.Fill(DataSet1)

 

[santosh1]

See if this helps.


Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click


Dim dgrResult As DialogResult

If dsDetailData.HasChanges() = True Then
dgrResult = MessageBox.Show("Are you sure you want to save the changes?", _
"Donor Center Details", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
If dgrResult = DialogResult.Yes Then
Try
Dim dRow As DataRow

Dim myselectQuery As String = " Select * from Employee"
Dim myConn As New OleDbConnection(ConnectionString)
' Dim myDataAdapter As New OleDbDataAdapter()
detailAdapter.SelectCommand = New OleDbCommand(myselectQuery, myConn)
Dim detailCB As OleDbCommandBuilder = New OleDbCommandBuilder(detailAdapter)
myConn.Open()
' Code to modify data in DataSet here
dsChanges = dsDetailData.GetChanges()
' Without the OleDbCommandBuilder this line would fail.
detailAdapter.Update(dsChanges, Details_TABLE_NAME) 'save using the Update method
'Save the changes to the original data source
'Reset the dataset to reflect the new data
dsDetailData.AcceptChanges()
myConn.Close()

Catch err As Exception
MessageBox.Show(err.Message, "Donor Center Details&quot
End Try
End If
Else
MessageBox.Show("There are no changes.", "Donor Center Details", _
MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
End Sub