SQL Server select query execution from c#

Question

string user = "1234";
string strSQL = string.Format("Select * From User where UserId = '{0}'",user);
SqlCommand myCommand = new SqlCommand(strSQL, cnn);
reader = myCommand.ExecuteReader();

My User table consists of UserId and Password columns. The UserId column type is nchar and so I've used the single quotes. I get an error saying that

incorrect syntax near the keyword User"

(I guess the table name User is being referred to here).

I have the connection string and other database environment related things correctly for I've checked the database connection status and it is open(during program execution).

What is the error in the syntax? I'm unable to retrieve the rows from my table.

Stick a break point on your SQLCommand line and grab the value of strSQL, then copy the string value and paste it into SSMS. Execute and examine the query. Without having the design of your table to hand its hard to diagnose what the syntax error would be. — zeencat
– zeencat, Commented Jan 2, 2013 at 10:59

codingbiz · Accepted Answer · 2016-07-09 01:23:08Z

9

User is a Keyword. Use square bracket around it to avoid the error. Select * from [User]

string strSQL = string.Format("Select * From [User] where UserId = '{0}'",user);

Also, you should always use parameterized query like below to prevent SQL Injection attack:

string strSQL = string.Format("Select * From [User] where UserId = @UserId");

edited Jul 9, 2016 at 1:23

answered Jan 2, 2013 at 10:57

codingbiz

26.4k9 gold badges62 silver badges99 bronze badges

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

Comments

codingbiz · Accepted Answer · 2013-01-02 11:01:24Z

8

You should really use parameters for this:

string user = "1234";

using (SqlCommand command = new SqlCommand("select * from [User] where UserId = @userid", cnn))
{
    command.Parameters.AddWithValue("@userid", user);

    using (SqlDataReader reader = myCommand.ExecuteReader())
    {
        // iterate your results here
    }
}

Well spotted by other posters, I never caught the reserved word thing with your table name. I've amended my answer - but can't take credit for missing the obvious!

edited Jan 2, 2013 at 11:01

codingbiz

26.4k9 gold badges62 silver badges99 bronze badges

answered Jan 2, 2013 at 10:58

Paul Aldred-Bann

6,0306 gold badges39 silver badges55 bronze badges

Comments

Reece Kenney · Accepted Answer · 2016-07-15 09:57:20Z

4

you should wrap user with brackets []

string strSQL = string.Format("Select * From [User] where UserId = '{0}'",user);

The query above is vulnerable to SQL Injection. It should be parameterized to avoid this. The following is an example:

string user = "1234";
string strSQL = "Select * From [User] where UserId = @userID";
SqlCommand myCommand = new SqlCommand(strSQL, cnn);
myCommand.AddWithValue("@userID", user);
reader = myCommand.ExecuteReader();

use the following

Try-Catch block for proper catching of errors
using statement for proper object disposal

snippet:

string user = "1234";
string strSQL = "Select * From [User] where UserId = @userID";
using (SqlConnection cnn = new SqlConnection("connection string here"))
{
    using (SqlCommand myCommand = new SqlCommand(strSQL, cnn))
    {
        myCommand.Parameters.AddWithValue("@userID", user);
        using (SqlDataReader reader = myCommand.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine(reader["columnName"].ToString());
            }
        }
    }
}

edited Jul 15, 2016 at 9:57

Reece Kenney

2,9743 gold badges28 silver badges59 bronze badges

answered Jan 2, 2013 at 10:57

John Woo

265k70 gold badges509 silver badges500 bronze badges

1 Comment

Matt Over a year ago

I think you've got some errors in the code. "myCimmand" is misspelled, and it looks like you used parenthesis when you should have used brackets, perhaps? I'm not exactly sure what you did on line 4?

Soner Gönül · Accepted Answer · 2013-03-31 12:31:49Z

2

Wrap with []. It is a keyword. Read Reserved Keywords article from MSDN.

string strSQL = string.Format("Select * From [User] where UserId = '{0}'",user);

But more important part, your query is open for an SQL Injection attack. You should always use parameterized queries.

string strSQL = "Select * From [User] where UserId = @userID";
SqlCommand myCommand = new SqlCommand(strSQL, cnn);
myCommand.Parameters.AddWithValue("@userID", user);

edited Mar 31, 2013 at 12:31

answered Jan 2, 2013 at 10:57

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

Collectives™ on Stack Overflow

SQL Server select query execution from c#

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related