Update Sql in Vb While in a Read Loop

1. October 3rd, 2013,09:16 AM #1

   

   Thread Starter

   Registered User

   ---

   How to use LOOP and SQL to update database

   How to utilise a loop and an update query to update all the records of a column?

   ---

2. Oct 3rd, 2013,11:52 AM #two

   Re: How to use LOOP and SQL to update database

   Code:

   Do While Not rs.Eof     rs!<ColumnName> = value     rs.MoveNext Loop

   ---

3. October third, 2013,12:22 PM #3

   Re: How to use LOOP and SQL to update database

   Welcome to the forum...

   Originally Posted past TrevorPhillips

   How to employ a loop and an update query to update all the records of a cavalcade?

   Although information technology does appear that gibra has answered your question - that is a really improper way to interact with SQL database.

   SQL is meant to exist used in a Fix-Base fashion - that loop could be does in a unmarried command to SQL

   UPDATE sometable Set up columnname='123' where {some status}

   That is the correct way to interact with SQL.

   ---

4. Oct tertiary, 2013,02:05 PM #4

   Re: How to use LOOP and SQL to update database

   Originally Posted by szlamany

   Welcome to the forum...

   Although it does announced that gibra has answered your question - that is a really improper way to interact with SQL database.

   SQL is meant to exist used in a SET-BASE fashion - that loop could be does in a single control to SQL

   UPDATE sometable SET columnname='123' where {some condition}

   That is the correct manner to collaborate with SQL.

   My response was meant to be provocative, because the request was made in the incorrect mode, as well as incomplete.
   He asked for a loop... then I provided a loop.

   Trevor should give more than details.

   ---

5. Oct tertiary, 2013,07:42 PM #five

   

   Thread Starter

   Registered User

   ---

   Re: How to utilise LOOP and SQL to update database

   Thanks for the help. It seems I but placed the wrong variable in my code that'south why it only updates the final record.

   ---

6. October quaternary, 2013,09:37 AM #vi

   Re: How to use LOOP and SQL to update database

   How to utilize a loop and an update query to update all the records of a column?

   Try the followig Fashion . finally you can update when all the records Done.

   Code:

   Do While Not rs.Eof     rs!<ColumnName> = value     rs.MoveNext Loop                        rs.update                      

   or you lot can use Exercise Until .... Loop likewise

   Lawmaking:

   Exercise until rs.Eof     rs!<ColumnName> = value     rs.MoveNext Loop rs.update

   Concluding edited by firoz.raj; Oct 4th, 2013 at 09:41 AM.

   ---

7. Oct 4th, 2013,09:47 AM #7

   Re: How to use LOOP and SQL to update database

   Originally Posted past szlamany

   Welcome to the forum...

   Although it does appear that gibra has answered your question - that is a really improper way to interact with SQL database.

   SQL is meant to be used in a SET-Base of operations fashion - that loop could be does in a unmarried command to SQL

   UPDATE sometable Fix columnname='123' where {some condition}

   That is the right manner to interact with SQL.

   Maybe... as usual nosotros don't accept all of the facts... what we don't know is if the field even exists in the database... we don't know if the database needs to be updated immediately or not... and by doing a set up-based transactions (which admittedly was my first idea too) information technology means it would have to be re-queried to get the new value back into the customer side space... unfortunately in that location was too much that WASN'T in the mail service to make a truly best exercise answer... that said, gibra probably posted the best respond given what nosotros practice know...

   Originally Posted by firoz.raj

   Endeavour the followig Mode finally you can update when all the records Done.

   Code:

   Do While Non rs.Eof     rs!<ColumnName> = value     rs.MoveNext Loop                          rs.update                        

   Sigh... really? Now I think you're just trying to heave your post count... yous simply copied and pasted code (that'south already in this thread) and posted information technology as if it'southward something new... information technology wasn't ... it was an verbal copy of gibra's code... adjacent time, if you want to do that, at least have the decentcy to QUOTE the original post and add some value to it. Even if it's a annotate about how this is proficient, bad, or a good idea, bad idea, what always... sheesh.

   -tg

   ---

8. October 4th, 2013,06:38 PM #8

   Re: How to utilise LOOP and SQL to update database

   Originally Posted by szlamany

   UPDATE sometable SET columnname='123' where {some condition}
   That is the correct way to interact with SQL.

   When working through a DB-brainchild-layer, then writing ones own
   SQL-DML-statements can be useful (eastward.yard. as you said, when yous want to update
   ColumnValues with a constant Expression beyond multiple records) - but information technology
   is by no means the but "correct way".

   I see a whole lot of postings hither on the forum, which are fiddling (and failing) with exactly that -
   composing (concatenating) right SQL-DML-statements.

   At that place's quite a lot of things i tin practise incorrect in specifying the correctly formatted value-expression
   (quotes or non quotes, quotes within string-values... then correctly formatted appointment-values ... or the
   correctly hex-encoded formats for Blob-values, which can differ from DB to DB).

   With the above in mind, I'd personally like when the inexperienced Users of ADO would be
   encouraged by "The DB-Pros" hither, to work "through the Recordset" a bit more.

   In that location'due south non much which can go wrong with datatypes and stuff - SQL-injection would be
   no problem too - and the usage is quite error-free and simple - it'due south only a matter of opening
   the Recordset with the right parameters beforehand.

   Recordsets are quite similar in many regards to Control-Objects - and they don't even
   require the definition of an SQL-DML-statement beforehand.

   Under the hood of an Rs.Update or Rs.UpdateBatch method, there'south (in the end) likewise a
   construction of ane (or many) SQL-DML-statements in the stop, it'southward just hidden from the
   user.

   Olaf

   ---

9. October fourth, 2013,07:04 PM #9

   Re: How to use LOOP and SQL to update database

   Cheers for your posting - this thread should have been marked airtight already as the OP - in mail service #five - their 2nd post on the forum - said they figured out what the issue was.

   We were all given very petty info to get on - I fabricated a rash statement about "iterating" and "looping"...

   I certainly didn't want to give someone a push into using hardcoded sql statements - I'm a 100% sproc user anyway - oasis't written an inline t-sql statement in 12 years...

   @tg - I've been doing web apps for a couple of years now - and the re-querying you speak of I consider an important function of the "customer" knowing the "server" did the save properly. Fits nicely into my ajax-web-method handshakes...

   ---

allenthadons1992.blogspot.com

Source: https://www.vbforums.com/showthread.php?736959-How-to-use-LOOP-and-SQL-to-update-database

Share this post