updating a table with the row that is not already present in a tab
- From: Kum <Kum@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 13 May 2005 16:46:03 -0700
I'm new to Visual Basic .Net and I'm using it to develope a scheduling
application.I have a table say "Stage Two" which has fields WO#, Cus , Date,
AG#(This is the primary key for this table). I have another table "USER
ENTRY" WHICH HAS FIELDS ID, WO#, cUS, dATE, AG#(This is the forein key here
(indexed and no duplicate). I made a OleDbdataadapter2 to query the "STAGE
TWO" table and created a dataset called "Sun". OleDbdataadapter3 to query the
"USER ENTRY" I have a update button on the form And once I press the update
key the Data in the Dataset(from table STAGE TWO) is sent to "User Entry"
table. The update works fine but I'm unable to update the table with the new
row for a date the data already exist.Assuming for a particular day say 9th
of May they check and have say 4 rows on the dataset(From table "STAGE TWO"
) displayed on the Datagrid they update it so that the data is transferred to
the "USER ENTRY TABLE".Now the "USER ENTRY" table will have all 4 rows from
the "STAGE TWO" table. Now say two hours later for the same date, "STAGE TWO"
received another order" so the users check the application they have 5 rows
on the datagrid(from "STAGE TWO"). I just want the new row to be tranfered,
but guess I would get an error can't send duplicate because the other 4 rows
are already in the table, how can I just send the new row that is not already
present in the table without getting this error and without duplicating or
modifying the row that already exit in the 'USER ENTRY" Table. I had to make
OleDbDataAdapter1.AcceptChangesDuringFill = False on loading the form else it
was not updating at all.
Please find my code below.
Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
OleDbDataAdapter2.AcceptChangesDuringFill = False
End Sub
'THIS IS TO HANDLE THE UPDATE BUTTON CLICK EVENT
Private Sub IMPBTN_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles IMPBTN.Click
Try
OleDbDataAdapter2.SelectCommand.Parameters("Final_Date").Value =
SMTC.Value ' input to the query
Sun1.Clear()
OleDbDataAdapter2.Fill(Sun1, "STAGE TWO")
Dim Shchange = Sun1.GetChanges
Dim nno As DBNull
If (Shchange Is nno) Then
MsgBox("No change")
Else
MessageBox.Show("Dataset has changes:" &
Sun1.HasChanges.ToString)
OleDbDataAdapter3.Update(Shchange, "STAGE TWO")
MessageBox.Show("Order has been setup for Scheduling. Use View
Schedule to View Orders And Modify Schedule to Modify")
End If
Catch ex As Exception
MsgBox(" You have already Imported all the Orders that are to be
shipped for this date, Please Use View Schedule to View the order and Modify
Schedule to Modify the Order for this Date or select a different Ship Date")
'MsgBox(ex.Message + vbCrLf + ex.Source & vbCrLf & vbCrLf &
ex.StackTrace, MsgBoxStyle.Critical, "Error")
End Try
End Sub
I will really appreciate if anybody can let me know how I can do this.
THANKS A MILLION.
.
- Follow-Ups:
- Prev by Date: Re: Visual Basic.net, ADO.Net
- Next by Date: Re: updating a table with the row that is not already present in a tab
- Previous by thread: 3707 Cannot change the ActiveConnection property of a Recordset ..
- Next by thread: Re: updating a table with the row that is not already present in a tab
- Index(es):