Passing NULL value not working

I currently have a stored procedure that looks something like this

SELECT * FROM tblQuestions WHERE Title LIKE ISNULL('%'+@.Name+'%', Title)

I have a form that supplies this value. This statement should make it so that if a NULL value is passed in, then it will return all the rows, if some text is specified, then it will not. On my SQLDataSource on the page where the parameter is supplied I have set ConvertEmptyStringsToNull to True, but when I say in my code,

SqlDataSource1.SelectParameters.Add("@.Name", TextBox1.Text);

It won't give me back any of the rows, I know that the stored procedure works fine because I can get it to work by a basic query and other testing on it, so somewhere in my form, the NULL value isn't being passed, I belive that it is passing an empty string and I don't know why. Thank you in advance

/jcarver

Try this:

WHERE ([Title] Like '%' + @.Name + '%') or ( @.Name is NULL)"

And add this to your SqlDatasource: CancelSelectOnNullParameter="False"

|||

The two where clauses are logically equivalent.

I'm a bit curious why you have a column called title and a parameter called @.name instead of @.title.

As for passing in a null, you could check the length of the textbox text, and if it's 0, send DBValue instead of the Text property.

|||

Try

IF @.Name IS NULL SET @.Name = ''

IF DATALENGTH(@.Name) > 0
SELECT * FROM tblQuestions WHERE Title LIKE '%'+@.Name+'%'
ELSE
SELECT * FROM tblQuestions

|||

SELECT * FROM tblQuestions WHERE Title LIKE '%' + @.Name + '%'

ConvertEmptyStringToNull must be set to False!

sql

Passing mdx parameters to RS through URL (SharePoint Integrated Mode)

Hi there,

I'm running MOSS 2007 in Integrated Mode, and I have a number of reports that are deployed to the Reports Library, and are working just fine when you go there and run them interactively.

Now however, I need to address the report via a URL, and pass it a parameter. Just to add a bit more pain, the report is in mdx, and expects an mdx parameter.

Here is the parameter "prm_cost_centre":

[DIM LEARNER AIM].[COST CENTRE DESC].&[Business]

Here is the URL of the report:

http://vmmoss:88/ReportsLibrary/Report1.rdl

(If you hit that URL as is, it runs fine, using the default parameter)

Now, I know that we have to escape the ampersand, so I'm thinking that this URL should do it.

http://vmmoss:88/ReportsLibrary/Report1.rdl&rs:Command=Render&prm_cost_centre=[DIM LEARNER AIM].[COST CENTRE DESC].%26[Business]

But it doesn't - It throws a 400 Bad Request error.

I know I must be very close, but I just can't work out what URL I need to pass in order to get the damn thing to run.

Any ideas?

I don't have very much experience with MDX params, but I might recommend escaping the periods or possibly the [brackets] too. Sorry I can't offer much more advice than that, but as in any debug scenario, try truncating the param string until you get something that works, then go from there!

|||

It's not possible - you can't pass parameters through the URL when running Reporting Services in Integrated Mode.

http://technet.microsoft.com/en-us/library/bb326290.aspx

How lame is that? All of a sudden, Integrated Mode becomes...worthless to most people, surely.

Passing mdx parameters to RS through URL (SharePoint Integrated Mode)

Hi there,

I'm running MOSS 2007 in Integrated Mode, and I have a number of reports that are deployed to the Reports Library, and are working just fine when you go there and run them interactively.

Now however, I need to address the report via a URL, and pass it a parameter. Just to add a bit more pain, the report is in mdx, and expects an mdx parameter.

Here is the parameter "prm_cost_centre":

[DIM LEARNER AIM].[COST CENTRE DESC].&[Business]

Here is the URL of the report:

http://vmmoss:88/ReportsLibrary/Report1.rdl

(If you hit that URL as is, it runs fine, using the default parameter)

Now, I know that we have to escape the ampersand, so I'm thinking that this URL should do it.

http://vmmoss:88/ReportsLibrary/Report1.rdl&rs:Command=Render&prm_cost_centre=[DIM LEARNER AIM].[COST CENTRE DESC].%26[Business]

But it doesn't - It throws a 400 Bad Request error.

I know I must be very close, but I just can't work out what URL I need to pass in order to get the damn thing to run.

Any ideas?

I don't have very much experience with MDX params, but I might recommend escaping the periods or possibly the [brackets] too. Sorry I can't offer much more advice than that, but as in any debug scenario, try truncating the param string until you get something that works, then go from there!

|||

It's not possible - you can't pass parameters through the URL when running Reporting Services in Integrated Mode.

http://technet.microsoft.com/en-us/library/bb326290.aspx

How lame is that? All of a sudden, Integrated Mode becomes...worthless to most people, surely.

sql

Passing DB as parameter to stored procedure

I'm working with an application that has multiple databases for different
clients. I have a stored procedure that I would like to use for all of
these databases that is stored in a separate database. In the past, I have
passed the database name to the stored procedure and used dynamic sql to
build the select statements. In this case, I need to select to a temporary
table and then have a second query that uses the data in the temporary
table. With dynamic sql, the temporary table is removed after the first
query finishes, so this approach won't work.
Is there a way to switch databases in a stored procedure? In the foxpro
days, I think there was a Set Database procedure that would allow you to do
this.
TIA,
JohnSee: http://www.sommarskog.se/dynamic_sql.html
There is a section which explains getting data from another database.
Anithsql

Passing data from a sub-report to the main report

Hi,

I'm working with SRS2000.
I have a report with a sub-report.
I need to pass data from the sub-report to the main report.

How to do this ?

Ok, after sending messages to SRS specialists, it seems that there is no answer to this question...

It is impossible to do that with Reporting Services 2000!

Crystal Report 1 - SSRS 0.

|||

That's correct - you can't pass parameters from a subreport to the main report.

When I was using Crystal reports I used a lot of subreports. However, with SSRS, for many of the reports that used to require a subreport, I now use stored procedures - so I can gather all of the data, then create a sql select statement that outputs the data - all inside the stored procedure

|||

jamvir:

That's correct - you can't pass parameters from a subreport to the main report.

When I was using Crystal reports I used a lot of subreports. However, with SSRS, for many of the reports that used to require a subreport, I now use stored procedures - so I can gather all of the data, then create a sql select statement that outputs the data - all inside the stored procedure

Right, I use the same technic so this is workaround.

|||

Pluginbaby:

jamvir:

That's correct - you can't pass parameters from a subreport to the main report.

When I was using Crystal reports I used a lot of subreports. However, with SSRS, for many of the reports that used to require a subreport, I now use stored procedures - so I can gather all of the data, then create a sql select statement that outputs the data - all inside the stored procedure

Right, I use the same technic so this is workaround.

Is it a workaround when it becomes more efficient?

passing boolean to stored proc as SQLDBtype.bit not working

Hi I was hoping that someone might be able to help me with this.

I'm trying to figure out why my VB.net code below generates 0 or 1 but doesn't insert it when I can execute my stored procedure with: exec sp 0

myParm = myCommand.Parameters.Add("@.bolProMembCSNM", SqlDbType.Bit)
myParm.Value = IIf(CBool(rblProMembCSNM.SelectedItem.Value) = True, 1, 0)

I've tried everything I used to use with Classic ASP and am stumped now.
Any ideas? I will have to do this for numerous controls on my pages.

Thanks in advance for any advice.If you can execute the stored procedure with bit value in database, that means the value passed from application is not right. You may check the input value by inserting into a temp. table or as a return value to the calling app.|||thank you, that is what I thought.

I have printed out the value of the param in the trace and it is 0 which I find odd as that should be accepted as a valid bit, unless I'm totally missing something.

Is there not a way to pass 'true' or 'false' to the SQLDBtype.bit and have SQL Server convert it to 1 or 0?

still working on it here.
thanks again for your input|||What kind of control is "rblProMembCSNM"?|||thank you,

I took your advice and created a temp table and insert all of my values, after all that it was a different parameter I was passing and I was not escaping the ' ...... very frustrating to find that out after everything but I did learn quite a bit in my research.

Passing a value to another stored procedure...need urgent help

Hi i have been working on these stored procedures for what seems like ages now and i just cant understand why they dont work. I have followed many tutorials and think i have the correct synat but i jus keep getting errors.

Basically, i have SPOne and SPTwo - SPOne is writing to a table called Publication that has PublicationID as its PK (auto generated). SPtwo needs to get this PublicationID from SPOne and use it to insert rows into a second table PublicationAuthors - the PublicationID is hence a FK in the table PublicationAuthors.

The error i get is: Procedure or function 'SPOne' expects parameter '@.publicationID', which was not supplied. Cannot insert the value NULL into column 'publicationID', table .dbo.PublicationAuthors'; column does not allow nulls. INSERT fails.

SPOne is as follows:

ALTER PROCEDUREdbo.StoredProcedureOne @.typeIDsmallint=null, @.titlenvarchar(MAX)=null, @.publicationIDsmallint OUTPUTASBEGINSET NOCOUNT ON INSERT INTOPublication (typeID, title) VALUES(@.typeID, @.title) SELECT@.publicationID =scope_identity()

END

and SPtwo is as follows:

ASDECLARE@.NewpublicationIDIntEXECStoredProcedureOne @.NewpublicationID =OUTPUTSET@.publicationID = @.NewpublicationIDINSERT INTOPublicationAuthors (publicationID, authorID)VALUES(@.publicationID, @.authorID)

SELECT@.NewpublicationID

Thanks

Gemma

Is there any reason for separating them instead of putting the INSERTs into one proc?

|||

For SP 2, try this:

exec StoredProcedureOne null, null, @.publicationId = @.NewpublicationID output

|||

Thanks for your responses

Mark - it doesnt seem to like the syntax when putting the nulls in?

Dinakar - I have tried in one procedure but had trouble with arrays as i need to pass in multiple values from listbox for the second insert (publicationAuthors) so i gave up and wanted to try this way instead...and now im still stuck :(

|||

What exactly is the error when you use nulls?

Try this, for grins:

exec StoredProcedureOne @.typeId = 0, @.title='test', @.publicationId = @.NewpublicationID output

|||

with the nulls i just get 'Incorrect Syntax near @.publicationID'

with your last selection - exec StoredProcedureOne @.typeId = 0, @.title='test', @.publicationId = @.NewpublicationID output

i get the error:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Publication_PublicationType". The conflict occurred in database "C:\INETPUB\WWWROOT\SOSYM WEBSITE\APP_DATA\SOSYM DATABASE.MDF", table "dbo.PublicationType", column 'typeID'. Cannot insert the value NULL into column 'publicationID', table 'C:\INETPUB\WWWROOT\SOSYM WEBSITE\APP_DATA\SOSYM DATABASE.MDF.dbo.PublicationAuthors'; column does not allow nulls. INSERT fails

but i shouldnt be referencing the typeID from the PublicationType Table this doesnt get inserted into the PublicaitonAuthors table at all.

|||

Ok, but the good news is that the stored proc is called correctly

The insert probably failed because publicationId wasn't defined as an identity column, and it wasn't part of the insert statement.

|||

but the publicationID isdefined as the identity column in proc one and its part of the insert statement in proc 2 ?

|||

Hi,

From the error you provided, basically it has to do with putting invalid data into a column that references the data in the primary (or unique) key of another table, or what we can say that the value you are going to insert into the foreign key field doesn't exist in the corresponding filed of the parent table.

In order to have a test, you can split your stored procedure into two parts, first to check if the inserting to the parent table can work successfully. If so, we can know is issue is related to the parameter passing between these two procs.

After that ,try the following code:

exec StoredProcedureOne @.typeId = 0, @.title='test', @.publicationIdoutput INSERT INTO PublicationAuthors (publicationID, authorID)VALUES (@.publicationID, @.authorID)SELECT @.publicationId

Thanks.

Passing a Simple Parameter....Help!

Hello and thanks for the help.
I am trying to pass a simple report parameter to a report and cannot
figure out why it is not working.
I run this in Start - Run
The report runs but it will not use the parameter.
http://twsql/Reports/Pages/Report.aspx?ItemPath=%2fPlant%2fPlant+-+Saw+Planning&rs:Command=Render&JobID=55029
The Data Source uses this parameter to return the correct result set.
I set the data type on the parameter to int.
Any Ideas?
Thanks, Leocan you be a little clear on your question please
"cte25117@.centurytel.net" wrote:
> Hello and thanks for the help.
> I am trying to pass a simple report parameter to a report and cannot
> figure out why it is not working.
> I run this in Start - Run
> The report runs but it will not use the parameter.
> http://twsql/Reports/Pages/Report.aspx?ItemPath=%2fPlant%2fPlant+-+Saw+Planning&rs:Command=Render&JobID=55029
> The Data Source uses this parameter to return the correct result set.
> I set the data type on the parameter to int.
> Any Ideas?
> Thanks, Leo
>|||1. Have you defined the parameter in the Designer. If Yes what is the "Name"
2. In the URL, you should specify the same name as you define in the designer.
If the above 2 are taken care of, then you are fine and it should work..
"RP" wrote:
> can you be a little clear on your question please
> "cte25117@.centurytel.net" wrote:
> > Hello and thanks for the help.
> >
> > I am trying to pass a simple report parameter to a report and cannot
> > figure out why it is not working.
> >
> > I run this in Start - Run
> >
> > The report runs but it will not use the parameter.
> >
> > http://twsql/Reports/Pages/Report.aspx?ItemPath=%2fPlant%2fPlant+-+Saw+Planning&rs:Command=Render&JobID=55029
> >
> > The Data Source uses this parameter to return the correct result set.
> >
> > I set the data type on the parameter to int.
> >
> > Any Ideas?
> >
> > Thanks, Leo
> >|||Suresh,
The Parameter is in the query as follows
WHERE (TWN$Job.[HB Job ID] = @.JobID)
The Parameter name in the report is the same. (Default)
I know it should work but for some simple reason it is not. It makes no
sense. Is there some setting I have to set in reporting services to
allow this? I have SP2.
http://twsql/Reports/Pages/Report.aspx?ItemPath=%2fPlant%2fPlant+-+Saw+Planning&rs:Command=Render
This is all should have to add to the string correct?
&JobID=55029
Thanks, Leo
Suresh wrote:
> 1. Have you defined the parameter in the Designer. If Yes what is the "Name"
> 2. In the URL, you should specify the same name as you define in the designer.
> If the above 2 are taken care of, then you are fine and it should work..
> "RP" wrote:
>
>>can you be a little clear on your question please
>>"cte25117@.centurytel.net" wrote:
>>
>>Hello and thanks for the help.
>>I am trying to pass a simple report parameter to a report and cannot
>>figure out why it is not working.
>>I run this in Start - Run
>>The report runs but it will not use the parameter.
>>http://twsql/Reports/Pages/Report.aspx?ItemPath=%2fPlant%2fPlant+-+Saw+Planning&rs:Command=Render&JobID=55029
>>The Data Source uses this parameter to return the correct result set.
>>I set the data type on the parameter to int.
>>Any Ideas?
>>Thanks, Leo|||Leo,
Here's the syntax through URL:
"&JobID=" + <variableName>
Tell me what is the error that you are getting...
"cte25117@.centurytel.net" wrote:
> Suresh,
> The Parameter is in the query as follows
> WHERE (TWN$Job.[HB Job ID] = @.JobID)
> The Parameter name in the report is the same. (Default)
> I know it should work but for some simple reason it is not. It makes no
> sense. Is there some setting I have to set in reporting services to
> allow this? I have SP2.
> http://twsql/Reports/Pages/Report.aspx?ItemPath=%2fPlant%2fPlant+-+Saw+Planning&rs:Command=Render
>
> This is all should have to add to the string correct?
> &JobID=55029
> Thanks, Leo
>
> Suresh wrote:
> > 1. Have you defined the parameter in the Designer. If Yes what is the "Name"
> > 2. In the URL, you should specify the same name as you define in the designer.
> >
> > If the above 2 are taken care of, then you are fine and it should work..
> >
> > "RP" wrote:
> >
> >
> >>can you be a little clear on your question please
> >>
> >>"cte25117@.centurytel.net" wrote:
> >>
> >>
> >>Hello and thanks for the help.
> >>
> >>I am trying to pass a simple report parameter to a report and cannot
> >>figure out why it is not working.
> >>
> >>I run this in Start - Run
> >>
> >>The report runs but it will not use the parameter.
> >>
> >>http://twsql/Reports/Pages/Report.aspx?ItemPath=%2fPlant%2fPlant+-+Saw+Planning&rs:Command=Render&JobID=55029
> >>
> >>The Data Source uses this parameter to return the correct result set.
> >>
> >>I set the data type on the parameter to int.
> >>
> >>Any Ideas?
> >>
> >>Thanks, Leo
> >>
>|||Suresh,
This is exaclty what I put in through URL:
Tried this:
http://twsql/Reports/Pages/Report.aspx?ItemPath=%2fPlant%2fPlant+-+Saw+Planning&rs:Command=Render&JobID=55029
Tried this:
http://twsql/Reports/Pages/Report.aspx?ItemPath=%2fPlant%2fPlant+-+Saw+Planning&rs:Command=Render&JobID+55029
Tried this:
http://twsql/Reports/Pages/Report.aspx?ItemPath=%2fPlant%2fPlant+-+Saw+Planning&JobID+55029
Tried this:
http://twsql/Reports/Pages/Report.aspx?ItemPath=%2fPlant%2fPlant+-+Saw+Planning&rs:Command=Render&"JobID"+55029
Tried this:
http://twsql/Reports/Pages/Report.aspx?ItemPath=%2fPlant%2fPlant+-+Saw+Planning&rs:Command=Render&"JobID=55029"
Tried this:
JobID=55029 - JobID is the ParameterName - 55029 is the value
I do not get an error. The report returns nothing. It is still showing
the Prompt for the paramter JobID. It is as if it will not accept a
parameter.
I am trying to return the report based on the JobID = 55029
Suresh wrote:
> Leo,
> Here's the syntax through URL:
> "&JobID=" + <variableName>
> Tell me what is the error that you are getting...
>
> "cte25117@.centurytel.net" wrote:
>
>>Suresh,
>>The Parameter is in the query as follows
>>WHERE (TWN$Job.[HB Job ID] = @.JobID)
>>The Parameter name in the report is the same. (Default)
>>I know it should work but for some simple reason it is not. It makes no
>>sense. Is there some setting I have to set in reporting services to
>>allow this? I have SP2.
>>http://twsql/Reports/Pages/Report.aspx?ItemPath=%2fPlant%2fPlant+-+Saw+Planning&rs:Command=Render
>>
>>This is all should have to add to the string correct?
>>&JobID=55029
>>Thanks, Leo
>>
>>Suresh wrote:
>>1. Have you defined the parameter in the Designer. If Yes what is the "Name"
>>2. In the URL, you should specify the same name as you define in the designer.
>>If the above 2 are taken care of, then you are fine and it should work..
>>"RP" wrote:
>>
>>can you be a little clear on your question please
>>"cte25117@.centurytel.net" wrote:
>>
>>Hello and thanks for the help.
>>I am trying to pass a simple report parameter to a report and cannot
>>figure out why it is not working.
>>I run this in Start - Run
>>The report runs but it will not use the parameter.
>>http://twsql/Reports/Pages/Report.aspx?ItemPath=%2fPlant%2fPlant+-+Saw+Planning&rs:Command=Render&JobID=55029
>>The Data Source uses this parameter to return the correct result set.
>>I set the data type on the parameter to int.
>>Any Ideas?
>>Thanks, Leo
>>|||Suresh and Leo,
We are attempting to do the same exact thing and it isn't working. We have:
http://server-1/Reports/Pages/Report.aspx?ItemPath=%2fMarkListingbyStudent%2fMarkListingbyStudent&rs:Command=Render&calendarID=155
The report doesnt error. It just shows all the parameters and is waiting
for us to type in the calendarID instead of inserting 153 (what we are
passing through and want it to use).
Has anyone resolved this problem? Please help!!!!!!!! This is urgent and I
just don't understand why it isn't working. It appears we have everything
set up the way the documentation recommends. Is there anyone that has seen
this actually work?
Thank you for your help! :)
Sharlyn
"cte25117@.centurytel.net" wrote:
> Suresh,
> This is exaclty what I put in through URL:
> Tried this:
> http://twsql/Reports/Pages/Report.aspx?ItemPath=%2fPlant%2fPlant+-+Saw+Planning&rs:Command=Render&JobID=55029
> Tried this:
> http://twsql/Reports/Pages/Report.aspx?ItemPath=%2fPlant%2fPlant+-+Saw+Planning&rs:Command=Render&JobID+55029
> Tried this:
> http://twsql/Reports/Pages/Report.aspx?ItemPath=%2fPlant%2fPlant+-+Saw+Planning&JobID+55029
> Tried this:
> http://twsql/Reports/Pages/Report.aspx?ItemPath=%2fPlant%2fPlant+-+Saw+Planning&rs:Command=Render&"JobID"+55029
> Tried this:
> http://twsql/Reports/Pages/Report.aspx?ItemPath=%2fPlant%2fPlant+-+Saw+Planning&rs:Command=Render&"JobID=55029"
> Tried this:
> JobID=55029 - JobID is the ParameterName - 55029 is the value
> I do not get an error. The report returns nothing. It is still showing
> the Prompt for the paramter JobID. It is as if it will not accept a
> parameter.
> I am trying to return the report based on the JobID = 55029
>
> Suresh wrote:
> > Leo,
> >
> > Here's the syntax through URL:
> > "&JobID=" + <variableName>
> >
> > Tell me what is the error that you are getting...
> >
> >
> > "cte25117@.centurytel.net" wrote:
> >
> >
> >>Suresh,
> >>
> >>The Parameter is in the query as follows
> >>WHERE (TWN$Job.[HB Job ID] = @.JobID)
> >>The Parameter name in the report is the same. (Default)
> >>
> >>I know it should work but for some simple reason it is not. It makes no
> >>sense. Is there some setting I have to set in reporting services to
> >>allow this? I have SP2.
> >>
> >>http://twsql/Reports/Pages/Report.aspx?ItemPath=%2fPlant%2fPlant+-+Saw+Planning&rs:Command=Render
> >>
> >>
> >>This is all should have to add to the string correct?
> >>
> >>&JobID=55029
> >>
> >>Thanks, Leo
> >>
> >>
> >>Suresh wrote:
> >>
> >>1. Have you defined the parameter in the Designer. If Yes what is the "Name"
> >>
> >>2. In the URL, you should specify the same name as you define in the designer.
> >>
> >>If the above 2 are taken care of, then you are fine and it should work..
> >>
> >>"RP" wrote:
> >>
> >>
> >>
> >>can you be a little clear on your question please
> >>
> >>"cte25117@.centurytel.net" wrote:
> >>
> >>
> >>
> >>Hello and thanks for the help.
> >>
> >>I am trying to pass a simple report parameter to a report and cannot
> >>figure out why it is not working.
> >>
> >>I run this in Start - Run
> >>
> >>The report runs but it will not use the parameter.
> >>
> >>http://twsql/Reports/Pages/Report.aspx?ItemPath=%2fPlant%2fPlant+-+Saw+Planning&rs:Command=Render&JobID=55029
> >>
> >>The Data Source uses this parameter to return the correct result set.
> >>
> >>I set the data type on the parameter to int.
> >>
> >>Any Ideas?
> >>
> >>Thanks, Leo
> >>
> >>
>|||Two examples for you depending on whether you are creating the URL yourself
from your own app or if you are using the jump to URL.
Jump to URL use this:
The below example specifies the directory starting from the root. I have
this because this particular report can reside in a different directory than
the report I am jumping to. If it is in the same directory you can just not
worry about specifying the directory. A couple of others things to note, I
use the globals variable so this will work regardless of where I deploy it.
Also, note that parameter values are case sensitive so you must match it
exactly or you will end up without values in your parameter fields. Also
note that you can add to this URL a command to tell it to hide the parameter
toolbar. Anyway, this should get you started.
=Globals!ReportServerUrl & "?/Inventory/Similar Loads&Manifest=" &
Fields!manifstdocno.Value & "&WasteIDNum=" & Fields!wasteidnum.Value
Creating your own URL use this:
Here is a working URL. The folder is called Demo. The report is called
Sales Order 2 Two parameters StartDate and EndDate
Note that parameters are case sensitive. The %20 is the encoding for space.
Rendering is the default so you can leave it off the line.
Try to match my pattern here and hopefully it will work for you. It defaults
to rendering to HTML so you can leave that off as well.
http://YourServerName/ReportServer?/Demo/Sales%20Order%20Detail%202&StartDate=9/1/2003&EndDate=9/2/2003--Bruce Loehle-CongerMVP SQL Server Reporting Services"SharinDenver" <SharinDenver@.discussions.microsoft.com> wrote in messagenews:84E2CB92-0349-42B2-91C3-B3131B7301FD@.microsoft.com...> Suresh and Leo,>> We are attempting to do the same exact thing and it isn't working. Wehave:>>http://server-1/Reports/Pages/Report.aspx?ItemPath=%2fMarkListingbyStudent%2fMarkListingbyStudent&rs:Command=Render&calendarID=155>> The report doesnt error. It just shows all the parameters and is waiting> for us to type in the calendarID instead of inserting 153 (what we are> passing through and want it to use).>> Has anyone resolved this problem? Please help!!!!!!!! This is urgent andI> just don't understand why it isn't working. It appears we have everything> set up the way the documentation recommends. Is there anyone that hasseen> this actually work?>> Thank you for your help! :)>> Sharlyn>> "cte25117@.centurytel.net" wrote:>> Suresh,>> This is exaclty what I put in through URL:>> Tried this:>>http://twsql/Reports/Pages/Report.aspx?ItemPath=%2fPlant%2fPlant+-+Saw+Planning&rs:Command=Render&JobID=55029>> Tried this:>>http://twsql/Reports/Pages/Report.aspx?ItemPath=%2fPlant%2fPlant+-+Saw+Planning&rs:Command=Render&JobID+55029>> Tried this:>>http://twsql/Reports/Pages/Report.aspx?ItemPath=%2fPlant%2fPlant+-+Saw+Planning&JobID+55029>> Tried this:>>http://twsql/Reports/Pages/Report.aspx?ItemPath=%2fPlant%2fPlant+-+Saw+Planning&rs:Command=Render&"JobID"+55029>> Tried this:>>>">http://twsql/Reports/Pages/Report.aspx?ItemPath=%2fPlant%2fPlant+-+Saw+Planning&rs:Command=Render&"JobID=55029">> Tried this:>> JobID=55029 - JobID is the ParameterName - 55029 is the value>> I do not get an error. The report returns nothing. It is still showing>> the Prompt for the paramter JobID. It is as if it will not accept a>> parameter.>> I am trying to return the report based on the JobID = 55029>> Suresh wrote:>> > Leo,>> >> > Here's the syntax through URL:>> > "&JobID=" + <variableName>> >> > Tell me what is the error that you are getting...>> >> >> > "cte25117@.centurytel.net" wrote:>> >> >> >>Suresh,>> >> >>The Parameter is in the query as follows>> >>WHERE (TWN$Job.[HB Job ID] = @.JobID)>> >>The Parameter name in the report is the same. (Default)>> >> >>I know it should work but for some simple reason it is not. It makesno>> >>sense. Is there some setting I have to set in reporting services to>> >>allow this? I have SP2.>> >>http://twsql/Reports/Pages/Report.aspx?ItemPath=%2fPlant%2fPlant+-+Saw+Planning&rs:Command=Render>> >> >> >>This is all should have to add to the string correct?>> >> >>&JobID=55029>> >> >>Thanks, Leo>> >> >> >>Suresh wrote:>> >> >>1. Have you defined the parameter in the Designer. If Yes what is the"Name">> >> >>2. In the URL, you should specify the same name as you define in thedesigner.>> >> >>If the above 2 are taken care of, then you are fine and it shouldwork..>> >> >>"RP" wrote:>> >> >> >> >>can you be a little clear on your question please>> >> >>"cte25117@.centurytel.net" wrote:>> >> >> >> >>Hello and thanks for the help.>> >>> >>I am trying to pass a simple report parameter to a report and cannot>> >>figure out why it is not working.>> >>> >>I run this in Start - Run>> >>> >>The report runs but it will not use the parameter.>> >>>>http://twsql/Reports/Pages/Report.aspx?ItemPath=%2fPlant%2fPlant+-+Saw+Planning&rs:Command=Render&JobID=55029>> >>> >>The Data Source uses this parameter to return the correct resultset.>> >>> >>I set the data type on the parameter to int.>> >>> >>Any Ideas?>> >>> >>Thanks, Leo>> >>> >>

Passing a list of numbers to a stored procudure, having a size more than 8000 characters

Hi..

I m working on MS SQL Server 2000.

I am trying to pass a list of numbers to a stored procedure to be used with 'IN()' statement.

I was doing something like..

Create Procedure proc

(

@.Items varchar(100) List of numbers

)

AS Begin

Declare @.SQL varchar(8000)

Set @.SQL =

'

Select Query......

Where products IN (' + @.items + ') '

'

Exec (@.SQL)

This stored procedure is working fine, but when i m adding more required stuff to that, the size exceeds 8000, & it gives the error "Invalid operator for data type. Operator equals add, type equals text."

Can any1 please help me out with this ASAP?

In 2000, you can split the list over mulitple parms and then concat them together when you call the sporc like so:

create proc whatever

( @.Items1 varchar(8000),

@.Itmes2 varchar(8000),

...

)

Declare @.SqlStart Varchar(8000),

@.SqlEnd varchar(8000)

set @.SqlStart = '

Select bla

where products in ('

set SqlEnd = ')'

exec (@.sqlStart + @.Items1 + @.Items2 + ... + @.SqlEnd)

That should work although it tedious to split the items on the calling end. Where do the itmes come from, can;t you use a join or something to get at the records you need?

Regards GJ

|||

Change the datatype from Varchar to Text & execute the query directly dont use any variable.

Code Snippet

Create Procedure [Proc]

(@.Items text)

AS

Begin

Exec ('Select Query......

Where products IN (' + @.items + ')')

End

|||

Thanks GJ...

Works fine after splitting the varchar...

I was thinking about using joins as well but I guess its not possible the way my query is.

Anyways... Thanks for ur help both of Us

Passed SQL Parms Not Working

I have the following Query:
SELECT T12_R
FROM SUMMARY
WHERE (DECILE = '@.SQLDecile')
I have defined '@.SQLDecile' in the dataset parameters and 'SQLDecile' in the
report parameters. The dataset parameters use '@.SQLDecile' and points to the
report parameters variable 'SQLDecile'. When I run the query in the data tab
it prompts me for the parm and I enter it and it works.
I know the report parm of SQLDecile is getting the correct value because I
am displaying it on a form field also.
When I preview the chart it returns no data!!
Am I right to assume I use the same variable for the report parm and the
data parm and point the report parm to the data parm in the dataset parm
screen?
Thanks for any help!
GeorgeI'm surprized it works in the data tab. Try to remove apostropthes from
around @.SQLDecile and see if it works.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"George" <George@.discussions.microsoft.com> wrote in message
news:FF5222C0-C9D1-4B90-A537-7E957C0DB3FA@.microsoft.com...
>I have the following Query:
> SELECT T12_R
> FROM SUMMARY
> WHERE (DECILE = '@.SQLDecile')
> I have defined '@.SQLDecile' in the dataset parameters and 'SQLDecile' in
> the
> report parameters. The dataset parameters use '@.SQLDecile' and points to
> the
> report parameters variable 'SQLDecile'. When I run the query in the data
> tab
> it prompts me for the parm and I enter it and it works.
> I know the report parm of SQLDecile is getting the correct value because I
> am displaying it on a form field also.
> When I preview the chart it returns no data!!
> Am I right to assume I use the same variable for the report parm and the
> data parm and point the report parm to the data parm in the dataset parm
> screen?
> Thanks for any help!
> George
>|||Thanks. I'll try it. But SQL uses the apostropthes in a normal query. Why
would it not want them there? I thought RS would just replace my parm with
the data so the sql statement would be there. Or does RS add the apostropthes
on its own?
"Lev Semenets [MSFT]" wrote:
> I'm surprized it works in the data tab. Try to remove apostropthes from
> around @.SQLDecile and see if it works.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "George" <George@.discussions.microsoft.com> wrote in message
> news:FF5222C0-C9D1-4B90-A537-7E957C0DB3FA@.microsoft.com...
> >I have the following Query:
> >
> > SELECT T12_R
> > FROM SUMMARY
> > WHERE (DECILE = '@.SQLDecile')
> >
> > I have defined '@.SQLDecile' in the dataset parameters and 'SQLDecile' in
> > the
> > report parameters. The dataset parameters use '@.SQLDecile' and points to
> > the
> > report parameters variable 'SQLDecile'. When I run the query in the data
> > tab
> > it prompts me for the parm and I enter it and it works.
> >
> > I know the report parm of SQLDecile is getting the correct value because I
> > am displaying it on a form field also.
> >
> > When I preview the chart it returns no data!!
> >
> > Am I right to assume I use the same variable for the report parm and the
> > data parm and point the report parm to the data parm in the dataset parm
> > screen?
> >
> > Thanks for any help!
> >
> > George
> >
>
>|||It is all done by data provider. It substitutes parameter values in its own
way, without requiring apostrophes.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"George" <George@.discussions.microsoft.com> wrote in message
news:49E1C902-A3B0-4B53-B2F8-44A9CEE829CB@.microsoft.com...
> Thanks. I'll try it. But SQL uses the apostropthes in a normal query. Why
> would it not want them there? I thought RS would just replace my parm with
> the data so the sql statement would be there. Or does RS add the
> apostropthes
> on its own?
> "Lev Semenets [MSFT]" wrote:
>> I'm surprized it works in the data tab. Try to remove apostropthes from
>> around @.SQLDecile and see if it works.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "George" <George@.discussions.microsoft.com> wrote in message
>> news:FF5222C0-C9D1-4B90-A537-7E957C0DB3FA@.microsoft.com...
>> >I have the following Query:
>> >
>> > SELECT T12_R
>> > FROM SUMMARY
>> > WHERE (DECILE = '@.SQLDecile')
>> >
>> > I have defined '@.SQLDecile' in the dataset parameters and 'SQLDecile'
>> > in
>> > the
>> > report parameters. The dataset parameters use '@.SQLDecile' and points
>> > to
>> > the
>> > report parameters variable 'SQLDecile'. When I run the query in the
>> > data
>> > tab
>> > it prompts me for the parm and I enter it and it works.
>> >
>> > I know the report parm of SQLDecile is getting the correct value
>> > because I
>> > am displaying it on a form field also.
>> >
>> > When I preview the chart it returns no data!!
>> >
>> > Am I right to assume I use the same variable for the report parm and
>> > the
>> > data parm and point the report parm to the data parm in the dataset
>> > parm
>> > screen?
>> >
>> > Thanks for any help!
>> >
>> > George
>> >
>>|||It worked!! Thanks so much for your help!
"Lev Semenets [MSFT]" wrote:
> It is all done by data provider. It substitutes parameter values in its own
> way, without requiring apostrophes.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "George" <George@.discussions.microsoft.com> wrote in message
> news:49E1C902-A3B0-4B53-B2F8-44A9CEE829CB@.microsoft.com...
> > Thanks. I'll try it. But SQL uses the apostropthes in a normal query. Why
> > would it not want them there? I thought RS would just replace my parm with
> > the data so the sql statement would be there. Or does RS add the
> > apostropthes
> > on its own?
> >
> > "Lev Semenets [MSFT]" wrote:
> >
> >> I'm surprized it works in the data tab. Try to remove apostropthes from
> >> around @.SQLDecile and see if it works.
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "George" <George@.discussions.microsoft.com> wrote in message
> >> news:FF5222C0-C9D1-4B90-A537-7E957C0DB3FA@.microsoft.com...
> >> >I have the following Query:
> >> >
> >> > SELECT T12_R
> >> > FROM SUMMARY
> >> > WHERE (DECILE = '@.SQLDecile')
> >> >
> >> > I have defined '@.SQLDecile' in the dataset parameters and 'SQLDecile'
> >> > in
> >> > the
> >> > report parameters. The dataset parameters use '@.SQLDecile' and points
> >> > to
> >> > the
> >> > report parameters variable 'SQLDecile'. When I run the query in the
> >> > data
> >> > tab
> >> > it prompts me for the parm and I enter it and it works.
> >> >
> >> > I know the report parm of SQLDecile is getting the correct value
> >> > because I
> >> > am displaying it on a form field also.
> >> >
> >> > When I preview the chart it returns no data!!
> >> >
> >> > Am I right to assume I use the same variable for the report parm and
> >> > the
> >> > data parm and point the report parm to the data parm in the dataset
> >> > parm
> >> > screen?
> >> >
> >> > Thanks for any help!
> >> >
> >> > George
> >> >
> >>
> >>
> >>
>
>

pass user name as a parameter in a query

just getting started with my first db driven web project...

I am using a MySql database with membership and roles and got that working fine. I have tables containing details of courses that users are enrolled on etc. and want to display a list of courses for the user that is signed in, so he can continue lessons on the one of his choice.

How do I pass the users name to the database query for a DataList control. So far I have tried lots of variations of the following:

<asp:SqlDataSourceID="dsCourses"runat="server"ConnectionString="<%$ ConnectionStrings:xxx %>"
ProviderName="<%$ ConnectionStrings:xxx.ProviderName %>"
SelectCommand="SELECT c.CourseName FROM courses c, enrolments e
WHERE c.CourseID=e.CourseID AND e.Username='<% =User.Identity.Name %>'">
</asp:SqlDataSource>

<asp:DataListID="DataList1"runat="server"DataSourceID="dsCourses">
<ItemTemplate>
<asp:HyperLinkID="HyperLink1"runat="server"NavigateUrl="Lesson.aspx">'<%# Eval("CourseName") %>'</asp:HyperLink><br/>
</ItemTemplate>
</asp:DataList> </p>

However, the <% =User.Identity.Name %> doesn't work here (but it does elsewhere on the page) - not sure why?? The code works fine if I hard code a user name into the select statement.

Suggestions pleeeeeese!!

You need to use ProfileParameter for the SQL SelectParameters on SqlDataSource.

<asp:SqlDataSourceID="dsCourses"runat="server"ConnectionString="<%$ ConnectionStrings:xxx %>"
ProviderName="<%$ ConnectionStrings:xxx.ProviderName %>"
SelectCommand="SELECT c.CourseName FROM courses c, enrolments e
WHERE c.CourseID=e.CourseID AND e.Username=@.userName">
<SelectParameters>
<asp:ProfileParameter Name="userName" PropertyName="UserName" />
</SelectParameters>
</asp:SqlDataSource
where Name is the name of the parameter and PropertyName is the name of the profile property( current user)

Thanks