Updating variables using a With statement.

Updating variables using a With statement.

Post by Stephen Hartfiel » Thu, 10 Jan 2002 06:58:10



I am trying to update a set of variables from the fields
of a recordset.  I want to use a with statement to
economize on the code, but I don't believe I am addressing
the correct property of the recordset.  Here is what I
have.  

Thanks in advance for your help.

Steve

For i = 1 To 10
    With oRs

         .oRs(0) = mSelect5
         .oRs(1) = mSelect6
         .oRs(2) = mSelect7
         .oRs(3) = mSelect8
         .oRs(4) = mSelect9
         .oRs(5) = mSelect10

     mSelect4 = "INSERT INTO Temp.dbf(custno, item, invno,
cost, price, invdte) VALUES (" & mSelect5 & ", " &
mSelect6 & ", " & mSelect7 & ", " & mSelect8 & ", " &
mSelect9 & ", " & mSelect10 & ")"

        oRs1.Open mSelect4, oCn
        oRs1.MoveNext
     End With
 Next

 
 
 

Updating variables using a With statement.

Post by Harinatha Reddy Gorl » Thu, 10 Jan 2002 15:06:02


Hi
I think you need to read ADO documentation  little bit better..:)
If you are using INSERT , UPDATE statements , you have to use Connection
or command objects depending on your requirement.
If you want to update recordset using with(not tested),
with rs
   .fields(0).value="xx"
..............
..............
   .update
end with

If you are using INSERT statement:

Quote:>mSelect4 = "INSERT INTO Temp.dbf(custno, item, invno,
>cost, price, invdte) VALUES (" & mSelect5 & ", " &
>mSelect6 & ", " & mSelect7 & ", " & mSelect8 & ", " &
>mSelect9 & ", " & mSelect10 & ")"

use connection object here..
cn.execute mSelect4

Best of luck
Harinatha Reddy Gorla
        Software Engineer
Smart Software Technologies,
Hyderabad, India

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Updating variables using a With statement.

Post by Val Mazu » Thu, 10 Jan 2002 22:30:41


Hi Stephen,

First of all, you cannot open recordset usinf action
query, like INSERT, UPDATE, DELETE. And it does not make
any sence. If you want to insert new record, then you have
two choices:
1. Prepare INSERT SQL statement (like you do it now) and
execute it using Execute method of your connection or
Command object.

2. Open recordset (in your case oRs1) ONLY ONCE, before
loop. Add new record to that recordset using AddNew method
of recordset.

oRs1.AddNew
oRs1.Fields("MyField1").Value="test"
.
.
.
oRs1.Fields("MyFieldn").Value="testn"

and call Update method of recordset to accept new record.

oRs1.Update

Val

Quote:>-----Original Message-----
>I am trying to update a set of variables from the fields
>of a recordset.  I want to use a with statement to
>economize on the code, but I don't believe I am
addressing
>the correct property of the recordset.  Here is what I
>have.  

>Thanks in advance for your help.

>Steve

>For i = 1 To 10
>    With oRs

>         .oRs(0) = mSelect5
>         .oRs(1) = mSelect6
>         .oRs(2) = mSelect7
>         .oRs(3) = mSelect8
>         .oRs(4) = mSelect9
>         .oRs(5) = mSelect10

>     mSelect4 = "INSERT INTO Temp.dbf(custno, item,
invno,
>cost, price, invdte) VALUES (" & mSelect5 & ", " &
>mSelect6 & ", " & mSelect7 & ", " & mSelect8 & ", " &
>mSelect9 & ", " & mSelect10 & ")"

>        oRs1.Open mSelect4, oCn
>        oRs1.MoveNext
>     End With
> Next

>.

 
 
 

1. using a variable as a column name in the set clause of an update statement

I want to update a column in a table within a T-SQL stored procedure.
However, I do not want to hard code the column name in the update
statement because the column to be updated changes every month.  So, I
want to use a variable in the place of a column name in the set clause
of an update statement.  However, when I use a variable in the place of
a column name, the update statement does not update the table.  For
example:




UPDATE booked_project

SELECT june FROM booked_project

This code returns the following results:

----
june

(1 row(s) affected)

(3 row(s) affected)

june
-----------------------------------------------------
3.0
3.0
3.0

(3 row(s) affected)

However, when I run the following code, the update statement updates the
table:
UPDATE booked_project

SELECT june FROM booked_project

This code returns the following results:

(3 row(s) affected)

june
-----------------------------------------------------
55.0
55.0
55.0

(3 row(s) affected)

Does anyone know why the first update statement that uses the variable
name as the column name is not updating the table?

Thanks,
Ali Galewski

2. Locking error 3202

3. Using variables in an update statement

4. Permission Control Design

5. Using variables in a update statement

6. SQL Problem

7. issuing update statements using variables

8. Reject user connect to database via SQL*PLUS ??

9. Using variables in an update statement

10. Using a variable in SQL with an Update statement

11. Query in using variable in UPDATE statement

12. Error using variable for IN clause in UPDATE statements

13. Using Variables for Field Names using .AddNew and .Update