CODEWITHSUNDEEP
c#asp.netsql-server
Sonu_Orai
Sonu_Orai

Reputation: 397

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

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;
}

Upvotes: 0

Views: 1773

Answers (2)

Khan
Khan

Reputation: 18162

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;

Upvotes: 0

Gary Walker
Gary Walker

Reputation: 9134

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

Upvotes: 1

Related Questions