c# asp.net sql read - selecting record & getting variable transferred locally

Development Platform : c#, asp.net 2.x, VIsual Studio Pro utilising Web Developer, IIS6, SQL Server 2005

I am trying to pull out a single user's (ASPnetUserName), associated CompanyAgent_ID number from the StaffMember database table as below -- then I want a local variable to equal the CompanyAgent_ID value.

The Login1.Username variable is from the login.aspx page (entry box) -- thats what I am wanting to compare with database -- ie. in selecting the correct username record.

In short, not sure if the sql statement is 100% correct and 2., how to get the CompanyAgent_ID value into a local variable.

Your time and efforts with this enquiry are much apprieated.

Thank you in advance.




SqlDataSource testSQLDataSource = new SqlDataSource();

            testSQLDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["sqlConnectionString"].ToString();

            System.Data.SqlClient.SqlCommand scCommand = new System.Data.SqlClient.SqlCommand();

            scCommand.CommandType = System.Data.CommandType.Text;
            scCommand.CommandText = "SELECT [ASPnetUserName], [CompanyAgent_ID] FROM [StaffMember] WHERE ([ASPnetUserName] = @Login1.Username)";
            scCommand.Parameters.Add("@RETURN_VALUE", System.Data.SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

            SqlConnection sqlConnection1 = new SqlConnection(testSQLDataSource.ConnectionString);

            try
            {
                sqlConnection1.Open();
                scCommand.Connection = sqlConnection1;
                scCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                SQL_FailureHandler(ex);
            }
            finally
            {
                sqlConnection1.Close();

                Label_SubmissionStatus.Text = "[ Comitted to Database OK ]";
            }