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

Question

When I execute my code below, this error message occurs:

"An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code

Additional information: Incorrect 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();

What is causing this, and how can I resolve it?

It is very important not to concatenate input into SQL. Use parameters. Otherwise it will either just fail occasionally (there's hope there are no O'Neil's in your company), or will act as a security hole (see: "bobby tables") — Marc Gravell
– Marc Gravell, Commented Nov 18, 2013 at 8:25

TylerH · Accepted Answer · 2020-08-19 21:05:48Z

14

There are some problems with your code. First I advise to use parametrized queries so you avoid SQL Injection attacks and also parameter types are discovered by framework:

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

Second, as you are interested only in one value getting returned from the query, it is better to use ExecuteScalar:

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 into using so any resources used by those will be disposed of:

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.");
    }
  }
}

edited Aug 19, 2020 at 21:05

TylerH

21.3k84 gold badges84 silver badges122 bronze badges

answered Nov 18, 2013 at 8:18

gzaxx

17.6k2 gold badges40 silver badges55 bronze badges

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

1 Comment

The incredible Jan Over a year ago

All well and good, but the question is not answered.

Soner Gönül · Accepted Answer · 2013-11-18 08:28:33Z

9

I think your EmpID column is string and you forget to use ' ' in your value.

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

Change your SqlCommand to

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

Or as a better way you can (and should) always use parameterized queries. This kind of string concatenations are open for 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 keeps your employee id's, so it's type should some numerical type instead of character.

edited Nov 18, 2013 at 8:28

answered Nov 18, 2013 at 8:16

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

1 Comment

The incredible Jan Over a year ago

I bet this is the real answer.

Dmytro Rudenko · Accepted Answer · 2013-11-18 08:16:26Z

3

Try this

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

answered Nov 18, 2013 at 8:16

Dmytro Rudenko

2,5242 gold badges15 silver badges22 bronze badges

Comments

Yuresh Karunanayake · Accepted Answer · 2018-09-05 09:33:27Z

1

use try-catch to see real error occurred on you

 try
{
  //Your insert code here
}
catch (System.Data.SqlClient.SqlException sqlException)
{
  System.Windows.Forms.MessageBox.Show(sqlException.Message);
}

answered Sep 5, 2018 at 9:33

Yuresh Karunanayake

5851 gold badge4 silver badges11 bronze badges

Comments

Soner Gönül · Accepted Answer · 2024-09-06 17:12:31Z

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();
}

Jason Aller · Accepted Answer · 2017-10-24 23:10:36Z

-2

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

put [] around table name ;)

edited Oct 24, 2017 at 23:10

Jason Aller

3,66028 gold badges43 silver badges40 bronze badges

answered Oct 24, 2017 at 21:07

mahmoud atef

1

Collectives™ on Stack Overflow

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

6 Answers 6

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related