Date problems between VB and SQL

I have a problem that I have spent an age on and can't resolve.

Basically, I have a table that has a date field. I have created SP's to
return values within a given date range.

The problem that I have is that when I append some data to the table,
inconsistency occurs.

What basically happens is that when I run the SP within SQL Analyser,
the resultset returns correctly, including ALL data between the two date
ranges. I can see the query in SQL Profiler and can see that the correct
SP and parameters are being passed to SQL.

The problem occurs when I run the SP from VB. If I pass the date
parameters from the original table (before I append any data), the
correct set of results are returned. However, if I then APPEND some
data, VB returns a "Runtime Error 3265 - this item cannot be found in
the collection corresponding to the requested name or ordinal."

In other words, if the original table contains data from say the 1st to
the 8th February, the SP's work fine. If I then add data for the 9th, VB
returns the error indicated.

What is really strange is that at all times, the correct SP and
parameters are being passed to SQL (I can see this using SQL Profiler)
and if I cut and paste the SP and parameters into SQL Analyser, the
correct data is always returned, even if the date range includes the
appended data.

The table is set up correctly using the correct data type for the
column.

I am thinking that this has something to do with the appended data - it
all appends OK so I assume that SQL is happy with the data and data
types. Would it have something to do with the SP being created on the
original table and not on the latest table that has had data appended to
it?

I'll also post this in the VB section as I'm not really sure where this
item should be posted.

Very many thanks for your kind consideration.

Shane

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Shane (Shane) writes:
> The problem occurs when I run the SP from VB. If I pass the date
> parameters from the original table (before I append any data), the
> correct set of results are returned. However, if I then APPEND some
> data, VB returns a "Runtime Error 3265 - this item cannot be found in
> the collection corresponding to the requested name or ordinal."
> In other words, if the original table contains data from say the 1st to
> the 8th February, the SP's work fine. If I then add data for the 9th, VB
> returns the error indicated.
> What is really strange is that at all times, the correct SP and
> parameters are being passed to SQL (I can see this using SQL Profiler)
> and if I cut and paste the SP and parameters into SQL Analyser, the
> correct data is always returned, even if the date range includes the
> appended data.

A lot of text, but little code...

I don't even understand when you get the VB Error. Is when you use .Append?
Or is it when you call the procedure after you have appended?

And with which collection do you get the error?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp