locked
simple ExecuteScalar causing A first chance exception RRS feed

  • 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.

     

    Thursday, May 19, 2011 11:02 AM

Answers

  • 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)

     

     

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 19, 2011 9:34 PM

All replies

  • User-1360095595 posted

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

    Thursday, May 19, 2011 11:09 AM
  • 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

    Thursday, May 19, 2011 11:17 AM
  • 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();

     

    Thursday, May 19, 2011 11:25 AM
  • 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

    Thursday, May 19, 2011 1:21 PM
  • 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.
    Thursday, May 19, 2011 1:36 PM
  • 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();
    }

    Thursday, May 19, 2011 2:43 PM
  • User1468581411 posted

    What is the error message?

    Thursday, May 19, 2011 9:27 PM
  • 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)

     

     

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 19, 2011 9:34 PM
  • User-1349096529 posted

    BostonDilbert - That did it! 

    Thanks...

     - James

    Friday, May 20, 2011 8:06 AM