Object cannot be cast from DBNull to other types error using C#

Question

I am calling stored procedure and it does check 2 things and based on that it displays message but if both conditions are good then it updates the table. The table is updated correctly but at the end i get this error: "Object cannot be cast from DBNull to other types". I am not sure why this is happening because i have used similar codes somewhere else. It is failing at this line

 userId = Convert.ToInt32(cmd.ExecuteScalar());

here is code behind

protected void Ccs_Click(object sender, EventArgs e)
        {

            string Ques = secQues.SelectedValue;

           int userId = 0;

           using (SqlConnection con = new SqlConnection(constr))
           {
               using (SqlCommand cmd = new SqlCommand("myStPR"))
               {
                   using (SqlDataAdapter sda = new SqlDataAdapter())
                   {


                       cmd.CommandType = CommandType.StoredProcedure;

                       cmd.Parameters.Add("@TempID", SqlDbType.VarChar).Value = tempID.Text.Trim();
                       cmd.Parameters.Add("@Username", SqlDbType.VarChar).Value = txtUID.Text.Trim();
                       cmd.Parameters.Add("@Loc", SqlDbType.VarChar).Value = txtLoc.Text.Trim();

                       cmd.Connection = con;
                       con.Open();
                       userId = Convert.ToInt32(cmd.ExecuteScalar());
                       con.Close();
                   }
               }
               string message = string.Empty;
               switch (userId)
               {
                   case -1:
                       message = "some message.";
                       break;
                   case -2:
                       message = "some message";
                       break;
                   default:
                       message = "Registration successful. You will receive an email once your application is approved.  Thanks.";

                       break;
               }
               ClientScript.RegisterStartupScript(GetType(), "alert", "alert('" + message + "');", true);

           }

        }

Just to be clear, when you say "// run some UPDATE here", if you are using an update statement and don't do an insert, then SCOPE_IDENTITY() is going to return null. It will only return the last INSERTED value within the given scope. If you meant that your update was actually inserting something, then check the insert to see if it succeeded as that should be the only time that SCOPE_IDENTITY will return null, when a value wasn't inserted. — Mark Fitzpatrick
– Mark Fitzpatrick, Commented Feb 6, 2015 at 4:17

Jeff Mahoney · Accepted Answer · 2015-02-06 02:08:21Z

1

Have you tried running your stored procedure "by itself" (i.e. in the query analyzer in the database) and seeing what the procedure returns when both conditions are such that an update is allowed to happen? I don't see anything obviously wrong with your code, so I'm wondering if the stored proc is returning a NULL that's killing your switch statement.

answered Feb 6, 2015 at 2:08

Jeff Mahoney

2311 silver badge6 bronze badges

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

Comments

Diego Balduino · Accepted Answer · 2015-02-06 02:26:21Z

0

Look i noticed that has a parameter you don't use "@Username", try to remove this and if it doesn't solve the problem, change his query just to this above and execute again.

// New query just to test "select count(1) FROM myTable WHERE TempID = @TempID"

I hope it helps.

answered Feb 6, 2015 at 2:26

Diego Balduino

589 bronze badges

Collectives™ on Stack Overflow

Object cannot be cast from DBNull to other types error using C#

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related