C# - Select query not working in SqlCommand

Question

I have to select user name from a database in SQL Server. The query that is generated by SqlCommand works in SQL Server Management Studio but not in my code.

And this only happens when the input is like AFFAQPC/affaq containing /.

The code is:

public int? getid()
{
    SqlConnection Db = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);

    Db.Open();

    // Searching for id in Users table from the logged in username
    SqlCommand command = new SqlCommand("SELECT TOP 1 id FROM UsersLogin where username = '@user1';", Db);
    command.Parameters.AddWithValue("@user1", userName);
    string query = command.CommandText;

    foreach (SqlParameter p in command.Parameters)
    {
        query = query.Replace(p.ParameterName, p.Value.ToString());
    }

    Trace.WriteLine(query);

    using (SqlDataReader reader = command.ExecuteReader())
    {
        if (reader.Read())
        {
            Trace.WriteLine("111");
            int id = Convert.ToInt32(reader["id"]);
            Trace.WriteLine(id);
            Db.Close();
            return id;
        }
    }

    Db.Close();
    return null;
}

The error occurs when input contains a /.

The query that is generated in SqlCommand:

SELECT TOP 1 id 
FROM UsersLogin 
WHERE username = 'AFFAQPC\affaq';

You are not handling your IDisposable objects (such as SqlConnection) correctly. — mason
– mason, Commented Jul 12, 2017 at 17:19
In C# strings, the backslash is an escape character. If you're building this query in C#, and that's a string literal, then you'll want to use a "@" before the string to specify a literal string with no escaping: where username = @'AFFAQPC\affaq'; — pmbAustin
– pmbAustin, Commented Jul 12, 2017 at 17:19
what @pmbAustin said is correct additionally if "@user1" is a string parameter you do not need enclose it with single quotes. You should try: "SELECT TOP 1 id FROM UsersLogin where username = @user1;" — DaniDev
– DaniDev, Commented Jul 12, 2017 at 17:28
@DaniDev, for me it seems like PmbAustin was talking about declaring a string as literal in C#, which is not necessarily the same as passing a backslash inside a string that came from a file or whatever. Key question is what Mason asked. — Daniel Manta
– Daniel Manta, Commented Jul 12, 2017 at 17:35

DRapp · Accepted Answer · 2017-07-12 17:27:04Z

3

remove the single quotes from your query string

username = '@user1'
to
username = @user1

The "Parameter" should be WITHOUT the "@", just the name

 command.Parameters.AddWithValue("user1", userName);

if the userName value is a string (confirming whatever your source is), that will be properly recognized when processed. You do not need to explicitly quote-it. Otherwise, the query is specifically looking for a user '@user1' which is probably why it is not returning what you expect.

I have NO idea why you are cycling through all parameters to assign the value... the userName field should already be good to go without doing your foreach parameter check.

answered Jul 12, 2017 at 17:27

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

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

2 Comments

Daniel Manta Over a year ago

agree the first assertion, it is at user1 without quotes, but when calling AddWithValue, @userName, should work.

Affaq Aslam Over a year ago

I was cycling through the parameters to generate a real query generated by sql command.

Collectives™ on Stack Overflow

C# - Select query not working in SqlCommand

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related