“System.Data.SqlClient.SqlException: Invalid column name” when checking for username

I’m trying to check a database for a line in a username creation system, to see if the username is in use. Whenever I try to run the program, it tells me there is an invalid column name.

bool tooLong = true;
        conn.Open();
        while (tooLong == true)
        {
            Console.WriteLine("Enter a username:");
            username = Console.ReadLine();
            if (username.Length > 64)
            {
                Console.WriteLine("Username too long");
                tooLong = true;
            }
            else if (username.Length == 0)
            {
                Console.WriteLine("Username cannot be blank");
                tooLong = true;
            }
            else
            {
                SqlCommand testUser = new SqlCommand($"SELECT Username FROM Users WHERE Username = {username}", conn);
                if (testUser.ExecuteNonQuery() == -1)
                {
                    tooLong = false;
                    conn.Close();
                    Console.WriteLine("Username accepted");
                }
                else
                {
                    Console.WriteLine("Username already in use");
                }
            }

I have also tried changing

SqlCommand testUser = new SqlCommand($"SELECT Username FROM Users WHERE Username = {username}", conn);

to

SqlCommand testUser = new SqlCommand($"SELECT Username FROM Users WHERE Username = '{username}'", conn);

but this just lets everything through instead of throwing the error

Answer

I would make sure you are not missing the schema in the table name. They are not always a dbo.Users. Try copying the SQL as you have it, paste it in SSMS, and simply replace the {username} with an actual ‘userName’.

Also, instead of passing in your variable directly, you should add a parameter to prevent SQL injection. Store your SQL query in a variable (here: sqlQuery). Then, add your UserName as a parameter.

Note: by adding the parameter as below, you will not need to pass in the single quotes.

const string sqlQuery = @"SELECT Username FROM Users WHERE Username = @userName";

var cmd = new SqlCommand(sqlQuery, conn);

cmd.Parameters.Add("@UserName", SqlDbType.VarChar);
cmd.Parameters["@UserName"].Value = userName

Leave a Reply

Your email address will not be published. Required fields are marked *