Passing null parameters via Query-String

I'm trying to call a report, passing my parameters via query-string...
I have one parameter that can receive null values...
Following Books Online, the syntax for null values is :isnull, giving the following sample of URL:
http://exampleWebServerName/reportserver?/foldercontainingreports/orders&division=mailorder®ion=west&sales:isnull
In my report, I shadowed this syntax, it looks like the following:
http://localhost/ReportServer?/ctr_es_reports/entrada_saida&dh_ini=2004-06-01&dh_fim=2004-07-20&func_cd_matricula:isnull
If I pass a common value to func_cd_matricula, like 1 or 2, it works fine, but with this isnull syntax, it gives me the following error:
"The path of the item '/ctr_es_reports/entrada_saida,func_cd_matricula:isnull' is not valid. The full path must be less than 260 characters long, must start with slash; other restrictions apply. Check the documentation for complete set of restrictions."
Does anybody know why this?
Thanks,
Rafa®BOL is incorrect.
Right syntax is
sales:isNull=true
Lev
http://blogs.msdn.com/levs
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rafa®" <Rafa®@.discussions.microsoft.com> wrote in message
news:4308595B-5A3C-4F4D-ADAD-D9288B71B926@.microsoft.com...
> I'm trying to call a report, passing my parameters via query-string...
> I have one parameter that can receive null values...
> Following Books Online, the syntax for null values is :isnull, giving the
> following sample of URL:
> http://exampleWebServerName/reportserver?/foldercontainingreports/orders&division=mailorder®ion=west&sales:isnull
> In my report, I shadowed this syntax, it looks like the following:
> http://localhost/ReportServer?/ctr_es_reports/entrada_saida&dh_ini=2004-06-01&dh_fim=2004-07-20&func_cd_matricula:isnull
> If I pass a common value to func_cd_matricula, like 1 or 2, it works fine,
> but with this isnull syntax, it gives me the following error:
> "The path of the item
> '/ctr_es_reports/entrada_saida,func_cd_matricula:isnull' is not valid. The
> full path must be less than 260 characters long, must start with slash;
> other restrictions apply. Check the documentation for complete set of
> restrictions."
> Does anybody know why this?
> Thanks,
> Rafa®sql

passing multiple values from a listbox into a stored procedure

hi i have a listbox with selectedmode = multiple, i am currently using this code in my code behind (c#) to call the storedprocedure within the datasource but its not working: Do i have to write specific code in c# to send the mulitple values through?

protectedvoid confButton_Click(object sender,EventArgs e)

{

try

{

foreach (ListItem itemin authorsListBox4.Items)

{

if (item.Selected)

{

AddConfSqlDataSource.Insert();

}

}

saveStatusLabel.Text ="Save Successfull: The above publication has been saved";

}

catch (Exception ex)

{

saveStatusLabel.Text ="Save Failed: The above publication failed to save" + ex.Message;

}

}

The code you posted looks right, as much as you've posted. You'll have to loop, check for selected, and add. You're on the right track.
One thing that jumps out right away is that I can't see where you're passing any argument into your AddConfSqlDataSource.Insert(); function.
Did you mean something like:

AddConfSqlDataSource.Insert(item);

?

|||

Code looks fine. You are already looping thru each selected item of your multi select list box. So just check the code in this method AddConfSqlDataSource.Insert()

There should be some problem in it that is causing you the issue. Or put the code here for us to take a look.

|||

hmmm if i pass in item to the datasource:AddConfSqlDataSource.Insert(item);

i recieve error: No overload for method 'Insert' takes '1' arguments

the parameters i am passing through the datasource to the SP look like this:

<asp:SqlDataSourceID="AddConfSqlDataSource"runat="server" ConnectionString="<%$ ConnectionStrings:SoSymConnectionString%>" InsertCommand="StoredProcedureTest2" InsertCommandType="StoredProcedure"> <InsertParameters> <asp:ControlParameterControlID="PubTypeDropDownList"Name="typeID" PropertyName="SelectedValue"Type="Int16"/> <asp:ControlParameterControlID="titleTextBox4"Name="title" PropertyName="Text"Type="String"/> <asp:ControlParameterControlID="authorsListBox4"Name="authorID" Type="String"/> </InsertParameters> </asp:SqlDataSource>

and my SP as follows: maybe i have a problem within my SP - looping?

ALTER PROCEDUREdbo.StoredProcedureTest2 @.publicationIDInt=null, @.typeIDsmallint=null, @.titlenvarchar(MAX)=null, @.authorIDsmallint=null ASBEGIN TRANSACTIONSET NOCOUNT ON DECLARE@.ERRORInt SET@.ERROR=0IF EXISTS(SELECT*FROMPublicationWHEREtitle = @.title)SELECT@.publicationID = (SELECTpublicationIDFROMPublicationWHEREtitle = @.title)ELSE BEGIN INSERT INTOPublication (typeID, title) VALUES(@.typeID, @.title) SET@.publicationID = @.@.IDENTITY--Obtain the ID of the created publication SET@.ERROR = @.@.ERROREND IF NOT EXISTS(SELECT*FROMPublicationAuthorsWHEREpublicationID = @.publicationIDANDauthorID = @.authorID)BEGIN INSERT INTOPublicationAuthors (publicationID, authorID)VALUES(@.publicationID, @.authorID) SET@.ERROR = @.@.ERROR END IF(@.ERROR<>0)ROLLBACK TRANSACTIONELSECOMMIT TRANSACTION

Sorry to post loads of code! ...

Thanks

Passing GUID into dtexec, invalid cast to a string variable. Solution?

I am getting an invalid cast specification when I call dtexec and try to /SET a user variable within the package that is defined as a string data type. Is there any solution to this? I have tried passing the GUID with {} w/o {} w/ '' w/ "" etc.. many variations... and still get an invalid cast specification. Is there a data type that I need to set the User variable to besides String? The User Variable is used to Select records from a SQL data source where the GUID is stored. I do not have an option of GUID data type for a User Variable.

Thanks for any help! Aaron B.

What does your SET look like, this works fine for me. I just set it, and check the value in a Script Task, just to ensure the value is being passed in OK.

/SET "\Package.Variables[StringVariable].Value";"{2B7045E0-F3D2-478a-BCC3-0E73858C59A8}"

|||

Thanks for your help i am using another field for the subquery which is not GUID and its working fine. Thanks for your help.

passing an int parameter

Hello,

This is the way I call report viewer in my asp.net application.

this.ReportViewer1.ServerUrl=serverUrl;

this.ReportViewer1.ReportPath=repName;

this.ReportViewer1.Parameters=Microsoft.Samples.ReportingServices.ReportViewer.multiState.False;

this.ReportViewer1.SetQueryParameter("MyName",myname);

this.ReportViewer1.SetQueryParameter("MyID",myID);

MyName is a string and MyID is an int. Without MyID parameter it is working fine, however, when I add MyID it does not work, so how can I pass an int parameter to reporting services.

Thanks,

Cast its type as an integer

Passing a session parameter to a report call

Hi

I've created my report with a parameter and a dependent cascading parameter, this all work fine when I preview the .rdl

It also works fine when I access it through my asp.net application.

When accessed the report via my asp,net application the report prompts the user for both parameters, which, when I enter them, also works fine.

I would like to call the report while passing the first parameter to it so that the user only has to select the second parameter, which is dependent on the first.

The first parameter is stored as a session variable, but I cannot figure out how to pass this as a parameter to the report.

I have embedded a report view as:

rsweb:reportviewer id="ReportViewer1" runat="server" processingmode="Remote" width="795px" Font-Names="Verdana" Font-Size="8pt" Height="764px" ShowExportControls="False" ShowFindControls="False" ShowRefreshButton="False" ShowParameterPrompts="true">
<ServerReport ReportPath="/SubSrvs Reports/SubscribedServices" DisplayName="Confirmation Invoices" />
</rsweb:reportviewer>

Is there a way to pass my session value as a parameter? If so where do I do this pls.

I am not using any code behind at the moment.

I have tried passing it in the url of the page I call e.g. Invoices.aspx?ClientId=?? and in thehttp://localhost/reportserver?ClientId=??? but no luck, oh yes I have also tried surpressing the ShowParameterPrompts of the report view, but again no luck

many thanks

string sessParam = Convert.ToString(Session["myparam"]);

RptParameters[0] =
new Microsoft.Reporting.WebForms.ReportParameter("ClientId", sessParam );

this.ReportViewer1.ServerReport.SetParameters(RptParameters);
this.ReportViewer1.ServerReport.Refresh();

Check a basic tutorial over here:

http://www.codeproject.com/sqlrs/ReportViewer2005.asp

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

muchas gracias! that worked a treat, used the C# equivalent and placed in the page load event with a !Page.IsPostBack.

also a very good articlehttp://www.codeproject.com/sqlrs/ReportViewer2005.asp

thanks again

Passing a date report parameter on the URL

I'm trying to call a report I created (and deployed to our dev server -
running reporting services 2005), while passing the parameters on the URL.
I think I'm very close, but it doesn't seem to like the format that the date
is in. However, I've tried everything I can think of (surrounding the date
with quotes, double quotes, pound symbols, and every combination of those I
could think of), and yet I'm still getting the error "The value provided for
the report parameter 'StartDate' is not valid for its type.
(rsReportParameterTypeMismatch) "
The URL I'm using is:
http://ssrsdev/ReportServer/Pages/ReportViewer.aspx?/CSR.Reports/UserLogReport&rs:Command=Render&PersonID='12345'&StartDate='06/11/2007'&EndDate='06/15/2007'
The two "date" parameters are obviously set up as date types (especially as
when I browse to the report without passing parameters, it allows me to pick
the dates for each parameter, rather than just allowing text entry).
Any ideas/suggestions?Scott,
Try to not use the ' " or # and just the value like
Start_Date=06/17/2007&End_Date=06/20/2007
Reeves
"Scott Lyon" wrote:
> I'm trying to call a report I created (and deployed to our dev server -
> running reporting services 2005), while passing the parameters on the URL.
> I think I'm very close, but it doesn't seem to like the format that the date
> is in. However, I've tried everything I can think of (surrounding the date
> with quotes, double quotes, pound symbols, and every combination of those I
> could think of), and yet I'm still getting the error "The value provided for
> the report parameter 'StartDate' is not valid for its type.
> (rsReportParameterTypeMismatch) "
>
> The URL I'm using is:
> http://ssrsdev/ReportServer/Pages/ReportViewer.aspx?/CSR.Reports/UserLogReport&rs:Command=Render&PersonID='12345'&StartDate='06/11/2007'&EndDate='06/15/2007'
>
> The two "date" parameters are obviously set up as date types (especially as
> when I browse to the report without passing parameters, it allows me to pick
> the dates for each parameter, rather than just allowing text entry).
>
> Any ideas/suggestions?

Pass Variables from Trigger to SP ?

Fairly new to triggers and stored procedures and so far I haven't had to pass any variables or even call an SP from a trigger. The problem I have is to pass a 'callid' variable from an insert trigger to a stored procedure.

The SP is to delete any rows that already exist that contain the passed 'callid' and then insert the new inserted data (using variables again hopefully).

Is this at all possible and if so what is the syntax for passing the variable from the trigger and then reading it into the stored procedure ?

TIASomething like:

CREATE TRIGGER trg_mytrigger ON tbl_my_table INSEAD OF INSERT
AS

DECLARE @.callid INT

SELECT @.callid = callid FROM inserted

--At this point, you can just to the delete from here, rather than calling a seperate sp_, so here are both ways:
EXEC sp_delete_rows_by_callid @.callid

--or
DELETE tbl_my_table WHERE callid = @.callid

--then do the insert - (you may want to be more explicit with field names here)

INSERT tbl_my_table
SELECT * FROM inserted

--end of trigger

sp_code:

CREATE PROCEDURE sp_delete_rows_by_callid
@.callid INT
AS
DELETE tbl_my_table WHERE callid = @.callid

-b|||Thanks bpdWork. I have modified the trigger as you suggested but I get an ODBC 3146 database error. Can you see where I may have got the syntax wrong. Thx.

CREATE TRIGGER trg_mytrigger ON callshistory INSTEAD OF INSERT
AS

DECLARE @.callid INT

SELECT @.callid = fkey FROM inserted as ins where ins.actiontext = 'hold' and ins.Subactiontext in ('completed','pending user')

DELETE hold_complete WHERE fkey = @.callid

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
insert hold_complete
select ins.AddedDT, ins.fkey, ins.actiontext,
ins.subactiontext, con.emailaddress, ca.loggeddt,
(con.forename + ' ' + con.surname) as contactname,
ca.summary, ca.notes,co.coordinator, co.coordinator,getdate(), ca.status,ca.lastsubaction,getdate(),ca.dateopened ,ca.companyname,getdate(),(null),ch.notes
FROM inserted as ins with (nolock)
join calls as ca with (nolock)on
ins.fkey = ca.callid
join contact as con with (nolock) on
ca.contactid = con.contactid
join company as co with (nolock) on
ca.companyid = co.companyid
join callshistory as ch with (nolock) on
ins.historyid = ch.historyid
where ins.actiontext = 'hold' and ins.Subactiontext in ('completed','pending user')|||I believe this error means Invalid Object Name, so the problem is probably one of your table names.

I'm not sure you can alias the "inserted" keyword. Try it without that.|||The syntax from SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED was used in my original trigger which allowed the duplicates so I know that bit works. I also tried the SP option you suggested and got no error message but no rows were deleted or inserted.

Can the inserted table be used outside of the original trigger for the SP ? When does it get deleted ?|||It's only around for the virtual instance the trigger is fired...once the trigger is done, it's gone...|||So should this work ? (BTW it don't.)

CREATE TRIGGER tr_DELETE_DUPES ON Hold_Complete
after insert AS

DECLARE @.callid varchar

SELECT @.callid = fkey FROM inserted

delete from hold_complete where addeddt <> (select max(addeddt)from hold_complete) and fkey = @.callid|||delete from hold_complete where addeddt NOT IN (select max(addeddt)from hold_complete) and fkey = @.callid|||I'm afraid that didn't delete the dupe rows either. Is there anyway that a message can be printed that will show the @.callid variable to make sure that's being captured properly|||You need to understand that the virtual tables will hold ALL of the affected rows...not just 1...your code will only grab the last one from the table...

you need to think set based...

And if you trying to DELETE dups AFTER the INSERT, I would think you would gewt a dup key error, and the trans would rollback and the trigger won't fire...

Assuming you have a pk contraint that is...

Also I would recommend removing the ISOLATION LEVEL code...

If you check @.@.ERROR in the sproc that is doing the INSERT you'll catch the error there...

You can then do an ipdate if you want to change the values...

I wouldn't do this in a trigger (Unless of course you don't have control over the DML, then I would)...|||CREATE TRIGGER tr_DELETE_DUPES ON Hold_Complete
after insert AS

DELETE
FROM hold_complete
INNER JOIN inserted
ON hold_complete.fkey = inserted.fkey
WHERE addeddt <> (select max(addeddt) from hold_complete)|||Brett

The hold_complete table is updated from an insert trigger on another table when certain criteria are met. This is unlikely to insert more than 1 row at a time. How would you suggest I go about deleting any previous entries with the same callid ?

bpd

It didn't like the join on a delete statement.|||My Bad:

DELETE hold_complete
FROM hold_complete
INNER JOIN inserted
ON hold_complete.fkey = inserted.fkey
WHERE addeddt <> (select max(addeddt) from hold_complete)|||Originally posted by Bracksboy
Brett

The hold_complete table is updated from an insert trigger on another table when certain criteria are met. This is unlikely to insert more than 1 row at a time. How would you suggest I go about deleting any previous entries with the same callid ?

bpd

It didn't like the join on a delete statement.

Cascading triggers?

Also it's not a matter of how likely an event can happen...it's whether it can or it can't...there are no colors here...it's either black or white...

It's not a matter of IF, it's a matter of WHEN

First, do you have a Primary Key contraint on CallId now?

If not, first find out what you're dealing with...

SELECT CallId, COUNT(*) FROM hold_complete GROUP BY CallId HAVING COUNT(*) > 1

If that returns nothing, you're in business...and just add the contraint, and you won't have to worry about the trigger at all...

If it does, then you need to sanatize the data, then add the contraint...|||Originally posted by Brett Kaiser

First, do you have a Primary Key contraint on CallId now?

If not, first find out what you're dealing with...

SELECT CallId, COUNT(*) FROM hold_complete GROUP BY CallId HAVING COUNT(*) > 1

If that returns nothing, you're in business...and just add the contraint, and you won't have to worry about the trigger at all...

If it does, then you need to sanatize the data, then add the contraint...

I don't have a constraint on callid and I don't think it will work with this. If I understand constraints correctly if I set one on callid it won't allow an insert where that callid already exists. Is that correct ?

The inserted data is not going to always have a unique callid so I just need to store the latest row of data and remove all previous rows with that callid. The rows that are inserted are tracking details from a call logging system that meet certain criteria and this can have multiple rows with the same callid. From this data I have a VB app that emails confirmation and then sets dates for chasers and automated closure. I need to delete the existing rows so only the latest info is used in the app.

It's all very new to me.|||You could always just do the delete first, then do an insert.|||but that's the whole point of a realtional database...

You should check to see if the row exists first...if it does grab it, and update it...

Then there's the problem if the row doesn't exist, you start enetering info, someone slides the key while you're typing and get it in...

You need to error handle and check @.@.Error to see if the execution was successful or not....

no MOO's about it...|||Thanks for your help guys but had a complete rethink and I have the application sorting the records I want using the following select statement in the ADO connection :

SELECT * FROM hold_complete a
WHERE exists (SELECT fkey FROM hold_complete b
GROUP BY fkey HAVING max(b.addeddt) = a.addeddt)
ORDER by fkey

Pass text (or, best, ntext) type value to an OLE Automation function call

Hello!
Is that possible to create a user-defined function, that would take value of
type text, and pass it to the OLE Automation function call?
The SP code I use to do OLE Aut. call, is as follows:
DECLARE @.object int
DECLARE @.hr int
DECLARE @.property nvarchar(255)
DECLARE @.src nvarchar(255), @.desc nvarchar(255)
DECLARE @.return nvarchar(255)
EXEC @.hr = sp_OACreate 'Blah.Something', @.object OUT
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
EXEC @.hr = sp_OAMethod @.object, 'myMeth', @.return OUT, 'Test string'
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
PRINT @.return
EXEC @.hr = sp_OADestroy @.object
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
****
I have currently working version, that uses char type, but I'd prefer to use
text or even ntext type. It seems like, that if it is not possible to pass
the text-type value to the OLE function call, I will be furced to pass a
record ID instead, and read the text value within the external code from the
same database, what actually sounds like risky way.
Thanks,
PavilsThere is no way to declare a local n/text variable. So, the answer is 'no'.
-oj
"Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
news:ufTfn5KTFHA.548@.tk2msftngp13.phx.gbl...
> Hello!
> Is that possible to create a user-defined function, that would take value
> of type text, and pass it to the OLE Automation function call?
> The SP code I use to do OLE Aut. call, is as follows:
> DECLARE @.object int
> DECLARE @.hr int
> DECLARE @.property nvarchar(255)
> DECLARE @.src nvarchar(255), @.desc nvarchar(255)
> DECLARE @.return nvarchar(255)
> EXEC @.hr = sp_OACreate 'Blah.Something', @.object OUT
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
> RETURN
> END
> EXEC @.hr = sp_OAMethod @.object, 'myMeth', @.return OUT, 'Test string'
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
> RETURN
> END
> PRINT @.return
> EXEC @.hr = sp_OADestroy @.object
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
> RETURN
> END
> ****
> I have currently working version, that uses char type, but I'd prefer to
> use text or even ntext type. It seems like, that if it is not possible to
> pass the text-type value to the OLE function call, I will be furced to
> pass a record ID instead, and read the text value within the external code
> from the same database, what actually sounds like risky way.
> Thanks,
> Pavils
>