Setting DateTime as a SqlDataSource parameter for Gridview

Question

Hey I would like to display certain data with my stored procedure for the last 30 days. here is what I have done (aspx.cs file):

 protected void Page_Load(object sender, EventArgs e)     
        {
          DateTime toDate, fromDate;
          toDate = DateTime.Now;

          fromDate = toDate.Subtract(new TimeSpan(31, 0, 0, 0));

          SqlDataSource1.SelectParameters.Add("fromDate", DbType.DateTime, fromDate.ToString());
          SqlDataSource1.SelectParameters.Add("toDate", DbType.DateTime, toDate.ToString());                    

    }

here is my aspx file

 <form id="form1" runat="server">
        <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" Width="232px" DataKeyNames="CustomerId" DataSourceID="SqlDataSource1">
            <Columns>
                <asp:BoundField DataField="CreationDate" HeaderText="CreationDate" SortExpression="CreationDate" />
            </Columns>
        </asp:GridView>
        <br />
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SSEnewConnectionString %>"
            SelectCommand="procCustomer_SelectbyCreationDate" SelectCommandType="StoredProcedure">
            <SelectParameters>
                <asp:Parameter DbType="DateTime" Name="fromDate" />
                <asp:Parameter DbType="DateTime" Name="toDate" />
            </SelectParameters>
        </asp:SqlDataSource>
        </form>

when I test this my screen comes up blank (other than the masterpage elements) and no errors. any ideas?

well do you have any data there ? – V4Vendetta Jan 09 '12 at 11:04 — V4Vendetta, Jan 09 '12 at 11:04

Jon Skeet · Accepted Answer · 2014-03-04T15:26:47.850

4

EDIT: Even though an earlier version of this answer was accepted, it looks like I had misunderstood the parameter type used. The web controls ParameterCollection looks somewhat awful.

I would suggest converting date values to a SQL format (much as that pains me, frankly - string conversions should be avoided as far as possible). For example:

SqlDataSource1.SelectParameters.Add("fromDate", DbType.DateTime,
    fromDate.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture));
SqlDataSource1.SelectParameters.Add("toDate", DbType.DateTime,
    toDate.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture));

(Change to yyyy-MM-dd for date-only types.)

I haven't used SqlDataSource myself, but it also looks like you're introducing the parameters twice - once in the markup, and once in the code. Given that you don't have the values in the markup (including in bindings), you may want to remove them from there - but I could be wrong on that front.

If your query isn't doing what you expect, you should check your database logs (or whatever tool is appropriate) to check what actual query is executing.

edited Mar 04 '14 at 15:26

answered Jan 09 '12 at 11:05

Jon Skeet

1,421,763
867
9,128
9,194

1

If i remove the toString() i get following error "The best overloaded method match for 'System.Web.UI.WebControls.ParameterCollection.Add(string, System.Data.DbType, string)' has some invalid arguments" – user1051364 Jan 09 '12 at 11:30
1

also, removing the parameters from the aspx file seemed to solve the problem... at least I get some data on the screen now. I had to use the toString() conversion though... – user1051364 Jan 09 '12 at 11:32
I have the exact same problem. The .Add method only allows for strings and in my case it causes an error in the SQL. Not really sure how to proceed. – carny666 Mar 04 '14 at 14:35
@carny666: No, it really doesn't only allow for strings. I suggest you ask a new question with appropriate details. – Jon Skeet Mar 04 '14 at 14:38
@JonSkeet Sure.. but I'd ask the EXACT same question but state that the .Add method will only allow me to use a string regardless of what type I chose, furthermore the .DefaultValue will only accept a string as an argument. In my case, converting my DateTime to a string to accommodate these methods causes an SQL error.. "ORA-01036: illegal variable name/number" – carny666 Mar 04 '14 at 14:59
@carny666: What do you mean by "will only allow me to use a string"? Where, exactly, and what error do you get otherwise? And what is the type of your data source here? (Note that this question is about SqlDataSource...) – Jon Skeet Mar 04 '14 at 15:00
@JonSkeet dsLadleStatus.SelectParameters["SHIFT_ST_EST"].DefaultValue = shiftd; shiftd must be of typestring... dsLadleStatus.SelectParameters.Add("SHIFT_ST_EST", TypeCode.DateTime, shiftd); Again... shiftd must be of typestring... – carny666 Mar 04 '14 at 15:06
1

@carny666: Right, yes, the third parameter is of type `string`. (It wasn't clear to me what you meant before.) I have to admit, when I wrote this answer (a long time ago now) I suspect I was getting confused with `SqlDbParameter`. This looks like a thoroughly awful API - I'll edit with some suggestions though. – Jon Skeet Mar 04 '14 at 15:24
If `yyyy-MM-dd` has issues since the date format on the server is YDM, then try `yyyyMMdd` instead. – mjwills Nov 30 '20 at 04:52

Setting DateTime as a SqlDataSource parameter for Gridview

1 Answers1

Linked

Related