Passing multiple parameters to stored procedure using SqlDataSource

Hi,

I have a stored procedure that takes 3 parameters. I am using a sqldatasource to pass the values to the stored procedure. To better illustrated what I just mention, the following is the code behind:

SqlDataSource1.SelectCommand = "_Search"
SqlDataSource1.SelectParameters.Add("Field1", TextBox1.Text)
SqlDataSource1.SelectParameters.Add("Field2", TextBox2.Text)
SqlDataSource1.SelectParameters.Add("Field3", TextBox3.Text)
SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure

GridView1.DataSourceID = "SqlDataSource1"
GridView1.DataBind()
MsgBox(GridView1.Rows.Count)

It doesn't return any value. I am wondering is that the correct way to pass parameters to stored procedure?

Stan

Hope this will help:

http://www.codeproject.com/cs/database/CSCodeBuilder.asp

BTW: there are many posts explaining this, just use the "Search".

Good luck.

|||

Thanks. That gives me a clue and helps me to solve my problem.

Passing arrays as parameter (SqlDataSource)

My sql-string looks like this:

 SelectCommand="SELECT *FROM Table1WHERE Field1IN @.target"

And my parameter looks like this:

<asp:ControlParameter Name="target" ControlID="CheckBoxList1" PropertyName="SelectedValue" />

This code gives me a syntax error near @.target. Someone got a solution?

I wish I new how to do it with a Parameter, but I'm running into the same problem as you. However, here's an example on how to do it by simple modifying the CommandText during the SqlDataSource.Selecting event:

ASPX

<asp:checkboxlist id="lstProducts" runat="server" datasourceid="sdsProducts" datatextfield="ProductName"datavaluefield="ProductID" autopostback="True" onselectedindexchanged="lstProducts_SelectedIndexChanged"></asp:checkboxlist><asp:sqldatasource id="sdsProducts" runat="server" connectionstring="<%$ ConnectionStrings:NorthwindConnectionString%>"selectcommand="SELECT TOP 10 ProductID, ProductName FROM Products"></asp:sqldatasource><br /><asp:gridview id="gvProducts" runat="server" datakeynames="ProductID" datasourceid="sdsProducts2"></asp:gridview><asp:sqldatasource id="sdsProducts2" runat="server" connectionstring="<%$ ConnectionStrings:NorthwindConnectionString%>"onselecting="sdsProducts2_Selecting" selectcommand="SELECT * FROM Products"></asp:sqldatasource>

CODE-BEHIND

protected void sdsProducts2_Selecting(object sender, SqlDataSourceSelectingEventArgs e){if (this.IsPostBack){e.Command.CommandText = String.Format("SELECT * FROM Products WHERE ProductID IN ({0})",this.GetInExpression(lstProducts));}else{e.Cancel =true;}}private string GetInExpression(ListControl control){List<string> list =new List<string>();foreach (ListItem itemin control.Items){if (item.Selected){list.Add(item.Value);}}return String.Join(", ", list.ToArray());}protected void lstProducts_SelectedIndexChanged(object sender, EventArgs e){gvProducts.DataBind();}

|||There must be a more simple way to do this?

Passing ANY value to the <InsertParameters> section of a SQLDataSource

Ok-

I'm new at this, but just found out that to get data off a form and insert into SQL I can scrape it off the form and insert it in the <insertParameter> section by using

<asp:ControlParameterName="text2"Type="String"ControlID="TextBox2"PropertyName="Text"> (Thanks CSharpSean)

Now I ALSO need to set the user name in the same insert statement. I put in a UserName control that is populated when a signed in user shows up on the page. But in my code I've tried:

<asp:ControlParameter Name="UserName" Type="String" ControlID="LoginName1" DefaultValue="Daniel" PropertyName="Text"/>

and I get teh error

DataBinding: 'System.Web.UI.WebControls.LoginName' does not contain a property with the name 'Text'.

So I take PropertyName="Text" out, and get the error:

PropertyName must be set to a valid property name of the control named 'LoginName1' in ControlParameter 'UserName'.

what is the proper property value?

SO....BIG question...

Is there a clean way to pass UserName to the insert parameters? Or ANY value for that matter? Id like to know how to write somehting like

String s_test = "test string";

then in the updateparameter part of the sqldatasource pass SOMEHITNG like (in bold) <asp:Parameter Name="UserName" Type="String"Value=s_test/>

Thanks in advance...again!

Dan

Create the parameter, with a name attribute and a type. Then in SqlDataSource_Inserting event, just set the value of the parameter to whatever you want it to be. I believe the syntax (VB.NET) is either

e.Command.Parameters("@.UserName").Value=s_test

or

e.InsertCommand.Parameters("@.UserName").Value=s_test

|||

Sounds easy.

Im writing in c#

But Im not sure where you are suggesting dropping in the code?

Is it in the '<InsertParameters> of the sql data source?

What is the 'E' in the e.insert...

Sorry for being so dense, but can you give me a psudo-code example?

Thanks for the help!
Dan

|||

Step by step:

"Create the parameter, with a name attribute and a type." This is what you did before, just add:

<asp:Parameter name="UserName" type="String" /> in the <InsertParameters> section of the sqldatasource control.

"Then in SqlDataSource_Inserting event": double click the sqldatasource control while in design mode.

That should take you to your code behind and create a dummy event called "SqlDatasource1_Selecting".

Now at the top of the window, there are two dropdown list boxes. The right one should say "Selecting", change it to "Inserting". Now you should have a dummy event sub called "SqlDataSource1_Inserting". The code I gave goes in there. "e" is the second parameter of the two that gets passed in whenever the sqldatasource control is about to do an insert.

If you were doing this in VB.Net, your code behind page (Mypage.aspx.vb) would have these two new subs:

ProtectedSub SqlDataSource1_Inserting(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlDataSource1.Inserting

e.Command.Parameters("@.UserName").Value=s_test

EndSub

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

EndSub

You can now delete the "SqlDataSource1_Selecting" sub if you want, since you aren't really using it.

|||

Thanks a BUNCH!
(Will try it after i make MORE coffee...)

|||

C# People can use the above discussion and use

protected void SqlDataSource1_Inserting(object sender, SqlDataSourceCommandEventArgs e)
{
String var1 = TextBox1.Text;
e.Command.Parameters["@.text1"].Value = var1;
}

in the inserting function

sql

Passing a String into the InsertCommand of SqlDataSource at the @color character

Ok, so I'm a JSP guy and thing it should be easy to replace "@.color" with t_color after I initialized it to red by

String t_color = "red";

and then calling the insert

SqlDataSource1.Insert();

here is insert command:

InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@.name, @.color)"

I've tried InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@.name, "+ t_color+")"

Ive tried InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@.name, "<%$ t_color %>" )"

Is there any easy way to do this?

or

Can I set it like

@.color = t_color?

Thanks in advance for ANY help

JSP turning ASP (Maybe)

Dan

Hi Dan,

Hope your day goes better!

If you are using @.something, this tells your database that a parameter is expected, in which case you would instantiate a new parameter and sent your string value.

your line would have worked (this one): InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@.name, "+ t_color+")"

had you remembered to use single-quotes around your color value (strings are interpreted in sql if they have single quotes around them)

so it would have been like this:

InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@.name,'"+ t_color+"')"

it know its hard to see, but in the end, the insertcommand recieved by your database server is...

INSERT INTO [favcolor] ([name], [color]) VALUES (@.name, 'red') -- which would have worked.

hope this helps!!

|||

DARN!
THought that was it.

However, when I try '"+ t_color+"' i getParser Error Message:The server tag is not well formed.
when I try "'+ t_color+'" I getParser Error Message:The server tag is not well formed.

How avout setting the value of @.color to 'red' ?

Is that an option?

Sorry Im so green at this c# stuff

|||

In your GUI, look at the properties of the sqldatasource. click the [...] button for your insert command, and check to see if you have your parameters already created there.

if so, you can set your parameter value like this...

sdsMySQLDataSource.InsertParameters[

"color"].DefaultValue = t_string;

If you don't have any parameters, then you should add them using the GUI... you can do it programmatically though like this...

sdsMySQLDataSource.InsertParameters.Add(

newParameter("color",TypeCode.String,"black"));|||Incase you want to see my test code on this issue...

 Hereis the test code that I usedin testing your issue. it compiled nicely (I just don't have the tables to actually write the data to... cant test that)// add a parameter to the insert query string t_color = "red"; string myInsertSQL;// parameters myInsertSQL = "INSERT INTO [favcolor] ([name], [color]) VALUES (@.name, @.color)"; sdsTestFiltering.InsertParameters.Add(new Parameter("color", TypeCode.String, "black")); sdsTestFiltering.InsertParameters["color"].DefaultValue = t_color;// sql injection myInsertSQL = "INSERT INTO [favcolor] ([name], [color]) VALUES (@.name, '" + t_color + "')"; sdsTestFiltering.InsertCommand = myInsertSQL;

pass a parameter to a SqlDataSource

I'm trying to pass my SqlDataSource a parameter that is defined in the code-behind file for the same page. I've tried the method below but it does not work. Is there a better way?

SubmitForm.ascx page:

<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ connection string..... %>"
SelectCommand="sp_CourseMaterialShipment_GetCourses" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:Parameter DefaultValue="<% ProgramID %>" Name="programID" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>

SubmitForm.ascx.vb page:

Private ProgramID as string = "25"

Public ReadOnly Property ProgramID() As String
Get
Return _ProgramID
End Get
End Property

Thanks

Jason

Hello my friend,

If this is what you want to do, take the DefaultValue attribute out of the aspx page and use the following in your code behind: -

sqlDataSource2.SelectParameters["programID"].DefaultValue = ProgramID;

Kind regards

Scotty

|||

worked. Thank you.

Jason