Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to specify parameter value for stored procedure in SqlDataSource

Being new to using the declarative syntax of SqlDataSource I am trying to figure out a way to set the value of a parameter to a stored procedure. I have a Client_ID that is passed via the Request Object and I need to set the Client_ID before the stored procedure of the SqlDataSource is executed.

I have a couple of questions.

  1. Does the stored procedure parameter have to be predefined in the ASPX markup or can it be added dynamically in the code-behind?

  2. Would anyone have an example that demonstrates the SqlDataSource markup with a stored procedure and parameter as well as setting that parameter value in code-behind?

like image 367
webworm Avatar asked Mar 16 '11 13:03

webworm


People also ask

How do you pass a list of values to a parameter of a stored procedure?

CREATE FUNCTION dbo. SplitInts ( @List VARCHAR(MAX), @Delimiter VARCHAR(255) ) RETURNS TABLE AS RETURN ( SELECT Item = CONVERT(INT, Item) FROM ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)') FROM ( SELECT [XML] = CONVERT(XML, '<i>' + REPLACE(@List, @Delimiter, '</i><i>') + '</i>'). query('.

Can we pass parameters to stored procedure in SQL?

There are two ways to pass parameters to a stored procedure using SQLExec. One way, which works across all versions of Visual FoxPro, is to build the SQL command as a string variable. The advantage of this method is that you can check the string and see exactly which SQL command you are passing to the back end.

Which parameter is used to store values in procedure?

Input parameters allow the caller to pass a data value to the stored procedure or function. Output parameters allow the stored procedure to pass a data value or a cursor variable back to the caller.

Can pass 3 types of parameters to stored procedures What are they?

As a program, a stored procedure can take parameters. There are three types of parameters: IN, OUT and INOUT.


2 Answers

With .net 4 framework you can ...

1.It can be added dynamically, but you would have to provide your own code expression builder (for further info see here) 1.1 You can also use different parameter to achieve same goal, like :

enter image description here

2.

  <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
  ConnectionString="<%$ ConnectionStrings:yourConnectionString %>" 
  SelectCommand="YourStoreProcedure" SelectCommandType="StoredProcedure">
  <SelectParameters>
     <asp:ControlParameter Name="yourParameterName" ControlID="controlThatHoldsParameterValue" PropertyName="Text" />
  </SelectParameters>

like image 65
krul Avatar answered Oct 10 '22 19:10

krul


This is a fairly thorough example of using parameterized queries (Stored Procedure and Text) with SqlDataSource, including programmatically setting parameters. ASP.NET - Using Parameterized Queries with the SqlDataSource.

like image 31
Zach Green Avatar answered Oct 10 '22 19:10

Zach Green