load a report with a parameter...

I have a parameter on my report.

the report show someting in the load event.

the code in Load_ event is something like this:
{
m_strCode=txtCode.Text;
repDoc.SetParameterValue("stringP",m_strCode);
repDoc.SetDataSource(dsCodes1);
repViewer.ReportSource-repDoc;
repViewer.RefreshReport();
}

also i reload the Data when i push the button,so the code is the same:

Button1_click(...)
{
m_strCode=txtCode.Text;
repDoc.SetParameterValue("stringP",m_strCode);
repDoc.SetDataSource(dsCodes1);
repViewer.ReportSource=repDoc;
repViewer.RefreshReport();
}
also there is 2 lines in the repViewer_ReportRefresh(....)
{
repDoc.SetParameterValue("stringP",m_strCode);
repViewer.ReportSource=repDoc;
}

The problem is that,if i put this code in Load_ event too as shown above, because i want to show the data with that parameter on report, from the start, it still ask me to gave to the report the parameter.WHY?

ThanksYou need to supply the parameter value if any. Otherwise you have to suppress the popup by setting enable popup property to false|||I have supplied the value, if you follow closely the code, at the beginning of the message, i wrote, the LOAD event, where i give the parameter value.so even if i suply value, still ask me,for value.this was the problem! why?|||If you supply parameter values, you wont be prompted to give them. Open the report and do verify database

listbox contents to sql stored procedure command parameter?

Hello,

Stuck in a spot and hoping someone will nudge me in the right direction...

I'm trying to write to a sql db via a storedprocedure using a parameter. i'm pretty certain the below statement is causing the problem. but i'm not sure how to properly refer to it...

Dim connString As String
connString = "integrated security=false;user id=sa;server=HCENT1;database=LicenseRenewal;persist security info=False"
Dim myConnection As New SqlConnection(connString)
Dim myCommand As New SqlCommand("InsertPage1", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
' ......other (working) parameter statements.......
Dim parameterDates As New SqlParameter("@.Dates", SqlDbType.VarChar, 4000)
parameterDates.Value = Session("lstDates")
myCommand.Parameters.Add(parameterDates)
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

the session("lstDates") is the contents of lstDates.Items (listbox) from a previous page.
i'm guessing its not valid to refer to it as a varchar, can someone point me to the proper way to handle this?

tia
andyI would try Session("lstDates").ToString(), though I personally would try and debug it to see what exactly is in that session variable.

Are you getting an error message? If so, please give the EXACT message.|||thank you for the suggestion.

I've tried what you're saying and the table gets populated with the string:

"System.Web.UI.WebControls.ListItemCollection"

i took a stab in the dark and tried varbinary (sans .tostring) as the sqldatatype for the parameter/storedprocedure/columntype and i received:

Server Error in '/NET/LicenseRenewal' Application.
------------------------
Object must implement IConvertible.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidCastException: Object must implement IConvertible.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[InvalidCastException: Object must implement IConvertible.]
 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +196
 LicenseRenewal.W4.WriteDBPage1() in C:\Documents and Settings\andrzej\VSWebCache\www.aea13.org\NET\LicenseRenewal\W4.aspx.vb:453
 LicenseRenewal.W4.btnDONE_Click(Object sender, EventArgs e) in C:\Documents and Settings\andrzej\VSWebCache\www.aea13.org\NET\LicenseRenewal\W4.aspx.vb:158
 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
 System.Web.UI.Page.ProcessRequestMain() +1277

Seems it can't automatically convert a collection of listbox items into a sql column list, a string list (not one long string however) in the column would be sufficient.

i welcome/appreciate any further input.

thank you
andy|||forgot to say, the contents of the session("lstDates") are various quantities (less then 25 typically) of strings following the 'syntax' of "02/02/04 From: 08:00 AM To 09:00 AM". The string gets compiled from a text box and 2 other listboxes and added to the lstDates. I know the session("lstDates") holds valid data because i've used the session info to reload the listbox without problems

http://www.aea13.org/net/licenserenewal/w1p2.aspx is the page that has the listbox, the page following that is supposed to write to the database when someone clicks on the bottom button.

I took copies of these 2 pages out of the full application, spliced them together and have butchered the code to get rid of non (error) relevant functions. only the relevant parts work for this testing purpose, also the button is only writing the single parameter to a lone table with a lone column.

thanks again.|||ok, got tired of fighting with it so went around it...

Dim connString As String
connString = "............."
Dim myConnection As New SqlConnection(connString)
Dim myCommand As New SqlCommand("test1", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
Dim strTempString As String
Dim lstTempListbox As New ListBox
Dim arrTemparray As New ArrayList
Dim x As Integer
lstTempListbox.DataSource = Session("lstDates")
lstTempListbox.DataBind()
For x = 0 To lstTempListbox.Items.Count - 1
If strTempString = "" Then
strTempString = lstTempListbox.Items(x).ToString
Else
strTempString = strTempString + lstTempListbox.Items(x).ToString
End If
Next
Dim parameterDates As New SqlParameter("@.Dates", SqlDbType.VarChar, 4000)
parameterDates.Value = strTempString
myCommand.Parameters.Add(parameterDates)
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

the table gets the data nicely formatted into a single cell and i can spit it back out in the RTF without formatting issues.

hope it helps someone else :)
andy

Listbox

I have a report with a single parameter, named param1. The parameter is
a list box that accepts multiple values.
When I select a single value from a listbox, the report works fine, But
when I select more than one value, the stored procedure call fails
saying '[Query execution failed for data set 'XXX' Must decalare the
variable '@.param1'.]'
I initially assumed that the multiple values would be passed to my SP
in the form of a single comma-delimited varchar, but this does not
seems to be the case. How can I set up the stored procedure call to
take multiple values from a listbox? Do I need to do something special
in the SP to process the multiple values?Hi,
you will have to write your query like this here:
WHERE SomeColumn IN (@.parametername)
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||It is passed the way you suppose. But, try calling your stored procedure
yourself (not from Reporting Services). Manually pass it a comma separated
string for the parameter. It won't work. This is a stored procedure issue,
not a Reporting Services issue. If you have the query defined in RS you can
do like this: select * from sometable where somefield in (@.MyParam) but you
cannot do this if that statement is in a stored procedure.
What you can do is to have a string parameter that is passed as a multivalue
parameter and then change the string into a table.
This technique was told to me by SQL Server MVP, Erland Sommarskog
For example I have done this
select * from sometable where somefield in (select str from
charlist_to_table(@.MyParam,Default))
So note this is NOT an issue with RS, it is strictly a stored procedure
issue.
Here is the function:
CREATE FUNCTION charlist_to_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
END
GO
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"melishbd" <melissa@.hbdc.com> wrote in message
news:1169581326.626144.35060@.v45g2000cwv.googlegroups.com...
>I have a report with a single parameter, named param1. The parameter is
> a list box that accepts multiple values.
> When I select a single value from a listbox, the report works fine, But
> when I select more than one value, the stored procedure call fails
> saying '[Query execution failed for data set 'XXX' Must decalare the
> variable '@.param1'.]'
> I initially assumed that the multiple values would be passed to my SP
> in the form of a single comma-delimited varchar, but this does not
> seems to be the case. How can I set up the stored procedure call to
> take multiple values from a listbox? Do I need to do something special
> in the SP to process the multiple values?
>

list of strings passed into a parameter

I am trying to pass multiple values as parameters into my update command:

UPDATE tblUserDetails SET DeploymentNameID = 102 WHERE ((EmployeeNumber IN (@.selectedusersparam)));

I develop my parameter (@.selectedusersparam) using the following subroutine:

PrivateSub btnAddUsersToDeployment_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles btnAddUsersToDeployment.Click

Dim iValAsInteger = 0

Dim SelectedCollectionAsString

SelectedCollection =""

If (lsbUsersAvail.Items).Count > 1Then

For iVal = 0To lsbUsersAvail.Items.Count - 1

If lsbUsersAvail.Items(iVal).Selected =TrueThen

SelectedCollection = SelectedCollection &"," & lsbUsersAvail.Items(iVal).Value

EndIf

Next

SelectedCollection = Mid(SelectedCollection, 2, Len(SelectedCollection))

Session.Item("SelectedCollectionSession") = SelectedCollection

SqlDataSource4.Update()

ltlUsersMessage.Text =String.Empty

'UPDATE tblUserDetails SET DeploymentNameID = @.DeploymentNameIDparam WHERE (EmployeeNumber IN (@.selectedusersparam))

'SqlDataSource4.UpdateCommand = "UPDATE tblUserDetails SET DeploymentNameID = @.DeploymentNameIDparam WHERE (EmployeeNumber IN (" + SelectedCollection + ")"

Else

ltlUsersMessage.Text ="Select users before adding to deployment. Hold Control for multiselect"

EndIf

EndSub

For some reason the query does not pass the parameters which are "21077679,22648722,22652940,21080617" into the query

I don't understand why.

hi,

could you debug your application, i want to know what actual query is being passed. you've for loop for some list, while debugging does it go inside this loop or not.

also if i am not wrong you've commented out following line

'SqlDataSource4.UpdateCommand = "UPDATE tblUserDetails SET DeploymentNameID = @.DeploymentNameIDparam WHERE (EmployeeNumber IN (" + SelectedCollection + ")".

seems like it is the cause as i dont see other line for setting updatecommand.

please check & let me know.

regards,

satish

|||

This works but does not use parameters:

ProtectedSub btnRemoveUsersFromDeployment_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles btnRemoveUsersFromDeployment.Click

Dim iVal2AsInteger = 0

Dim SelectedCollection2AsString

SelectedCollection2 =""

If (lstUsersToRemoveFromDeployment.Items).Count > 0Then

For iVal2 = 0To lstUsersToRemoveFromDeployment.Items.Count - 1

If lstUsersToRemoveFromDeployment.Items(iVal2).Selected =TrueThen

SelectedCollection2 = SelectedCollection2 &"," & lstUsersToRemoveFromDeployment.Items(iVal2).Value

EndIf

Next

SelectedCollection2 = Mid(SelectedCollection2, 2, Len(SelectedCollection2))

Session.Item("SelectedCollectionSession") = SelectedCollection2

SqlDataSourceInCurrentDeployment.UpdateCommand ="UPDATE tblUserDetails SET DeploymentNameID = null WHERE ((EmployeeNumber IN (" + SelectedCollection2 +")))"

SqlDataSourceInCurrentDeployment.Update()

Else

MsgBox("Please select user(s) first")

EndIf

EndSub

|||

hi,

i am not clear mate...in earlier post you were passing parameter for DeploymentNameID in update command now you've removed and used null!!!

i m confused, what is actual problem.

regards,

satish.

|||

I have used the same concept for two different situations.

In the last post I did I was removing the Deployment name ID created by the first update command.

cheers.

Ben.

List of parameters used together with "rs:" Parameter in SSRS 2000 and SSRS 2005

Hi,

Does anyone know of a link or list that has all the parameters for the "rs:" section of the URL access parameter, except for the ones in the Microsoft books?

Thanks,

Hi eduard,

Here's what you are looking for

Not sure whether you had seen this, but this is perhaps the most comprehensive for the rs and rc parameter.

http://msdn2.microsoft.com/en-us/library/ms152835.aspx

List as report parameter

Any hint how to build a report that prompts user to select multiple values
from a lookup table and uses the multiple values in WHRERE myfield IN
(<user-selected-list>) to select the data?
ThanksReporting Services does not provide this functionality ... supposedly coming
in a future release.
For now, you can just make the parameter a text box so the user can type in
a comma separated list ... then parse the parameter in the filter.
--
Shaun Beane, MCT, MCDBA, MCDST
http://dbageek.blogspot.com
"TheTechie" <TheTechie@.discussions.microsoft.com> wrote in message
news:5398BCA1-4839-46FC-8D40-1C4B81EE8B9E@.microsoft.com...
> Any hint how to build a report that prompts user to select multiple values
> from a lookup table and uses the multiple values in WHRERE myfield IN
> (<user-selected-list>) to select the data?
> Thanks|||Basically you can't - multi value lists are not natively support in the
current version.
You can however roll it yourself, either by dynamically building the sql
string to use JobID In (@.somecommadelimitedlist) or if you have some SQL
skills you can create a function in SQL Server to take a comma separated
list and return a table for use in a query.
Check this out:
http://www.windowsitpro.com/Article/ArticleID/26244/26244.html?Ad=1
--
Mary Bray [SQL Server MVP]
Please reply only to newsgroups
"TheTechie" <TheTechie@.discussions.microsoft.com> wrote in message
news:5398BCA1-4839-46FC-8D40-1C4B81EE8B9E@.microsoft.com...
> Any hint how to build a report that prompts user to select multiple values
> from a lookup table and uses the multiple values in WHRERE myfield IN
> (<user-selected-list>) to select the data?
> Thanks|||Chapter 11 of the book "Hitchhiker's Guide to SQL Server 2000 Reporting
Services" provides a work-round to have a multi-select pick list in the
parameter area.
It is quite well explained, starting out with a comma-separated textbox, but
involves some careful editing...
HTH