Passing Date parameter from C# WinForm to SQL stored procedure

Question

I have a form with 2 dateTimePicker controls (dtpStart and dtpEnd), a button, and a datagridview to show results. The datagridview is bound to a bindingSource control.

I want to pass two date parameters from the dateTimePicker controls to the stored procedure in order to return the required scope on my datagridview.

My stored procedure looks like this:

CREATE PROC [dbo].[ProcTest](@StartDate date, @EndDate date)
AS
SELECT * FROM Test WHERE ModifiedDate BETWEEN @StartDate AND @EndDate

My C# code is:

    private void button1_Click(object sender, EventArgs e)
    {
        dc = new NorthwindDataContext();

        var Qry = dc.ProcTest(dtpStart.Value, dtpEnd.Value);
        bindingSource1.DataSource = Qry;
    }

When I run the code above I receive nothing on my datagrid, the dtpEnd.value shows: 13/08/2012 02:15:29, I assume that this is a conversion issue since I use date type in my stored procedure and the datetimepicker value is a dateTime type.

Please, how to resolve this ?

@AVD, dtpStart.Value.Date shows 13/08/2012 00:00:00 which doesn't work, and I think that to get my code to work I'll need that my SP receives something like this: '2012/08/13'. — Sami-L
– Sami-L, Commented Aug 13, 2012 at 2:28
I think a part of the solution is to use ToShortDateString() like this: dtpStart.Value.ToShortDateString() returns 13/08/2012, the query need it at this format: '2012/08/13' or '20120813' — Sami-L
– Sami-L, Commented Aug 13, 2012 at 3:18
Try to convert result to List<T> - dataGridView1.DataSource=res.ToList(); — KV Prajapati
– KV Prajapati, Commented Aug 13, 2012 at 4:09
@AVD, this seems to be a solution, I used your proposal successfuly in the SP, and dtpStart.Value.ToString("yyyy/MM/dd") returned the right format, but still cannot receive data in my datagrid, need to verify datagrid binding... I have made some google search, we have here a culture matter, so it is also possible to keep date type for the parameters and use dtpStart.Value.ToUniversalTime() — Sami-L
– Sami-L, Commented Aug 13, 2012 at 4:13

Dave R. · Accepted Answer · 2012-08-13 04:10:50Z

2

You need to retrieve the results of your query, e.g.:

bindingSource1.DataSource = Qry.ToList();

I don't think the issue is with the Date parameter at all. That's supported according to:

http://msdn.microsoft.com/en-us/library/bb386947.aspx

answered Aug 13, 2012 at 4:10

Dave R.

7,3033 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sami-L Over a year ago

Thank you for your advice, nothing changed when tried bindingSource1.DataSource = Qry.ToList();

Dave R. Over a year ago

@AlphaBird - Have you tried hard-coding the date parameters, to ensure that your stored procedure is actually returning something?

Sami-L Over a year ago

After removing parameters from SP and running the program, I found that the problem was that the bindingsource was not bound at design time to the SP result, so I created a new datasource object pointing to the result of the SP and then the datagrid shown data. And there is no need to add .ToList() to the query.

Dave R. Over a year ago

@AlphaBird - I'm sorry ToList() wasn't required after all. The examples I'd seen use it, so I thought that was the issue. It's great that the project is all fixed now, though.

Pintu Paul · Accepted Answer · 2012-08-13 05:23:56Z

0

Instead of this

var Qry = dc.ProcTest(dtpStart.Value, dtpEnd.Value);

use this and check if your problem get solved.

var Qry = dc.ProcTest(dtpStart.ToString("dd-MMM-yyyy"), dtpEnd.ToString("dd-MMM-yyyy"));

answered Aug 13, 2012 at 5:23

Pintu Paul

3993 silver badges18 bronze badges

2 Comments

Sami-L Over a year ago

No overload for method 'ToString'.

Pintu Paul Over a year ago

k,I have edited the code, and posted just below. just check this also. var Qry = dc.ProcTest(Convert.ToDateTime(dtpStart).ToString("dd-MMM-yyyy"), Convert.ToDateTime(dtpEnd).ToString("dd-MMM-yyyy"));

Sami-L · Accepted Answer · 2012-08-13 14:28:21Z

0

Thanks to the link provided by Dave R.:http://msdn.microsoft.com/en-us/library/bb386947.aspx I found out that my code could work without any modification. So I searched the error first by removing parameters from SP, then I found that I had to create a new datasource object pointing to the result of the SP, and to bind it at design time to the datagridview. Now the datagrid shows data and parameters are working.

Sorry Guys, I had to verify that before posting my question, but I was very confused about Date parameters usage.

so I Keep the code as it is in the initial question:

private void button1_Click(object sender, EventArgs e)
{
    dc = new NorthwindDataContext();

    var Qry = dc.ProcTest(dtpStart.Value, dtpEnd.Value);
    bindingSource1.DataSource = Qry;
}

edited Aug 13, 2012 at 14:28

answered Aug 13, 2012 at 14:17

Sami-L

5,70518 gold badges66 silver badges93 bronze badges

Comments

Michael A · Accepted Answer · 2012-08-13 03:05:32Z

-1

I beleive you need to send the date, not the value of your datetime picker.

Basically, change this line:

var Qry = dc.ProcTest(dtpStart.Value, dtpEnd.Value);

To read:

var Qry = dc.ProcTest(dtpStart.SelectedDate.Value, dtpEnd.SelectedDate.Value);

If that still doesn't work I would try:

var Qry = dc.ProcTest(dtpStart.SelectedDate.Value.Date, dtpEnd.SelectedDate.Value.Date);

answered Aug 13, 2012 at 3:05

Michael A

9,98022 gold badges74 silver badges117 bronze badges

2 Comments

Michael A Over a year ago

If you're going to downvote, could you please at least explain why?

Sami-L Over a year ago

Someone have downvoted I think because SelectedDate is not a property of dateTimePicker control.

Collectives™ on Stack Overflow

Passing Date parameter from C# WinForm to SQL stored procedure

4 Answers 4

4 Comments

2 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related