simple ExecuteScalar causing A first chance exception

Question

User-1349096529 posted

Here is my code:

        private void GetUserRoll()
        {
            string Roll = "0";

            SqlConnection thisConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["GoliathConnectionString"].ConnectionString);
            SqlCommand Command = thisConnection.CreateCommand();

            try
            {
                thisConnection.Open();

                Command.CommandText = "SELECT RollType FROM Users WHERE (Username = @Username))";
                Command.Parameters.Add("@Username", SqlDbType.NChar, 20);
                Command.Parameters["@Username"].Value = txtUserName.Text;

                Roll = (string)Command.ExecuteScalar();
            }
            catch (SqlException ex)
            {
                lblErrMsg.Text = ex.ToString();
                lblErrMsg.Visible = true;
            }
            finally
            {
                thisConnection.Close();
            }

            if (Roll != "0")
            {
                System.Diagnostics.Trace.WriteLine("UserRoll:" + Roll);
                Session["UserRoll"] = Roll;
            }
            else
            {
                System.Diagnostics.Trace.WriteLine("UserRoll:" + Roll);
            }

On running i receive "A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll" and see UserRoll:0 . This should not be hard, so i assume i am doing something stupid, but i cant for the life of me see where my problem is.

As always, any help is appreciated!

 - James.

 

Answer 1

User-1360095595 posted

Put a breakpoint, step through your code and see exactly what line the exception is occuring on.

Answer 2

User1468581411 posted

James,

    The information is too little to draw a conclusion. A good start would be to run it in debug mode and see where it breaks.

-Dilbert

Answer 3

User1468581411 posted

try this for adding the parameter

SqlParameter param  = new SqlParameter();
param.ParameterName = "@Username";
param.Value         = txtUserName.Text;

Command.Parameters.Add(param);

Roll = (string)Command.ExecuteScalar();

 

Answer 4

User-1349096529 posted

BostinDilbert - thanks for the suggestion, buti recieve the same error.

I have set several breakpoints, and the error happens on 

Roll = (string)Command.ExecuteScalar();

Which does not tell me much. I am also not catching an exception, which i find odd.

This connection string is used MANY MANY times, so i know that is not the issue.

 - James

Answer 5

User-1360095595 posted
I can suggest, if you have SQL Profiler, that you put a trace to see what SQL is being sent for the database.

Answer 6

User636427837 posted

I would suggst changing the code a little; It look like creating a command from a connection that has not been opened yet;

....

string Roll = "0";            

using(SqlConnection thisConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["GoliathConnectionString"].ConnectionString))
{
	//Open Connection
	thisConnection.Open();
	
	using(SqlCommand Command = thisConnection.CreateCommand())
	{
		Command.CommandText = "SELECT RollType FROM Users WHERE (Username = @Username))";                
		Command.Parameters.Add("@Username", SqlDbType.NChar, 20);                
		Command.Parameters["@Username"].Value = txtUserName.Text;                
	
		Roll = (string)Command.ExecuteScalar();       
	}
	
	//Close Connection
	thisConnection.Close();
}

Answer 7

User1468581411 posted

What is the error message?

Answer 8

User1468581411 posted

Not sure if this is the only issue, but I think I see a syntax error in your sql query

 

try this

 

Command.CommandText = "SELECT RollType FROM Users WHERE (Username = @Username)";

(You had an extra paranthesis after @username)

 

 

 

 

Answer 9

User-1349096529 posted

BostonDilbert - That did it! 

Thanks...

 - James