I have a web form with a gridview and two datepickers and a submit to submit the new query with the datetime constraints. to be the start and end dates to filter the data. The data loads fine without the data and when the submit button with the chosen dates is clicked, nothing happens. I wonder if the filtered data isn't being bound properly(I'm new to web forms and gridview.) Here is the codebehind for the page:

protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
            BindDataToGridView();
    }

    protected void dashboard_RowEditing(object sender, GridViewEditEventArgs e)
    {
        gridErrors.Text = string.Empty;
        dashboard.EditIndex = e.NewEditIndex;
        BindDataToGridView();
    }

    protected void dashboard_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        dashboard.EditIndex = -1;
        BindDataToGridView();
    }
    protected void dashboard_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        dashboard.PageIndex = e.NewPageIndex;
        BindDataToGridView();
    }
    protected void dashboard_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        gridErrors.Text = string.Empty;
        GridViewRow tabRow = (GridViewRow)dashboard.Rows[e.RowIndex];
        HiddenField hdnTabId = (HiddenField)tabRow.FindControl("hdnTabId");
        TextBox TxtName = (TextBox)tabRow.Cells[1].Controls[0];
    }

    protected void button1_Click(object sender, EventArgs e)
    {
        var start = startDate.Text;
        var startTime = DateTime.Parse(start);
        var sqlStart = startTime.ToString("yyyy-MM-dd");

        var end = endDate.Text;
        var endTime = DateTime.Parse(end);
        var sqlEnd = endTime.ToString("yyyy-MM-dd");

        string sqlQuery = "SELECT TOP(100) TabID, TabName, Title, CreatedOnDate, TabPath From TableName "  
            "where CreatedOnDate >= "   sqlStart   " and CreatedOnDate <= "   sqlEnd     " Order By TabName";

        BindDataToGridView(sqlQuery);
    }

    public void BindDataToGridView(string sqlQuery =
        "SELECT TOP(100) TabID, TabName, Title, CreatedOnDate, TabPath From TableName Order By TabName")
    {
        var connectionFromConfig = WebConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;

        using (SqlConnection db = new SqlConnection(connectionFromConfig))
        {
            try
            {
                db.Open();
                SqlCommand command = new SqlCommand(sqlQuery, db);
                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                DataSet dataSet = new DataSet();
                dataAdapter.Fill(dataSet);

                if (dataSet.Tables[0].Rows.Count > 0)
                {
                    dashboard.DataSource = dataSet;
                    dashboard.DataBind();
                }
            }
            catch (SqlException ex)
            {
                gridErrors.Text = ex.Message;
            }
            finally
            {
                db.Close();
                db.Dispose();
            }
        }
    }

OnPageIndexChanging Method

protected void dashboard_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        dashboard.PageIndex = e.NewPageIndex;
        BindDataToGridView();
    }

CodePudding user response：

ok, you found the issue (missing quotes). But, at the end of the day? Yes, everyone will come pile in and note how "when" taking user input, you don't want to concatenate into the sql (too high risk for sql injection).

However, JUST telling you not to concatenate parameters don't help UNLESS we ALSO give you a nice design approach. Looking at your code, you did a GREAT idea to have one grid view load routine. And you ALSO want with great ease to pass sql to that one routine. However, that great idea goal conflicts with using strong typed parameters.

So, lets kill two birds with one stone here.

and find that using parameters is often LESS code over all then a long messy sql string, which is prone to errors (quite much why you had to post here).

And for numbers - no quotes, for strings - yes, and for dates, again yes. So that's an extra developer workload. And that long messy string is HARD to write, debug, and manage.

So, I suggest this code:

protected void Page_Load(object sender, EventArgs e)
{
        if (!Page.IsPostBack)
            BindDataToGridView();
}

void BindDataToGridView(SqlCommand cmdSQL = null)
{
    // default sql
    if (cmdSQL is null)
    {
        cmdSQL = new 
            SqlCommand("SELECT TabID, TabName, Title, CreatedOnDate, TabPath From TableName Order By TabName");
    }
        
    using (cmdSQL)
    {
        cmdSQL.Connection = new SqlConnection(conString);
        cmdSQL.Connection.Open();
        DataTable rst = new DataTable();            
        rst.Load(cmdSQL.ExecuteReader());
        dashboard.DataSource = rst;
        dashboard.DataBind();
    }
}

note several things: We did NOT have to create a separate connection object - sqlcommand has one!! (and since we did not create a separate connection object, then disposing of the sql command takes care of that with our using block!!!

We did not need a separate datatable/dataset, and we did NOT need a data adaptor, and we did NOT need a reader either!! (sql command object has a reader too!!!).

So, now we can call our load grid without a parameter.

But, for the case WHEN we want to pass sql with parameters? Then we can do this:

{
    string sqlQuery 
      = "SELECT TabID, TabName, Title, CreatedOnDate, TabPath From TableName "  
        "WERE CreatedOnDate >= @Start AND <= @End ORDER By TabName";

    SqlCommand cmdSQL = new SqlCommand(sqlQuery);

    cmdSQL.Parameters.Add("@Start", SqlDbType.Date).Value = startDate.Text;
    cmdSQL.Parameters.Add("@End", SqlDbType.Date).Value = endDate.Text;

    BindDataToGridView(cmdSQL);
}

So, in fact, we can have parameters, we can have less code. We can have easy to read SQL, and we can pass the cmd SQL to that one routine.

So it NOT ONLY the suggesting to not concatenate strings, but with the right approach, we don't have to, we get strong data type conversion, and less code, and even less chances of errors in that code.

Also, check if the sql server column is a date, or datetime. If it is datetime, then specify that data type in the query parameter.

Also, you can droop the TOP clause - it only required when creating a SQL view which in theory does not support a sorted output. But for raw sql, then you can drop the TOP clause.

CodePudding user response：

I needed to add single quotes before and after the sqlStart and sqlEnd variables for Sql Server to use the values with comparison operators.