UpdateCommand wont update

[Pipo]

Hi,

Im trying to make an update command for Access:
adpNew.SelectCommand = New OleDb.OleDbCommand("SELECT " + strColumns + "
FROM " + Tablename, con)

Dim bld As New OleDb.OleDbCommandBuilder(adpNew)

adpNew.InsertCommand = bld.GetInsertCommand

adpNew.UpdateCommand = bld.GetUpdateCommand

The insert command works fine.

In the table are boolean values and these go wrong!

There are no boolean values that are NULL, they are false or true

When I past the generated SQL command in Access:

UPDATE Data SET Deleted =true , code = '800308714548216008' , ID =
'0005038154' WHERE ( ((1 = 1 AND Deleted IS NULL) OR (Deleted = true)) AND
(code = '800308714548216008') AND (ID = '0005038154') )

Access says you are about to update 0 rows.
When I change the ...AND Deleted IS NULL... into AND Deleted = False it
works fine.
Is there a solution for this behaviour??

tia

 

[W.G. Ryan - MVP]

Pipo: As a general note, i'd double check the Rowstate values and ensure
that I have some rows with a Rowstate of modified. In this case though,
that doesn't appear to be the problem. If you examine the specific values
in question, are they DbNull or are they false? I didn't understand exactly
from your post. In the first query you are saying Deleted = true but the
one you change, you are changing it to Deleted equal false. But double
check the value in the coulmn and see if it's True/False or Null. Also, what
is the type of the column? I know it's probably system.Boolean but just
double check it. And then make sure that the value in each row is either
true or false.

 

[Pipo]

Thanks W.G. Ryan,

The rowstate is modified. I'm trying to update deleted to True.
The Type is system.boolean and the values are True (in the dataset)

When I ask what the updatecommand is I get (after filling in the
parameters):
UPDATE Data SET Deleted =true , code = '800308714548216008' , ID =
'0005038154' WHERE ( ((1 = 1 AND Deleted IS NULL) OR (Deleted = true)) AND
(code = '800308714548216008') AND (ID = '0005038154') )

But if I past this in Access (Query) access says 'you are about to update 0
rows'

thanks you

 

[W.G. Ryan - MVP]

So if the value is false though, it won't ever update. Is that correct? In
this case though, the value is definitely true right?

 

[Pipo]

Correct
Yes definitely true.
In the database they are all false and in the dataset they are true but wont
update
I also dont get errors but no update...

 

[P. Van Den Goess]

Pipo:

Are you using Parameters by chance? I ask because Access/Oledb , each ?
becomes its own parameter so they must need to be sent twice. In thread
below with title: Ado.net concurrency exeptions by Ed Warren, Mark Ashton
has given answer that is of possible relevant to your problem. If you are
not using paramater values though, then there is no use following that line
of thinking to solve this as it would not be part of solution.

 

[Pipo]

thanks for looking at my question.
Still havent found what goes wrong.
Here is the code:

clsConnection:
Public Sub UpdateDatabase(ByVal dts As DataSet, ByVal Tablename As String)

Dim adpNew As New OleDb.OleDbDataAdapter

Dim strColumns As String

For Each col As DataColumn In dts.Tables(0).Columns

strColumns += col.ColumnName + ", "

Next

'remove last ", "

strColumns = strColumns.Remove(strColumns.Length - 2, 2)

adpNew.SelectCommand = New OleDb.OleDbCommand("SELECT " + strColumns + "
FROM " + Tablename, con)

Dim bld As New OleDb.OleDbCommandBuilder(adpNew)

'adpNew.InsertCommand = bld.GetInsertCommand

'adpNew.DeleteCommand = bld.GetDeleteCommand

adpNew.UpdateCommand = bld.GetUpdateCommand



Try

adpNew.Update(dts)

Catch ex As Exception

Debug.Write(ex.Message)

End Try

end sub

In a form:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim con As New clsConnection

Dim dts As DataSet = con.GetDataSet("SELECT * FROM Table1 WHERE 1=0")

Dim row As DataRow = dts.Tables(0).NewRow

row("ID") = 2

row("Description") = "Pipo"

row("num") = 666

dts.Tables(0).Rows.Add(row)

dts.Tables(0).AcceptChanges()

row("ID") = 2

con.UpdateDatabase(dts, "table1")

End Sub



But the same thing happens, no errors but also no updates!!!

What am I doing wrong???



many thanks

 

[Bart Mermuys]

Hi,

thanks for looking at my question.
Still havent found what goes wrong.
Here is the code:

The problem is caused because you're changing the RowState (using
AcceptChanges). Each row with a Modified rowstate has both old and new
values for each field, where at least one new value is different from the
old value.

if dataRow.RowState == RowState.Modified then
dataRow["FieldName", DataRowVersion.Original] Old value
dataRow["FieldName", DataRowVersion.Current] New value

The old values usually come from a Fill operation, meaning that the old
values matches the values in the DB and the new values are the changed
values.

When you do a DataAdapter.Update with a CommandBuilder generated command
then the update command checks if the old values for each row are still the
same in the DB and if they are not then a concurrency violation occurs
because normally that would indicate that the row in the DB has changed
between your Fill and Update. ( Optimistic Concurrency )

Now, you don't use a Fill (well you do, but it doesn't return any rows), so
the old values come from whatever you set the fields to before you call
AcceptChanges. And if these old values don't match with the ones in the DB
updating fails ...

If by any chance all the rows in the db are indentical except for the
primary key then you could assign those values and a pk to the DataRow, then
call AcceptChanges and then make your modifications.

Otherwise you will need to assign your own Command (update query) that
doesn't check the old values to the DataAdapter you use for updating. (The
old values are currently checked in the WHERE clause of the CommandBuilder
generated update query)


HTH,
Greetings

 

[Pipo]

Great!! many many thanks Bart!!
This was very very usefull...learned alot and fixed the problem.
Thank you.

Pipo

Hi,

thanks for looking at my question.
Still havent found what goes wrong.
Here is the code:

The problem is caused because you're changing the RowState (using
AcceptChanges). Each row with a Modified rowstate has both old and new
values for each field, where at least one new value is different from the
old value.

if dataRow.RowState == RowState.Modified then
dataRow["FieldName", DataRowVersion.Original] Old value
dataRow["FieldName", DataRowVersion.Current] New value

The old values usually come from a Fill operation, meaning that the old
values matches the values in the DB and the new values are the changed
values.

When you do a DataAdapter.Update with a CommandBuilder generated command
then the update command checks if the old values for each row are still the
same in the DB and if they are not then a concurrency violation occurs
because normally that would indicate that the row in the DB has changed
between your Fill and Update. ( Optimistic Concurrency )

Now, you don't use a Fill (well you do, but it doesn't return any rows), so
the old values come from whatever you set the fields to before you call
AcceptChanges. And if these old values don't match with the ones in the DB
updating fails ...

If by any chance all the rows in the db are indentical except for the
primary key then you could assign those values and a pk to the DataRow, then
call AcceptChanges and then make your modifications.

Otherwise you will need to assign your own Command (update query) that
doesn't check the old values to the DataAdapter you use for updating. (The
old values are currently checked in the WHERE clause of the CommandBuilder
generated update query)


HTH,
Greetings

clsConnection:
Public Sub UpdateDatabase(ByVal dts As DataSet, ByVal Tablename As String)

Dim adpNew As New OleDb.OleDbDataAdapter

Dim strColumns As String

For Each col As DataColumn In dts.Tables(0).Columns

strColumns += col.ColumnName + ", "

Next

'remove last ", "

strColumns = strColumns.Remove(strColumns.Length - 2, 2)

adpNew.SelectCommand = New OleDb.OleDbCommand("SELECT " + strColumns + "
FROM " + Tablename, con)

Dim bld As New OleDb.OleDbCommandBuilder(adpNew)

'adpNew.InsertCommand = bld.GetInsertCommand

'adpNew.DeleteCommand = bld.GetDeleteCommand

adpNew.UpdateCommand = bld.GetUpdateCommand



Try

adpNew.Update(dts)

Catch ex As Exception

Debug.Write(ex.Message)

End Try

end sub

In a form:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim con As New clsConnection

Dim dts As DataSet = con.GetDataSet("SELECT * FROM Table1 WHERE 1=0")

Dim row As DataRow = dts.Tables(0).NewRow

row("ID") = 2

row("Description") = "Pipo"

row("num") = 666

dts.Tables(0).Rows.Add(row)

dts.Tables(0).AcceptChanges()

row("ID") = 2

con.UpdateDatabase(dts, "table1")

End Sub



But the same thing happens, no errors but also no updates!!!

What am I doing wrong???



many thanks




true
but ID
=