How to change values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to change values

Author

Topic

knutso
Starting Member

20 Posts

Posted - 2006-05-03 : 18:45:21

Suppose a very basic question, but how can I easily update values in a record. I can easily READ the value with this code.

Set Rsx = Server.CreateObject("ADODB.RecordSet")
sSQL= "SELECT * from prodfeatures"
Rsx.Open sSQL, sDSN, adOpenStatic, adLockReadOnly, adCmdText

While Not Rsx.eof
i=i+1
mte(i)=Rsx("id")
mfnum(i)=Rsx("featureother1")
Rsx.movenext
Wend

..now I try an UPDATE sql to insert the values from the array into another field in the corresponding records - I can get it to work, but it is very stupid done. Actually I must open and close the database for every record to get it to work:

For j=1 To i

Set Rsx = Server.CreateObject("ADODB.RecordSet")
sSQL= "UPDATE prodfeatures SET featurenum=" & mfnum(j) & " WHERE id=" & mte(j)
Rsx.Open sSQL, sDSN, adOpenStatic, adLockReadOnly, adCmdText
next

...I suppose this is silly programming, but it actually works well, but it takes a really lot of time to execute...

...someone who has a faster way of doing this???

Norwich
Posting Yak Master

158 Posts

Posted - 2006-05-04 : 04:31:18

Hi

You are probably not going to get an answer here about Visual Basic stuff.
This forum concentrates on SQL related issues.

My advice is to post it at a VB forum

Regards
N

The revolution won't be televised!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-04 : 04:45:35

knutso, if i am not mistaken you are basically setting the value featurenum to value of featureother1 ?

Why not just


udpate prodfeatures
set    featurenum = featherother1
where  featurenum <> featherother1

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-04 : 04:52:59

change adlockreadonly to an option you can update (sorry, been a long time since I did VB)
then from your rsx, you can use .update for each of your records

HTH

--------------------
keeping it simple...

knutso
Starting Member

20 Posts

Posted - 2006-05-04 : 06:02:37

Yes! Thanks for amazingly good help! This did all the job (and it works several 1000 times as fast as the previous solution):

Set Rsx = Server.CreateObject("ADODB.RecordSet")
sSQL="update prodfeatures set featurenum = featureother1 where featurenum <> featureother1"
Rsx.Open sSQL, sDSN, adOpenStatic, adLockReadOnly, adCmdText

I do not know why it does the update with adLockReadOnly, but it works OK i think - will do som more testing..

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-05-04 : 09:56:00

the adlockreadonly only comes into effect when you have a recordset seturned to your app. in this case you are updating direct on the server and no recordset is being returned to you....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources