MVC C# SqlException

Question

I'm pretty new to MVC and Have created a registeration form link to my sqlserver. However I get this error:

An expression of non-boolean type specified in a context where a condition is expected, near 'ID

and here is my code:

public bool IsUserExist(string emailid)
{
     bool flag = false;
     SqlConnection connection = new SqlConnection("Data Source=HOMESERV;Initial Catalog=JSO;Integrated Security=True;MultipleActiveResultSets=True");
     connection.Open();
     SqlCommand command = new SqlCommand("select * from EMPLOYEE where EMP ID='" + EMP_ID + "'", connection);
     flag = Convert.ToBoolean(command.ExecuteScalar());
     connection.Close();
     return flag;
}

I get my error right here:

flag = Convert.ToBoolean(command.ExecuteScalar());

I know I'm doing something wrong in my where statemnet, but not sure what is that :( I would appreciate if you would help me. Thank you!

What is the value of `command.ExecuteScalar()` returns? Did you debug your code? And you should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks. — Soner Gönül, Mar 07 '14 at 08:13

score 3 · Accepted Answer · edited May 23 '17 at 11:49

If a field name contains a space like Emp ID then you need to use square brackets around that name

SqlCommand command = new SqlCommand("select * from EMPLOYEE where [EMP ID]=....", connection);

However, there is another big problem in your query and it is called SQL Injection.
Please change your code to something like this

 bool flag = false;
 using(SqlConnection connection = new SqlConnection("Data Source=HOMESERV;Initial Catalog=JSO;Integrated Security=True;MultipleActiveResultSets=True"))
 using(SqlCommand command = new SqlCommand("select * from EMPLOYEE where [EMP ID]=@id", connection);
 {
      connection.Open();
      command.Parameters.AddWithValue("@id", EMP_ID);
      flag = Convert.ToBoolean(command.ExecuteScalar());
 }
 return flag;

I have also added a using statement block around your SqlConnection and SqlCommand. This ensures that your disposable objects are correctly closed and disposed also in case of exceptions.

As a final note, I am a bit perplexed by the usage of ExecuteScalar on a SELECT * query. ExecuteScalar returns just the first field of the first row. So it seems inappropriate for a SELECT * query. If you are just testing the existence of the record why not use a simple SELECT COUNT(*) or IF EXISTS query without returning the whole record?

"select count(*) from EMPLOYEE where [EMP ID] = @id"
"if exists(select * from EMPLOYEE where [EMP ID] = @id) SELECT 1 ELSE SELECT 0"

How can i only insert to some fields not all of them? I did this, but still ask me to fill out the rest of table, but I want to leave them null for now SqlCommand command = new SqlCommand("insert into EMPLOYEE (EMP_ID, PASSWORD, CONFIRMPASSWORD) VALUES ('" + EMP_ID + "','" + Password + "','" + ConfirmPassword + "')", connection); — SamR, Mar 07 '14 at 08:36
If you have NOT NULL fields then you need to provide the values for them when you insert. And again, do no use string concatenation for creating queries. ALWAYS parameters for every kind of command text. String concatenation is a fatal error in the long run..... (If you still have problems, I suggest to post a new question) — Steve, Mar 07 '14 at 08:42

score 2 · Answer 2 · answered Mar 07 '14 at 08:16

2

I'll guess at your DB Schema, but you've referred to a column with a space in it. Try wrapping it in brackets:

select * from EMPLOYEE where [EMP ID] = ...

Or perhaps you have simply misspelled the column name.

answered Mar 07 '14 at 08:16

Ed Chapel

6,842
3
30
44

MVC C# SqlException

2 Answers2