Inserting Parameters, C#, T-Sql

Question

I am trying to insert a parameter through an aspx page via text box. I set my parameters up, but evertime I executenonquery, the @Username shows up in the database instead of the actual value. Below is my code. Can anyone shed a little insight?

This is the full code:

protected void btn_SubmitUserInfo_Click(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection();
        conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=c:\\Documents and Settings\\xm\\My Documents\\Visual Studio 2010\\Projects\\CreateUser\\CreateUser\\App_Data\\UserInformation.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True; Integrated Security=SSPI");

        //Open the connection outside of the try statement
        conn.Open();

        try
        {
            //Create a command for the stored procedure and tie it into the connection
            SqlCommand cmd = new SqlCommand("InsertUserValues", conn);

            //Set the command type so it know to execute the stored proc
            cmd.CommandType = CommandType.StoredProcedure;

            //Declare Parameters


            SqlParameter @UserID = new SqlParameter("@UserID", System.Data.SqlDbType.Int);
            @UserID.Direction = ParameterDirection.Input;
            @UserID.Value = txtUserID.Text;


            SqlParameter @UserName = new SqlParameter("@UserName", System.Data.SqlDbType.VarChar);
            @UserName.Direction = ParameterDirection.Input;
            @UserName.Value = txtUserName.Text;

            SqlParameter @UserPassword = new SqlParameter("@UserPassword", System.Data.SqlDbType.VarChar);
            @UserPassword.Direction = ParameterDirection.Input;
            @UserPassword.Value = txtPassword.Text;

            SqlParameter @FirstName = new SqlParameter("@FirstName", System.Data.SqlDbType.VarChar);
            @FirstName.Direction = ParameterDirection.Input;
            @FirstName.Value = txtFirstName.Text;

            SqlParameter @LastName = new SqlParameter("@LastName", System.Data.SqlDbType.VarChar);
            @LastName.Direction = ParameterDirection.Input;
            @LastName.Value = txtLastName.Text;

            SqlParameter @Address = new SqlParameter("@Address", System.Data.SqlDbType.VarChar);
            @Address.Direction = ParameterDirection.Input;
            @Address.Value = txtAddress.Text;

            SqlParameter @AptNum = new SqlParameter("@AptNum", System.Data.SqlDbType.VarChar);
            @AptNum.Direction = ParameterDirection.Input;
            @AptNum.Value = txtAptNumber.Text;

            SqlParameter @City = new SqlParameter("@City", System.Data.SqlDbType.VarChar);
            @City.Direction = ParameterDirection.Input;
            @City.Value = txtCity.Text;

            SqlParameter @State = new SqlParameter("@State", System.Data.SqlDbType.VarChar);
            @State.Direction = ParameterDirection.Input;
            @State.Value = txtState.Text;

            //SqlParameter @Zip = new SqlParameter("@Zip", System.Data.SqlDbType.Int);
            //@Zip.Direction = ParameterDirection.Input;
            //@Zip.Value = Convert.ToInt32(txtZip.Text);

            //add new parameter command to object
            cmd.Parameters.Add(@UserID);
            cmd.Parameters.Add(@UserName);
            cmd.Parameters.Add(@UserPassword);
            cmd.Parameters.Add(@FirstName);
            cmd.Parameters.Add(@LastName);
            cmd.Parameters.Add(@Address);
            cmd.Parameters.Add(@AptNum);
            cmd.Parameters.Add(@City);
            cmd.Parameters.Add(@State);
            //cmd.Parameters.Add(@Zip);

            //execute nonquery
            cmd.ExecuteNonQuery();

        }
        finally
        {
            lblSucess.Text = "Your information has been submitted";
            //Close the connection
            if (conn != null)
            {
                conn.Close();
            }
        }

This is the stored Procedure:

ALTER PROCEDURE dbo.InsertUserValues

@UserID int,
@UserName varchar(50),
@UserPassword varchar(100),
@FirstName varchar(50),
@LastName varchar(50),
@Address varchar(50),
@AptNum varchar(50),
@City varchar(50),
@State varchar(50)


AS
INSERT INTO tb_User( user_Name, password, f_Name, l_Name, address, apt_Number, city, state)
VALUES (                '@UserName', '@UserPassword', '@FirstName', '@LastName', '@Address', '@AptNum', '@City', '@State')

Please post the INSERT statement. I guess you write '@UserName' and should remove the quotes. — James Gaunt
– James Gaunt, Commented Jan 16, 2011 at 1:51
Yep - that's what you've done... remove the quotes around your paramters. — James Gaunt
– James Gaunt, Commented Jan 16, 2011 at 2:00
Oh my gosh. I wrestled with this for the past 4 hours. In this example is it bad practive to have the @ symbol in the begining of my variables? Should I go through and rename them to as Andrey has suggested to me and move forward with this practice? Thanks. — jpavlov
– jpavlov, Commented Jan 16, 2011 at 2:11
It's a bad practice to use '@' in the beginning of c# variables - you should only use it if you want to use reserved keywords as your variables, but that is even worse in 99,99999% cases. Your code won't blow but it's just not good, same as using goto statements. Just don't do that. — Andrey
– Andrey, Commented Jan 16, 2011 at 2:21

tvanfosson · Accepted Answer · 2011-01-16 01:58:09Z

5

You have your parameter quoted in your SQL statement. Remove the single quotes from around the parameters in your stored procedure definition. Quoting them treats them as literal strings instead of parameters to be replaced.

ALTER PROCEDURE dbo.InsertUserValues

@UserID int,
@UserName varchar(50),
@UserPassword varchar(100),
@FirstName varchar(50),
@LastName varchar(50),
@Address varchar(50),
@AptNum varchar(50),
@City varchar(50),
@State varchar(50)


AS
INSERT INTO tb_User( user_Name, password, f_Name, l_Name, address, apt_Number, city, state)
VALUES (@UserName, @UserPassword, @FirstName, @LastName, @Address, @AptNum, @City, @State)

answered Jan 16, 2011 at 1:58

tvanfosson

534k102 gold badges703 silver badges801 bronze badges

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

Comments

Andrey · Accepted Answer · 2011-01-16 01:47:29Z

1

Your parameter name is not correct - you shouldn't use @ in the beginning of C# variables. It should be:

SqlParameter UserName = new SqlParameter("@UserName", System.Data.SqlDbType.VarChar);
UserName.Direction = ParameterDirection.Input;
UserName.Value = txtUserName.Text;
cmd.Parameters.Add(UserName);

I don't think it's the root of your problem but that's just something I noticed. We need more code to see what's the problem

answered Jan 16, 2011 at 1:47

Andrey

21.4k30 gold badges119 silver badges181 bronze badges

4 Comments

James Gaunt Over a year ago

Having @ at the start of a C# variable is valid... @<identifier> is equivalent to <identifier>. Helpful in some obscure cases (e.g. using keywords as identifiers).

tvanfosson Over a year ago

I wouldn't use the @ in this case, but that's not the problem. I suspect the problem is with the SQL statement itself.

James Gaunt Over a year ago

I kinda like the @ here - as it mirrors the @ in the SQL - but maybe that's just me. Anywhere else certainly don't like it.

Andrey Over a year ago

I didn't say it's not valid - I said you shouldn't use it. Using "goto" is perfectly valid as well, but you wouldn't go near it, I believe :)

Collectives™ on Stack Overflow

Inserting Parameters, C#, T-Sql

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related