Updating Database Tables

[lucidity23]

Yet again I ask about updating multiple fields in a table....when I try to do so the code says the UPDATE syntax is wrong...however the syntax is right unless there is special code for updating multiple fields...

Can anyone help me out here plz?

 

[hblackorby]

The syntax should be:

UPDATE tblName SET firstfield=firstvalue, secondfield=secondfalue, thirdfield=thirdvalue where primarykeyfield=desiredprimarkeyvalue

where the primarykeyfield is the name of the unique key field, and the value you give is the specific row you want updated. Remember if you are using strings for values, to surround them with single quotes i.e. firstfield='firstvalue'.

Does that help? Harold Blackorby
hblackorby@scoreinteractive.com
St. Louis, MO

 

[Esoteric]

You can open the table and simply locate the records you wish to update and update them:

<%
Set conn = Server.CreateObject(&quot;ADODB.Connection&quot
conn.open &quot;Driver={SQL Server};Server=YOURSERVER;;User ID=YOURID;Password=YOURPASS;Initial Catalog=YOURCAT&quot;

sql = &quot;SELECT Table.Field1, Table.Field2, Table.Field3 FROM Table&quot;
Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
rs.Open sql, conn, 3, 3
if not rs.EOF then
do while not rs.EOF
rs(&quot;Field1&quot = YourData
rs(&quot;Field2&quot = YourData
rs(&quot;Field3&quot = YourData
rs.Update
rs.MoveNext
loop
end if
rs.close %>

It all depends on what you are trying to do, update a whole table, just certain records that meet a criteria, etc. hope this helps.

 

[lucidity23]

Thanks all...I plan on trying the 2nd idea later on when I am able to work on the site....

I have tried the first method of using UPDATE however I learned that it will not work for too many fields...

Thanks again!!

 

[Antzz]

The method recommended by Harold is better and more efficient. Also the number of fields should not affect the insert. The error message is probably right. There seems to be a silly mistake somewhere(as we all commit). If you are using any date field, remember to check the format according to the database.

 

[adr]

When using a recordset to update multiple records
in the same table, you're better off to use a adBatchLockOptimistic and use .updatebatch at the end
of all updates. Its a faster way of doing
multiple updates.