We help IT Professionals succeed at work.

How do I convert an sql parameter value into datetime?

579 Views
Last Modified: 2013-12-17
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

Comment
Watch Question

Commented:
Hmm, this is the syntax I use when doing a SQL datetime insert:

insertCommand.Parameters.Add("LaborDate", Data.SqlDbType.DateTime).Value = tbDateWorked.Text
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2015

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

Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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

Author

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.

Author

Commented:
That worked as well Fernando.

Can you help me with my other question stated above?
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2015

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

Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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

Author

Commented:
Thanks Fernando.

Author

Commented:
kaufmed,

How do I use your suggested code so my reportviewer control shows the correctly formatted date?
Retired
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.