CODEWITHSUNDEEP
asp.netsql-server-2005
Hakoo Desai
Hakoo Desai

Reputation: 341

why does SELECT SCOPE_IDENTITY() returning null?

I am trying to get ID generated by last Insert function. I understand very little about Scope and Session. But by reading blogs and other sources, I understood that, I should use Scope_Identity() function. But I am getting null value. Here is my code :

 public int InsertUser(string username, string gender, string agegroup, string email, int partnerID, string userType)
{
    try
    {
        string query = "Insert into tblUser (username,gender,agegroup,email,partnerid,usertype) values (@username,@gender,@age,@email,@partnerid,@usertype)";
        SqlCommand cmd = new SqlCommand(query, _dbConnection.getCon());
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.AddWithValue("@username", username);
        cmd.Parameters.AddWithValue("@gender", gender);
        cmd.Parameters.AddWithValue("@age", agegroup);
        cmd.Parameters.AddWithValue("@email", email);
        cmd.Parameters.AddWithValue("@partnerid", partnerID);
        cmd.Parameters.AddWithValue("@usertype", userType);
        if (cmd.ExecuteNonQuery() > 0)
        {
            query = "select scope_identity() as id";
            cmd = new SqlCommand(query, _dbConnection.getCon());
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            adp.Fill(dt);// dt is showing no value in it
            return 1;// This should return ID

        }
        else {
            return -1;
        }

    }
    catch (Exception e) {
        throw e;
    }
}

How can I achieve this?

Upvotes: 1

Views: 1890

Answers (1)

SteveChapman
SteveChapman

Reputation: 3081

Try appending SELECT scope_identity() to your first query and then capture the identity using var identity = cmd.ExecuteScalar() instead of running cmd.ExecuteNonQuery().

Upvotes: 4

Related Questions