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 Total: Summing on Distinct Amount

I am trying to do a sum on a goal amount that is repeated for each record. But what is the the forumla to only sum on the distinct goal amount.

Example:

Month Year Goal Other Value

March 2007 500 5568

March 2007 500 5568

March 2007 500 5569

April 2007 600 5568

April 2007 700 5569

Total (I am receive)

March-April 2007 1600 5568

March-April 2007 1200 5569

Total (I excpect)

March-April 2007 1100 5568

March-April 2007 1200 5569

I haven't found anything online to help.

Thanks,

Sam

You could define you data set using the following SQL.

SELECT Month, Year, [Other Value], SUM(Goal)

FROM (

SELECT DISTINCT Month, Year, [Other Value], Goal

FROM TableName

)

GROUP BY Month, Year, [Other Value]

|||

I actually have more data being returned than what is being displayed in my example. I am already doing a group by in data set. Thanks for the thought.

Sam

|||

I hope this example is better:

Example:

Month Year Goal SalesID ActualSales

March 2007 500 5568 550

March 2007 500 5568 475

March 2007 500 5569 605

April 2007 600 5568 700

April 2007 700 5569 710

Running total using day before record.

I have 1 record per day with a Daily Difference. I would like to also keep
a
running difference. So I am looking for a way to insert the value for
RunningDiff
RunningDiff = PreviousDay's RunningDiff - current day's DailyDiff
date, $ShouldHave, $Have, DailyDiff, RunningDiff
1/23/2006, 12.50, 10.00, 2.50, 4.00
1/24/2006, 10.00, 7.00, 3.00, 1.00
1/25/2006, 10.00, 15.00, - 5.00, 6.00
1/26/2006, 17.00, 13.00, 4.00, 2.00
The RunnningDiff I can have update at the time a record is added, or can
have a stored procedure run multiple times throughout the day to update the
value.
thank you for any ideas,Ashley,
This could be achieved using cursors although I wouldn't recomend it if
the Table is going to be huge and processing time is going to be an
issue.
Barry|||Using a cursor would be find in this problem. Any suggestions of an example
to look at?
thanks!
"Barry" wrote:

> Ashley,
> This could be achieved using cursors although I wouldn't recomend it if
> the Table is going to be huge and processing time is going to be an
> issue.
> Barry
>|||Ahsley,
Have a look at this - I would test it in some different scenarios...I
haven't got time to fully test it I'm afraid.
HTH
Barry
Create Table Test
(
ProcessingDate Datetime Not Null,
TargetTotal Money Not Null,
CurrentTotal Money Not Null,
DailyDiff Money Null,
RunningDiff Money Null
)
Insert Into Test (ProcessingDate, TargetTotal, CurrentTotal)
Values ('2006-01-23', 12.50, 10.00)
Insert Into Test (ProcessingDate, TargetTotal, CurrentTotal)
Values ('2006-01-24', 10.00, 7.00)
Insert Into Test (ProcessingDate, TargetTotal, CurrentTotal)
Values ('2006-01-25', 10.00, 15.00)
Insert Into Test (ProcessingDate, TargetTotal, CurrentTotal)
Values ('2006-01-26', 17.00, 13.00)
Insert Into Test (ProcessingDate, TargetTotal, CurrentTotal)
Values ('2006-01-27', 16.50, 14.00)
Declare @.ProcessingDateFirst Datetime,
@.TargetTotalFirst Money,
@.CurrentTotalFirst Money,
@.DailyDiffFirst Money,
@.RunningDiffFirst Money,
@.ProcessingDateSecond Datetime,
@.TargetTotalSecond Money,
@.CurrentTotalSecond Money,
@.DailyDiffSecond Money,
@.RunningDiffSecond Money
Declare FirstRowCursor Cursor For
Select ProcessingDate, TargetTotal, CurrentTotal, DailyDiff,
RunningDiff
From Test
Open FirstRowCursor
Fetch Next From FirstRowCursor Into @.ProcessingDateFirst,
@.TargetTotalFirst, @.CurrentTotalFirst,
@.DailyDiffFirst, @.RunningDiffFirst
Declare SecondRowCursor Cursor For
Select ProcessingDate, TargetTotal, CurrentTotal, DailyDiff,
RunningDiff
From Test
Open SecondRowCursor
Fetch Next From SecondRowCursor Into @.ProcessingDateSecond,
@.TargetTotalSecond, @.CurrentTotalSecond,
@.DailyDiffSecond, @.RunningDiffSecond
Fetch Next From SecondRowCursor Into @.ProcessingDateSecond,
@.TargetTotalSecond, @.CurrentTotalSecond,
@.DailyDiffSecond, @.RunningDiffSecond
While @.@.Fetch_Status = 0
Begin
If @.DailyDiffFirst Is Null And @.RunningDiffFirst Is Null
Begin
Update Test
Set DailyDiff = TargetTotal - CurrentTotal, @.DailyDiffFirst =
TargetTotal - CurrentTotal,
RunningDiff = TargetTotal - CurrentTotal, @.RunningDiffFirst =
TargetTotal - CurrentTotal
Where ProcessingDate = @.ProcessingDateFirst
End
If @.DailyDiffFirst Is Not Null And @.RunningDiffFirst Is Not Null
Begin
Update Test
Set DailyDiff = TargetTotal - CurrentTotal
Where ProcessingDate = @.ProcessingDateSecond
Update Test
Set RunningDiff = @.RunningDiffFirst - DailyDiff
Where ProcessingDate = @.ProcessingDateSecond
End
Fetch Next From FirstRowCursor Into @.ProcessingDateFirst,
@.TargetTotalFirst, @.CurrentTotalFirst,
@.DailyDiffFirst, @.RunningDiffFirst
Fetch Next From SecondRowCursor Into @.ProcessingDateSecond,
@.TargetTotalSecond, @.CurrentTotalSecond,
@.DailyDiffSecond, @.RunningDiffSecond
End
Close FirstRowCursor
Close SecondRowCursor
Deallocate FirstRowCursor
Deallocate SecondRowCursor
Select * From Test|||Perfect.. Thank you!
"Barry" wrote:

> Ahsley,
> Have a look at this - I would test it in some different scenarios...I
> haven't got time to fully test it I'm afraid.
> HTH
> Barry
>
> Create Table Test
> (
> ProcessingDate Datetime Not Null,
> TargetTotal Money Not Null,
> CurrentTotal Money Not Null,
> DailyDiff Money Null,
> RunningDiff Money Null
> )
>
> Insert Into Test (ProcessingDate, TargetTotal, CurrentTotal)
> Values ('2006-01-23', 12.50, 10.00)
> Insert Into Test (ProcessingDate, TargetTotal, CurrentTotal)
> Values ('2006-01-24', 10.00, 7.00)
> Insert Into Test (ProcessingDate, TargetTotal, CurrentTotal)
> Values ('2006-01-25', 10.00, 15.00)
> Insert Into Test (ProcessingDate, TargetTotal, CurrentTotal)
> Values ('2006-01-26', 17.00, 13.00)
> Insert Into Test (ProcessingDate, TargetTotal, CurrentTotal)
> Values ('2006-01-27', 16.50, 14.00)
>
> Declare @.ProcessingDateFirst Datetime,
> @.TargetTotalFirst Money,
> @.CurrentTotalFirst Money,
> @.DailyDiffFirst Money,
> @.RunningDiffFirst Money,
> @.ProcessingDateSecond Datetime,
> @.TargetTotalSecond Money,
> @.CurrentTotalSecond Money,
> @.DailyDiffSecond Money,
> @.RunningDiffSecond Money
>
> Declare FirstRowCursor Cursor For
> Select ProcessingDate, TargetTotal, CurrentTotal, DailyDiff,
> RunningDiff
> From Test
>
> Open FirstRowCursor
> Fetch Next From FirstRowCursor Into @.ProcessingDateFirst,
> @.TargetTotalFirst, @.CurrentTotalFirst,
> @.DailyDiffFirst, @.RunningDiffFirst
>
>
> Declare SecondRowCursor Cursor For
> Select ProcessingDate, TargetTotal, CurrentTotal, DailyDiff,
> RunningDiff
> From Test
>
> Open SecondRowCursor
> Fetch Next From SecondRowCursor Into @.ProcessingDateSecond,
> @.TargetTotalSecond, @.CurrentTotalSecond,
> @.DailyDiffSecond, @.RunningDiffSecond
> Fetch Next From SecondRowCursor Into @.ProcessingDateSecond,
> @.TargetTotalSecond, @.CurrentTotalSecond,
> @.DailyDiffSecond, @.RunningDiffSecond
>
>
> While @.@.Fetch_Status = 0
> Begin
> If @.DailyDiffFirst Is Null And @.RunningDiffFirst Is Null
> Begin
> Update Test
> Set DailyDiff = TargetTotal - CurrentTotal, @.DailyDiffFirst =
> TargetTotal - CurrentTotal,
> RunningDiff = TargetTotal - CurrentTotal, @.RunningDiffFirst =
> TargetTotal - CurrentTotal
> Where ProcessingDate = @.ProcessingDateFirst
>
> End
>
> If @.DailyDiffFirst Is Not Null And @.RunningDiffFirst Is Not Null
> Begin
>
> Update Test
> Set DailyDiff = TargetTotal - CurrentTotal
> Where ProcessingDate = @.ProcessingDateSecond
> Update Test
> Set RunningDiff = @.RunningDiffFirst - DailyDiff
> Where ProcessingDate = @.ProcessingDateSecond
> End
>
> Fetch Next From FirstRowCursor Into @.ProcessingDateFirst,
> @.TargetTotalFirst, @.CurrentTotalFirst,
> @.DailyDiffFirst, @.RunningDiffFirst
> Fetch Next From SecondRowCursor Into @.ProcessingDateSecond,
> @.TargetTotalSecond, @.CurrentTotalSecond,
> @.DailyDiffSecond, @.RunningDiffSecond
>
> End
> Close FirstRowCursor
> Close SecondRowCursor
> Deallocate FirstRowCursor
> Deallocate SecondRowCursor
>
> Select * From Test
>

Running SP with a different user

We are using SQL2000. User insert table in one database DB1 and trigger insert the record into another database DB2. In this scenario, is it possible have a trigger in DB1 to execute a stored procedure in DB2 with a different user?

try define trigger with execute as clause

CREATE TRIGGER YourNAme ON Yourtable
WITH EXECUTE AS 'CompanyDomain\SqlUser1'

AFTER INSERT, UPDATE AS.......

--call your procedure here

GO

Running SP with a different user

We are using SQL2000. User insert table in one database DB1 and trigger insert the record into another database DB2. In this scenario, is it possible have a trigger in DB1 to execute a stored procedure in DB2 with a different user?

I think you may use openrowset in the trigger to update the other database. it is not the best practice but it is the only solution I know

OPENROWSET('SQLOLEDB','ServerName';'user';'Password',

'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname')|||

hello,

if you will be doing this most of the times

you might as well use the sp_addlinkserver options "useself" set to false to create a

linked server

Syntax
sp_addlinkedsrvlogin [ @.rmtsrvname = ] 'rmtsrvname'
[ , [ @.useself = ] 'useself' ]
[ , [ @.locallogin = ] 'locallogin' ]
[ , [ @.rmtuser = ] 'rmtuser' ]
[ , [ @.rmtpassword = ] 'rmtpassword' ]

by setting useself to false you can enter the desired username and password

regards

|||

USE DB1

Go

EXEC sp_

Trigger ON INSERT

USE DB2

Go

EXEC sp_

Adamus

|||i wish the new sp for 200 would allow "execute as" feature in 2k5|||

If this is in the trigger, is there any way I can run stored procedure with OpenRowSet and make trigger code invisible to the user who is inserting record since the password is there.
Thanks,