Issues with Increment MS-SQL, C#

Question

I am having an issue with the increment for the ID. The ID would increase by one every time I click insert, but the problem occurs when the ID 2, it would insert the values twice, if ID 3, it would insert the values three times, and so on.

There are couple of options that I have been trying. One is Max and the other one is finding the last inserted value and add one to the ID just.

I would appreciate if anyone can help me out with this. Thanks

public partial class LoginInfo : System.Web.UI.Page
{
    static string myConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

    private void GenerateID()
    {
        SqlConnection myConnection = new SqlConnection(myConnectionString);
        string myQuery1 = "Select Count(S_ID) from Student_Name";
        SqlCommand cmd = new SqlCommand(myQuery1, myConnection);
        myConnection.Open();
        int addOneS_ID_Table1 = Convert.ToInt32(cmd.ExecuteScalar());
        myConnection.Close();
        addOneS_ID_Table1++;
        lblstdID.Text = addOneS_ID_Table1.ToString();

        myConnection.Open();
        cmd.CommandText = "Select Count(P_ID) from Student_Pass";
        int addOneP_ID_Table2 = Convert.ToInt32(cmd.ExecuteScalar());
        myConnection.Close();
        addOneP_ID_Table2++;
        lblstdPass.Text = addOneP_ID_Table2.ToString();
        /*-----------------------------------------------------------------*/
        //SqlConnection myConnection = new SqlConnection(myConnectionString);

        //SqlCommand cmd = new SqlCommand("SELECT MAX(S_ID) as max_S_ID from Student_Name",myConnection);
        //cmd.CommandType = CommandType.Text;
        //myConnection.Open();
        //lblstdID.Text = Convert.ToString(cmd.ExecuteScalar());
        //cmd.CommandText = "SELECT MAX(P_ID) as max_P_ID FROM Student_Pass";
        //lblstdPass.Text = Convert.ToString(cmd.ExecuteScalar());
        //myConnection.Close();
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if(!IsPostBack)
        {
            GenerateID();
        }
    }

    protected void btnInsert_Click(object sender, EventArgs e)
    {
        SqlConnection myConnection = new SqlConnection(myConnectionString);

        string myQuery = "Insert into Student_Name(S_ID,STUDENT_NAME) VALUES" + "(@S_ID,@STUDENT_NAME)";

        SqlCommand cmd = new SqlCommand(myQuery,myConnection);

        cmd.Parameters.Add("@S_ID", SqlDbType.Int).Value = lblstdID.Text;
        cmd.Parameters.Add("@STUDENT_NAME", SqlDbType.VarChar).Value = txtstdName.Text;

        if(myConnection.State == ConnectionState.Closed)
        {
            myConnection.Open();
        }

        cmd.ExecuteNonQuery();
        cmd.Parameters.Clear();

        //Second Table

        cmd.CommandText = "Insert into Student_Pass(P_ID,PASSWORD) VALUES" + "(@P_ID,@PASSWORD)";
        cmd.CommandType = CommandType.Text;

        cmd.Parameters.Add("@P_ID", SqlDbType.Int).Value = lblstdPass.Text;
        cmd.Parameters.Add("@PASSWORD", SqlDbType.VarChar).Value = txtStdPass.Text;

        cmd.ExecuteNonQuery();
        cmd.Parameters.Clear();
        myConnection.Close();
        GenerateID();
        lblResult.Text = "Successfully Saved";
        GridView1.DataBind();
    }
}

Answer 1

It's highly recommended to not use max or top in order to determine the "next" identifier to use, simply because of the cost associated with it.

However, there are some other pitfalls to using max and top especially if there is a chance that nolock is used (which is a whole other conversation). I've seen a lot of web applications use max and has proven to be a performance killer.

Rahul is right, @@identity or scope_identity are good alternatives. However, I think this calls for using a native SQL Server sequence, which was introduced in SQL Server 2012. It was something that application developers have been waiting for and Microsoft finally delivered.

The issue with using @@identity or scope_identity is that you actually have to write rows to some table before you can even contemplate doing something.

This makes it a bit more costly and messier than what it may need to be. In the case of using a sequence, you can issue a new sequence number and then decide what to do and once you decide what to do you're still guaranteed that you're the only one with that sequence number.

You would create a sequence like this. You should check out the documentation as well.

create sequence dbo.StudentIdSeq
    as int -- this can be any integer type
    start with 1 -- you can start with any valid number in the int, even negative
    increment by 1;
go

Then you issue new sequence numbers by doing this ...

select next value for StudentIdSeq;

It may still be good to create a stored procedure with an output parameter that you can call from C# (which is what I would do). In fact you may want to take it a step further, in the case that you have a bunch of sequences, and create a slick stored procedure that will get a new sequence based on the type that is being requested from the caller.

Answer 2

Problem is with your query since you are getting COUNT(S_ID) which is going to get you count of records doesn't necessarily will give exact ID number. You should rather try MAX(S_ID) or ORDER BY clause saying

Select MAX(S_ID) from Student_Name 

(OR)

Select TOP 1 S_ID from Student_Name ORDER BY S_ID DESC; 

But recommended, You should actually go with SQL Server @@IDENTITY or SCOPE_IDENTITY() to get the last inserted record ID (assuming that S_ID is an IDENTITY column)