Hassaan Sohail Hassaan Sohail - 5 months ago 23
SQL Question

Invalid column name on adpater fill

I am getting a System.Data.SqlClient.SqlException with additional information of an Invalid column name Jun on Fill function while I am entering 19-jun-2016 from the datetimePicker and here is a Jun is a month but it taking it as a column.

ReportForm.cs

public void MakeDailyReport(string givenDate, DataGridView view)
{
con.Open();
cmd = new SqlCommand("SELECT Date FROM FinalSales where Date = @datePicker", con);
cmd.Parameters.AddWithValue("@datePicker", givenDate);
cmd.ExecuteNonQuery();
DateTime dateObject = (DateTime)cmd.ExecuteScalar();
string dateObjectstring = Convert.ToString(dateObject.ToShortDateString());
string givenDateString = Convert.ToString(givenDate);

if (dateObjectstring == givenDateString)
{
DataTable dt = new DataTable();
adapt = new SqlDataAdapter("SELECT Date FROM FinalSales where Date = " + givenDate + "", con);
if (adapt != null)
{
adapt.Fill(dt);

view.DataSource = dt;
}
else
{
MessageBox.Show("No Record found againts that date");
con.Close();
}
}
else
{
con.Close();
}
}

Answer

Don't use string concatenation to build your query but sql parameters with the correct type. That will also prevent you from sql injection and other possible issues (like this one).

adapt = new SqlDataAdapter("SELECT [Date] FROM FinalSales where [Date] = @givenDate", con);
var dateParameter = adapt.SelectCommand.Parameters.Add("@givenDate", SqlDbType.DateTime);
dateParameter.Value = dateTimePicker.Value;  // not string but the correct type DateTime