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.)

INSERTING NULL VALUES VIA STORED PROCEDURES

Hi,

Becouse some of my stored procedure parameters can be NULL, for every parameter with potential NULL value I have to do checking like this:

errorParams[3].Value = (e.InnerException==null)?(object)DBNull.Value:(object)e.InnerException;

When I do it like this ( it is a part of preparing values for insert ):

errorParams[3].Value = e.InnerException

no row is added. Is there any way to pass over that check : (e.InnerException==null)?(

and do it easier?

This the sample exception message for that issue:

{System.Data.SqlClient.SqlException: Procedure or Function 'sp_addError' expects parameter '@.InnerException', which was not supplied.

Thanks,

Pawe?

That depends on your Procedure declaration, you will have to allow a default parameter in orde to let it work.

CREATE PROCEDURE sp_addError
(
@.InnerException VARCHAR(1000) = NULL --Which declares the default if no value is passed through
)
(...)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Cool :) Big thanks!

Pawe?

|||Thank you very much
It was solution of my Big problem

INSERTING NULL VALUES VIA STORED PROCEDURES

Hi,

Becouse some of my stored procedure parameters can be NULL, for every parameter with potential NULL value I have to do checking like this:

errorParams[3].Value = (e.InnerException==null)?(object)DBNull.Value:(object)e.InnerException;

When I do it like this ( it is a part of preparing values for insert ):

errorParams[3].Value = e.InnerException

no row is added. Is there any way to pass over that check : (e.InnerException==null)?(

and do it easier?

This the sample exception message for that issue:

{System.Data.SqlClient.SqlException: Procedure or Function 'sp_addError' expects parameter '@.InnerException', which was not supplied.

Thanks,

Pawe?

That depends on your Procedure declaration, you will have to allow a default parameter in orde to let it work.

CREATE PROCEDURE sp_addError
(
@.InnerException VARCHAR(1000) = NULL --Which declares the default if no value is passed through
)
(...)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Cool :) Big thanks!

Pawe?

|||Thank you very much
It was solution of my Big problem