how to get data between two dates from sql server 2008 in asp.net

Question

I'm using referenced date-picker control to select date. I get the error

Conversion failed when converting date and/or time from character string

public DataSet comsn(string x, DatePickerControl.DatePicker a, DatePickerControl.DatePicker b)
{

    ConnectionStringSettings connectionstringsql = ConfigurationManager.ConnectionStrings["plprojectConnectionString"];
    SqlConnection connectionsql = new SqlConnection(connectionstringsql.ConnectionString);
    if (connectionsql.State != System.Data.ConnectionState.Open)
    {
        connectionsql.Open();
    }
    SqlCommand cmd = new SqlCommand("select a_id,commtyp,comm,primm,c_id,agent from comm where a_id= '" + x + "' AND  date>= '" + a.CalendarDate + "'  AND date <= '" + b.CalendarDate + "' ", connectionsql);
    SqlDataAdapter adp = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    adp.Fill(ds, "comm");     //<------ At this line error occurred [ Conversion failed when converting date and/or time from character string ]
    adp.Dispose();
    return ds;
}

I suspect your calendar picker is returning a string. Try DateTime.Parse(a.CalendarDate) — Melanie
– Melanie, Commented Sep 26, 2013 at 16:47

Gary Walker · Accepted Answer · 2013-09-26 16:51:01Z

1

You can make this work by controlling the date format. When supplied to sql server as a string, I always format my data only string using 'YYYY-MM-DD', i.e., using the ToString('yyyy-MM-dd') on a date field.

However, you are better of turning your query into a parameter driven sql.

i.e., instead of "date >= '" a.Calendardate.ToString("yyyy-MM-dd")

Use "date >= @ADate"

and the supply the parameter value as

cmd.Parameters.Add("ADate", SqlDbType.DateTime).Value = a.Calendardate

I am assuming your datepicker has a "DateTime" property I am treating a.Calendardate as the DateTime property

answered Sep 26, 2013 at 16:51

Gary Walker

9,2043 gold badges22 silver badges43 bronze badges

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

Comments

Khan · Accepted Answer · 2013-09-26 16:52:30Z

0

You can avoid the exception and sql injection by using a parameterized query.

Replace:

SqlCommand cmd = new SqlCommand("select a_id,commtyp,comm,primm,c_id,agent from comm where a_id= '" + x + "' AND  date>= '" + a.CalendarDate + "'  AND date <= '" + b.CalendarDate + "' ", connectionsql);

With:

string sql = "select a_id,commtyp,comm,primm,c_id,agent from comm where a_id= @x AND  date>= @a  AND date <= @b "
var cmd = new SqlCommand(sql);
cmd.Parameters.Add("@x", SqlDbType.NVarChar, x);
cmd.Parameters.Add("@a", SqlDbType.DateTime, a);
cmd.Parameters.Add("@b", SqlDbType.DateTime, b);
cmd.Connection = connectionsql;

answered Sep 26, 2013 at 16:52

Khan

18.2k5 gold badges52 silver badges60 bronze badges

Collectives™ on Stack Overflow

how to get data between two dates from sql server 2008 in asp.net

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related