runtime error 3265: item cannot be found in this collection

Hello!

I connected to sqlserver 7 db from vb6.I got one record displayed from my db table as rs.fields(0).If i give rs.fields(1) , it gives me the error, item cannot be found in this collection.When i dispalyed the record count as msgbox rs.recordcount it shows -1.what may be the error, pls help.

LydiaPlease provide your sql statement within vb - and which cursorlocation are you setting (client or server). It sounds like your sql statement is only returning 1 column. When you use rs.fields(1) accesses the 2nd column returned by your sql query - not the next record in the recordset.|||Hello!

Thank you very much, i fixed the error as soon as i written to you.That is the same thing that you replied.It's coz i am quite new to VB and am learning.

Two more issues - i need ur help

1. But still i am getting the record count as -1 . why is it? but i am getting all my records in the record set.

2. I created a function to fill up the list box as

Function FillList(Listitem as Listbox, FieldName as Field, TableName as Table)

--Code--Goes here

End Function

when i tried to call the function as

FillList(List1, m_email,members)

it is returning error as : ByVal ref argument type mismatch

how should i refer the values with the arguments in the function?

Pls help

Lydia|||Which cursorlocation are you using ?|||Originally posted by rnealejr
Which cursorlocation are you using ?

What do you mean by cursor location? i gave the code like this:

--some code here--
opened the record set as AdopenforwardOnly|||Both the connection and recordset objects contain a property called cursorlocation. If you are not setting it, go into debug and view the properties of the object and examine the cursorlocation property.|||Thank you very much, After setting the cursor location, i got the correct record count.|||Happy to help.|||I have a function to fill up list box with a DB Field values as

Public Function FillList(ListItem As ListBox, FieldName As Field,TableName as Table)
Dim i As Integer
i = 0
DbConnect
strSQL = "select FieldName from TableName"

'without using the connection object
ObjRs.Open strSQL, strConn

' Add Items in the list box
ObjRs.MoveFirst
Do While Not ObjRs.EOF
ListItem.AddItem ObjRs(0), i
ObjRs.MoveNext
i = i + 1
Loop

'Be sure you close and destroy your objects.

ObjRs.Close
ObjConn.Close
Set ObjConn = Nothing
Set ObjRs = Nothing
End Function

' Then i called the function as

call FillList(List1,"M_email","Members")

It gives me error as : Type Mismatch

What is the mistake in the code?|||Please post the code before the FillList. How are the Field and Table referenced ( like through adodb ...) ?|||Yes, I connected to the DB as ADODB.Connection|||How are you able to reference "Table" ? Also, you should be explicit in your declarations (Adodb.Field).

The problem is that you are trying to pass a string to a function expecting an object. Both the field/table should be strings - so you would build your sql statement like:

strSQL = "select " & FieldName & " from " & TableName

Running Sum Query ?

Following query is calculating running sum and running average of an Invento
ry Item, positive quantities are receipts and negative quantities are issued
, last two columns are running sum of quantity and amount column.
While running sum of quantity is ok, but Amt running sum is not correct, any
idea what am I missing ?
My table data is as under:-
Please note, negative quantites have no rates and no amounts, this will be c
alculated on average basis in above query.
Sno field is just an auto numbering field for query purpose.
PRITC QTY RATE PRAMT SNO
-- -- -- -- --
1111-034 200 1494.5 298900 1
1111-034 218 1559 339862 2
1111-034 -150 3
1111-034 -5 4
1111-034 -200 5
1111-034 -5 6
1111-034 -50 7
Best Regards,
Luqman
select a.sno,a.qty,
case when a.qty>0 then a.rate
else
(select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0) end rate,
case when a.qty>0 then a.pramt
else
(select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0)*a.qty
end Amt,sum(b.qty),
sum(case when a.qty>0 then a.pramt
else
(select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0)*a.qty
end) Amt
from test a, test b
where a.sno>=b.sno
group by a.sno,a.qty,a.pramt,a.rate
SNO QTY RATE AMT SUM(B.QTY) AMT
-- -- -- -- --
--
1 200 1494.5 298900 200 298900
2 218 1559 339862 418 679724
3 -150 1528.13876 -229220.81 268 -687662.44
4 -5 1528.13876 -7640.6938 263 -30562.775
5 -200 1528.13876 -305627.75 63 -1528138.8
6 -5 1528.13876 -7640.6938 58 -45844.163
7 -50 1528.13876 -76406.938 8 -534848.56Hello, Luqman
Please post DDL (as "CREATE TABLE" statements) and sample data (as
"INSERT INTO ... VALUES ..." statements), like this:
CREATE TABLE test (
PRITC varchar(10) NOT NULL,
QTY numeric(10,3) NOT NULL,
RATE numeric(18,4) NULL,
PRAMT numeric(18,4) NULL,
SNO int IDENTITY PRIMARY KEY,
CHECK (PRAMT=QTY*RATE)
)
INSERT INTO test VALUES ('1111-034',200,1494.5,298900)
INSERT INTO test VALUES ('1111-034',218,1559,339862)
INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-150)
INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-5)
INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-200)
INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-5)
INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-50)
The above DDL is just a guess; please correct it if necessary.
Your current query returns the following error: "Cannot perform an
aggregate function on an expression containing an aggregate or a
subquery." The reason is obvious: you cannot use SUM() for an
expression that contains a subquery (for the last column in your
query).
You probably want something like this:
SELECT a.sno, a.qty, a.rate, a.pramt, (
SELECT SUM(qty) FROM test b
WHERE b.sno<=a.sno
) as qty_sum, (
SELECT SUM(pramt) FROM test b
WHERE b.sno<=a.sno AND qty>0
) as pramt_sum, (
SELECT SUM(pramt)/SUM(qty) FROM test b
WHERE b.sno<=a.sno AND qty>0
) as avg_rate
FROM test a
If you want something else, please post the expected results.
Razvan|||The following query will output something like this.
select a.sno,a.qty,
case when a.qty>0 then a.rate
else
(select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0) end rate,
case when a.qty>0 then a.pramt
else
(select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0)*a.qty
end Amt,sum(b.qty),
case when a.qty>0 then a.pramt
else
(select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0)*a.qty
end Bal
from test a, test b
where a.sno>=b.sno
group by a.sno,a.qty,a.pramt,a.rate
sno qty rate Amount qtyTotal AmtTotal
1 200.000 1494.500000 298900.000000 200.000 298900.000000
2 218.000 1559.000000 339862.000000 418.000 339862.000000
3 -150.000 1528.138755 -229220.813250 268.000 -229220.813250
4 -5.000 1528.138755 -7640.693775 263.000 -7640.693775
5 -200.000 1528.138755 -305627.751000 63.000 -305627.751000
6 -5.000 1528.138755 -7640.693775 58.000 -7640.693775
7 -50.000 1528.138755 -76406.937750 8.000 -76406.937750
Currently the Qtytotal is ok, but AmtTotal is not ok, I want the correct
running sum of Amount Field in AmtTotal.
The correct display should be:
sno qty rate Amount qtyTotal AmtTotal
1 200.000 1494.500000 298900.000000 200.000 298900.000000
2 218.000 1559.000000 339862.000000 418.000 638762.000000
3 -150.000 1528.138755 -229220.813250 268.000 409541.19
4 -5.000 1528.138755 -7640.693775 263.000 401900.4962
5 -200.000 1528.138755 -305627.751000 63.000 96272.7462
6 -5.000 1528.138755 -7640.693775 58.000 88632.0524
7 -50.000 1528.138755 -76406.937750 8.000 12225.1144
Any idea please ?
Best Regards,
Luqman
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1149586142.846012.210890@.f6g2000cwb.googlegroups.com...
> Hello, Luqman
> Please post DDL (as "CREATE TABLE" statements) and sample data (as
> "INSERT INTO ... VALUES ..." statements), like this:
> CREATE TABLE test (
> PRITC varchar(10) NOT NULL,
> QTY numeric(10,3) NOT NULL,
> RATE numeric(18,4) NULL,
> PRAMT numeric(18,4) NULL,
> SNO int IDENTITY PRIMARY KEY,
> CHECK (PRAMT=QTY*RATE)
> )
>
> INSERT INTO test VALUES ('1111-034',200,1494.5,298900)
> INSERT INTO test VALUES ('1111-034',218,1559,339862)
> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-150)
> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-5)
> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-200)
> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-5)
> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-50)
> The above DDL is just a guess; please correct it if necessary.
> Your current query returns the following error: "Cannot perform an
> aggregate function on an expression containing an aggregate or a
> subquery." The reason is obvious: you cannot use SUM() for an
> expression that contains a subquery (for the last column in your
> query).
> You probably want something like this:
> SELECT a.sno, a.qty, a.rate, a.pramt, (
> SELECT SUM(qty) FROM test b
> WHERE b.sno<=a.sno
> ) as qty_sum, (
> SELECT SUM(pramt) FROM test b
> WHERE b.sno<=a.sno AND qty>0
> ) as pramt_sum, (
> SELECT SUM(pramt)/SUM(qty) FROM test b
> WHERE b.sno<=a.sno AND qty>0
> ) as avg_rate
> FROM test a
> If you want something else, please post the expected results.
> Razvan
>|||Luqman wrote:
> The correct display should be: [...]
OK, then let's save the SELECT statement in my previous post as a view:
create view myview as
SELECT a.sno, a.qty,
ISNULL(a.rate, (
SELECT SUM(pramt)/SUM(qty) FROM test b
WHERE b.sno<=a.sno AND qty>0
)) as rate, (
SELECT SUM(qty) FROM test b
WHERE b.sno<=a.sno
) as qtyTotal, (
SELECT SUM(pramt) FROM test b
WHERE b.sno<=a.sno AND qty>0
) as pramt_sum
FROM test a
And now we can use the following query:
SELECT x.sno, x.qty, x.rate, x.qty*x.rate as Amount,
x.qtyTotal, x.pramt_sum+isnull((
select sum(qty*rate) from myview y
where y.sno<=x.sno and y.qty<0
),0) as AmtTotal
FROM myview x
The above query returns the expected results (I'm not sure what is the
number of decimals that you want; the number of decimals in the result
changes according to the data types in the table).
However, to be sure that the above query meets all your requirements,
you should also post some sample data where there is a positive value
AFTER some negative values and expected results for this new sample
data.
Razvan|||Hi,
Did you work on my queries, please ?
Best Regards,
Luqman
"Luqman" <pearlsoft@.cyber.net.pk> wrote in message
news:%232Kn49ZiGHA.3884@.TK2MSFTNGP04.phx.gbl...
> The following query will output something like this.
> select a.sno,a.qty,
> case when a.qty>0 then a.rate
> else
> (select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0) end rate,
> case when a.qty>0 then a.pramt
> else
> (select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0)*a.qty
> end Amt,sum(b.qty),
> case when a.qty>0 then a.pramt
> else
> (select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0)*a.qty
> end Bal
> from test a, test b
> where a.sno>=b.sno
> group by a.sno,a.qty,a.pramt,a.rate
> sno qty rate Amount qtyTotal
> AmtTotal
> 1 200.000 1494.500000 298900.000000 200.000 298900.000000
> 2 218.000 1559.000000 339862.000000 418.000 339862.000000
> 3 -150.000 1528.138755 -229220.813250 268.000 -229220.813250
> 4 -5.000 1528.138755 -7640.693775 263.000 -7640.693775
> 5 -200.000 1528.138755 -305627.751000 63.000 -305627.751000
> 6 -5.000 1528.138755 -7640.693775 58.000 -7640.693775
> 7 -50.000 1528.138755 -76406.937750 8.000 -76406.937750
> Currently the Qtytotal is ok, but AmtTotal is not ok, I want the correct
> running sum of Amount Field in AmtTotal.
> The correct display should be:
> sno qty rate Amount qtyTotal
> AmtTotal
> 1 200.000 1494.500000 298900.000000 200.000 298900.000000
> 2 218.000 1559.000000 339862.000000 418.000 638762.000000
> 3 -150.000 1528.138755 -229220.813250 268.000 409541.19
> 4 -5.000 1528.138755 -7640.693775 263.000 401900.4962
> 5 -200.000 1528.138755 -305627.751000 63.000 96272.7462
> 6 -5.000 1528.138755 -7640.693775 58.000 88632.0524
> 7 -50.000 1528.138755 -76406.937750 8.000 12225.1144
> Any idea please ?
> Best Regards,
> Luqman
>
> "Razvan Socol" <rsocol@.gmail.com> wrote in message
> news:1149586142.846012.210890@.f6g2000cwb.googlegroups.com...
>|||Razvan Socol wrote:
Luqman wrote:
> Did you work on my queries, please ?
My last message was:
http://groups.google.com/group/micr...br />
5b133039
Did you read it ?
If yes, you didn't post any additional information. If you need more
help, post sample data AND EXPECTED RESULTS where there is a positive
value after some negative values.
Razvan

Running Sum on Query

Hye Guys,

I am new in this forums and programming..

I have data like

S.N Item Qty
1 ABC 10
2 ABC 20
3 ABC 5
4 XYZ 2
5 XYZ 10
6 XYZ 1

And I want the result

S.N Item Qty RunTot
1 ABC 10 10
2 ABC 20 30
3 ABC 5 35
4 XYZ 2 2
5 XYZ 10 12
6 XYZ 1 13
Plz Help meAre you going to be presenting these rows in a client application? If so, it will be VASTLY more efficient to do these sub-totals there. It is easy as you're looping one by one through the individual rows to keep a tally of Qty for like Item values. With T-SQL, you will have to use a sub-query that is evaluated for every row, and this is going to put your performance into the toilet. wrote in message news:d85d7cec-a74b-47be-a43d-6b4c56306acd@.discussions.microsoft.com...
> Hye Guys, >
> I am new in this forums and programming.. >
> I have data like >
> S.N Item Qty
> 1 ABC 10
> 2 ABC 20
> 3 ABC 5
> 4 XYZ 2
> 5 XYZ 10
> 6 XYZ 1 >
> And I want the result >
> S.N Item Qty RunTot
> 1 ABC 10 10
> 2 ABC 20 30
> 3 ABC 5 35
> 4 XYZ 2 2
> 5 XYZ 10 12
> 6 XYZ 1 13 > >
> Plz Help me
>|||Try this one:

select t.[S.N], t.Item, t.Qty, sum(t1.Qty) as RunTot
from
YourTable as t
inner join YourTable as t1
on t.Item = t1.Item and t.[S.N]>=t1.[S.N]
group by t.[S.N], t.Item, t.Qty|||Here is an article on it. I have not tested it, I just came across it once and bookmarked it..

http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=23349&DisplayTab=Article

Hope that helps!

BobP|||Hye friends,
The replies you have posted had helped me but as one of our friend said I tried it in a database containing large volumes of records.. and the result is ...... 2 slow. So need some other technique..

Always Ready 2 Learn
Kabindra Bakey|||Are there any reasons why it has to be in one select statement?

If it is for a report, let the reporting application calculate it.
If it is for an application, let the app calc it.

The other thing is to pre-process the statement, and store the results in a table, and just select from that table when you need the data.

You could setup the pre-process to happen at night, or every hour, etc... You would just need to dump the results to a temp table, start a transaction, delete from the processed table, populate the processed table from the temp table, and then commit the transaction.

To the users it is transparent, except that you dont have "real-time" data.

BobP|||

Dear guys,

Previously For such running totals I use to have a seperate column in a transaction table. And I used to calculate the value during the insertion of the record and store it in the respective column. that was quite easy way but by any means my rows get unsorted or the order of the transaction has to be changed manually I have w calculate the values again and maintain the column sum once more. which was totally manual calculation based. So I am trying to find the way out fo it...

Always there 2 learn
Kabindra Bakey

Running Sum on Query

Hye Guys,

I am new in this forums and programming..

I have data like

S.N Item Qty
1 ABC 10
2 ABC 20
3 ABC 5
4 XYZ 2
5 XYZ 10
6 XYZ 1

And I want the result

S.N Item Qty RunTot
1 ABC 10 10
2 ABC 20 30
3 ABC 5 35
4 XYZ 2 2
5 XYZ 10 12
6 XYZ 1 13
Plz Help meAre you going to be presenting these rows in a client application? If so,

it will be VASTLY more efficient to do these sub-totals there. It is easy

as you're looping one by one through the individual rows to keep a tally of

Qty for like Item values. With T-SQL, you will have to use a sub-query that

is evaluated for every row, and this is going to put your performance into

the toilet.

wrote in message

news:d85d7cec-a74b-47be-a43d-6b4c56306acd@.discussions.microsoft.com...

> Hye Guys,

>

> I am new in this forums and programming..

>

> I have data like

>

> S.N Item Qty

> 1 ABC 10

> 2 ABC 20

> 3 ABC 5

> 4 XYZ 2

> 5 XYZ 10

> 6 XYZ 1

>

> And I want the result

>

> S.N Item Qty RunTot

> 1 ABC 10 10

> 2 ABC 20 30

> 3 ABC 5 35

> 4 XYZ 2 2

> 5 XYZ 10 12

> 6 XYZ 1 13

>

>

> Plz Help me

>|||Try this one:

select t.[S.N], t.Item, t.Qty, sum(t1.Qty) as RunTot
from
YourTable as t
inner join YourTable as t1
on t.Item = t1.Item and t.[S.N]>=t1.[S.N]
group by t.[S.N], t.Item, t.Qty|||Here is an article on it. I have not tested it, I just came across it once and bookmarked it..

http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=23349&DisplayTab=Article

Hope that helps!

BobP|||Hye friends,
The replies you have posted had helped me but as one of our friend said I tried it in a database containing large volumes of records.. and the result is ...... 2 slow. So need some other technique..

Always Ready 2 Learn
Kabindra Bakey|||Are there any reasons why it has to be in one select statement?

If it is for a report, let the reporting application calculate it.
If it is for an application, let the app calc it.

The other thing is to pre-process the statement, and store the results in a table, and just select from that table when you need the data.

You could setup the pre-process to happen at night, or every hour, etc... You would just need to dump the results to a temp table, start a transaction, delete from the processed table, populate the processed table from the temp table, and then commit the transaction.

To the users it is transparent, except that you dont have "real-time" data.

BobP|||

Dear guys,

Previously For such running totals I use to have a seperate column in a transaction table. And I used to calculate the value during the insertion of the record and store it in the respective column. that was quite easy way but by any means my rows get unsorted or the order of the transaction has to be changed manually I have w calculate the values again and maintain the column sum once more. which was totally manual calculation based. So I am trying to find the way out fo it...

Always there 2 learn
Kabindra Bakey