InsertParameters for SqlDataSource

Hello, I have a SqlDataSource named "OrderHistoryDataSource" that I want to add a parameter to from the code behind file. THe reason I want to do this is todynamicly add the UserID from a currently logged on user. What am I doing wrong? Is there a better way?My code below:

---------------------Code behind file-----------------------

using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;public partialclass OrderHistory : System.Web.UI.Page{protected void Page_Load(object sender, EventArgs e) {//If the user is not logged in this will redirect to Login.aspxif (!Request.IsAuthenticated) { Response.Redirect("~/Login.aspx"); }//test to display userID MembershipUser myObject = Membership.GetUser();string UserGUID = myObject.ProviderUserKey.ToString(); SqlDataSource DataSource = Page.FindControl("OrderHistoryDataSource"); DataSource.InsertParameters.Add("CurrentUserId", UserGUID.ToString()); DataSource.Select(); }}

---------------------Asp.netfile-----------------------

<%@. Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="OrderHistory.aspx.cs" Inherits="OrderHistory" Title="Untitled Page" %><asp:Content ID="Content1" ContentPlaceHolderID="CONTENT" Runat="Server"> <table id="ContentTable"> <tr> <td id="ContentTitleCell" colspan="3" style="width: 684px"> ORDER HISTORY</td> </tr> <tr> <td colspan="3" rowspan="2" style="width: 684px"> <asp:GridView ID="OrderHistoryGrid" runat="server"> </asp:GridView>   <asp:SqlDataSource ID="OrderHistoryDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString%>" SelectCommand="SELECT * FROM [UserOrders] WHERE ([UserID] = [CurrentUserID]) ORDER BY [OrderID] VALUES (@.UserId, @.Address, @.City, @.State, @.Zip)" ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName%>"> <InsertParameters></InsertParameters> </asp:SqlDataSource> </td> </tr> <tr> </tr> </table></asp:Content>

Hello,

By looking in the code that you have given, I can definitely say there is some thing wrong. Please check youe code again.

You can follow the code below.

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="UserID" HeaderText="UserID" SortExpression="UserID" />
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
<asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
<asp:BoundField DataField="MiddleName" HeaderText="MiddleName" SortExpression="MiddleName" />
<asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="OrderHistoryDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [UserID], [Title], [FirstName], [MiddleName], [LastName] FROM [UserOrders] WHERE ([UserID] = @.UserID)">
<SelectParameters>
<asp:Parameter Name="UserID" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

---------------------Codebehindfile-----------------------

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partialclass OrderHistory : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
 {
//If the user is not logged in this will redirect to Login.aspx
if (!Request.IsAuthenticated)
 {
 Response.Redirect("~/Login.aspx");
 }
//test to display userID
 MembershipUser myObject = Membership.GetUser();
string UserGUID = myObject.ProviderUserKey.ToString();
OrderHistoryDataSource.SelectParameters["UserID"].DefaultValue = UserGUID;
        GridView1.DataBind();
 }
}

Thanks,

Deepesh Verma

|||

Thank you very much. I figured I had the general idea; I was just off by a bit.

|||

I would typically suggest moving the code to the SqlDatasource_Selecting event. Within that event, you can set any parameters you want through the e.Command property. It also will get executed each time a select will fire, and only when a select will fire.

Inserting the date as defaultvalue in a sqldatasource Parameter

Hi Guys.

I am trying to insert the date as the default value into the DatePosted parameter in the sqldatasource object. I have put have the following below but it doesn't work. I have also tried <asp:Parameter Name="DatePosted" Type="DateTime" DefaultValue="<%= Date.Now %>" />

<asp:Parameter Name="DatePosted" Type="DateTime" DefaultValue="<%= now() %>" />

I know the solution is probably simple and I look like an idiot, but excuse me because I am very knew and fragile at this lol...

any help would be great :).

Mike.

Change your code to this,and it is OK.

<asp:Parameter Name="DatePosted" Type="DateTime" DefaultValue='<%# DateTime.Now %>' />

|||

Hi Jason,This does not work either. It says you can't use databind in a parameter I tried it with <%= %> as well but still nothing?

Any other ideas?

|||

If you can't override the parameter, it would be easiest to change the sql statement to reference getdate or getutcdate instead like:

INSERT INTO MyTable(col1,DatePosted) VALUES (@.col1,getutcdate())

If you have to allow for the parameter to be overridden, try putting this code into page_init:

SqlDatasource1.InsertParameters("DatePosted").DefaultValue=now

|||just do it at the database level via enterprise manager by modifying the table in question and set the default for the column by specifying the getdate() function so when a recorded is inserted via code with dateposted not supplied the sql server will insert that column with the current date.|||I have the same problem and this solution doesnt work. The column in the table has a default of getdate(), but I still get an error that NULL cannot be insert into the column. The problem is there is no way to tell it to not supply a value--if a value is not set it will supply NULL. Is there another way to default a DateTime parameter to DateTime.Now or getdate()? TIA|||

The solution I posted should work. The solution posted after mine should also work.

Please post your code.

|||

Doing it at the database level only works if you can remove it as a parameter entirely. In my case I needed to allow the user to enter datetime and only default it to getdate()/DateTime.Now when they choose not to enter specific date.

I knew of the page init method you suggested, but I was trying to avoid this as I was experimenting with how much you can do with only declarative code--I guess I just hit one of the limits of declarative coding.

(For the record, I much prefer DDD with NHibernate, NUnit, etc., but in some cases, especially for small/trivial/non-critical projects, being able to do things quickly outweighs other concerns.)