Conversion failed when converting datetime from character string ASP.NET

Question

I get a Conversion failed error when converting DateTime from character string, any help?

sqlcon.Open();
sqlcmd = new SqlCommand("select [Uzsak_Nr],[Preke],[Uzsak_Kiekis],[Gaut_Kiekis],[MyDate],[Gaut_Data] from emp where MyDate between '" + TextBoxData1  + "'  and  '" + TextBoxData2 + "' ", sqlcon);
da = new SqlDataAdapter(sqlcmd);
da.Fill(dt);
GridViewRodyti.DataSource = dt;
GridViewRodyti.DataBind();
sqlcon.Close();

What are the values of your TextBoxData1 and TextBoxData2 variables? Are they strings? — Kevin Brydon
– Kevin Brydon, Commented Jan 11, 2013 at 11:54
No i slelected it from callendar control TextBoxData1.Text = CalendarRodyti.SelectedDate.ToString("yyyy/MM/dd"); this.CalendarRodyti.Visible = false; — Erikas
– Erikas, Commented Jan 11, 2013 at 11:55
That's great but you would be best to show the code which populates those variables. Its likely that the TexBoxDataX variables are in a date format that your database cannot parse. What DB are you using? — Kevin Brydon
– Kevin Brydon, Commented Jan 11, 2013 at 11:58
Im using sql server express 2012 and i want to select tada beetween one date and other and those dates i want to choose from textboxes and those text boxes are connected with callendar when i select date on callendar the text wrotes on textboxdate1 and so on.. Sry for my bad english grama :( i hope u understand what i want to say :) — Erikas
– Erikas, Commented Jan 11, 2013 at 12:04

James · Accepted Answer · 2013-01-11 12:06:27Z

2

This has got SQL Injection written all over it...

Use parameterized queries e.g.

var sqlCmd = new SqlCommand("select [Uzsak_Nr],[Preke],[Uzsak_Kiekis],[Gaut_Kiekis],[MyDate],[Gaut_Data] from emp where MyDate between '@startDate' and '@endDate'", sqlcon);
sqlCmd.Parameters.Add(new SqlParameter("@startDate", DateTime.Parse(TextBoxData1)));
sqlCmd.Parameters.Add(new SqlParameter("@endDate", DateTime.Parse(TextBoxData2)));

Your issue is probably due to an invalid date format, if you use SqlParameter and convert the string to a DateTime it should take care of the conversion for you.

edited Jan 11, 2013 at 12:06

answered Jan 11, 2013 at 11:57

James

82.4k19 gold badges172 silver badges245 bronze badges

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

6 Comments

Kevin Brydon Over a year ago

user1945561 is not asking for advice on how to make their code more secure

James Over a year ago

@KevinBrydon maybe not, but I am giving the advice anyway. Not only will it make his code more secure but it will fix his conversion issue. The problem is trying to pass dates to SQL as strings is never a good idea due to potential localization issues. Best letting the framework do that for you.

Erikas Over a year ago

Compilation Error Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. Compiler Error Message: CS1026: ) expected

James Over a year ago

@user1945561 looks like you have an extra closing bracket somewhere in your code.

El Ronnoco Over a year ago

You probably have other issues. See this

|

El Ronnoco · Accepted Answer · 2013-01-11 12:19:20Z

0

I would advise you do it like this...

sqlcon.Open();
DateTime date1;
DateTime date2;
DateTime.TryParse(TextBoxData1.Text, out date1); //is TextBoxData1 a TextBox?
DateTime.TryParse(TextBoxData2.Text, out date2);


if (date1 != null && date2 != null) {    
    sqlcmd = new SqlCommand(
      "select [Uzsak_Nr],[Preke],[Uzsak_Kiekis],[Gaut_Kiekis],[MyDate],[Gaut_Data] " +
      "from emp where MyDate between @dt1 and  @dt2 ", sqlcon);
    sqlcmd.Parameters.AddWithValue("@dt1", date1);
    sqlcmd.Parameters.AddWithValue("@dt2", date2);
    da = new SqlDataAdapter(sqlcmd);
    da.Fill(dt);
    GridViewRodyti.DataSource = dt;
    GridViewRodyti.DataBind();
    sqlcon.Close();
}

Using parameterised queries is much nicer and doesn't run the risk of injection.

I have assumed you are using SQLServer.

edited Jan 11, 2013 at 12:19

answered Jan 11, 2013 at 12:01

El Ronnoco

11.9k5 gold badges41 silver badges67 bronze badges

1 Comment

El Ronnoco Over a year ago

@user1945561 If answer has solved your problem, please mark it as correct!

Collectives™ on Stack Overflow

Conversion failed when converting datetime from character string ASP.NET

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related