Sql error when selecting a record from database through C#?

[Shammikit]

so in this C# program im trying to get the value inserted at texbox1 and check if its there in the database to get a count and display a label in the form accordingly.This code gives me a error saying incorect syntax near the keyword 'Employee'. i havent misspelled anything in the sql query and the field EmpID exists in the table employee on the database. this exact code worked to me in another form where i put the sql to select a bookid from book table. but it doesnt work here.if anyone could help it would be a great support as i cant find whats wrong here.thanks 

 

 

 private void textBox10_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                textBox11.Focus();
                e.Handled = true;



                string str = @"Data Source=Dell-PC\SQLEXPRESS;Initial Catalog=employeedb;Integrated Security=True";
                SqlConnection cn = new SqlConnection(str);
                SqlCommand cmd;

                string eid = textBox1.Text;

                string sql = "select COUNT(*) from Employee where EmpID='" + eid + "'"; //count records that tally user input
                try
                {
                    cn.Open();
                    cmd = new SqlCommand(sql2, cn);
                    int count = Convert.ToInt32(cmd.ExecuteScalar());
                    cmd.Dispose();
                    cn.Close();
                    label1.Visible = false;
                    if (count1 == 0)
                    {
                        label2.Visible = true;
                    }
                    else
                    {
                        label2.Visible = false;
                    }

                }

                catch (Exception ex)
                {

                    MessageBox.Show(ex.Message);
                }
            }
        }

 

[Timothy11]

Did you mean to use uid in the where clause I can not see where it is defined, did you mean to use eid instead?

[Shammikit]

2 minutes ago, Timothy11 said:

Did you mean to use uid in the where clause I can not see where it is defined, did you mean to use eid instead?

ops mistake that should be eid.but changing it to eid didnt work 

[madknight3]

It often helps when you also include the exact error message when you post issues.

 

The variables sql2 and sql aren't matching up. Could that be your issue?

// ...
string sql = "select COUNT(*) from Employee where EmpID='" + eid + "'"; //count records that tally user input
// ...
cmd = new SqlCommand(sql2, cn);
// ...

 

[Shammikit]

30 minutes ago, madknight3 said:

It often helps when you also include the exact error message when you post issues.

 

The variables sql2 and sql aren't matching up. Could that be your issue?

// ...
string sql = "select COUNT(*) from Employee where EmpID='" + eid + "'"; //count records that tally user input
// ...
cmd = new SqlCommand(sql2, cn);
// ...

 

thank you for showing the mistake,but changing this to sql still did not fix the issue. the error is a exception error that shows up. it says incorrect syntax near the keyword 'Employee'. 

this visual studio show output from debug shows this:

A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

[Timothy11]

Is EmpID an int field? If so why are you comparing it to a string in the where clause?

[vorticalbox]

You should use HasRows to check if there is data. I haven't used c# for a long time but try

 

sql = string.Format("SELECT * FROM Employee Where EmpID='{0}'", eid); 
try{
    cn.Open();
    cmd = new SqlCommand(sql, cn);
    SqlDataReader rdr = cmd.ExecuteReader();
    if(rdr.HasRows){
      // if there any rows
    }else{
      //no rows == no in database
    }
}catch (Exception ex)
{
  MessageBox.Show(ex.Message);
}

 

 

 

 

[Shammikit]

1 hour ago, Timothy11 said:

Is EmpID an int field? If so why are you comparing it to a string in the where clause?

No EmpID is a varchar in the database

[Shammikit]

55 minutes ago, vorticalbox said:

You should use HasRows to check if there is data. I haven't used c# for a long time but try

 

sql = string.Format("SELECT * FROM Employee Where EmpID='{0}'", eid); 
try{
    cn.Open();
    cmd = new SqlCommand(sql, cn);
    SqlDataReader rdr = cmd.ExecuteReader();
    if(rdr.HasRows){
      // if there any rows
    }else{
      //no rows == no in database
    }
}catch (Exception ex)
{
  MessageBox.Show(ex.Message);
}

 

 

 

 

Still no luck. It says incorrect syntax near the keyword 'Employee'. i think there is  a problem in the sql statement but i dont get what it is

[vorticalbox]

1 minute ago, Shammikit said:

Still no luck. It says incorrect syntax near the keyword 'Employee'. i think there is  a problem in the sql statement but i dont get what it is

is the table name correct?

 

[ChuckMaurice]

1 hour ago, vorticalbox said:

You should use HasRows to check if there is data. I haven't used c# for a long time but try

Nah, count is ok for his use case. 

In C# you need to use parameter queries with this syntax:

 

string sql = @"select COUNT(*) from Employee where EmpID=@EmpID";   //I added an "@" to allow escaping of '' characters just in case
sql.Parameters.AddWithValue("@EmpID", eid);

                try
                {
                    cn.Open();
                    cmd = new SqlCommand(sql, cn);
                    int count = Convert.ToInt32(cmd.ExecuteScalar());
                    cmd.Dispose();
                    cn.Close();
                    label1.Visible = false;
                    if (count == 0)
                    {
                        label2.Visible = true;
                    }
                    else
                    {
                        label2.Visible = false;
                    }
                  }
                  catch (SqlException ex)   //using SqlException will provide you a better error message
                {

                    MessageBox.Show(ex.Message);
                }
                    

 

[Shammikit]

5 minutes ago, vorticalbox said:

is the table name correct?

 

yes the table name is employee and empid is a column name

[Shammikit]

8 minutes ago, jldjul said:

Nah, count is ok for his use case. 

In C# you need to use parameter queries with this syntax:
 

string sql = @"select COUNT(*) from Employee where EmpID=@EmpID";   //I added an "@" to allow escaping of '' characters just in case
sql.Parameters.AddWithValue("@EmpID", eid);

                try
                {
                    cn.Open();
                    cmd = new SqlCommand(sql, cn);
                    int count = Convert.ToInt32(cmd.ExecuteScalar());
                    cmd.Dispose();
                    cn.Close();
                    label1.Visible = false;
                    if (count == 0)
                    {
                        label2.Visible = true;
                    }
                    else
                    {
                        label2.Visible = false;
                    }
                  }
                  catch (SqlException ex)   //using SqlException will provide you a better error message
                {

                    MessageBox.Show(ex.Message);
                }
                    

 

still doesnt work! getting the error : incorrect syntax near the keyword 'Employee'.  empid is the primary key of the employee table in the database and also acts as some foreign keys in like 2 more tables.does this impact in any way?

[ChuckMaurice]

6 minutes ago, Shammikit said:

still doesnt work! getting the error : incorrect syntax near the keyword 'Employee'.  empid is the primary key of the employee table in the database and also acts as some foreign keys in like 2 more tables.does this impact in any way?

Are you sure you used the right letter case? is it EmpID or empid?

[Shammikit]

33 minutes ago, jldjul said:

Are you sure you used the right letter case? is it EmpID or empid?

its EmpID and yes its in the right case as on the database

[ChuckMaurice]

have you tried the query directly in the database manager? you will have help with it there.