asked on

'parameter is missing a value' error in dataset

Ive attached my stored procedure which is causing this error in my SSRS report:
The 'EventID' parameter is missing a value

I found this to be a common error and I checked most everything.
But i still have the error when I run my report.
I think it has to do with the attached stored procedure because when I execute this on its own, i dont get any results (when I should).
By running the report on its own (and not calling it from an asp page with parameters)-
Basically, when I pass start and end date and clientid, this brings up another dropdownlist of Events (if available) for the particular clientid.
Sometimes when I select a client, the dropdownlist that comes up is defaulted to <Select A Value>
which is no good because I cannot bypass this parameter.
Ive specified that the value for eventid can be NULL in design mode and I thought I accounted for every case for selection, but I am still getting the error.

Can anyone help?

Thanks.
John

USE [seMRM76]
GO
/****** Object:  StoredProcedure [dbo].[rsp_events_info]    Script Date: 11/06/2008 13:59:11 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[rsp_events_info] 
(
@startdate SMALLDATETIME,
@enddate SMALLDATETIME, 
@clientID INT
)
AS
BEGIN
IF EXISTS (SELECT * FROM Reservations AS T
WHERE  T.[General/ClientID]=@clientID AND ISNULL(T.[General/EventID],0)=0)
BEGIN
 
        SELECT DISTINCT R.[General/EventID] AS [General/EventID]  , ISNULL(E.[EventName],'') AS [EventName]
        FROM   Reservations R 
        LEFT JOIN 
        [vw_Events] E 
        ON R.[General/EventID]= E.[EventID]  
        WHERE (R.[General/ClientID]=@clientID)
        AND (R.[actual end]) BETWEEN @startdate AND @enddate 
        ORDER BY ISNULL(E.[EventName],'')
END
ELSE
 
BEGIN
   SELECT NULL AS '[General/EventID]','' AS [EventName]
   UNION
        SELECT DISTINCT R.[General/EventID] AS [General/EventID] , ISNULL(E.[EventName],'') AS [EventName]
        FROM   Reservations R 
        LEFT JOIN 
        [vw_Events] E 
        ON R.[General/EventID]= E.[EventID]  
        WHERE (R.[General/ClientID]=@clientID)
        AND (R.[actual end]) BETWEEN @startdate AND @enddate 
        ORDER BY [EventName]
end
 
END

Open in new window

ASKER CERTIFIED SOLUTION

tigin44

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jtrapat1

ASKER

tigin44-
thanks for the quick response-
I was just looking at that section of code and i think you are onto something-
1. The field in the Reservations table is called [General/EventID].
Are you saying that I should rename the field?
Or give it an alias?
Also, Valid values for the EventID in the Reservation Table are: NULL and 0.
If I change this code:
ISNULL(T.[General/EventID],0)=0) to ISNULL(T.[General/EventID],NULL)=NULL)
which makes no sense...
then the report launches.

Is there a way that I can account for both the NULL and 0 cases?
Thanks
John

tigin44

NULL = NULL do not gives the correct results.. use IS NULL isntead of Value = NULL..

I will ask you to rename the column names... especially change '/' with an other character such as '_' then try to run... The reporting tool may see that sign as division operator..

jtrapat1

ASKER

tigin44-
thanks-
could you give me an example syntax of what you mean for the NULL comparison?

Will do regarding the renaming.

Thanks
John

tigin44

SELECT *
FROM Table
WHERE Field = NULL

will not work... You can not compare a field withe the NULL, since NULL is not a value, its a state..

you should use the above query as

SELECT *
FROM Table
WHERE Field IS NULL

will give you the rows having the Field value NULL

SELECT *
FROM Table
WHERE Field IS NOT NULL

will also give you the rows having any value...
by the way NULL and '' (null string) are not the same too..
hope this helps.

jtrapat1

ASKER

tigin44:
Thanks for all your help-
Listen, I think i found the bug;
Ive attached a screenshot but basically, of the seven fields I have on my form, six are required;
and the seventh one, EventID is optional;

When I run my parameter form and submit the page, sometimes an Event shows up in the event dropdownbox, without a blank default option;
This is what is causing the bug!
99% of the time, after I select a client from the dropdown and is posts back,
it executes the events_info sp that Ive posted earlier;
This is my problem- How can I guarantee that I will get a blank selection in my Event dropdown box-
(i.e. in selectedindex=0 position)
atatched is my logic where I just go thru the seven text fields and get the parameters-

Thanks in Advance
John

                If startdate.Text <> "" Then
                    sb.Append("&startdate=" + startdate.Text.Trim())
                    sb.ToString()
                End If
                If enddate.Text <> "" Then
                    sb.Append("&enddate=" + enddate.Text.Trim())
                    sb.ToString()
                End If
                If ddClient.SelectedIndex > 0 Then
                    sb.Append("&clientID=" + ddClient.SelectedValue.Trim())
                    sb.ToString()
                End If
                'bug is in here - 
                'when first item is chosen, logic skips over eventid and 
                'therefore passes null string to report
                '
                If ddEvent.SelectedIndex > 0 Then
                    sb.Append("&EventID=" + ddEvent.SelectedValue.Trim())
                    sb.ToString()
                End If
                If duedate.Text <> "" Then
                    sb.Append("&duedate=" + duedate.Text.Trim())
                    sb.ToString()
                End If
                If invoice.Text <> "" Then
                    sb.Append("&invnum=" + invoice.Text.Trim())
                    sb.ToString()
                End If
                If amount.Text <> "" Then
                    sb.Append("&amt=" + amount.Text.Trim())
                    sb.ToString()
                End If

Open in new window

screen.png