Not triggering a change to rowstate - Maybe!!!!!

[Hexman]

Hello all,

I'm facing some issues with the update/insert process.

I have a datatable (dt), use oledbcommandbuilder, datarow (dr),
OledbAccess connection (cn), dataAdapter (adapter). here's the logic
I plan on using. (Ah, if it would only work!!!)

------------------------------------------------------------------------------------
Open a table in Access.
read a transaction from flat file until eof

Build a query ("Select * from purord where ponum = 'flat file
ponumber' ")
Build an Insert Query("insert into Access table ......")
adapter.fill(dt)
if dt.Rows.Count = 0 ' test if record found
Dim dr As DataRow = dt.NewRow
'update dr with key fields only
dr("PODate") = CDate(trnDate)
dt.Rows.Add(dr)
end if

Try
adapter.Update(dt)
Catch ex As Exception
'An exception occurred
MsgBox(ex.tostring())
End Try

'----- Update with non-Key fields -------
dt.Rows(0).Item("POTrnAmt") = CDbl(trnAmt)

Try
adapter.Update(dt)
Catch ex As Exception
'An exception occurred
MsgBox(ex.tostring())
End Try

Go read another transaction above.
-----------------------------------------------------------------------------------------------

Now the problem I have is that if no record is found, the non-Key
fields are never updated. It does create a new row in the datatable
containing the key fields only. It doesn't catch any errors on the
adapter.Update(). It performs the "dt.Rows(0).Item("POTrnAmt") =
CDbl(trnAmt)" statements without errors and It doen't catch any errors
on the second adapter.Update() command, but the non-Key fields don't
update.

My guess is that the update to non-key fields is not triggering a
change to rowstate or I'm missing a refresh command to rowstate or
just missing a command or two.

Can anyone help me out?

Hexman

P.S. If I process a transaction for a records that IS on file, the
update of non-Key fields works fine. Its only when a new record is to
be added that the non-Key updates don't take.

Access 2003, VB.net 2005,

 

[Hexman]

ponum is one of the key fields, which does get updated during an add,
not modified during an update.

Am I leaving out a command to refresh or re-query?

 

[Cor Ligthert [MVP]]

Hexman,

I would in your case look again to your logic. I find it strange that you
selects rows depending on ponum. And I see nowhere in your code again ponum
back.

Cor

 

[Cor Ligthert [MVP]]

Hexman,

This assumes that your key is an autokey, is that true?

Otherwise you have to set that before you add the row

Be aware that with access and autokey you have normally forever to clean the
dataset and do a complete fill again after updating that. (In SQLClient is
the generated key automaticly set).

I hope this helps,.

Cor

 

[Hexman]

Hexman,

This assumes that your key is an autokey, is that true?

No, it is not an autokey. I haven't put all the code in this thread,
thinking it may be a little too much to stomach.

Otherwise you have to set that before you add the row

See below where I've added actual code.

Be aware that with access and autokey you have normally forever to clean the
dataset and do a complete fill again after updating that. (In SQLClient is
the generated key automaticly set).

Sorry if I'm being thick with this. Are you saying that after I add a
record to the datatable (which the program already does). That I have
to update the datatable then do another select and datatable fill
before I issue the update statements?

I hope this helps,.

Cor

QryIns = "INSERT INTO Purord (PODate, PONum) " & _
"VALUES (@PODate, @PONum)"
adapter.InsertCommand = New OleDbCommand(QryIns, cn)

adapter.InsertCommand.Parameters.Add("@PODate",
OleDbType.Date, 6, "PODate")
adapter.InsertCommand.Parameters.Add("@PONum",
OleDbType.VarChar, 3, "PONum")

 

[Cor Ligthert [MVP]]

Hexman,

If it is not an autokey, than I am supprised that it does not give an error
when you add the row to the datatable. A datatable with a key will normally
do that.

dim dr as datarow = dt.newrow
the key has to be set before the datarow is attached to the table
dt.rows.add(dr)

Cor