executing scalar against insert statement

I am trying to get the id for the last record that I have in开发者_Python百科sert into the database table through my asp.net website.

My insert statment is a stored procedure, I have tried using the scalar function but it is giving me this Specified cast is not valid.

This is the insert statement that I am using:

@ClientID varchar(250),
@BType varchar(250),
@CIP varchar(50),
@Sess varchar(500),
@LoggedIn int,
@LoggedInTime datetime

AS
BEGIN
    INSERT INTO tblSession ( ClientID, BType, CIP, Sess, LoggedIn, LoggedInTime) VALUES ( @ClientID, @BType, @CP,@Sess, @LoggedIn, @LoggedInTime) 
    SELECT SCOPE_IDENTITY()
END

And the method that I am calling:

 using (SqlConnection con = DBConnection.getConnection())
 {
    string sql = "Add";
    SqlCommand command = new SqlCommand(sql, con);
    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.Add("@ClientID ", SqlDbType.VarChar).Value = id;
    command.Parameters.Add("@BType", SqlDbType.VarChar).Value = bt;
    command.Parameters.Add("@CIP", SqlDbType.VarChar).Value = cip;
    command.Parameters.Add("@Sess", SqlDbType.VarChar).Value = s;
    command.Parameters.Add("@LoggedIn", SqlDbType.Int).Value = LoggedIn;
    command.Parameters.Add("@LoggedInTime", SqlDbType.DateTime).Value = session.LoggedInTime;

    int x;
    x = (int)command.ExecuteScalar();
    return x;
 }

please any help is much appreiciate it

Create an output parameter in the stored procedure and in the C# code, and simply executeNonQuery

I found an example on MSDN which i think can help you, because it seems to solve a situation like yours. In the example it uses just a query and not a stored procedure, but maybe could help. The scalar query always return a value or NULL if there is nothing to return. Are you sure your insert is working? Maybe it is returning NULL because your insert failed.

static public int AddProductCategory(string newName, string connString)
{
    Int32 newProdID = 0;
    string sql =
        "INSERT INTO Production.ProductCategory (Name) VALUES (@Name); "
        + "SELECT CAST(scope_identity() AS int)";
    using (SqlConnection conn = new SqlConnection(connString))
    {
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.Add("@Name", SqlDbType.VarChar);
        cmd.Parameters["@name"].Value = newName;
        try
        {
            conn.Open();
            newProdID = (Int32)cmd.ExecuteScalar();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
    return (int)newProdID;
}

I´m absolutly not sure if I understand your problem, but the call should be

command.ExecuteNonQuery();