CODEWITHSUNDEEP
asp.netsql
Erikas
Erikas

Reputation: 117

Conversion failed when converting datetime from character string ASP.NET

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();

Upvotes: 1

Views: 1560

Answers (2)

El Ronnoco
El Ronnoco

Reputation: 11922

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.

Upvotes: 0

James
James

Reputation: 82096

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.

Upvotes: 2

Related Questions