How can I query a DataGridView using SQL?

Question

I need to query a DataGridView using SQL but don't show to DataGridView.

public chkTime()
{
    InitializeComponent();
}

HRTaffDataContext db = new HRTaffDataContext();
SqlConnection Conn;
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da;
DataTable dt = new DataTable();
DataSet ds = new DataSet();
StringBuilder sb = new StringBuilder();
string appConn = ConfigurationManager.ConnectionStrings["connDB"].ConnectionString;
string strDate;

private void chkTime_Load(object sender, EventArgs e)
{
    connStr();
    return;
}

public void connStr()
{
    Conn = new SqlConnection();
    if (Conn.State == ConnectionState.Open)
    {
        Conn.Close();
    }
    Conn.ConnectionString = appConn;
    Conn.Open();
}

private void button2_Click(object sender, EventArgs e)
{
    SqlConnection conn = new SqlConnection(appConn);

    string sql = "SELECT [filesTA].EmpNo,[Employee].[First Name],[filesTA].ChkDate,[filesTA].ChkIn,[filesTA].ChkOut,[CompanyData].ShortName"
    + " From [WebSP].[dbo].[filesTA] inner join [WebSP].[dbo].[Employee] on [Employee].EmployeeNo=[filesTA].EmpNo INNER JOIN [WebSP].[dbo].[CompanyData] On [CompanyData].Company = [Employee].Company"
    + " WHERE [filesTA].ErrorCode = 0"; // It's work
    + " WHERE [filesTA].ErrorCode = 0 and [filesTA].ChkDate ='" + dateTimePicker.Text.ToString() + "'";

    da = new SqlDataAdapter(sql, Conn);
    DataSet ds = new DataSet();
    da.Fill(ds);
    Conn.Close();

    dgvShow.DataSource = ds.Tables[0];
}

"WHERE [filesTA].ErrorCode = 0" works fine.
"WHERE [filesTA].ErrorCode = 0 and [filesTA].ChkDate ='" + dateTimePicker.Text.ToString() + "'" does not work.

I need to set where DateTime.

That means your [filesTA].ChkDate doesn't match the date you selected. — MVCKarl
– MVCKarl, Commented Nov 13, 2012 at 7:46
FYI, why don't you use the SQLParameter? Easy to use and prevent any SQL injection. Here you can see a little example — Gianni B.
– Gianni B., Commented Nov 13, 2012 at 7:47

joseangelmt · Accepted Answer · 2012-11-13 08:03:13Z

1

$dateTimePicker.Text returns a string for human reading and your server maybe don't like it.

Try something like:

string sql = string.Format("SELECT [filesTA].EmpNo,[Employee].[First Name],[filesTA].ChkDate,[filesTA].ChkIn,[filesTA].ChkOut,[CompanyData].ShortName"
            + " From [WebSP].[dbo].[filesTA] inner join [WebSP].[dbo].[Employee] on [Employee].EmployeeNo=[filesTA].EmpNo INNER JOIN [WebSP].[dbo].[CompanyData] On [CompanyData].Company = [Employee].Company"
            + " WHERE [filesTA].ErrorCode = 0 and [filesTA].ChkDate ='{0}-{1}-{2}'",
                dateTimePicker.Value.Year,
                dateTimePicker.Value.Month,
                dateTimePicker.Value.Day);

edited Nov 13, 2012 at 8:03

answered Nov 13, 2012 at 7:57

joseangelmt

2,16820 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nettoon493 Over a year ago

thank so much! it's work. :D ='{0}-{1}-{2}'" << does it's mean??

Sami · Accepted Answer · 2012-11-13 08:47:54Z

0

You need to use dateTimePicker.Value.ToString() instead of dateTimePicker.Text.ToString()

A simple example of using this can be dateTimePicker.Value.ToString("yyyy-MM-dd")

answered Nov 13, 2012 at 8:47

Sami

8,4599 gold badges72 silver badges104 bronze badges

1 Comment

nettoon493 Over a year ago

thanks so much. i try dateTimePicker.Value.ToString("yyyy-MM-dd") it's workd. :D

Collectives™ on Stack Overflow

How can I query a DataGridView using SQL?

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related