• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

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

Sub ADOUpdate()
Dim cmd as New ADODB.Command
Dim RSList as ADODB.Recordset
Dim lRowID as Long
Dim sSQL as string
cmd.ActiveConnection = "Provider=SQLOLEDB.1;Data Source=MYSQLSERVER;user ID=sa;Password=qwerty;Initial Catalog=MYDATABASE"

'this works just fine
      cmd.CommandText = "update my_table set my_fielda = 0.0 where my_fielda is null"
      cmd.Execute

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

'this syntax is wrong (these next two lines of code)  HOW IS THIS DONE?  All I want to do, using ADO is assign a value from a record set to a variable
      RSList.Open sSQL, cmd.ActiveConnection
      lRowID = RSList("next_row_id")

'this works if I set lRowID = to a number so the syntax of the next 2 lines is good
      cmd.CommandText = "update my_table set my_fieldb = 'Test' where row_id = " & lRowID & ";"
      cmd.Execute

End Sub
0
dmeenan
Asked:
dmeenan
  • 7
  • 6
  • 4
  • +2
1 Solution
 
Marv-inCommented:
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"))
0
 
Michael_DCommented:
Put NEW keyword into RSlist Declaration
                     |  
                     V
Dim RSList as NEW ADODB.Recordset
0
 
dmeenanAuthor Commented:
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)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Marv-inCommented:
did you also add the new as Michael_D stated
0
 
dmeenanAuthor Commented:
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.
0
 
dmeenanAuthor Commented:
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?
0
 
Marv-inCommented:
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
0
 
anvCommented:
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
0
 
anvCommented:
can we have the reason
why the question is being closed??
0
 
dmeenanAuthor Commented:
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.
0
 
Michael_DCommented:
>>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.
0
 
dmeenanAuthor Commented:
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
0
 
Michael_DCommented:
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

0
 
dmeenanAuthor Commented:
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  
0
 
Marv-inCommented:
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.
0
 
Michael_DCommented:
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.
0
 
dmeenanAuthor Commented:
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.
0
 
Michael_DCommented:
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
0
 
Michael_DCommented:
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

 
0
 
moduloCommented:
Closed, 250 points refunded.

modulo
Community Support Moderator
Experts Exchange
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 6
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now