Paging, update, concurrency violation..

[Guest]

Dear reader

because of a large number of records in a database table I'm using paging. 1 record is being fetch everytime I requery the database (just the record the user requires). When I skip to the next record I check if the row has changes. If it does I generate this updatecommand

cmd = New OleDbCommand("UPDATE Customers SET Customers.[Name] = ?, Customers.[Lastname] = ? " &
"WHERE Customers.[CustID] = ?", mdbConnection
cmd.Parameters.Clear(
cmd.Parameters.Add("CustID", OleDbType.Char).Value = CType(dsCustomers.Tables(0).Rows(0).Item("CustID"),Integer)
cmd.Parameters.Add("Name", OleDbType.Char).Value = Me.txtTName.Tex
cmd.Parameters.Add("Lastname", OleDbType.Char).Value = Me.txtTLastname.Tex
daCustomer.UpdateCommand = cm

The problem is now that when I update, I get a concurrency violation (???). The strange thing is I'm the only one using this database and I don't get the concurrency violation when I'm using this parameter

cmd.Parameters.Add("CustID", OleDbType.Char).Value = 2

What could this be

Thx!

 

[Cor]

Hi Maurice,

Why do you not put a line between it to debug (I do not know if you use
VS.net)

cmd.Parameters.Clear()

dim mytest as integer =
CType(dsCustomers.Tables(0).Rows(0).Item("CustID"),Integer))

(and if you have not vs.net)
messagebox.show(mytest.tostring)

cmd.Parameters.Add("CustID", OleDbType.Char).Value = >

cmd.Parameters.Add("Name", OleDbType.Char).Value = Me.txtTName.Text

I think than you should know it.

I hope this helps?

Cor

 

[Guest]

Hi Cor

The messagebox shows 0
Why do I get a concurrency violation? I don't get it
I constructed the dataset and dataadapter in run-time.

 

[Cor]

Hi Maurice,

Show some code from the Select part.
It means that that ID is empty, so there it cannot find the row to update.
(Or you should have a record with an id 0, which you have already readed
(fill) again)

Cor

 

[Guest]

Hi Cor,

the select statement has 4 different selects, depends on what direction the user wants to go to (paging up or down or first or last record):

This is one statement:
selCmd.CommandText = "SELECT TOP " & pageSize & " Customers.[CustID],* FROM Customers " & _
"WHERE Customers.[CustID] > ? ORDER BY Customers.[CustID]"
selCmd.Parameters.Add("CustID", OleDbType.Integer).Value = CType(lastVisibleCustomer, Integer)

I checked the code but the key isn't 0. It has a value. When I enter this value in the code it does update without a concurrency violation. When I refer to the key in the dataset (which has the same value) I do get the concurrency violation.

thx..

 

[Cor]

Hi Maurice,

Can you check your columnnames
To check that
messagebox.show(dsCustomers.tables(0).datacolumn(0).name)
messagebox.show(dsCustomers.tables(0).datacolumn(1).name)
etc

Cor

 

[Guest]

Cor this is the situation

(1
cmd = New OleDbCommand("UPDATE Customers SET Customers.[Name] = ?, customers.Lastname] " &
"WHERE Customers.[CustID] = " & intCurrentKey & "", mdbConnection
cmd.Parameters.Clear(
.....

(2
cmd = New OleDbCommand("UPDATE Customers SET Customers.[Name] = ?, customers.Lastname] " &
"WHERE Customers.[CustID] = ?", mdbConnection
cmd.Parameters.Clear(
cmd.Parameters.Add("CustID", OleDbType.Integer).Value = intCurrentKe
....

Situation 1 works fine, Situation 2 gives a concurrency violation........

 

[Cor]

Hi Maurice,

I never used this with OleDb, however the documentation says that it has to
be the same as with the SQL parameter. (Although strange is that that 20 did
work)

cmd.Parameters.Clear()
cmd.Parameters.Add("@CustID", OleDbType.Integer).Value = intCurrentKey
....

Can you try that?

Cor

 

[Guest]

Hi Cor

when I use the @ I get a lot of errors.
e.g. This doesn't work for typ DBNull

Strange problem, but I will use the first solution which does work. Thank you for the support!!

 

[Ron Allen]

#2) You need a second parameter fir the name added BEFORE the CustId
parameter. OleDb parameters must be added in order.

Ron Allen

Cor this is the situation:

(1)
cmd = New OleDbCommand("UPDATE Customers SET Customers.[Name] = ?, customers.Lastname] " & _
"WHERE Customers.[CustID] = " &

intCurrentKey & "", mdbConnection)

cmd.Parameters.Clear()
.....

(2)
cmd = New OleDbCommand("UPDATE Customers SET Customers.[Name] = ?, customers.Lastname] " & _
"WHERE Customers.[CustID] = ?", mdbConnection)
cmd.Parameters.Clear()
cmd.Parameters.Add("CustID", OleDbType.Integer).Value = intCurrentKey
....

Situation 1 works fine, Situation 2 gives a concurrency violation.........