Passing parameter from Asp.Net

Dear Sir,
How can reporting servicing receive parameter from Asp.net program?You could call a report via URL access and pass the parameter to the URL.
Refer to
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_urlaccess_2v74.asp
for details.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:uvABWtmaEHA.1840@.TK2MSFTNGP11.phx.gbl...
> Dear Sir,
> How can reporting servicing receive parameter from Asp.net program?
>|||Yes, via the SOAP API Render() method. Check
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_soapapi_service_lz_6x0z.asp
for an example.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"yoclee" <yoclee@.discussions.microsoft.com> wrote in message
news:886688CD-0C5F-41D4-88E8-8134AD61D579@.microsoft.com...
> Can we pass the parameter using others method instead of using URL?
> "Ravi Mumulla (Microsoft)" wrote:
> > You could call a report via URL access and pass the parameter to the
URL.
> > Refer to
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_urlaccess_2v74.asp
> > for details.
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > "ad" <ad@.wfes.tcc.edu.tw> wrote in message
> > news:uvABWtmaEHA.1840@.TK2MSFTNGP11.phx.gbl...
> > > Dear Sir,
> > > How can reporting servicing receive parameter from Asp.net program?
> > >
> > >
> >
> >
> >sql

Passing arrays to stored procedures

Dear all,

i want to know how i can pass multiple values in the form of arrays to a stored procedures.

the technique by which i pass multiple values to a stored procedure beginning along with declarations are as follows:

Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader()
 Me.cmdInsSlabHmst = New System.Data.OleDb.OleDbCommand()
 Me.OleDbConnection1 = New System.Data.OleDb.OleDbConnection()
 Me.cmdInsSlabDMst = New System.Data.OleDb.OleDbCommand()
 '
 'cmdInsSlabHmst
 '
 Me.cmdInsSlabHmst.CommandText = "PKGSLABHMST.INSSLABHMST"
 Me.cmdInsSlabHmst.CommandType = System.Data.CommandType.StoredProcedure
 Me.cmdInsSlabHmst.Connection = Me.OleDbConnection1
 Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iSLABDESC", System.Data.OleDb.OleDbType.VarChar, 50))
 Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iSLABUNIT", System.Data.OleDb.OleDbType.VarChar, 1))
 Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iREMARKS", System.Data.OleDb.OleDbType.VarChar))
 Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iSLABFROM", System.Data.OleDb.OleDbType.VarChar))
 Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iSLABRATE", System.Data.OleDb.OleDbType.VarChar))
 Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iNOOFRECORDS", System.Data.OleDb.OleDbType.Integer))
 '
 'OleDbConnection1
 '
 Me.OleDbConnection1.ConnectionString = CType(configurationAppSettings.GetValue("ConnectionString", GetType(System.String)), String)
'Passing multiple values to the procedure with the help of ~ sign
 Dim strCode As String
 Dim i As Integer
 'Dim dblSlabRate As Decimal
 'Dim dblSlabFrom As Decimal
 Dim strSlabRate As String
 Dim strSlabFrom As String
 Dim strSlabRateP As String
 Dim strSlabFromP As String
 Dim intCntr As Integer
 'Me.cmdInsSlabHmst.Parameters("iSLABDESC").Value = txtSlabDesc.Text
 'Me.cmdInsSlabHmst.Parameters("iSLABUNIT").Value = ddlSalbUnit.SelectedItem.Value
 'Me.cmdInsSlabHmst.Parameters("iREMARKS").Value = txtRemarks.Text
 'OleDbConnection1.Open()
 'strCode = cmdInsSlabHmst.ExecuteScalar
 'OleDbConnection1.Close()
 For i = 0 To dgSlabDtl.Items.Count - 1
 If i = dgSlabDtl.Items.Count - 1 Then
 'dblSlabRate = CType(dgSlabDtl.Items(i).FindControl("txtSlabRate"), TextBox).Text
 'dblSlabFrom = CType(dgSlabDtl.Items(i).FindControl("txtSlabFrom"), TextBox).Text
 strSlabRate = CType(dgSlabDtl.Items(i).FindControl("txtSlabRate"), TextBox).Text
 strSlabFrom = CType(dgSlabDtl.Items(i).FindControl("txtSlabFrom"), TextBox).Text
 Else
 'dblSlabRate = CType(dgSlabDtl.Items(i).FindControl("lblSlabRate"), Label).Text
 'dblSlabFrom = CType(dgSlabDtl.Items(i).FindControl("lblSlabFrom"), Label).Text
 strSlabRate = CType(dgSlabDtl.Items(i).FindControl("lblSlabRate"), Label).Text
 strSlabFrom = CType(dgSlabDtl.Items(i).FindControl("lblSlabFrom"), Label).Text
 End If
 strSlabRateP += strSlabRate & "~"
 strSlabFromP += strSlabFrom & "~"
 intCntr += 1
 'If dblSlabRate <> "" And dblSlabFrom <> "" Then
 'InsDtl(strCode, dblSlabFrom, dblSlabRate)
 'End If
 Next
 If strSlabRateP <> "" And strSlabFrom <> "" Then
 With cmdInsSlabHmst
 .Parameters("iSLABDESC").Value = UCase(txtSlabDesc.Text)
 .Parameters("iSLABUNIT").Value = ddlSalbUnit.SelectedItem.Value
 .Parameters("iREMARKS").Value = txtRemarks.Text
 .Parameters("iSLABFROM").Value = strSlabFromP
 .Parameters("iSLABRATE").Value = strSlabRateP
 .Parameters("iNOOFRECORDS").Value = intCntr
 End With
 OleDbConnection1.Open()
 cmdInsSlabHmst.ExecuteNonQuery()
 OleDbConnection1.Close()
 End If

to the insert procedure i am passing multiple values with the help of ~ sign and in the procedure the individual values are separated by identifying the position of ~ sign and the no. of records which have been passed. For which a complicated stored procedure has been written.

i want to pass multiple values in an array, so that my stored procedure becomes simple and runs faster. So, if someone tells me how to pass arrays to a stored procedure (with code example), it will be of real help.

regards
subhajitWell, what you are doing is probably the way to go. I don't understand though, how you can say a 'complicated stored procedure' was written to chop up a ~ delimited list. It should be only a couple of very simple statements to do.

I do the same thing quite often, for example if I have a checkboxlist and I want to pass a list of all items that the user checked to a stored proc, I go through the list in my ASP code and create a string in much the same way you do. Then in my stored proc I do something like:

while strpos(@.List, '~') > 0
begin
 insert into #Table values (substring(@.List, 1, strpos(@.List, '~') - 1))
 set @.List = substring(@.List, strpos(@.List, '~') + 1, length(@.List))
end

which is easy enough for me, and executes in a flash, even for hundreds of items.

If you reach the limit of this code (@.List close to 8000 chars) you need to do something else. In this case it can be useful to consider your specific application. Where are those values coming from? Are you storing it in bits and pieces to the array, and then in one shot you want to write it to your DB? If so you should think about writing the values to your DB as they become available - instead of keeping them in an array in server memory.

But from your code it looks like you're just doing the same sort of thing I do, so I don't see the problem.

BTW, your code is very difficult to look at, since 1/2 of it is commented out. If you can distill it into just what we need to see your problem, it makes it easier for us to help you. It will also increase the likelyhood of an accurate answer.sql

passing a parameter to TOP when getting random data

> Dear all,
> I am trying to write a stored procedure for a recipe database so that the
> user can choose how many recipes from what category.
> I have written this one to get 7 recipes from dessert category:
>
> create proc sp_ran(
>
> @.cat varchar(30)
> )
> as
> begin
>
> SELECT TOP 7 r.name,r.method FROM recipe r join Reccat rc on
> r.ID=rc.recipeID
> join category ca on ca.ID=rc.categoryID where ca.name=@.cat
> ORDER BY NEWID()
>
> end
>
> just want to know if I want the user to decide the number of recipe, how
> can
> I pass this parameter to the TOP? so a certain number of recipe will be
> randomly selected from the table?
>
> Thanks!!

Quote:

Originally Posted by crazyfisher

> Dear all,
> I am trying to write a stored procedure for a recipe database so that the
> user can choose how many recipes from what category.
> I have written this one to get 7 recipes from dessert category:
>
> create proc sp_ran(
>
> @.cat varchar(30)
> )
> as
> begin
>
> SELECT TOP 7 r.name,r.method FROM recipe r join Reccat rc on
> r.ID=rc.recipeID
> join category ca on ca.ID=rc.categoryID where ca.name=@.cat
> ORDER BY NEWID()
>
> end
>
> just want to know if I want the user to decide the number of recipe, how
> can
> I pass this parameter to the TOP? so a certain number of recipe will be
> randomly selected from the table?
>
> Thanks!!

Have one more input variable @.num in the procedure that accepts no of rows required and make use of this @.num in your query.

Pass variable value to DataReader Source

Dear All,

I have created a DTS Package in Integration Services 2005.

Within the DTS Package declared a variable named xxx and passed a value 1234.

In the control flow i dropped a Data flow task and in the Property Expression Editor of DataFlow Task i defined

Property = [DataReader Source].[sqlCommand]

Expression = Variable name.

Now in the DataFlow Task Canvas dropped DataReaderSource.

How can i pass variable value to the SQLCommand ="Select * from table where name = Variable value.

regards

Sufian

This post explains how to do that...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2009522&SiteID=1

|||

sorry , i have a diffrent problem.

I need to know how can i pass the variable value to the query supplied in the SQLCommand custom properties in datareader source on execution.

the variable datatype is Int32 and i am getting error when i set the expression property in the expression builder (Cannot convert System.Int32 to System.String).

Regards

Sufian

|||

mohd sufian wrote:

the variable datatype is Int32 and i am getting error when i set the expression property in the expression builder (Cannot convert System.Int32 to System.String).

You can't concatenate a string with an integer. You'll have to cast the integer as a string and THEN concatenate.

The cast operator is:

(DT_STR, <length>, <code_page>) variable_value

Code page will probably be 1252.

-Jamie

pass hash (#) table with different structure to stored procedure

Dear Techies,

I making one stored procedure, which does some operation based on an
interface hash (#) table -- name #mydata.

This stored has two section of code (seperated by parameter value 0
and 1)
But hash table #mydata (same name) number/name of columns changes as
per call 0 or 1.

e.g.
when call for 0, --> Pass 2 columns as company_cd and section_cd in
interface hash (#) table -- name #mydata.
when call for 1, --> Pass 3 columns as Section_cd, line_cd and
subline_cd in interface hash (#) table -- name #mydata.

As a result, none of the case (0 or 1) is running properly, It gives
problem.

When I execute procedure for 0 by passing #mydata with two columns
--> it gives problem in 1 section code

And When I execute procedure for 1 by passing #mydata with three
columns --> it gives problem in 0 section code

Please suggest !!! If anybody have faced the same problem or have any
idea about this case.
(I think passing hash table with 3 column as col1,col2,col3 can serve
the purpose, but this may cause rework in my case, so looking for
alternate solution)

Thanks in Advance,
T.S.NegiIt sounds like creating the temp table with all three columns is
probably the best solution - see here for more details and other
options:

http://www.sommarskog.se/share_data.html

Simon