Re: UpdateCommand wont update

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi,

"Pipo" <NoSpam@xxxxxx> wrote in message
news:%235zDWa70FHA.448@xxxxxxxxxxxxxxxxxxxxxxx
> 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
>
>
>
>
> "P. Van Den Goess" <PeterVandengoess@xxxxxxxxxxxxxxxxxxxxxxxx> wrote in
> message news:ecSmkM50FHA.1252@xxxxxxxxxxxxxxxxxxxxxxx
>> 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" <Pipo@xxxxxxxx> wrote in message
>> news:eCiOc7z0FHA.2212@xxxxxxxxxxxxxxxxxxxxxxx
>> > 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...
>> >
>> >
>> > "W.G. Ryan - MVP" <WilliamRyan@xxxxxxxxxxxxxxxx> schreef in bericht
>> > news:%23aAEmIz0FHA.1264@xxxxxxxxxxxxxxxxxxxxxxx
>> > > 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" <NoSpam@xxxxxx> wrote in message
>> > > news:%23B9wuRy0FHA.2348@xxxxxxxxxxxxxxxxxxxxxxx
>> > >> 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" <WilliamRyan@xxxxxxxxxxxxxxxx> wrote in message
>> > >> news:%23KDEmLy0FHA.3720@xxxxxxxxxxxxxxxxxxxxxxx
>> > >>> 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" <NoSpam@xxxxxx> wrote in message
>> > >>> news:u3lRE4x0FHA.612@xxxxxxxxxxxxxxxxxxxxxxx
>> > >>> > 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
>> > >>> >
>> > >>> >
>> > >>>
>> > >>>
>> > >>
>> > >>
>> > >
>> > >
>> >
>> >
>>
>>
>
>


.

Relevant Pages

  • Tool to get MCP ODT at Windows CMD prompt
    ... You basically send a series of ODT commands of your choosing, once, or in a loop, and get the output right in the command ... Dim sHostname ... Sub WriteIt ... WriteIt "" ...
    (comp.sys.unisys)
  • Re: Running Excel
    ... I've gotten away from the SHELL command and am stuck with what appears to be ... Private Sub Command32_Click ... Dim xlWorkbook As Excel.Workbook ... > When you use Shell, you lose all subsequent control of the shelled program. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Read value from text file line, process, then Loop until EOF
    ... Public Sub UserForm_Activate ... Dim inputcmd As String ... 'cmd_execute is a command button on a form that is clicked to excute the ... Dim FileNum As Long ...
    (microsoft.public.excel.programming)
  • retrive preselected value in second drop down list from the first drop down list
    ... Dim objConnection As SqlConnection ... Sub Page_Load ... ' Set up our connection. ... ' Create new command object passing it our SQL query and ...
    (microsoft.public.dotnet.framework.aspnet)
  • Password
    ... It bombs in the COMMAND BUTTON code at: ... Private Sub cmdLetMeIn_Click ... Sub cpw(empid As Long, pw As String) ... Dim cmd1 As Command ...
    (microsoft.public.access.formscoding)