C# - Web Site - SQL Select Statement

Question

I want to use a select statement to find if there is a record that already exists. I've put the code below but it throws an error at the dReader = comm.ExecuteReader(); and i'm unsure why. Any help?

    string connString = "Data Source=KIMMY-MSI\\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True";

    SqlDataReader dReader;
    SqlConnection conn = new SqlConnection(connString);
    SqlCommand comm = new SqlCommand();
    comm.Connection = conn;

    comm.CommandText = "SELECT * FROM Customers WHERE CustomerID == " + txtID.Text;
    comm.Connection.Open();

    dReader = comm.ExecuteReader();

    if (dReader.HasRows == true)
    {
        Response.Write("Exists");
    }

The error:

Invalid Column Name (whatever I input)

It seems to be looking for a column named what I input rather than looking for the actual data.

Josh M. · Accepted Answer · 2011-05-09 23:41:48Z

5

Change your == to =. That is invalid SQL as it is.

Also if txtID.Text is non-numeric then it needs to be in single quotes. You should not be constructing your SQL like this, instead use a parameter:

comm.CommandText = "SELECT * FROM Customers WHERE CustomerID = @CustomerID";
comm.Parameters.AddWithValue("CustomerID", txtID.Text);

More Info

C# using statement

SQL reference

SQL injection (why you should parameterize your queries)

edited May 9, 2011 at 23:41

answered May 9, 2011 at 22:20

Josh M.

28.1k27 gold badges135 silver badges223 bronze badges

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

7 Comments

6TTW014 Over a year ago

When changing it to = it says : Assignment in conditional expression is always constant; did you mean to use == instead of = ?

6TTW014 Over a year ago

And : Property or indexer 'System.Data.Common.DbDataReader.HasRows' cannot be assigned to -- it is read only

Josh M. Over a year ago

You're changing that in the wrong spot - check my answer here for the two lines you need to edit. (Leave your if statement as is.)

6TTW014 Over a year ago

Ok, well thats sorted, but it is still looking for a Column Named what I input, rather than looking for the data I input.

Josh M. Over a year ago

You probably didn't put the value in single quotes. Just copy and paste my two lines in place of your one comm.CommandText = ... line.

|

AdamV · Accepted Answer · 2011-05-09 22:24:46Z

4

It looks like your command has an issue:

SELECT * FROM Customers WHERE CustomerID == 1

In SQL you don't need to use the == operator to ensure something is equal to another.

Try:

SELECT * FROM Customers WHERE CustomerID = 1

In addition, you might want to read up about SQL Injection, the way you are binding the value is directly from a textbox value. This has a huge security hole which could lead to arbitrary sql command execution.

answered May 9, 2011 at 22:24

AdamV

5942 silver badges19 bronze badges

Comments

Serhat Ozgel · Accepted Answer · 2011-05-09 22:23:35Z

2

Change this line:

comm.CommandText = "SELECT * FROM Customers WHERE CustomerID == " + txtID.Text;

To this line:

comm.CommandText = "SELECT * FROM Customers WHERE CustomerID = @id";
comm.Parameters.AddWithValue("id", int.Parse(txtID.Text));

Assuming that your customer id is int on the database.

answered May 9, 2011 at 22:23

Serhat Ozgel

23.9k29 gold badges106 silver badges142 bronze badges

Comments

Dan Diplo · Accepted Answer · 2011-05-09 22:28:28Z

1

The equals operator in SQL is just a single =.

Also, you really shouldn't be concatenating SQL queries like that, you are just opening yourself up to SQL Injection attack. So change it to be like this:

comm.CommandText = "SELECT * FROM Customers WHERE CustomerID = @CustomerId";
comm.Parameters.AddWithValue("@CustomerId", txtID.Text);

See Stop SQL Injection Attacks Before They Stop You on MSDN.

answered May 9, 2011 at 22:28

Dan Diplo

25.4k4 gold badges71 silver badges90 bronze badges

Comments

Kevin Babcock · Accepted Answer · 2011-05-09 22:29:54Z

You are using invalid SQL. You name to change "==" to "=".

You should also consider wrapping your IDisposable objects in using statements so that unmanaged objects are properly disposed of and connections are properly closed.

Finally, think about using parameters in your SQL, instead of concatenating strings, to avoid SQL injection attacks:

string connString = @"Data Source=KIMMY-MSI\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True";
string sql = "SELECT * FROM Customers WHERE CustomerID = @CustomerID";
using (SqlConnection conn = new SqlConnection(connString))
using (SqlCommand comm = new SqlCommand(sql, conn))
{
    comm.Connection.Open();
    comm.Parameters.AddWithValue("@CustomerID", txtID.Text);
    using (SqlDataReader dReader = comm.ExecuteReader())
    {
        if (dReader.HasRows == true)
        {
            Response.Write("Exists");
        }   
    }
}

Collectives™ on Stack Overflow

C# - Web Site - SQL Select Statement

5 Answers 5

More Info

7 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

More Info

7 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related