CODEWITHSUNDEEP
c#sqldatabasegridviewtextbox
Rico Brouwer
Rico Brouwer

Reputation: 81

Sql query between two dates with textboxes

I have a query that gets data from my database and it needs to filter out data between certain dates that the user will fill in two textboxes. I need to fill in the textboxes like: 2016-9-13 otherwise it wont work (the date in the database is also 2016-9-13). But when it fills the gridview the cell says: 13-9-2016.

I want to fill in the dates in the order like: 13-9-2016. How can i do this and what do i need to change?

Here is some code that gives me the data from the database.

 connect = new SqlConnection(@"Data Source=LP12;Initial Catalog=Data;Integrated Security=True");
        connect.Open();

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = connect;
        cmd.CommandText = "SELECT DrukSensor, FlowSensor, TempSensor, KwaliteitSensor, OlieVerbruik, Toerental, DateTime, Schip FROM SysteemSensorInfo WHERE DateTime BETWEEN @StartDate AND @EndDate";

        cmd.Parameters.AddWithValue("@StartDate", TextBoxBeginDatum.Text);
        cmd.Parameters.AddWithValue("@EndDate", TextBoxEindDatum.Text);
        DataSet ds = new DataSet();
        new SqlDataAdapter(cmd).Fill(ds);
        GridView1.DataSource = ds.Tables[0];
        GridView1.DataBind();

Upvotes: 1

Views: 1631

Answers (2)

maulik kansara
maulik kansara

Reputation: 1107

You should use Datediff() function. Internally, It will manage valid date in any format for dates comparison. 

"SELECT DrukSensor, FlowSensor, TempSensor, KwaliteitSensor, OlieVerbruik,     Toerental, DateTime, Schip 
FROM SysteemSensorInfo 
WHERE datediff(day,DateTime,@StartDate)<=0 AND datediff(day,DateTime,@EndDate)>=0"

Upvotes: 1

Marc Gravell
Marc Gravell

Reputation: 1063609

Basically, parse the dates and pass down DateTime values:

cmd.Parameters.AddWithValue("@StartDate", ParseDate(TextBoxBeginDatum.Text));
cmd.Parameters.AddWithValue("@EndDate", ParseDate(TextBoxEindDatum.Text));

...

static DateTime ParseDate(string text) {
    // TODO; possibly just: return DateTime.Parse(text);
}

Upvotes: 3

Related Questions