How to convert selected date to string and view on DataGrid

Question

I'm trying to search for data between two dates and show on the datagrid. However I'm getting an error says that toString is unable to convert the selected date to string.

private void searchButton_Click(object sender, RoutedEventArgs e)
    {
        SqlConnection con = new SqlConnection(@"Data Source = xmsql04.australiaeast.cloudapp.azure.com,6302 ;Initial Catalog=DAD_TruckRental_RGM;Persist Security Info=True;User ID=xxxxxx;Password=xxxxxx");
        SqlDataAdapter sda = new SqlDataAdapter("SELECT RentalId,TruckId,CustomerID,TotalPrice,RentDate,ReturnDueDate FROM TruckRental where JoiningDate between'"+fromText.SelectedDate.Value.ToString("MM/DD/YYYY")+"'AND'"+toText1.SelectedDate.Value.ToString("MM/DD/YYYY")+"'", con);

        DataSet ds = new DataSet();

        sda.Fill(ds, "TruckRental");

        gridView2.ItemsSource = ds.DefaultViewManager;
    }

UPDATE: I have changed my code and have gotten rid of the error. However, no data is showing on in the grid, only an empty row. Would anyone know why that is?

string sqlStr = "SELECT RentalId,TruckId,CustomerID,TotalPrice,RentDate,ReturnDueDate FROM TruckRental where RentDate between @fromDT AND @toDT";
        string connStr = @"Data Source = xmsql04.australiaeast.cloudapp.azure.com,6302 ;Initial Catalog=DAD_TruckRental_RGM;Persist Security Info=True;User ID=xxxxxx;Password=xxxxxx";
        using (SqlConnection con = new SqlConnection(connStr))
        using (SqlDataAdapter sda = new SqlDataAdapter(sqlStr, con))
        {
            sda.SelectCommand.Parameters.Add(new SqlParameter("@toDT", SqlDbType.DateTime)).Value = toText1.SelectedDate.Value;
            sda.SelectCommand.Parameters.Add(new SqlParameter("@fromDT", SqlDbType.DateTime)).Value = fromText.SelectedDate.Value;

            DataSet ds = new DataSet();
            con.Open();
            sda.Fill(ds, "TruckRental");
            gridView2.ItemsSource = ds.DefaultViewManager;
        }

Possible duplicate of [What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) — mjwills, Nov 18 '18 at 22:58
Recommend you immediately delete your question as you have something in it you shouldn't — , Nov 18 '18 at 23:17

score 2 · Answer 1 · edited Nov 18 '18 at 23:18

There are some issues in your code.

Your con connection string didn't' open when you use Fill method, so you can't execute the SQL statement.
Your code has a SQL-Injection problem, I would suggest you use parameters instead of connected SQL statement string, make sure your parameter data type size as same as your table schema.
You didn't return the resource when you finish you have executed your SQL statement, I would use using statement because the purpose of Using statement is that when control will reach the end of using it will dispose that object of using block and free up memory. its purpose is not only for auto connection close, basically it will dispose of the connection object and obviously, the connection also closed due to it.

using SqlParameter class to make it.

private void searchButton_Click(object sender, RoutedEventArgs e)
{
    string sqlStr = "SELECT RentalId,TruckId,CustomerID,TotalPrice,RentDate,ReturnDueDate FROM TruckRental where JoiningDate between @fromDt AND @toDt";
    string connStr = @"Data Source = xmsql04.australiaeast.cloudapp.azure.com,6302 ;Initial Catalog=DAD_TruckRental_RGM;Persist Security Info=True;User ID=DDQ4_Melveena;Password=xxxxx";
    using (SqlConnection con = new SqlConnection(connStr))
    using (SqlDataAdapter sda = new SqlDataAdapter(sqlStr, con))
    {
        sda.SelectCommand.Parameters.Add(new SqlParameter("@toDt", SqlDbType.DateTime)).Value = toText1.SelectedDate.Value;
        sda.SelectCommand.Parameters.Add(new SqlParameter("@fromDt", SqlDbType.DateTime)).Value = fromText.SelectedDate.Value;

        DataSet ds = new DataSet();
        con.Open();
        sda.Fill(ds, "TruckRental");
        gridView2.ItemsSource = ds.DefaultViewManager;
    }
}

This answer stopped me from getting the error. However, when I click the search button, no data would show on the datagrid only an empty row. Would you have any idea why that would be? @D-Shih — Grace, Nov 19 '18 at 00:02
@GraceDelosReyes make sure the condition contain data rows, you can set a breakpoint to see is there any data rows in `ds`. — D-Shih, Nov 19 '18 at 00:26

How to convert selected date to string and view on DataGrid

1 Answers1