Showing posts with label basically. Show all posts
Showing posts with label basically. Show all posts

Tuesday, March 27, 2012

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

Thursday, March 22, 2012

Date parameter issue when created from query

I have a date parameter for a report that is intended to search 'within the last X week/month...'. The SQL for this is basically WHERE [datefield] >= @.dateparam AND [datefield] <= GETDATE().

The @.dateparam is defined in the report as 'From query', the value field is 'thedate', and the label field is 'thetext', and the dataset is:

Date Query dataset

SELECT GETDATE()-7 as thedate, '1 Week' as thetext, 1 as theorder
UNION
SELECT GETDATE()-14 as thedate,'2 Weeks' as thetext, 2 as theorder
UNION
SELECT GETDATE()-21 as thedate,'3 Weeks' as thetext, 3 as theorder
UNION
SELECT GETDATE()-31 as thedate,'1 Month' as thetext, 4 as theorder
UNION
SELECT GETDATE()-62 as thedate,'2 Months' as thetext, 5 as theorder
UNION
SELECT GETDATE()-(31*3) as thedate,'3 Months' as thetext, 6 as theorder
UNION
SELECT GETDATE()-(31*6) as thedate,'6 Months' as thetext, 7 as theorder
UNION
SELECT GETDATE()-365 as thedate,'1 Year' as thetext, 8 as theorder
ORDER BY 3


When I go to run the report and selecting a value for the date parameter, there is never any output, and the data selection combo box just gets reset to show <select a value>.

What am I doing wrong?

What happens if you set your "From query" up with converted dates as strings, versus a datetime type, and then re-cast the value in your WHERE clause? I'm thinking maybe this is a date formatting problem....

>L<

|||Thank you!

I quickly changed the dataset to be:

Code Snippet

SELECT CONVERT(nvarchar(10),GETDATE()-7, 101) as thedate, '1 Week' as thetext, 1 as theorder
...

...

ORDER BY 3


And the query worked.