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 log on info to sub reports

I am trying to access a Crystal report from ASP. This report has a linked sub report. I am able to view the main report from the ASP page. but when i access the sub report i get the error--> Server has not been started. which means that my sub report is not getting the database log on info. i tried passin the info with the code available on net. but the first line itself fails saying Object does not suppot this method. My code is

set crSections=session("oRpt").sections
'if i print the error message here it says object does not support this method.

For Each crSection In crSections
crReportObjects = crSection.ReportObjects

'loop through all the report objects to find all the subreports
For Each crReportObject In crReportObjects
If crReportObject.Kind = ReportObjectKind.SubreportObject Then

'you will need to typecast the reportobject to a subreport()
'object once you find it

crSubreportObject = CType(crReportObject, SubreportObject)
'open the subreport object
crSubreportDocument = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName)

'set the database and tables objects to work with the subreport()
crDatabase = crSubreportDocument.Database
crTables = crDatabase.Tables
'loop through all the tables in the subreport and
'set up the connection info and apply it to the tables
For Each crTable In crTables
crTable.SetLogOnInfo "test" , "","test","test"
if crTable.TestConnectivity then
Response.Write "Connecting to sub" ' I am getting this message

end if

Next

End If
Next
Next

please point out what i am doin wrong. Greatly appreciate the help!The culprit is
crSubreportObject = CType(crReportObject, SubreportObject)
i commented this line and it works! :)

Passing Form values from ASP to RS?

Hi everybody,
does anybody knows, if it is possible to pass input from an ASP-page (not
.NET) to RS and populate the dataset with the given values? We are trying to
use the RS so the user is able to print out, export to excel or pdf AFTER
he/she did the collection of the data. For example a search for address data
where the user types in the postal code and/or the cityname, gets the result
inside the browser and is then capable to print out the specific data. I
figured out that to use parameters for this, but as I don't know which
parameters a user typed in, I am not able to build the sql statement in
advance.
Any help would be great.
Regards
MichaelI have not tried this from a form but I have built a URL dynamically to do
the same thing. You could use some client side javascript to build the URL
dynamically or you could just build an interim ASP page with a redirect.
Good Luck
Bill
Michael Bender wrote:
>Hi everybody,
>does anybody knows, if it is possible to pass input from an ASP-page (not
>.NET) to RS and populate the dataset with the given values? We are trying to
>use the RS so the user is able to print out, export to excel or pdf AFTER
>he/she did the collection of the data. For example a search for address data
>where the user types in the postal code and/or the cityname, gets the result
>inside the browser and is then capable to print out the specific data. I
>figured out that to use parameters for this, but as I don't know which
>parameters a user typed in, I am not able to build the sql statement in
>advance.
>Any help would be great.
>Regards
>Michael
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200512/1|||Hi Bill,
thx for your tips. Maybe I'm not getting it, but I don't have the problem to
built the URL dynamically in the ASP page. A more exact example would be:
In a form where a user is able to select from search criteria like: First
Name, Last Name, Postal Code, Town and Country the user only chooses to look
for data based on a certain postal code.
My problem now is to use ONLY the parameter postal code in the SQL statement
to generate the report in RS. (eg: SELECT * from Table_AdressData where
postalcode = @.postalcode) I understand that I can't use all the parameters
in the sql statement (Select * from Table... where postalcode = @.postalcode
and firstname = @.firstname and etc.) because then the statement would fail
if the user does not enter every parameter as a search criteria.
Thx.
Michael
"William N via SQLMonster.com" <u3357@.uwe> schrieb im Newsbeitrag
news:58c684e7207be@.uwe...
>I have not tried this from a form but I have built a URL dynamically to do
> the same thing. You could use some client side javascript to build the
> URL
> dynamically or you could just build an interim ASP page with a redirect.
> Good Luck
> Bill
> Michael Bender wrote:
>>Hi everybody,
>>does anybody knows, if it is possible to pass input from an ASP-page (not
>>.NET) to RS and populate the dataset with the given values? We are trying
>>to
>>use the RS so the user is able to print out, export to excel or pdf AFTER
>>he/she did the collection of the data. For example a search for address
>>data
>>where the user types in the postal code and/or the cityname, gets the
>>result
>>inside the browser and is then capable to print out the specific data. I
>>figured out that to use parameters for this, but as I don't know which
>>parameters a user typed in, I am not able to build the sql statement in
>>advance.
>>Any help would be great.
>>Regards
>>Michael
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200512/1|||Your question really has nothing to do with integration with ASP. This is
strictly a RS question. For each of your parameters set the default to All
(the string all, if the data type is integer then set the default to an
integer that will not return data).
Then design your query like this:
select * from mytable where (field1 = @.Param1 or @.Param1 = 'All') and
(field2 = @.Param2 or @.Param2 = 'All')
Then in your URL leave off the other parameters and send only the one you
care about. Or, don't have defaults and pass all the parameter but pass the
ones not filled in with the word All
One reason you might not want to use defaults is if the report is opened on
its own and all the parameters have defaults it will immediate execute.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael Bender" <technik@.salescom.de> wrote in message
news:dnmn4d$lnb$03$1@.news.t-online.com...
> Hi Bill,
> thx for your tips. Maybe I'm not getting it, but I don't have the problem
> to built the URL dynamically in the ASP page. A more exact example would
> be:
> In a form where a user is able to select from search criteria like: First
> Name, Last Name, Postal Code, Town and Country the user only chooses to
> look for data based on a certain postal code.
> My problem now is to use ONLY the parameter postal code in the SQL
> statement to generate the report in RS. (eg: SELECT * from
> Table_AdressData where postalcode = @.postalcode) I understand that I can't
> use all the parameters in the sql statement (Select * from Table... where
> postalcode = @.postalcode and firstname = @.firstname and etc.) because then
> the statement would fail if the user does not enter every parameter as a
> search criteria.
> Thx.
> Michael
>
>
> "William N via SQLMonster.com" <u3357@.uwe> schrieb im Newsbeitrag
> news:58c684e7207be@.uwe...
>>I have not tried this from a form but I have built a URL dynamically to do
>> the same thing. You could use some client side javascript to build the
>> URL
>> dynamically or you could just build an interim ASP page with a redirect.
>> Good Luck
>> Bill
>> Michael Bender wrote:
>>Hi everybody,
>>does anybody knows, if it is possible to pass input from an ASP-page (not
>>.NET) to RS and populate the dataset with the given values? We are trying
>>to
>>use the RS so the user is able to print out, export to excel or pdf AFTER
>>he/she did the collection of the data. For example a search for address
>>data
>>where the user types in the postal code and/or the cityname, gets the
>>result
>>inside the browser and is then capable to print out the specific data. I
>>figured out that to use parameters for this, but as I don't know which
>>parameters a user typed in, I am not able to build the sql statement in
>>advance.
>>Any help would be great.
>>Regards
>>Michael
>> --
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200512/1
>|||Thanks very much, Bruce.
Regards
Michael
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
news:OACNUv$$FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Your question really has nothing to do with integration with ASP. This is
> strictly a RS question. For each of your parameters set the default to All
> (the string all, if the data type is integer then set the default to an
> integer that will not return data).
> Then design your query like this:
> select * from mytable where (field1 = @.Param1 or @.Param1 = 'All') and
> (field2 = @.Param2 or @.Param2 = 'All')
> Then in your URL leave off the other parameters and send only the one you
> care about. Or, don't have defaults and pass all the parameter but pass
> the ones not filled in with the word All
> One reason you might not want to use defaults is if the report is opened
> on its own and all the parameters have defaults it will immediate execute.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Michael Bender" <technik@.salescom.de> wrote in message
> news:dnmn4d$lnb$03$1@.news.t-online.com...
>> Hi Bill,
>> thx for your tips. Maybe I'm not getting it, but I don't have the problem
>> to built the URL dynamically in the ASP page. A more exact example would
>> be:
>> In a form where a user is able to select from search criteria like: First
>> Name, Last Name, Postal Code, Town and Country the user only chooses to
>> look for data based on a certain postal code.
>> My problem now is to use ONLY the parameter postal code in the SQL
>> statement to generate the report in RS. (eg: SELECT * from
>> Table_AdressData where postalcode = @.postalcode) I understand that I
>> can't use all the parameters in the sql statement (Select * from Table...
>> where postalcode = @.postalcode and firstname = @.firstname and etc.)
>> because then the statement would fail if the user does not enter every
>> parameter as a search criteria.
>> Thx.
>> Michael
>>
>>
>> "William N via SQLMonster.com" <u3357@.uwe> schrieb im Newsbeitrag
>> news:58c684e7207be@.uwe...
>>I have not tried this from a form but I have built a URL dynamically to
>>do
>> the same thing. You could use some client side javascript to build the
>> URL
>> dynamically or you could just build an interim ASP page with a redirect.
>> Good Luck
>> Bill
>> Michael Bender wrote:
>>Hi everybody,
>>does anybody knows, if it is possible to pass input from an ASP-page
>>(not
>>.NET) to RS and populate the dataset with the given values? We are
>>trying to
>>use the RS so the user is able to print out, export to excel or pdf
>>AFTER
>>he/she did the collection of the data. For example a search for address
>>data
>>where the user types in the postal code and/or the cityname, gets the
>>result
>>inside the browser and is then capable to print out the specific data. I
>>figured out that to use parameters for this, but as I don't know which
>>parameters a user typed in, I am not able to build the sql statement in
>>advance.
>>Any help would be great.
>>Regards
>>Michael
>> --
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200512/1
>>
>sql

Passing data to SQL Server

How to pass big set of data from ASP to SQL Server stored procedure (for example 30 rows and 5 columns - content of html table) ?Stored procedures cannot accept recordsets as parameters, so you will need to pass the data to the procedure one record at a time.

Another alternative is to insert your data into a staging table. Then your application either calls the stored procedure or you create a scheduled job that calls the stored procedure. The procedure checks the staging table for records and processes them as a set.

blindman|||What about XML?sql

Passing Column Name as parameter to sql store procedure

i am using asp.net 2005 with sql server 2005. in my database table contains

Table Name : Page_Content

Page_Id

1011021AbcPqr2Lmnoiu

ALTER PROCEDURE[dbo].[SELECT_CONTENT]

(@.lang_codevarchar(max))

AS

begin

declare@.aas varchar(max)set@.a = @.lang_code

Selectpage_id,@.aFrompage_content

end

Here in this above store procedure i want to pass 101 to @.lang_code

here is my output, but this is wrong output

Page_Id

Column111012101

but i want following output

Page_Id

1011021AbcPqr2Lmnoiu

use dynamic sql.http://www.sommarskog.se/dyn-search.html
modify your procedure as:-

declare @.sql
set @.sql = 'Selectpage_id,' + @.a + 'Frompage_content'
exec sp_executesql @.sql

hope it helps

|||

aadreja:

use dynamic sql.http://www.sommarskog.se/dyn-search.html
modify your procedure as:-

declare @.sql
set @.sql = 'Selectpage_id,' + @.a + 'Frompage_content'
exec sp_executesql @.sql

hope it helps

The above code is subject to sql injection attacks. Query on sql injection attacks if you don't know what they are.

As coded, someone could force your page to reveal sensitive data in other tables, or alter or destroy data in your database in ways you do not want to allow.

Given that a column name has very specific naming rules, you can test that the value you get in @.a is a plausible, safe column name.

If @.a has any character other than a letter from a-z, A-Z or 0-9, you should trap that and raise an error.

One way to test is to make a copy of @.a and remove all the valid characters. If nothing is left in the copy, it's a safe column name to process.

|||

I know you could do this with dynamic sql, but that's not always the best solution.

I think you could use a CASE/WHEN block to do what you are wanting. Each field would have to be known in advance, this wouldn't work "on the fly" if you add new columns to the table without updating the SP

ALTER PROCEDURE[dbo].[SELECT_CONTENT]

(@.lang_codevarchar(max))

AS

begin

declare@.aas varchar(max)set@.a = @.lang_code

Selectpage_id,
CASE
WHEN @.a = '101' THEN 101
WHEN @.a = '102' THEN 102
ELSE 101 -- you don't need an else, but this query will fail in a syntax error if the input doesn't match one of your defined values.
END Frompage_content

end

|||

I agree!

Benefits of your approach:

Sql Injection safe

Passing C# Value to the ASP SQL Query

Hi Guys,

Im trying to pass a value from a C# page to the corresponding ASP page.

I want to use this value in my SQL Query on the asp page within a Details View.

Ive made the variable public in c# page.

And im trying to concatenate it to my sql query within the <asp: SqlDataSource.

Thanks.

Look at http://www.devx.com/webdev/Article/30811 (Passing Information Securely Between ASP and ASP.NET)From ASP to ASP.NET you could call MyAspNetPage.aspx?ID=1234 where 1234 is the id of the record to display.|||Im using these 2 lines of code in the C# Page to retrieve the UserId. UserNum ive made public.

Guid User = (Guid)Membership.GetUser().ProviderUserKey;

UserNum = User.ToString();

This is the select statement i want to use within the SQLDataSource.

SELECT * FROM [UserData] WHERE ([UserID] = @.UserNum)

@.UserNum i want to use as the parameter in the query.

I still cant get the value back into the @.UserNum of the query

|||Are you trying to pass information from an ASP.NET page to an ASP page?|||

No.

I retrieve the UserID in the Account.aspx.cs page (This is the C# Page)

I want to pass the retrieved value to the Account.aspx page because i need to use it in a query in a SQL Data Source.

I thought i could just call the variable like this @.UserNum withint the query.

But its not picking up the value from the C# page.

|||

Your question is now clear!

You need to populate the data control from within Account.aspx.cs. The SQL Data Source may get get some very quick results, but in the end end nothing beats regular coding.

|||

data control?

Im not sure what you mean?

|||I was using "Data Control" generically - I shoudl have said to populate the Details View - it shoudl be done entirely from code behind.|||

Oh ok.

I am using a query behind the details view to populate it.

But i need to retrieve the current logged in User-ID so that i can populate it correctly.

Thats is why i was using the c# page.

Is it possible to retrieve the current logged in User-ID within SQL?

|||

Assuming your back-end database is SQL Server, you can set up a stored procedure that takes the argument of the record id. To run it set up a command block and execute it.

Or you can just generate the SQL command dynamically as in http://www.dotnetjunkies.com/QuickStartv20/howto/doc/adoplus/GetDataFromDB.aspx

|||

We are using an MS SQL Server Database.

Is it possible to pass a value back from the C# page as a

<asp:Parameter> ?

|||Hi guys,

I still havent figured this out.

This is the code im using in the asp page.

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ASPNETDB.MDFConnectionString %>"

SelectCommand="SELECT FirstName, SecondName, DeviceNum, MobileNum, Address, Town, City, Country FROM aspnet_AddInformation WHERE (UserId = @.UserNumVal)"UpdateCommand="UPDATE aspnet_AddInformation SET FirstName =, SecondName =, DeviceNum =, MobileNum =, Address =, Town =, City =, Country ="><SelectParameters><asp:ParameterName="UserNum"DefaultValue="<%= UserNumVal %>"/></SelectParameters></asp:SqlDataSource>

This is the code i use in the C# Page to retrieve the userId Number

publicString UserNum;protectedvoid Page_Load(object sender, System.EventArgs e)

{

Guid User = (Guid)Membership.GetUser().ProviderUserKey;

UserNum = User.ToString();

}

I want to use the value of UserNum within the query the SQL SelectCommand

I think i ahve to pass the value back as a parameter?

|||

We fixed the problem.

We needed to add the following line of code to the C# Page.

SqlDataSource1.SelectParameters.Add("userNum", userNum);

Thanks For all the 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 parameter from a form to an sql sequel view

Hi,

We have recently upsized an access db to a sequel server db. The queries in access have been made into views in sql. ASP will be the front end. We are having problems passing an input from a form into a view. In access the parameter had to match what was selected on the form but sequel doesn't like it. At the moment, for testing purposes, the value has to be hard coded i.e. case_id = 64. Is there anything is ASP that can help?

Thank you

You stated that you're looking for something in ASP to help you... I assume you are using some version of ASP.NET?

If you are using ASP.NET 1.1, you have a number of options, the easiest of which is creating a SqlCommand or SqlDataReader object and filtering the rows on the parameters you use. You'll need to manually pass the value on your form into the parameter value.

However, if you're on ASP.NET 2.0, the solution is even simpler. Just create an instance of the SqlDataSource class and wire up a SelectParameter to the desired control.

Hope this helps...

Passing a fields.name.value in the jump to url

I have a web page (asp) that I want to pass the field. value to the url.
ie. http://someinet/Corp/QMS/application/CIA/aspfiles/CIAView.asp?CIANum=4285
this works but i want them to click on a field
="http://someinet/Corp/QMS/application/CIA/aspfiles/CIAView.asp?CIANum="+Fields!cianum.Value
no error but no hyper link on the field cellOk i got it figured out the cianum is a (int)field when I "cast(cianum as
varchar(15))as cianum" in my query it worked fine. Is this a bug with
reporting services?
"ljhopkins_LOST and Cant find My Way" wrote:
> I have a web page (asp) that I want to pass the field. value to the url.
> ie. http://someinet/Corp/QMS/application/CIA/aspfiles/CIAView.asp?CIANum=4285
> this works but i want them to click on a field
> ="http://someinet/Corp/QMS/application/CIA/aspfiles/CIAView.asp?CIANum="+Fields!cianum.Value
> no error but no hyper link on the field cell

passing a comma delimited string to stored procedure

Hello,

I have an asp page that sends a string,
ex. CO,S2,S3,S4,S5,S6,SA,SB,SD,SF,SG,SO,SQ,SR,ST
to a stored procedure in sql server as a single variable
(example @.str).

I want to then somehow split the variable's contents up as 'CO', 'S2', etc to use in a select statement's WHERE IN clause.

Ive tried the replace function to replace the , (comma) with ',' but didnt get the right syntax possibly...

Anybody have any leads or samples done before for this.

Very much appreciated in advance.What about this idea?

drop proc test2
go
create proc test2 @.line varchar(8000)
as
declare @.sql varchar(8000)
select @.sql='select ''ok'' where ''A'' in('+@.line+')'
select @.sql
exec(@.sql)
go
test2 '''A'',''B'',''C'',''D'''
go|||I assume you are going to use this in a dynamic SQL statement?

You say you got a syntax error. Did you remember to put single quotes before and after the string (as snail illustrates), as well as around the commas?

I find it helpful in debugging dynamic code to construct the code in a variable and then PRINT the variable immediately before executing it. If you do this and you are still having problems, post your SQL so that we can review it.

blindman

passing a binary value to sql server 2005

I have been pulling my hair out on this.

I am trying to pass a binary value from an ASP.NET app to a VERY simple stored proc, and I can NOT get it to work!

Here is some of my code:

Code Snippet

ALTERPROCEDURE [dbo].[sp_SelectAppointments] @.PID VarChar(50)

AS

BEGIN

--set @.PID = 0x00000000000000B1

--PLEASE NOTE HERE THAT THE ABOVE VALUE(WHEN UNCOMMENTED) RETURNS RECORDS

--WHEN THE VALUE IS IN QUOTES (AS IT WOULD BE) IT DOES NOT WORK

SETNOCOUNTON;

SELECT Appt_Date, Appt_Description, Rn_Appointments_Id

FROM Rn_Appointments

WHERE project = @.PID --Project is a binary field.

END

I created a Dataset in VS2005, and here is the code that passes the value to the Dataset:

Code Snippet

Dim PID As String = Request.QueryString("pid")

PID = "0x00000000000000B1"

Dim da2 AsNew sp_SelectAppointmentsTableAdapter

GridView1.DataSource = da2.GetData2(PID)

GridView1.DataBind()

What am I missing? Can anybody please help me out here?

Thank you.

Steve

You are capturing the incoming parameter as varchar() and they attempting to equate a varchar() to a binary in the WHERE clause filter.

Have you attempted with a binary input parameter datatype?

Or, convert the parameter to binary inside the procedure?

ALTERPROCEDURE [dbo].[sp_SelectAppointments] @.PID binary

or

WHERE project = cast( @.PID as binary )

Passing & Using variables in Stored Procs for table names

Hi all
SQL 2k, win 2kpro, vbscript asp
I'm just getting into stored procs and coming on quite good imho :)
but I'm stuck on a little problem
I want to pass a stored proc a variable eg "G001_"
create procedure dbo.myproc
@.prefix vchar (20)
as
I want to use the "prefix within the stored proc on table names I'm
wanting to grant access for a user on several tables with this prefix
eg
grant select on @.prefix+tablenameOne to UserName
grant select on @.prefix+tablenameTwo to UserName
grant select on @.prefix+tablenameThree to UserName
grant select, update on @.prefix+tablenamefour to UserName
grant select on @.prefix+tablenamefive (col1, col2) to UserName
Would look like this if fixed:
grant select on G001_tablenameOne to UserName
grant select on G001_tablenameTwo to UserName
etc.
but I cant seem to get this to work... How can I do this?
The tables will be created from a different stored proc and then I
want to grant users to be able to use them tables
thanks for any help.
Alyou will have to execute the 'GRANT' statment using EXEC.
Ex:
exec ('grant select on ' + @.prefix + ' tablenameOne to UserName')
--
-Vishal
"Harag" <harag@.softhome.net> wrote in message
news:5175kv4u71063eekbo7i46bjl03u0ok7um@.4ax.com...
> Hi all
> SQL 2k, win 2kpro, vbscript asp
> I'm just getting into stored procs and coming on quite good imho :)
> but I'm stuck on a little problem
> I want to pass a stored proc a variable eg "G001_"
>
> create procedure dbo.myproc
> @.prefix vchar (20)
> as
>
> I want to use the "prefix within the stored proc on table names I'm
> wanting to grant access for a user on several tables with this prefix
> eg
> grant select on @.prefix+tablenameOne to UserName
> grant select on @.prefix+tablenameTwo to UserName
> grant select on @.prefix+tablenameThree to UserName
> grant select, update on @.prefix+tablenamefour to UserName
> grant select on @.prefix+tablenamefive (col1, col2) to UserName
> Would look like this if fixed:
> grant select on G001_tablenameOne to UserName
> grant select on G001_tablenameTwo to UserName
> etc.
> but I cant seem to get this to work... How can I do this?
> The tables will be created from a different stored proc and then I
> want to grant users to be able to use them tables
> thanks for any help.
> Al

Pass value of report parameter from URL

Hello
I have an ASP.NET application where I can filter data from an SQL DB by certain criterias. Users are now also able to click on a link which redirects them directly to the SQL Server Reporting Services application.
What I'd like to have is a possibility to pass an object (as string) from my ASP.NET app to the textbox of the search criteria from the Reporting Services app.

Example:
- ASP.NET appl.: The user selected a FileId=84 -> then he clicks on the hyperlink to the SQL RS
- The SQL RS appl. opens and the textbox FileId is filled with "84"

it of course isn't a problem to pass the FileId to the hyperlink the user clicks on, but is it possible that the SQL RS appl. can somehow read it from the URL and pass it to the textbox?
Or does someone have another solution?

Thanks for your help!

You mean something like this? http://msdn2.microsoft.com/en-us/library/aa256630(SQL.80).aspx

It says it is for SQL (80), so I am not sure what problems carrying that over to 2005 will present.

Hope that helps.

|||well it looks like a step towards the solution, but it didn't really get me far...

the URL to my report looks like this:
http://reports.mycompany.com/Reports2005/Pages/Report.aspx?ItemPath=MyReport

in MyReport, I only have a textfield called "project" which I have to fill. When I put in a sample value "ABC", run the report and then take a look at the html code, I have something like this:

<tr IsParameterRow="true">
<td class="ParamLabelCell">
<span>project :</span>
</td>
<td class="ParamEntryCell" style="padding-right:0px;">
<span><input name="ctl137$ctl00$ctl03$ctl00" type="text" value="ABC" size="30" id="ctl137_ctl00_ctl03_ctl00" />
</span>
</td>

how can I pass the "ABC" text to this textfield over the URL (the report doesn't need to be run, I only want the textbox filled when I load the report for the first time!)|||still no idea?|||

I know it is kind of a late response, but your aspx page with the dropdown listbox should be able to pass the information. So, on the report page where you want the info, add a textbox and set the value to: Request.Form ("myfield") where myfield is the name of the dropdown box on the previous page.

The page the data is sent from will need to post to the report page I think.

Pass value of report parameter from URL

Hello
I have an ASP.NET application where I can filter data from an SQL DB by certain criterias. Users are now also able to click on a link which redirects them directly to the SQL Server Reporting Services application.
What I'd like to have is a possibility to pass an object (as string) from my ASP.NET app to the textbox of the search criteria from the Reporting Services app.

Example:
- ASP.NET appl.: The user selected a FileId=84 -> then he clicks on the hyperlink to the SQL RS
- The SQL RS appl. opens and the textbox FileId is filled with "84"

it of course isn't a problem to pass the FileId to the hyperlink the user clicks on, but is it possible that the SQL RS appl. can somehow read it from the URL and pass it to the textbox?
Or does someone have another solution?

Thanks for your help!

You mean something like this? http://msdn2.microsoft.com/en-us/library/aa256630(SQL.80).aspx

It says it is for SQL (80), so I am not sure what problems carrying that over to 2005 will present.

Hope that helps.

|||well it looks like a step towards the solution, but it didn't really get me far...

the URL to my report looks like this:
http://reports.mycompany.com/Reports2005/Pages/Report.aspx?ItemPath=MyReport

in MyReport, I only have a textfield called "project" which I have to fill. When I put in a sample value "ABC", run the report and then take a look at the html code, I have something like this:

<tr IsParameterRow="true">
<td class="ParamLabelCell">
<span>project :</span>
</td>
<td class="ParamEntryCell" style="padding-right:0px;">
<span><input name="ctl137$ctl00$ctl03$ctl00" type="text" value="ABC" size="30" id="ctl137_ctl00_ctl03_ctl00" />
</span>
</td>

how can I pass the "ABC" text to this textfield over the URL (the report doesn't need to be run, I only want the textbox filled when I load the report for the first time!)|||still no idea?|||

I know it is kind of a late response, but your aspx page with the dropdown listbox should be able to pass the information. So, on the report page where you want the info, add a textbox and set the value to: Request.Form ("myfield") where myfield is the name of the dropdown box on the previous page.

The page the data is sent from will need to post to the report page I think.

Pass UserName to the SQL Select ? (VB)

VB ASP.NET 2.0

How do I get the Authenticated UserName passed to a Select Command of a data control ?

I'm not clear on how to get the logged in username fromUser.Identity.Name as a string or how to pass it to the Select Command of a control.

I've had some success with SelectParameters / ControlParameter's in the Master Control context, but otherwise I don't understand how to create a Parameter for use with the SQL.

I want to show the user data from a database, based on who is logged in .

Help, greatly appreciated.

Chris

User.Identity.Name will hold the value of the currently authenticated user. How you deal with parameters depends on how you approach it. with a straightforward Sql command, the following is a guide:

Dim query As String = "Select * From Users WHERE UserName = @.UserName"
Dim conn As New SqlConnection(myconnectionstring)
Dim cmd As New SqlCommand(query, conn)
cmd.Parameters.AddWithValue("@.UserName",User.Identity.Name)
conn.Open()
Dim rdr As New SqlDataReader = cmd.ExecuteReader()
While rdr.Read()...

If you want to use a SqlDataSource, this article should help:http://aspnet.4guysfromrolla.com/articles/030106-1.aspx

|||

You can also use the SQL Function SUSER_SNAME() to get the login directly in the SQL query...

SELECT foo, bar, suser_sname() as user FROM some_table

|||

valenumr:

You can also use the SQL Function SUSER_SNAME() to get the login directly in the SQL query...

SELECT foo, bar, suser_sname() as user FROM some_table

But that'll get the SQL user, which probably won't be the same as the user logged on to the website..

Regards

Fredr!k

|||

Very true... it will work if your DB is using windows login's, and you don't have any impersonation going on in your web server... so I guess, I should have mentioned that.

|||

Thanks to all the rapid replies.

An error had lead me to believe that the User.Identity.Name did not return a string, but further research says it should.

I should be clearer about the approach I was attempting. Given that I'm struggling to learn .Net and ASP.Net I'm struggling to keep the issues clear.

I was trying to find a way to do the assignment in the Mark Up for the server controls.

I have found that you can also create a Parameter in the mark up for the SqlSourceControl section under the <SelectParameters> section that you can then use in the Select Command.

Still trying to tie that info together to do this.

BUT , your cmd.Parameters.AddWithValue method is eye opening. When I start working with code, I'm still trying to learn the models to understand whichevents to put such code.

(Anyone know a concise reference for this ? )

I want a dropdown box to be filled with a set of items from a database that are particular to the User that's logged on.

(ps. I find from my research that you can use My.User.Name , also to get the username)

Many Thanks for all the help !

Chris9876

|||

I'm not at my computer to verify, but try User.Identity.Name.tostring If that doesn't work, I will check when I get home as I do this exact thing, but with the userid and not the username.

|||

This is from memory, but you can create a parameter in your sqldatasource (Let's call it @.Username).

Then in the SqlDataSource1_Selecting event, just put:

e.Commad.Parameters("@.UserName").Value= My.User.Name

Then whenever the sqldatasource is about to do a select, you set the username parameter to whomever is currently logged it. Simple and easy.

If you are changing the output based on the persons roles, or if they are currently logged in, you might want to just use a loginview control.

|||

The following Markup and Event Code works great. (DropdownList is filled with data from MyField whereUserName=@.UserName)

Thank you all very much.

<asp:DropDownListID="DropDownList1"runat="server"DataSourceID="SqlDataSource1"DataTextField="ClassCode"DataValueField="ClassCode">

</asp:DropDownList>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT [MyField] FROM [MyView] WHERE ([UserName] = @.UserName)">

<SelectParameters>

<asp:ParameterName="UserName"Type="String"/>

</SelectParameters>

</asp:SqlDataSource>

ProtectedSub SqlDataSource1_Selecting(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs)Handles SqlDataSource1.Selecting

e.Command.Parameters("@.UserName").Value =My.User.Name

EndSub

Pass data to a report?

Hi all,

We have an ASP.NET application that generates various reports and word documents. The word documents and the reports require the exact same data and we have written some rather complex functionality that generates the SQL select statements to fetch this data. The statements are currently being used to create ADO.NET DataSets which feed the word documents. How can we now get these DataSets to feed the reports too? We would really, really, really like to avoid having to recreate the logic for constructing the select statements within SSRS. Can we pass the DataSets themselves, or can we serialize them to XML using the WriteXml method then pass the Xml data as parameters or is there a way to just pass the SQL to the report? We have had a play around and have done some research to figure this out but have so far been unsuccessful.
Any help you have to offer would be greatly appreciated!
Thanks,

Stephen.

Why not write this data to a table and just pass an ID to the report, identifying the rows to be printed?

Just an idea.

BobP

|||

Hi Stephen-

I can think of two possibilities if you are using Reporting Services 2005:

(1) You can create a report using the XML Data Provider. You can point the report to an external XML data source (such as a web service) and consume the data that way

(2) Probably, the easiest way would be to use a web report control which takes the data as a .NET data set and can be emebedded within your application.

If you are on RS 2000, you would need to implement a custom data extension. More information for this is available on the RS 2000 books online.

Thanks, Jon

|||

Reports use a store procedures to retrieve the data.

My suggestions is that after you build your SQL statement, you can take this process one step further and store this SQL as a stored procesure in the SQL server where your tables are. Say, you name this procedure SP_myreport.sql You will use this stored procedure name in your report.

To update the procedure in SQL server you will have to add a "DROP PROCEDURE" and "CREATE PROCEDURE" to your sql script.

|||

BobP - BIM wrote:

Why not write this data to a table and just pass an ID to the report, identifying the rows to be printed?

Just an idea.

BobP

Thanks for your help people and sorry for not getting back sooner. In the end, we opted for this solution as it best fit our situation (we are using 2000 and the custom data extensions are a little over the top for what we are doing. We also want to stay away from putting any logic in the DB). I've had a look and the other two suggestions will also work so I'm marking those as answers to.
Thanks again,

Stephen.

Pass data to a report?

Hi all,

We have an ASP.NET application that generates various reports and word documents. The word documents and the reports require the exact same data and we have written some rather complex functionality that generates the SQL select statements to fetch this data. The statements are currently being used to create ADO.NET DataSets which feed the word documents. How can we now get these DataSets to feed the reports too? We would really, really, really like to avoid having to recreate the logic for constructing the select statements within SSRS. Can we pass the DataSets themselves, or can we serialize them to XML using the WriteXml method then pass the Xml data as parameters or is there a way to just pass the SQL to the report? We have had a play around and have done some research to figure this out but have so far been unsuccessful.
Any help you have to offer would be greatly appreciated!
Thanks,

Stephen.

Why not write this data to a table and just pass an ID to the report, identifying the rows to be printed?

Just an idea.

BobP

|||

Hi Stephen-

I can think of two possibilities if you are using Reporting Services 2005:

(1) You can create a report using the XML Data Provider. You can point the report to an external XML data source (such as a web service) and consume the data that way

(2) Probably, the easiest way would be to use a web report control which takes the data as a .NET data set and can be emebedded within your application.

If you are on RS 2000, you would need to implement a custom data extension. More information for this is available on the RS 2000 books online.

Thanks, Jon

|||

Reports use a store procedures to retrieve the data.

My suggestions is that after you build your SQL statement, you can take this process one step further and store this SQL as a stored procesure in the SQL server where your tables are. Say, you name this procedure SP_myreport.sql You will use this stored procedure name in your report.

To update the procedure in SQL server you will have to add a "DROP PROCEDURE" and "CREATE PROCEDURE" to your sql script.

|||

BobP - BIM wrote:

Why not write this data to a table and just pass an ID to the report, identifying the rows to be printed?

Just an idea.

BobP

Thanks for your help people and sorry for not getting back sooner. In the end, we opted for this solution as it best fit our situation (we are using 2000 and the custom data extensions are a little over the top for what we are doing. We also want to stay away from putting any logic in the DB). I've had a look and the other two suggestions will also work so I'm marking those as answers to.
Thanks again,

Stephen.

Pass a parameter to SSIS from .NET App

I'd like to pass a parameter value from a ASP.NET application to an SSIS package. My SSIS package pulls data from SQL and loads it a flat file based on the parameter value.

Is this possible?

Very much possible.

Check out the example @. http://www.codeproject.com/useritems/CallSSISFromCSharp.asp

More details on "Loading and Running a Package Programmatically " @. http://msdn2.microsoft.com/en-us/library/ms136090.aspx

Thanks,
Loonysan

|||Sweet! Thanks :)

Pass a "Begin...End" Block from ASP

Is it okay to pass a Begin...End block to Sql Server from an ASP web page?
I
have a situation where one of my tables contains the column names that I nee
d
to select from another table. I have always used two separate select
statements (with two separate trips to the db) to get the values I need, but
I recently found that I can accomplish the same thing by passing a
Begin...End block like this...
begin
declare @.col_list varchar(8000)
select @.col_list = coalesce(@.col_list + ', ', '') +
approverlabel from approvers
where formid=6 order by approverorder
exec('select ' + @.col_list + ' from formconfigs where pid=2701')
end
Is there a reason why this should not be done? I realize this would be
better if it was implemented in a stored procedure.Yes this would be best in a stored procedure so it can reuse the query plan.
But in any case you don't need a BEGIN - END. If you send it as one batch
it will work fine.
Andrew J. Kelly SQL MVP
"creed1" <creed1@.discussions.microsoft.com> wrote in message
news:75660A09-951F-4D34-88BE-BFC26E13FED7@.microsoft.com...
> Is it okay to pass a Begin...End block to Sql Server from an ASP web page?
> I
> have a situation where one of my tables contains the column names that I
> need
> to select from another table. I have always used two separate select
> statements (with two separate trips to the db) to get the values I need,
> but
> I recently found that I can accomplish the same thing by passing a
> Begin...End block like this...
> begin
> declare @.col_list varchar(8000)
> select @.col_list = coalesce(@.col_list + ', ', '') +
> approverlabel from approvers
> where formid=6 order by approverorder
> exec('select ' + @.col_list + ' from formconfigs where pid=2701')
> end
> Is there a reason why this should not be done? I realize this would be
> better if it was implemented in a stored procedure.