How do I convert an sql parameter value into datetime?

I have written the attached C# code for my web site using visual studio 2008. I have created a dataset for my reportviewer control that is getting filled from the results of a stored procedure. The results from that stored procedure are of the smalldatetime value. Everytime I run my web site I get the error: "Cannot convert string to datetime" on the line:

da.Fill(thisDataSet)

I guess I need to do a conversion somewhere? I also want to show my smalldatetime data as just the date without a time value attached, is this possible?

Thanks.
ReportViewer1.Visible = true;
        SqlConnection thisConnection = new SqlConnection(thisConnectionString);
        SqlCommand cmd = new SqlCommand("CrucialDates.proc_ShowCrucialByDate", thisConnection);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@DueDateFrom", tbCrucialSearchDateFrom.Text);
        cmd.Parameters.AddWithValue("@DueDateTo".ToString(), tbCrucialSearchDateTo.Text);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        System.Data.DataSet thisDataSet = new System.Data.DataSet();
        da.Fill(thisDataSet);
 
        ReportDataSource datasource = new ReportDataSource("CrucialDatesDataSet_proc_ShowCrucialByDate", thisDataSet.Tables[0]);
 
        ReportViewer1.LocalReport.DataSources.Clear();
        ReportViewer1.LocalReport.DataSources.Add(datasource);
        if (thisDataSet.Tables[0].Rows.Count == 0)
        {
            lblMessage.Text = "No Crucial Dates are due!";
        }
 
        ReportViewer1.LocalReport.Refresh();

Open in new window

ShepweddAsked:
Who is Participating?
 
Fernando SotoConnect With a Mentor RetiredCommented:
Hi Shepwedd;

Try the following:

When you design your MicrosoftReportViewer and have it all defined, Page, Group and Columns click on the Row with the Date Field, the text box should start with =Field!... Once the text box is selected right click on it and select Properties from the context menu. When the TextBox Properties windows open select the Format tab at the top of the page. To the right of the "Format code:" textbox their is a button with the text "...", click on it. When the Choose Format window opens select date in the left list box and in the right listbox select 5/8/2009. Then click on "OK" button, Then click on "OK" button again in the other window and that should do it.

Fernando
0
 
arhameCommented:
Hmm, this is the syntax I use when doing a SQL datetime insert:

insertCommand.Parameters.Add("LaborDate", Data.SqlDbType.DateTime).Value = tbDateWorked.Text
0
 
käµfm³d 👽Commented:
Have you tried
        cmd.Parameters.AddWithValue("@DueDateFrom", Convert.ToDateTime(tbCrucialSearchDateFrom.Text));
        cmd.Parameters.AddWithValue("@DueDateTo".ToString(), Convert.ToDateTime(tbCrucialSearchDateTo.Text));

Open in new window

0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Fernando SotoRetiredCommented:
Hi Shepwedd;

Try changing the two lines in the code snippet.

Fernando
cmd.Parameters.AddWithValue("@DueDateFrom", DateTime.Parse(tbCrucialSearchDateFrom.Text));
cmd.Parameters.AddWithValue("@DueDateTo", DateTime.Parse(tbCrucialSearchDateTo.Text));

Open in new window

0
 
ShepweddAuthor Commented:
kaufmed,

That worked, great!

I also want to show my smalldatetime data as just the date without a time value attached, is this possible?

Thanks.
0
 
ShepweddAuthor Commented:
That worked as well Fernando.

Can you help me with my other question stated above?
0
 
käµfm³d 👽Commented:
DateTime objects have an overloaded ToString() method that takes a formatting parameter:
DateTime d = DateTime.Now;
 
d.ToString("MM/dd/yyyy");   // Note 'M' as opposed to 'm'; 'm' is for minutes

Open in new window

0
 
Fernando SotoRetiredCommented:
Hi Shepwedd;

I am not a ASP .Net programmer and am not sure about ReportViewer for the web. Let me look at the documentation and will post in a little while.

Fernando
0
 
ShepweddAuthor Commented:
Thanks Fernando.
0
 
ShepweddAuthor Commented:
kaufmed,

How do I use your suggested code so my reportviewer control shows the correctly formatted date?
0
 
Fernando SotoRetiredCommented:
Hi Shepwedd;

Try the following:

When you design your MicrosoftReportViewer and have it all defined, Page, Group and Columns click on the Row with the Date Field, the text box should start with =Field!... Once the text box is selected right click on it and select Properties from the context menu. When the TextBox Properties windows open select the Format tab at the top of the page. To the right of the "Format code:" textbox their is a button with the text "...", click on it. When the Choose Format window opens select date in the left list box and in the right listbox select 5/8/2009. Then click on "OK" button, Then click on "OK" button again in the other window and that should do it.

Fernando
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.