How do I assign a value to a variable from a recordset using ADO. see lines 15 & 16 below. What should the syntax be?

set RSList = cmd.execute(sSQL)

you may need to cast your variable to the correct type if not leave it like it is
lRowID = clng(RSList("next_row_id"))

Put NEW keyword into RSlist Declaration
                     |  
                     V
Dim RSList as NEW ADODB.Recordset

I changed the syntax to that shown below and now get [Run-time error '3265': Application defined or object-defined error] when indicated by !!!!!

'set collection nulls = 0
    cmd.CommandText = "update reserve_current set collection_total = 0.0 where collection_total is null and claim_id = " & ClaimID & " and reserve_type_code = " & lresType & ";"
    cmd.Execute
 'set dBalance, dPaidTotal, dCollectTotal, lrhVal
    sSQL1 = "SELECT BALANCE_AMOUNT, PAID_TOTAL, COLLECTION_TOTAL FROM RESERVE_CURRENT WHERE CLAIM_ID = " & ClaimID & " AND CLAIMANT_EID = " & ClaimantEID & " AND " + _
        "RESERVE_TYPE_CODE = " & lresType & ";"
    Set RSlist = cmd.Execute(sSQL1)
    dBalance = RSlist("balance_Amount")    '<-----!!!!!!! error occurs here.
    dPaidTotal = RSlist("paid_total")
    dCollectTotal = RSlist("collection_total")
    RSlist.Close

I don't think the RSList is actually being filled.  The sSQL1 statement fills correctly and returns data in isqlw.  I am referenced to Microsoft ActiveX Data Objects 2.8 Library (amongst others)

did you also add the new as Michael_D stated

yes I added new.  the complete code is:

Sub ADODBupdate(dAmt As Double, lresType As Long)
Dim cmd As New ADODB.Command, RSlist As New ADODB.Recordset
Dim dPaidTotal As Double, dCollectTotal As Double, dBalance As Double, lrhVal As Long, sDate1 As String, sSQL1 As String, db As Integer
cmd.ActiveConnection = "Provider=SQLOLEDB.1;Data Source=MEENANXPLAP;user ID=sa;Password=qwerty;Initial Catalog=Riskmaster"

'set collection nulls = 0
    cmd.CommandText = "update reserve_current set collection_total = 0.0 where collection_total is null and claim_id = " & ClaimID & " and reserve_type_code = " & lresType & ";"
    cmd.Execute
 'set dBalance, dPaidTotal, dCollectTotal, lrhVal
    sSQL1 = "SELECT BALANCE_AMOUNT, PAID_TOTAL, COLLECTION_TOTAL FROM RESERVE_CURRENT WHERE CLAIM_ID = " & ClaimID & " AND CLAIMANT_EID = " & ClaimantEID & " AND " + _
        "RESERVE_TYPE_CODE = " & lresType & ";"
    Set RSlist = cmd.Execute(sSQL1)
    dBalance = RSlist("balance_Amount")   '<-- this is where the error occurs.  
    dPaidTotal = RSlist("paid_total")
    dCollectTotal = RSlist("collection_total")
    RSlist.Close

end Sub

NOTE: when I hover over RSList("balance_amount") before I execute the problem line I see ->  RSList("balance_Am... = < Item cannot be found in the collection corresponding...
I tried to set dBalance = to the element number in the select query too but no joy. --> dBalance = RSList(0).  Same error.  I'm probably doing something stupid but can't see what.

OK I got it working with this:

    sSQL1 = "SELECT BALANCE_AMOUNT, PAID_TOTAL, COLLECTION_TOTAL FROM RESERVE_CURRENT WHERE CLAIM_ID = " & ClaimID & " AND CLAIMANT_EID = " & ClaimantEID & " AND " + _
        "RESERVE_TYPE_CODE = " & lresType & ";"
   
    RSlist.Open sSQL1, "Provider=SQLOLEDB.1;Data Source=MEENANXPLAP;user ID=sa;Password=proteus;Initial Catalog=Riskmaster", adOpenDynamic
    dBalance = RSlist("balance_Amount")
    dPaidTotal = RSlist("paid_total")
    dBalance = RSlist.Fields(0).Value
    dCollectTotal = RSlist("collection_total")
    RSlist.Close

Then it worked just fine.  Does any of this make sense?

it works because all your objects got created correctly

i code lots of database driven apps and here is the way i always do it

dim myconn as new adodb.connection
dim myrs as new adodb.recordset
dim strSQL as string

myconn.connectionstring="Provider...."
myconn.open

strSQL = "SELECT * FROM ...."
set myrs = myconn.execute(strSQL)

myvar = myrs("field")
do stuff..

strSQL = "SELECT * FROM ...."
set myrs = myconn.execute(strSQL)

myvar = myrs("field")
do stuff


then you can have multiple recordset if you need and your connection stays open which doesnt tie up the db server

when your done - cleanup
set myrs = nothing
set myconn = nothing

this is just a suggestion of course - it also allows you to define your connection string once

hi

before assigning Value to a varibale u must also check

if the value in the recordset is not null..

eg:

if not Isnull(RSList("next_row_id")) then
   lRowID = RSList("next_row_id")
else
  lRowID  = 0 'or whatever value u want to provide...
end if

can we have the reason
why the question is being closed??

I'm sorry everyone.  I got it working (see my comment above showing the method).  And thank you anv for your advice.  I too always do null checks where needed.  I am not reluctant to reward points, just got it done myself by tinkering this time.

>>yes I added new.  the complete code is:<<
After my comment saying explicitly to do it. So what did you do by yourself?
A am not agree to close this question.

Michael D,

Adding New did nothing for the code as you can see by my comment on 12/16 at 12:19 pm.  The method I used and posted at 12:36 is completely different.  And I removed the New to verify it has no effect.  New is not required.  I'm sorry for your trouble, but I am being fair.  Are you angry?

Dan

I am not angry. But it's impossible to use ado recordset without creating a new instanse of it.
you can do it either by declaring:
 Dim rs as NEW adodb.recordset
or this way
 Dim rs as adodb.recordset
 Set rs = NEW adodb.recordset

In your first example You haven't created instance of recordset. therefor your examle couldn't work.
If there was another error that you managed to fix by yourself dosen't make my comment pointless

No, perhaps not pointless, but it didn't solve the problem of what syntax to use to assign values to a variable.  You might have pointed out a better way of building a mousetrap but I was duck hunting.  It would be wildly inappropriate to give 250 pts for what I didn't ask for (and, as I said before, is apparently unnecessary - it works as I am doing it without the word New).  Do you think ActiveX v2.8 assumes New for something never previously declared?  In any case I do appreciate your comment but the original code I submitted is not fixed by adding "New" to the dim statements.

Dan  

dmeenan,

I would like to know how you got it working without the new statement. Could you please post the full code that works? You have an example that has the new keyword in it.

Moderator:

All objections and recomendations are posted here.
I don't want to mess with it for points.
I am sure that your any decision will be right.


Marv-in:

Let Dan hunt his ducks with "ActiveX v2.8"


Regards,

Michael.

And I am very new to this forum.  If I am doing something wrong or not according to tradition, by all means let me know and I will abide by the wishes of the community.  I'm not ovine, but neither am I bovine (neither a sheep, nor bull-headed).  If you feel the advice warrants points for my specific request for help, then so be it.

Dan, I want to make sure that your impression from this site is still good.
We are here to help each other.
Usually i don't post full solutions, I try to help step by step, to let YOU solve the problem by YOURSELF.
Your first (and critical) error was absence of NEW keyword in declaration.
Without fixing this error you never could make your code working.
Next
Even if you make your code working - its nothing more than workaround. Trust me I have more than 10 years experience in Database programming.
If you want to do things rigth follow Marv-in's advise how to create and use Data access objects. (Date: 11/19/2004 04:21PM EST)

Or I can post here the whole solution if you want it. Just don't tell me that you can access data without initiating recordset. :)

My best regards,

Michael

Dan,
Here is some code based on your examples:
You can "Mix and Match" as you wish.

Sub ADOUpdate()
' Declare variables
Dim cn As ADODB.Connection
Dim RSList As ADODB.Recordset
Dim lRowID As Long
Dim sSQL As String
Dim lresType As Long, ClaimID As Long
Dim dBalance As Double, dPaidTotal As Double, dCollectTotal As Double

'Create an instance of ADO Connection Object
Set cn = New ADODB.Connection
'Open Connection
cn.Open "Provider=SQLOLEDB.1;Data Source=MYSQLSERVER;user ID=sa;Password=qwerty;Initial Catalog=MYDATABASE"

'Execute SQL that doesn't return records
cn.Execute "update my_table set my_fielda = 0.0 where my_fielda is null", , adExecuteNoRecords

sSQL = "select next_row_id from my_glossary where table_number = 15"  'this would return a longint

'Create an instance of ADO Recordset Object
Set RSList = New ADODB.Recordset
'Retrive record(s) from Database
RSList.Open sSQL, cn

lRowID = RSList("next_row_id")

'Close recordset for reusing
RSList.Close


sSQL = "SELECT BALANCE_AMOUNT, PAID_TOTAL, COLLECTION_TOTAL FROM RESERVE_CURRENT WHERE CLAIM_ID = " & ClaimID & " AND CLAIMANT_EID = " & ClaimantEID & " AND " + _
        "RESERVE_TYPE_CODE = " & lresType & ";"

'Reuse the same object
RSList.Open sSQL, cn
   
dBalance = RSList("balance_Amount")
dPaidTotal = RSList("paid_total")
dCollectTotal = RSList("collection_total")

'Close Recordset & Connection
RSList.Close
cn.Close

'Clean up
Set RSList = Nothing
Set cn = Nothing


End Sub



If you have any questions please ask.
And all this I am doing not for points. But I want to keep this site clean. This mean I don't like when question is PAQ-ed with wrong solution. Maybe it works for you (somehow) but somebody may try to use this "solution" in the future.


Michael