Update problem ADO vbnet 2005

  • Thread starter Thread starter Scotty
  • Start date Start date
S

Scotty

I like to have a good insert, update and delete code
The code below sometimes workl ok sometimes doesnt work, what i am doing
wrong??


Sub SaveAny()


Dim command_builder As New OleDb.OleDbCommandBuilder(daOrders)
' Save any changes.
daOrders.Update(dsOrders, "tblOrders")


End Sub


Thanks for helping

Marc.
 
Can you tell why it "Sometimes doesnt work?, in other words does it gives no
others or what does not work.

Cor
 
Hi Cor,

Thanks for answering,
I don't have any adea why it does not work sometimes

The connection is coded in ADO (I did not use the grafical way)
Do you have a good sample for me?

Many thanks in advance,
Marc.
 
Marc,

A sample would not help you there are millions of sample on Internet, can
you show the code where you do the update.

Cor
 
Hi Cor,

Thanks for willing help me,
This evening (Belgium time)I will send you more info about my code

Best regards

Marc,
Action-Data
 
just for fun, let me take a wild guess and suggest that this is perhaps
due to optimistic record locking.

scotty, before executing the .Update command, determine what the SQL
text of the command is. See if the 'WHERE' clause is really long and
includes every one of the fields in your table. In such cases, the
Where clause is so specific that it fails to find the record to update,
thus triggering an error much of the time. If this is the case, perhaps
you can simplify that command by including only the primary key in the
Where clause. This might introduce some concurrency issues (i.e., last
one in will always win), but it may also resolve your error.

If this isn't the situation and my shot in the dark missed the mark,
please post more details on the error and your code.

adm
 
Adm,

But than he should in my opinion get an error instead of non updating.

Cor
 
Agreed, Cor...but to paraphrase Bill Clinton, "It depends on what the
meaning of 'doesn't work' is." I for one am interested to find out. :-)
 
Hi,



Thanks for answering,

Below the code I am using



Remark:

I tested on 2 database backends

one with a lot of data: here it does not work!!!

one with a few data in it: there it works fine!!



Thanks in advance

Marc.



++++++++++++++++++++++++++++++++++++++++++++++++++++





Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.Data.OleDb.OleDbCommandBuilder




Public Class Form1
Inherits System.Windows.Forms.Form



Dim strPath As String = Application.StartupPath & "\BeMyData.mdb"
Dim strconMyData As String = "provider=microsoft.jet.oledb.4.0; Data
Source=" & strPath



'Declaratie van het object Dataset
Private ObjectDataset As New DataSet 'Opgelet aan NEW
Dim dsMyData As New DataSet




Dim daMyData As OleDb.OleDbDataAdapter
Dim myDataTable As DataTable = New DataTable()
Dim cnMyData As New OleDb.OleDbConnection(strconMyData)




Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
daMyData = New OleDb.OleDbDataAdapter("select * from tblAdressen
order by naam", cnMyData)
dsMyData = New DataSet
Try
daMyData.Fill(dsMyData, "tblAdressen")



dgAdressen.DataSource = dsMyData.Tables("tblAdressen")
'Binding maken voor de tekstboxen
txtNaam.DataBindings.Add("text", dsMyData.Tables("TblAdressen"),
"Naam")
txtStraat.DataBindings.Add("text",
dsMyData.Tables("TblAdressen"), "Straat")
txtPostcode.DataBindings.Add("text",
dsMyData.Tables("TblAdressen"), "Postcode")
txtPlaats.DataBindings.Add("text",
dsMyData.Tables("TblAdressen"), "Plaats")




Catch ex As Exception
MsgBox(ex.ToString)
End Try




'Instellen naar rij 1 eerste record te gaan
Dim rij As DataRow
rij = dsMyData.Tables("tbladressen").Rows(0)



End Sub




Private Sub btnCloseForm_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnCloseForm.Click
SaveAny()
Me.Close()
End Sub




Sub SaveAny()



Dim command_builder As New OleDb.OleDbCommandBuilder(daMyData)

If dsMyData.HasChanges() = True Then
dsMyData.AcceptChanges()
daMyData.Update(dsMyData.Tables("tblAdressen")) ', myTableName)
End If



End Sub



End Class
 
Looks like the problem is probably:

If dsMyData.HasChanges() = True Then
****dsMyData.AcceptChanges() ***


Try removing the method call to .AcceptChanges.

..AcceptChanges sets the row state of all rows in the table back to
unmodified. Therefore, when your Update command comes along, it finds
no rows to update.

This is a VERY common error in the usage of .AcceptChanges.

Let us know how it goes.

hth,

adm
 
Hi,

so I changed the code as below, but the problem is the same...
Sub SaveAny()

' Save any changes.
Dim command_builder As New OleDb.OleDbCommandBuilder(daMyData)
daMyData.Update(dsMyData.Tables("tblAdressen")) ', myTableName)
MsgBox("update uitgevoerd SavaAny 1")
End Sub

Marc.
 
Scotty

Do that remove of the acceptchanges as adm already wrote
(It set all update rowstates to notchanges and something more)

and put before that if haschanges

BindingContext(dsMyData.Tables("TblAdressen")).EndCurrentEdit()

That is to push the text in the last edited textbox into the datatable, what
will probably be your next problem.

I hope this helps,

Cor
 
Hmm. If Cor's final tip does not work:

- Are you getting any errors, or is it just that modified records are
not appearing in the database?

- Have you set breakpoints to watch the program flow? Are you certain
that the update command is being executed.

- Again, you may wish to analyze the SQL text of the Update command to
see if you can make any sense of it. Perhaps it will tell you where the
problem lies.

Continue posting!

adm
 
Hi,

I tried also this code, but no difference no update is done

Sub SaveAny()

' Save any changes.
Dim command_builder As New OleDb.OleDbCommandBuilder(daMyData)
BindingContext(dsMyData.Tables("TblAdressen")).EndCurrentEdit()
If dsMyData.HasChanges() = True Then
' dsActionData.AcceptChanges()
daMyData.Update(dsMyData.Tables("tblAdressen")) ', myTableName)
MsgBox("update uitgevoerd SavaAny 2")

End If

End Sub
 
Scotty,

You say it is working on one computer, but "It *should* not work in the way
as you have showed it to us in your first sample on any computer".

This is very strange

Cor
 
Hi,

I have rewritten my code from beginning and now the updating is working
fine,
I will write the other code and controlling one by one if the problem would
happen again.

Many thanks again for all who has answered on the newsgroup.
Best regards
Marc.
 
Back
Top