Failed to convert parameter value from String to a DateTime

Post Reply
User avatar
Trebor29
Lieutenant
Lieutenant
Posts: 75
Joined: Thu Apr 29, 2010 12:34 am

Failed to convert parameter value from String to a DateTime

Post by Trebor29 » Wed Jan 18, 2012 9:23 am

Hi, im creating part of a recruitment agency website in (Visual Studio) ASP.Net 3.5 for an assignment that utilises a local web service to display job records. Im trying to get all jobs posted within the last 7 days.

Everything was working until I try to query the SqlDataBase (.mdf) by passing a date dd/mm/yyyy as a string. I receive the error "Failed to convert parameter value from a String to a DateTime" when I try to run. The db field type is set to 'date', and I have tried querying with a DateTime, instead of a string... but no joy! and dont think thats possible anyway.

I am double confused as when I run my query in the 'Query Builder' I need to use mm/dd/yyyy, but when I use 'Preview Data' option I have to use dd/mm/yyyy format...!?

Any help will be gratefully received...



Code behind (.cs)

protected void searchDaysButton_Click(object sender, EventArgs e)
{
if (daysRadioButtonList.SelectedIndex == 0)
{
string dateAdded = DateTime.Today.Date.Subtract(TimeSpan.FromDays(7)).ToString("dd/mm/yyyy");

//keywordTextBox.Text = dateAdded;
s.searchJobsByDateAdded(dateAdded);
}
}


Web Service method

[WebMethod] // this ges jobs by date added@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
public DataSet1.JobsTableDataTable searchJobsByDateAdded(string date2)
{
DataSet1TableAdapters.JobsTableTableAdapter ta = new DataSet1TableAdapters.JobsTableTableAdapter();
return ta.GetDataByDateAdded2(date2);
}


Query in Job table (works in query builder!)

SELECT JobId, AgencyId, Title, Salary_Rate, Benefits, Keywords, JobType, Location, Start_Date, Job_Skills_1, Job_Skills_2, Job_Skills_3, Job_Skills_4, Other_Experience,
Description, Job_Ref, Date_Posted, Expiry_Date
FROM JobsTable
WHERE (Date_Posted >= @date2)


Sorry about all the //notes!
User avatar
Enigma
Lieutenant
Lieutenant
Posts: 74
Joined: Sun Jan 16, 2011 12:40 am
Location: Colombo, Sri Lanka

Re: Failed to convert parameter value from String to a DateTime

Post by Enigma » Wed Jan 18, 2012 12:14 pm

Hi

Instead of

Code: Select all

string dateAdded = DateTime.Today.Date.Subtract(TimeSpan.FromDays(7)).ToString("dd/mm/yyyy");
try

Code: Select all

string dateAdded = DateTime.Today.Date.Subtract(TimeSpan.FromDays(7)).ToString("d");
Thanks
User avatar
Trebor29
Lieutenant
Lieutenant
Posts: 75
Joined: Thu Apr 29, 2010 12:34 am

Re: Failed to convert parameter value from String to a DateTime

Post by Trebor29 » Wed Jan 18, 2012 7:02 pm

Hi Enigma, thank you for your quick response...

Unfortunatly that has not worked. Its strange because the application does not let me get to the point when I specify a date or get to press the button_click to invoke that line of code, instead falls over before the application has even loaded. Do you know why it would want to convert a string to a DateTime at run time? (before Ive even asked it to run that particular query to get jobs by date).

I dont know if this is any good but ive posted the stack trace below;

System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.FormatException: Failed to convert parameter value from a String to a DateTime. ---> System.FormatException: String was not recognized as a valid DateTime.
at System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi, DateTimeStyles styles)
at System.Convert.ToDateTime(String value, IFormatProvider provider)
at System.String.System.IConvertible.ToDateTime(IFormatProvider provider)
at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType)
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType)
at System.Data.SqlClient.SqlParameter.GetCoercedValue()
at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
at System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters)
at System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at DataSet1TableAdapters.JobsTableTableAdapter.GetDataByDateAdded2(String date2)
at Service.searchJobsByDateAdded(String date2)
--- End of inner exception stack trace ---

Also -

Line 494: object[] results = this.Invoke("searchJobsByDateAdded", new object[] {

is highlighted in red... searchJobsByDateAdded is my web service method!

Thank you again! :clap:
User avatar
Enigma
Lieutenant
Lieutenant
Posts: 74
Joined: Sun Jan 16, 2011 12:40 am
Location: Colombo, Sri Lanka

Re: Failed to convert parameter value from String to a DateTime

Post by Enigma » Wed Jan 18, 2012 10:35 pm

Hi
Can you post your web service project. By looking at the exception its kind of hard to figure out the error . Did you try updating the service reference of your web project ?
Also when you ran the web service separately did it work ?

Thanks
User avatar
Trebor29
Lieutenant
Lieutenant
Posts: 75
Joined: Thu Apr 29, 2010 12:34 am

Re: Failed to convert parameter value from String to a DateTime

Post by Trebor29 » Wed Jan 18, 2012 11:16 pm

Yes, I tried save all, refresh all folders, update service reference about 100 times...

When I set the web service to start up project it doesn't run without the website, it just shows "Directory Listing -- /folderName/". When I run the query in the query builder it gets the correct information from the web service db.. providing i enter the correct date format! It is a local web service on my machine that I have made for this project, if that helps at all.

Ive attached the project to this post, Thanks for looking!

P.S. Please excuse the horrible Default page with all the GridViews, I have not found a way to properly display the data in a pleasing format yet... Ive been doing Windows form apps. for the last 3 years, you can tell iim new to this asp.net lark! :oops:

Thanks again!
Task3WebSite.zip
(1.07 MiB) Downloaded 461 times
User avatar
Enigma
Lieutenant
Lieutenant
Posts: 74
Joined: Sun Jan 16, 2011 12:40 am
Location: Colombo, Sri Lanka

Re: Failed to convert parameter value from String to a DateTime

Post by Enigma » Thu Jan 19, 2012 12:35 am

Hi
I have checked the web service and your asp.net project. Nothing wrong except that search by 28 days option. Since we are in 2012/1/19 if you ran this, the search date must be 2011/1/22. .NET fires an exception while converting 2011/1/22 to Date format. So that why I change the date format to MM/dd/yyyy. It did work for me.

try this

Code: Select all

 protected void searchDaysButton_Click(object sender, EventArgs e)
    {
        if (daysRadioButtonList.SelectedIndex == 0)
        {
            string dateAdded = DateTime.Today.Date.Subtract(TimeSpan.FromDays(7)).ToString("MM/dd/yyyy");

            keywordTextBox.Text = dateAdded;
            s.searchJobsByDateAdded(dateAdded);
        }
        if (daysRadioButtonList.SelectedIndex == 1)
        {
            string dateAdded = DateTime.Today.Date.Subtract(TimeSpan.FromDays(14)).ToString("MM/dd/yyyy");

            keywordTextBox.Text = dateAdded;
            s.searchJobsByDateAdded(dateAdded);
        }
        if (daysRadioButtonList.SelectedIndex == 2)
        {
            string dateAdded = DateTime.Today.Date.Subtract(TimeSpan.FromDays(28)).ToString("MM/dd/yyyy");

            keywordTextBox.Text = dateAdded;
            s.searchJobsByDateAdded(dateAdded);
        }
        if (daysRadioButtonList.SelectedIndex == 3)
        {
            // this is not correct because its calling the wrong method!
            // for display only!!!
            dateGridView.Visible = true;
            s.getAllJobs();
        }
    }


thanks
User avatar
Trebor29
Lieutenant
Lieutenant
Posts: 75
Joined: Thu Apr 29, 2010 12:34 am

Re: Failed to convert parameter value from String to a DateTime

Post by Trebor29 » Thu Jan 19, 2012 12:47 am

Ahhh.... just realised, I dont think that version I sent is set up to produce the error..

You will need to connect the objectdataSouceGridViewDate to the searchJobsByDateAdded() [webMethod] in service.cs, and then enter "" in the value box of 'Define Parameters' ..!

Sorry and

Thanks!
User avatar
Trebor29
Lieutenant
Lieutenant
Posts: 75
Joined: Thu Apr 29, 2010 12:34 am

Re: Failed to convert parameter value from String to a DateTime

Post by Trebor29 » Thu Jan 19, 2012 12:57 am

Hi Enigma, thank you for your time with this....

Unfortunatly that has not worked for, so possible it is something to do with my Visual Studio set-up or something... Hopefully it will work in class tomorrow, when it matters! :biggrin:

Thank you!
User avatar
Enigma
Lieutenant
Lieutenant
Posts: 74
Joined: Sun Jan 16, 2011 12:40 am
Location: Colombo, Sri Lanka

Re: Failed to convert parameter value from String to a DateTime

Post by Enigma » Thu Jan 19, 2012 1:31 am

Ok entering "" to the default value means when ever you get null value as a parameter it will pass "" . you need to handle this from your web service. try changing the web method like this

Code: Select all

[WebMethod] // this ges jobs by date added@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    public DataSet1.JobsTableDataTable searchJobsByDateAdded(string date2)
    {
        if(String.IsNullOrEmpty(date2.Trim()))
        {
            return new DataSet1.JobsTableDataTable();
        }
        DataSet1TableAdapters.JobsTableTableAdapter ta = new DataSet1TableAdapters.JobsTableTableAdapter();
        return ta.GetDataByDateAdded2(date2);
    }
Also in your objectdataSouceGridViewDate object source use a session variable as a parameter. Then once you get the keyword date pass it to that session variable.

Code: Select all

 if (daysRadioButtonList.SelectedIndex == 0)
        {
            string dateAdded = DateTime.Today.Date.Subtract(TimeSpan.FromDays(7)).ToString("MM/dd/yyyy");

            keywordTextBox.Text = dateAdded;
            Session["SelDate"] = dateAdded;
            dateGridView.Visible = true;
           // s.searchJobsByDateAdded(dateAdded);
        }
Also check the objectdataSouceGridViewDate configuration. Specially check the "Define Parameters". I have attached the updated project.

Thanks
Task3WebSite.zip
(1.07 MiB) Downloaded 424 times
User avatar
Trebor29
Lieutenant
Lieutenant
Posts: 75
Joined: Thu Apr 29, 2010 12:34 am

Re: Failed to convert parameter value from String to a DateTime

Post by Trebor29 » Thu Jan 19, 2012 4:56 am

Still not working Enigma... even with your copy!

It all looks fine to me, nothing obvious anyway, so I have a feeling it might be something to do with my end..!

If it works tomorrow on the Uni computers then we'll know it my set up, i'll post the outcome here to let you know...

Thank you for your time Enigma, appreciated! :D
Post Reply

Return to “ASP & ASP.Net”