View Full Version : What is the syntax for assigning a field's value in a recordset

02-17-2003, 05:02 PM
I need to know the syntax for updating a recordSet. After populating the recordSet from the database using a SQL statement, I need to be able to change the results. I've seen code simular to - rs.update field,value - but I can't get it to work.

I want to be able to assign a value to a field in a recordSet.

What am I missing?


02-17-2003, 05:15 PM
Don't you just do:




Or am I, too, missing something? :)

02-17-2003, 05:25 PM
Thanks, that makes sense.

I think the example I saw confused me because in updating the fields in tables in my db I list comma delimited fields followed by corresponding values comma seperated within parenthesis.

I just didn't realize it was a straight assignment.

Many thanks.

02-19-2003, 01:44 AM
I usually use a much different syntax for updates... like

Dim UpdateQuery
UpdateQuery = "UPDATE tablename SET blah = '" & blah & "' WHERE somecriteria = " & somecriteria

It might not be the most efficient way (or it might be, I'm not a DBA)... but it's easier to understand and it always works!

02-23-2003, 08:37 PM
Thank you everyone that replied to my post. I obviously did not communicate my question well, judging from the responses I got.

Most responses were recommendations on how to update the tables in the database. I posted the question from work, then looked at it again when I got home and saw how poorly I had phrased my question. I certainly didn't give good detail, which I appologize for.

My objective was to edit the recordset after it had been populated with data from the table in the database and the connection disconnected. I am going on the assumption that once the recordset is created and filled with data, that the connection can be destroyed leaving the recordset containing data. And, that all the normal ADO methods are still available to manipulate the recordset until the recordset is destroyed (other than methods used for changing data in the table, obviously).

To explain, I offer this senario:

Three sales persons have access to a database. Each sales person has a computer to access the database.

The database table contains field1, field2, field3, fieldSortOrder. Field1 contains the sales person's Id. The sortOrder field has a value of '0' in every record in the table.

When a sales person queries the database, all records are returned. Then the connection is destroyed.

A test is done on the recordset. If the logged in sales person's Id matches Field1 then fieldSortOrder is assigned a value of '1'.

Now I use ADO methods to sort the recordset using fieldSortOrder descending.

All records belonging to that sales person appear first.

Hopefully, this is clearer than my original posting.

There may be a simple straightforward way to get this same result, but I started in this direction because of code I recall seeing several years ago. Any help is greatly appreciated.



Roy Sinclair
02-24-2003, 03:55 PM
If all you want is to restrict the recordset to a single salesperson you don't need to update any of the fields in the recordset. Just use the "filter" function on the recordset to select out only that salesperson's data.

02-25-2003, 07:30 PM
I want each sales person to see all records. I just want the inquiring sales person's record to be displayed first.

Roy Sinclair
02-25-2003, 11:28 PM
Since you want to show all records but want the "current" salespersons records first you should add that into your SQL. Using something like the following in the SQL statement would get you a field named SortOrder containing a 1 for the current sales person and a 0 for all other sales persons.

,case Salespersonid when 'currentid' then 1 else 0 end SortOrder,

02-26-2003, 09:35 PM
I'm not sure I understand what you mean. Using SQL would return data from a table. I can't store the values in the db or all sales persons would be retrieving the same thing.

My purpose for having a dummy field (sortOrder) in the database is simply to make a place in the recordset when I run my query. All I need to do is know how to change the value of that field in the recordset after it is populated and the connection destroyed or to know if that is even possible.

I thought by using the recordset object I could more easily manipulate the data using the recordset's methods.

Hope this makes sense. It's been a long day.

Thanks for your response.


Roy Sinclair
02-27-2003, 10:18 PM
I understand the idea of the dummy field, the key point I was trying to make is that you can create the dummy field and populate it using SQL but that dummy field only exists in the recordset returned to your program, it doesn't exist in your database. The "case" statement in SQL is a powerful tool and you can use it to create your dummy field and to give that dummy field the value it needs as you select fields for inclusion in your recordset. That way your recordset comes complete with all the values it needs and you won't have to do the additional manipulations on the recordset that you've been contemplating.

02-28-2003, 02:12 AM
Truly I don't understand this thread at all... although if I do understand what was posted, wouldn't a WHERE clause achieve this?


Perhaps you should elaborate on exactly what you're trying to do in English, and not code?