Geeks With Blogs
AzamSharp Some day I will know everything. I hope that day never comes.

SqlDataSource allows you to Select/Insert/Update/Delete the data from the database without even writing a single line of code. But sometimes you need to dynamically assign a parameter. First let's take a look at the simple update which does not dynamically assign the parameters.

In the example below I am using a simple GridView control and updating the UserName, FirstName and LastName. The Bind("UserName") will get the username from the database and display it on the screen and it will also send the username back to the database when you update.

<asp:GridView AutoGenerateEditButton="true" AutoGenerateColumns="false" DataKeyNames="UserID" DataSourceID="SqlDataSource1" ID="gvUsers" runat="server" OnRowUpdating="gvUsers_RowUpdating">

<Columns>

<asp:TemplateField HeaderText="UserName">

<ItemTemplate>

<asp:Label ID="lblUserName" runat="server" Text='<%# Eval("UserName") %>' />

</ItemTemplate>

<EditItemTemplate>

<asp:TextBox ID="txtUserName" Enabled="false" runat="server" Text = '<%# Bind("UserName") %>' />

</EditItemTemplate>

</asp:TemplateField>

<asp:BoundField DataField="FirstName" HeaderText="First Name" />

<asp:BoundField DataField="LastName" HeaderText="Last Name" />

</Columns>

</asp:GridView>

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

SelectCommand="SELECT * FROM Users" UpdateCommand="UPDATE Users SET UserName = @UserName, FirstName = @FirstName, LastName = @LastName WHERE UserID = @UserID" OnUpdating="SqlDataSource1_Updating">

<UpdateParameters>

<asp:Parameter Name="FirstName" Type="String" />

<asp:Parameter Name="LastName" Type="String" />

<asp:Parameter Name="UserName" Type="String" />

</UpdateParameters>

</asp:SqlDataSource>

Now, consider a situation where you don't want to display the username from the database but you want to display the current user in the txtUserName TextBox. For this you can easily create a protected method and use databind to display the username in the txtUserName TextBox.

protected string GetUserName()

{

// This can also be User.Identity.Name

return "AzamSharp";

}

And in the GridView you can use something like this:

<EditItemTemplate>

<asp:TextBox ID="txtUserName" Enabled="false" runat="server" Text = '<%# GetUserName() %>' />

</EditItemTemplate>

But, now the question is that how will you update the username or send the username to the database. You cannot use ControlParameter since the name of the TextBox for each GridView row is different. You can however add assign the parameter in the SqlDataSource Updating event.

protected void SqlDataSource1_Updating(object sender, SqlDataSourceCommandEventArgs e)

{

// Attach another parameter

e.Command.Parameters["@UserName"].Value = GetUserName();

}

This will insert the username "AzamSharp" in the database. Offcourse you can use User.Identity.Name to get the current logged user.

Posted on Wednesday, August 30, 2006 8:52 AM | Back to top


Comments on this post: Dynamically Assign Parameter to SqlDataSource Parameter Collection

# re: Dynamically Assign Parameter to SqlDataSource Parameter Collection
Requesting Gravatar...
This is what I've been trying to do for about 5 hours now...be able to drop in a userID when updating the bound row. Great example, many thanks.
Left by Bryan Grimes on Sep 19, 2006 7:32 AM

# re: Dynamically Assign Parameter to SqlDataSource Parameter Collection
Requesting Gravatar...
perfect!!

Thank you.
Left by Paul on Oct 21, 2007 9:30 PM

# re: Dynamically Assign Parameter to SqlDataSource Parameter Collection
Requesting Gravatar...
This is exacly what I am trying to achieve and I am so close to a solution. I am very new to asp.net and have tried what you suggested. I dont want to sound stupid but how do you create a protected string and where is that code placed as I keep getting errors. Is there any chance you could provide the full code and I will be really greatful.

Cheers

David.
Left by David Brinn on Oct 24, 2007 11:26 AM

# re: Dynamically Assign Parameter to SqlDataSource Parameter Collection
Requesting Gravatar...
Thank You!! I ve been searching for this for so many hours.

Thanks again.
Left by tekxp on Jan 28, 2008 9:09 AM

# re: Dynamically Assign Parameter to SqlDataSource Parameter Collection
Requesting Gravatar...
This is very usefull

I'm havig a problem on Delete from the GridView

Could you email me how to make it work on Delete method please (VB)

Thanks,
Ed Dror
edd@andrewlauren.com
Left by Ed Dror on Apr 11, 2008 10:10 AM

# re: Dynamically Assign Parameter to SqlDataSource Parameter Collection
Requesting Gravatar...
Great example, many thanks.
Left by Frenky B. on May 06, 2008 1:14 AM

Your comment:
 (will show your gravatar)


Copyright © Mohammad Azam | Powered by: GeeksWithBlogs.net