Passing a parameter to sqldatasource with type stored procedure

[marconannini]

--------------------------------------------------------------------------------

I am developing a webpage using Visual Web Developer Express. I have created a GridView linked to a stored procedure in an SQL Database that takes one parameter. I have tested it using a default valued for the parameter and it works fine.

However, i need to pass the current logged in username as an input parameter. I have looked around and all example are for passing parameters to the select command but nothing that i found works for stored procedures.

I know how to get the username, but i dont know how to pass it to the stored procedure i'm calling with sqldatasource.
Cheers, M.

 

[ralphtrent]

Not sure how to do it with the gui but the code is as follows.

SqlConnection objConn = new SqlConnection([your_connection_string]);
SqlCommand objComm = new SqlCommand([your_sp_name],objConn);
objComm.CommandType = CommandType.StoredProcedure;
objComm.Parameters.Add([ParameterName],[ParameterValue])
objComm.ExecuteReader()

Let me know if you need any further help.

 

[marconannini]

Thanks, but i'm not using as sqlconnection but a sqldatasource. I need to add/change parameter value of the tag associated with sqldatasource.
<Asparameter name="UserID" Type="String">
Cheers.
M.

 

[dvannoy]

this may help you...it's for 2.0

http://aspnet.4guysfromrolla.com/articles/030106-1.aspx

 

[marconannini]

I have read that article already, it addresses how to change the value of parameters in a select statement but not in a stored procedure. The code must be very similar but i couldnt get it to work.
The select statement generates a tag:
<asp: selectparameter name"blabla" type"string">
whereas the stored procedure generates
<asp: parameter name"blabla" type"string">

When i write the code behind i cant get it to work with the parameter property as opposed to the selectparameter.

Any further help welcome.

Cheers, Marco.

 

[dvannoy]

cmd.Parameters.Add(New SqlParameter("@FieldName", txt.Text))

 

[jbenson001]

Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSource1.Selecting
e.Command.Parameters("myvalue").Value = Request.QueryString("value")
End Sub

 

[marconannini]

Hi all, i sorted out the problem last night... you dont need to ADD a new parameter as this will have already been declared...

The tags created are:
<Asp:SelectParameters>
<Asparameters Name="UserID" Type"String">
etc

in the Page_Load sub you simply:

SqlDataSource1.SelectParameters("UserID").DefaultValue = "Whatevere you want it to be"

Note UserID will be the same name without @ as @UserID, the parameter in your stored procedure.