Problem with Dataset Batch update....DOOOHHH!!!

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

Hi-

Here's the scenario. I'm retrieving data from two different tables. Once I
get this data I am looping through the data and I have to update a couple
columns in the same table. Doesn't anyone know of where I can find a good
example of this. I've been looking every where for this.

Example:

Stored Proc:

SELECT IL.listing#, IL.imageID, o.brokerNum, IL.imageStatus, o.companyid,
L.ListingID AS 'L_ListingID', IL.listingID AS 'IL_ListingID'
FROM ImageListings IL
JOIN listings L ON IL.listing# = L.listing#
AND (IL.SourceMLS = L.SourceMLS
OR IL.SourceMLS = '')
JOIN Offices O ON o.officeid = l.officeid
WHERE IL.imageStatus in(1,6)

I need to loop through the dataset and update the image status of
particuliar rows with a company id of a certain number within the
ImageListings table.

Can someone explain a simple way to do this? I would appreciate it greatly.

Thanks,
Rick
 
Hello Rick


Your scenerio below does not give a complete picture.

1. What columns need updating?
2. Where do the Update Values come from?
3. What is the criteria for deciding to update?

Depending on your answers here, you might be able to do the updating in a
single Stored Procedure call, or at worst, make the changes through a
Dataset update.

Ibrahim
 
There are many ways to accomplish this - I would suggest
the following:

1. Create a reference to all the rows you want to update
in your dataset. I think you said you wanted to update
rows based upon the value of "company". So to find those
rows, you would do the following (VB.Net)

dim myRows() as datarows
dim searchString as string = "Company = '10'"

You can add more criteria into the search string such as
"Company = '10' and ImageType = 'aaa'
"Company" and "ImageType" are column names from your
dataset.

To Invoke the search you would..

myRows = myDataset.Tables(TableName).select(SearchString)

You will need to test if any rows were returned...
if isnothing(myRows) then
do something if nothing was found
else
dim dr as datarow
for each dr in myDataset.tables(TableName).rows
' Place your logic here to update all of the
' Rows found
dr.item("SomeColumnName") = "???????"
next

This is all from memory, I did not sit down and make sure
it all works, but this should get you well on your way to
being able to make the changes you want to your dataset.

Remember, this does not actually update the base tables
and you may need to execute the "myDataset.tables
(tableName).acceptChanges in order to make them visible to
controls that you are using.

I hope this helps!
 
Hi Ibrahim

Thanks for the reply

I need to cycle through the data/dataset and first look at the companyid
returned and if it's a
particuliar number, let's say a 6, then I have to modify the imagestatus
column to a different number,.
There are going to be several rows that are going to have to be updated.

I could just do an update through a stored procedure in the loop, but I'd
rather do one batch update
at the end to the affected rows. I think this might be a little better
performance wise.

Your thoughts?
 
Hi Jim-

Thanks for replying to my question

I need to cycle through the data/dataset and first look at the companyid
returned and if it's a particuliar number, let's say a 6, then I have to
modify the imagestatus column to a 3 from a 1, There are going to be several
rows that are going to have to be updated.

I could just do an update through a stored procedure in the loop, but I'd

rather do one batch update at the end to the affected rows. I think this
might be a little better

For performance.

Does this clarify things?

What do you think?
 
I here what you are saying, but I am still not you found in my
suggestion to "not meet your needs".

Your comment about "one batch update" - The dataset has a method for
performing the updates to the table when the changes have been made to
the dataset. I believe the format of that command is
"Dataset.update()". If you have configured you DataAdapter correctly
(with all the appropriate SelectCommand, InsertCommand, UpdateCommand,
etc.) and there is no "complex" logic needed for your updates, then this
method will do the job.

My suggestion assumed that you would use this method and it also assumed
that you dataset might be large and as such, creating the filter and
performing the "select" might be more efficint especially if the dataset
was large.

If you want to know how to cycle through a dataset and interrogate each
row to see if it needs to be updated use the following logic...

dim row as datarow
for each row in dataset.table(MyTable).rows
if row.item("Comany") = 6 then
row.item("ImageStatus") = 3
end if

next

Assuming that you have made all the necessary changes then
dataset.update() which will apply the changes back to the sorce table.

Does this helP? Is this what you are looking for?
 
Hey Jim,

Thanks again for taking the time to answer my question. I got everything
working. I used some of your approach.
I just haven't really worked with updating datasets before and I was
confused with the sql adapter aspect of it.

Regards,
Rick
 
Back
Top