Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    New to the CF scene
    Join Date
    Jun 2002
    Location
    Atlanta
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    What is the syntax for assigning a field's value in a recordset

    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?

    Thanks

  • #2
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    Don't you just do:

    rs.Fields("myFieldname")=myVal

    rs.Update

    ?

    Or am I, too, missing something?

  • #3
    New to the CF scene
    Join Date
    Jun 2002
    Location
    Atlanta
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #4
    Senior Coder
    Join Date
    Jun 2002
    Location
    41° 8' 52" N -95° 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I usually use a much different syntax for updates... like

    Dim UpdateQuery
    UpdateQuery = "UPDATE tablename SET blah = '" & blah & "' WHERE somecriteria = " & somecriteria
    Conn.Execute(UpdateQuery)

    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!
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #5
    New to the CF scene
    Join Date
    Jun 2002
    Location
    Atlanta
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thumbs up

    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.

    Thanks,


    Theo

  • #6
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #7
    New to the CF scene
    Join Date
    Jun 2002
    Location
    Atlanta
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I want each sales person to see all records. I just want the inquiring sales person's record to be displayed first.

  • #8
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

    Code:
    select 
    ...
    ,case Salespersonid when 'currentid' then 1 else 0 end SortOrder,
    ...

  • #9
    New to the CF scene
    Join Date
    Jun 2002
    Location
    Atlanta
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

    Theo

  • #10
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Last edited by Roy Sinclair; 02-27-2003 at 10:21 PM.

  • #11
    Senior Coder
    Join Date
    Jun 2002
    Location
    41° 8' 52" N -95° 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?
    Last edited by whammy; 02-28-2003 at 02:15 AM.
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •