An exception of type "System.Data.SqlClient.SqlException" occurred in System.Data.dll

Question

I start with C #, when I execute the code, this error message appears →

'An exception of type' System.Data.SqlClient.SqlException 'occurred in System.Data.dll, but was not processed in the user code.

Additional Information: Invalid syntax near '='. "

And this is the code!

string position; SqlConnection con = new SqlConnection("server=free-pc\\FATMAH; Integrated Security=True; database=Workflow; "); con.Open(); SqlCommand cmd = new SqlCommand("SELECT EmpName FROM Employee WHERE EmpID=" + id.Text, con); SqlDataReader Read = cmd.ExecuteReader(); if (Read.Read()==true) { position = Read[0].ToString(); Response.Write("User Registration successful"); } else { Console.WriteLine("No Employee found."); } Read.Close(); 

Answer 1

There are some problems in the code. First, I recommend using parameterized queries to avoid SQL Injection attacks, and also parameter types are detected by the framework:

 var cmd = new SqlCommand("SELECT EmpName FROM Employee WHERE EmpID = @id", con); cmd.Parameters.AddWithValue("@id", id.Text); 

Secondly, since you are only interested in one value returned from the query, it is better to use ExecuteScalar MSDN :

 var name = cmd.ExecuteScalar(); if (name != null) { position = name.ToString(); Response.Write("User Registration successful"); } else { Console.WriteLine("No Employee found."); } 

The last thing is to wrap SqlConnection and SqlCommand in using so that any resources used by these are used:

 string position; using (SqlConnection con = new SqlConnection("server=free-pc\\FATMAH; Integrated Security=True; database=Workflow; ")) { con.Open(); using (var cmd = new SqlCommand("SELECT EmpName FROM Employee WHERE EmpID = @id", con)) { cmd.Parameters.AddWithValue("@id", id.Text); var name = cmd.ExecuteScalar(); if (name != null) { position = name.ToString(); Response.Write("User Registration successful"); } else { Console.WriteLine("No Employee found."); } } } 

Answer 2

I think your EmpID column is a string and you will forget to use ' ' in its meaning.

Because when you write EmpID=" + id.Text , your command looks like EmpID = 12345 instead of EmpID = '12345'

Change SqlCommand to

 SqlCommand cmd = new SqlCommand("SELECT EmpName FROM Employee WHERE EmpID='" + id.Text +"'", con); 

Or as the best way (and should) always use parameterized queries . This type of string concatenation is open to SQL Injection attacks.

 SqlCommand cmd = new SqlCommand("SELECT EmpName FROM Employee WHERE EmpID = @id", con); cmd.Parameters.AddWithValue("@id", id.Text); 

I think your EmpID column stores your employee ID, so it should have some kind of numeric type instead of character.

Answer 3

try it

 SqlCommand cmd = new SqlCommand("SELECT EmpName FROM Employee WHERE EmpID=@id ", con); cmd.Parameters.AddWithValue("id", id.Text); 

Answer 4

An unhandled exception of type "System.Data.SqlClient.SqlException" occurred in System.Data.dll

  private const string strconneciton = "Data Source=.;Initial Catalog=Employees;Integrated Security=True"; SqlConnection con = new SqlConnection(strconneciton); private void button1_Click(object sender, EventArgs e) { con.Open(); SqlCommand cmd = new SqlCommand("insert into EmployeeData (Name,S/O,Address,Phone,CellNo,CNICNO,LicenseNo,LicenseDistrict,LicenseValidPhoto,ReferenceName,ReferenceContactNo) values ( '" + textName.Text + "','" + textSO.Text + "','" + textAddress.Text + "','" + textPhone.Text + "','" + textCell.Text + "','" + textCNIC.Text + "','" + textLicenseNo.Text + "','" + textLicenseDistrict.Text + "','" + textLicensePhoto.Text + "','" + textReferenceName.Text + "','" + textContact.Text + "' )", con); cmd.ExecuteNonQuery(); con.Close(); } 

Answer 5

 using (var cmd = new SqlCommand("SELECT EmpName FROM [Employee] WHERE EmpID = @id", con)) 

put [] around the table name;)