Item cannot be found in the collection corresponding to the requested name or ordinal.

I am retrieving the data using the below sql statement in VB 6.0 & it giving me the Error
Item cannot be found in the collection corresponding to the requested name or ordinal.
I have tried to run the same sql statement it ran fine
I am using a recordset to retrive a data & it looks like recorset do have a data init but when I am assigning it to a temp variable it ging me th eabove stated error
strsql = "select sum(isnull(Amount,0)) from Payments where Name ='" & Name & "'"
objRS.CursorLocation = enmCursorLocation
Call objRS.Open(vntSPNameOrSQLOrCmdObject, vntConn, enmCursorType, enmLockType, lngOptions) If (Not objRS Is Nothing) Then
If (Not objRS Is Nothing) Then
dblAmount = CDbl(Trim$(objRS.Fields.Item("Amount").Value))
end if
Please advice
Thanks
Your column is no longer called amount, since you are performing
aggregations against it. You can change your SQL statement to provide an
alias, e.g.
SELECT Amount = SUM(...) FROM ...
Or you can use a different, more meaningful name, e.g.
SELECT SumAmount = SUM(...) FROM ...
Or in your code you can refer to the ordinal position of the column, instead
of the name.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"SAMAY" <anonymous@.discussions.microsoft.com> wrote in message
news:6C1456DD-FBC0-4ADC-8B99-F7BFA10821F1@.microsoft.com...
> I am retrieving the data using the below sql statement in VB 6.0 & it
giving me the Error
> Item cannot be found in the collection corresponding to the requested name
or ordinal.
> I have tried to run the same sql statement it ran fine
> I am using a recordset to retrive a data & it looks like recorset do have
a data init but when I am assigning it to a temp variable it ging me th
eabove stated error
> strsql = "select sum(isnull(Amount,0)) from Payments where Name ='" & Name
& "'"
> objRS.CursorLocation = enmCursorLocation
> Call objRS.Open(vntSPNameOrSQLOrCmdObject, vntConn, enmCursorType,
enmLockType, lngOptions) If (Not objRS Is Nothing) Then
> If (Not objRS Is Nothing) Then
> dblAmount = CDbl(Trim$(objRS.Fields.Item("Amount").Value))
> end if
> Please advice
> Thanks
|||Thanks for your help
it worked
Thanks again