Exception while inserting values in to SQL table through C#..

Question

HI i had manullay created textbox's and then used it for creating a new user. I am using SQL SERVER 2005 for backend and Visual Server 2008 for front..

I have this LoginAccount table which stores details of the new user created. When i Click the button(in which i have written code to create a new user through SQL insert),

  string strConnection = ConfigurationManager.ConnectionStrings"FHDLConnectionString"].ToString();
        SqlConnection sqlConnection = new SqlConnection(strConnection);
        string username = TextBox1.Text;
        string password = TextBox2.Text;
        string confirmpass = TextBox3.Text;
        string SQLQuery = "Select username From LoginAccount where '" + username + "'";
        string SQLQuery1 = "Insert into LoginAccount values ('" + username + "','" + password + "')";

        SqlCommand command = new SqlCommand(SQLQuery, sqlConnection);
        SqlCommand command1 = new SqlCommand(SQLQuery1, sqlConnection);

        sqlConnection.Open();
        string CheckUsername = "";

        if (password.ToString() != confirmpass.ToString())
        {
            Literal1.Text = " Password's does not match ";
        }
        else
        {
            try
            {
                CheckUsername = command.ExecuteScalar().ToString();
            }
            catch (Exception er)
            {
                Literal1.Text = " Username already exists ";
            }

            string insertQuery = "";

            try
            {
                insertQuery = command1.ExecuteScalar().ToString();
                Server.Transfer("Login_Created.aspx");
            }
            catch (Exception er)
            {
                Literal1.Text = " Could not create the user, Please retry with some other option ";
            }
        }
        sqlConnection.Close();

I am getting these exception's

An expression of non-boolean type specified in a context where a condition is expected, near 'fhdl' This error i got at the first catch

and

Object reference not set to an instance of an object. This for at the last catch.

But the main thing is i am able to insert the username and password into the LoginAccount table!!!!!!! i.e. when i saw the table contents i could see the new user created in that table. The other thing is This code executed perfectly once before but not now :( Please could anyone tell me where am i going wrong? I am new to C# with SQl ...

Answer 1

Three things:

1. Your first query doesn't do what you want it to. It resolves to:

   Select username From LoginAccount where 'username'

   You want to check the username against the database.

2. This code leaves you wide open to SQL Injection attacks. See this article for how to prevent them in C#.

3. On a similar note, you really don't want to store passwords in the clear in your database either.

Answer 2

1 - ExecuteScalar() means that there's a return value. The insert doesn't have a return value. Use ExecuteNonQuery instead.

2 - Also, for the insert statement, specify the fields you're inserting into. Without specifying the fields, it's trying to insert into the first two columns of the table, which may not be username and password.

insert into LoginAccount(UserName, Password) values ....

3 - your select statement is incorrect.

select from LoginAccount where UserName='... You're missing the field name.