Update Command Question

[Vayse]

I want to set up a form where a user can enter the Purchase Price for some
Assets. No other data will be entered, just the PurchacePrice. But I also
wish to display several other fields, like the AssetCode, So I load the form
as follows:

Dim stSQL As String = "SELECT AssetCode, AssetDesc, PurchasePrice FROM
Assets"
Dim connAsset As New OleDbConnection(conCONNECT)
Dim adapAssetReg As New OleDbDataAdapter(stSQL, connAsset)
Dim dtAsset As New DataTable
adapAssetReg.Fill(dtAsset)

Now when the user is finished I only wish to update the PurchasePrice.
Now if I use the standard command builder, I get a update command that
updates all fields.

Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(adapAssetReg)
adapAssetReg.Update(dtAsset)

(Ideally, I will first create a datatable which just has the updated rows)
Is it possible to write an updatecommand that only updates the
PurchasePrice? It seems a bit of overkill to update all fields.

Thanks
Vayse

 

[William \(Bill\) Vaughn]

See my articles on the CommandBuilder... it's really not the best choice for
a number of reasons--and this is one of them.
www.betav.com


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Marina]

If you trace the sql statements being sent to the database server, you will
see that only those fields that have been updated will be contained in the
SET clause.

Alternatively, before you create the commandbuilder, change the
SelectCommand's sql query to be "SELECT AssetCode,PurchasePrice FROM
Assets". This will trick the command builder into only paying attention to
the purchase price even if other fields were modified (asset code is there
since presumably it is the primary key).

 

[AMDRIT]

The commandbuilder just uses row level updates, where you want column level
updates. There are a couple of things you can do here. The most direct for
your needs would most likely be to:

1. Manaully create update statements
2. loop of dtAsset.GetChanges(Modified)
3. cmdAsset.ExecuteNonQuery

sub Update(assettable as datatable)
dim sSQL as string = "Update Assets set PurchasePrice = {0} were
assetcode = '{1}'"
for each dr as datarow in assettable.getchanges(modified)
dim cmd as oledbcommandtext
cmd = new oledbcommandtext(connAsset)
cmd.commandtext =
string.format(sSQL,dr("PurchasePrice"),dr("AssetCode").replace("'","''"))
cmd.executenonquery()
cmd.dispose()
next dr
end sub

another way to do it is

with adapAssetReg.updatecommand

.commandtext = "Update Assets set PurchasePrice = ? were assetcode = ?"
.paramaters.add(New System.Data.OleDb.OleDbParameter("PurchasePrice",
System.Data.OleDb.OleDbType.decimal, 8, "PurchasePrice"))
.paramaters.add(New System.Data.OleDb.OleDbParameter("assetcode",
System.Data.OleDb.OleDbType.char, 4, "assetcode"))
end with

adapAssetReg.Update(dtAsset)

 

[Vayse]

I kow CommandBuilder is not the best choice, I should have made that
clearer.
But in any examples I've seen, the query written by the programmer always
updates all fields
So I just want to know if the query I write can just update one field.
Thanks!
Vayse

 

[Vayse]

Thanks, I'll try this out and let you know if it worked.

 

[Vayse]

Reply inline.

If you trace the sql statements being sent to the database server, you
will see that only those fields that have been updated will be contained
in the SET clause.

Thanks, didn't know that.

Alternatively, before you create the commandbuilder, change the
SelectCommand's sql query to be "SELECT AssetCode,PurchasePrice FROM
Assets". This will trick the command builder into only paying attention
to the purchase price even if other fields were modified (asset code is
there since presumably it is the primary key).

Hmm, kind of sneaky way. I like it.
Thanks
Vayse

 

[Vayse]

I'm getting a Concurrency violation - any ideas?
Heres what I got, based on your code below:


' For sample, just changing one row
dtAsset.Rows(0).Item("PurchasePrice") = 20

stSQL = "Update Assets set PurchasePrice = ? WHERE AssetCode =
?"
Dim Upcommand As New OleDbCommand(stSQL, connAsset)
With Upcommand
.Parameters.Add(New
System.Data.OleDb.OleDbParameter("PurchasePrice", _
System.Data.OleDb.OleDbType.Decimal, 8,
"PurchasePrice"))
.Parameters.Add(New
System.Data.OleDb.OleDbParameter("AssetCode", _
System.Data.OleDb.OleDbType.Char, 4,
"AssetCode"))
End With
adapAssetReg.UpdateCommand = Upcommand
adapAssetReg.Update(dtAsset)

connAsset.Close()

 

[AMDRIT]

Vayse,

I think there could be a couple of things going on here, without seeing the
exception description....

First lets talk about what we are doing here; we are creating our
paramaters on the fly so they should reflect the data.

Take

.Add( _
New System.Data.OleDb.OleDbParameter( _
"AssetCode", _
System.Data.OleDb.OleDbType.Char, _
4, _
"AssetCode") _
)

This says, there will be an inbound column form the datarow named
"AssetCode" of type "Char" with a length of 4 and it should map back to a
column named "AssetCode" in the database. If this is not correct, please
update your code accordingly.

Second, it is suggested as best practice, by minds greater than mine to
recreate our dataadapters and open connections to the database when we are
ready to use them. So let's isolate the logic for updating the datatable
and create a fresh dataadapter, command, and connection.

Third, if you have the ability to trace your SQL execution, it would be nice
to see what VB thought you intended to do.

Finally, I attached a mockup update method see if this helps you get to
where you want to be.


Public Function UpdateAssets(ByVal dtAssets As DataTable) As Integer

Dim adapAssetReg As OleDb.OleDbDataAdapter
Dim iRet As Integer
adapAssetReg = New OleDb.OleDbDataAdapter

iRet = -2 'Error

Try
adapAssetReg.UpdateCommand = New OleDb.OleDbCommand("Update Assets set
PurchasePrice = ? WHERE AssetCode = ?")
adapAssetReg.UpdateCommand.Connection = New
OleDb.OleDbConnection(GetConnectionstring())

With adapAssetReg.UpdateCommand.Parameters

'///Overloads Public Function Add( _
'/// String, OleDbType, Integer, String _
'///) As OleDbParameter

'///String --> Local Name
'///OleDbType --> Datatype or castable type
'///Int --> DataLen
'///String --> Foriegn Name


.Add( _
New System.Data.OleDb.OleDbParameter( _
"PurchasePrice", _
System.Data.OleDb.OleDbType.Decimal, _
8, _
"PurchasePrice") _
)

.Add( _
New System.Data.OleDb.OleDbParameter( _
"AssetCode", _
System.Data.OleDb.OleDbType.Char, _
4, _
"AssetCode") _
)
End With

Try

'Only update changes, not appends or deletes
Dim dtLocal As DataTable =
dtAssets.GetChanges(DataRowState.Modified)

'///Test to see if there is data to update
If (Not dtLocal Is Nothing) AndAlso (dtLocal.Rows.Count > 0) Then
iRet = adapAssetReg.Update(dtLocal)
Else
iRet = 0 'No records to update
End If

Catch ex As Data.ConstraintException
Trace.WriteLine(String.Format("Inner try:
Data.ConstraintException:{0}", ex.tostring))
Catch ex As Data.DBConcurrencyException
'///The exception that is thrown by the DataAdapter during the
update operation if the number of rows affected equals zero.
'///The DataAdapter examines the number of rows affected by the
execution of each insert, update, or delete operation, and throws this
exception if the number equals zero. This is usually the result of a
concurrency violation.
Trace.WriteLine(String.Format("Inner try:
Data.DBConcurrencyException:{0}", ex.tostring))
Catch ex As OleDb.OleDbException
Trace.WriteLine(String.Format("Inner try: OleDb.OleDbException:{0}",
ex.tostring))
Catch ex As System.Exception
Trace.WriteLine(String.Format("Inner try: System.Exception:{0}",
ex.tostring))
End Try

Catch ex As Exception
Trace.WriteLine(String.Format("Outer try: System.Exception:{0}",
ex.tostring))
End Try

Return iRet


End Function

 

[Vayse]

Thanks, works perfectly now. In fact it was because I had not changed the
AssetCode length to 6.
I appreciate the detailed explanation,
Vayse

 

[Tiago Teixeira]

Hi,

I think something like this solves your issue, but the UPDATE statement
I mention is not automatically generated by CommandBuilder() object:

Dim stSQLUpdate As String = "UPDATE Assets SET PurchasePrice = " &
paramPurchasePrice & " WHERE AssetCode = " & paramAssertCodeToUpdate

Dim connAsset As New OleDbConnection(conCONNECT)
Dim cmdUpdateAssets As New OleDbCommand(stSQLUpdate,connAsset)
connAsset.Open()
cmdUpdateAssets.ExecuteNonQuery()
connAsset.Close()

Maybe a try catch block could be usefull

Regards,
Tiago Teixeira

-----Original Message-----
From: Vayse [mailto:[email protected]]
Posted At: segunda-feira, 16 de Janeiro de 2006 18:28
Posted To: microsoft.public.dotnet.framework.adonet
Conversation: Update Command Question
Subject: Update Command Question

I want to set up a form where a user can enter the Purchase Price for
some
Assets. No other data will be entered, just the PurchacePrice. But I
also
wish to display several other fields, like the AssetCode, So I load the
form
as follows:

Dim stSQL As String = "SELECT AssetCode, AssetDesc, PurchasePrice FROM
Assets"
Dim connAsset As New OleDbConnection(conCONNECT)
Dim adapAssetReg As New OleDbDataAdapter(stSQL, connAsset)
Dim dtAsset As New DataTable
adapAssetReg.Fill(dtAsset)

Now when the user is finished I only wish to update the PurchasePrice.
Now if I use the standard command builder, I get a update command that
updates all fields.

Dim builder As OleDbCommandBuilder = New
OleDbCommandBuilder(adapAssetReg)
adapAssetReg.Update(dtAsset)

(Ideally, I will first create a datatable which just has the updated
rows)
Is it possible to write an updatecommand that only updates the
PurchasePrice? It seems a bit of overkill to update all fields.

Thanks
Vayse