I have a alphanumeric id. how do i auto increment(+1) the numbers?

I'm supposed to set the Survey ID to SVY0001 to SVY9999.

How do i increase the numbers by 1 each time i add a new survey?

Currently i have a regular expression validator in my survey id text box for the SVY to be compulsary.

ValidationExpression="^SVY([0-9]{4})$"

Please advise me.

Here's my .cs code.

protected void btnCreateSurvey_Click(object sender, EventArgs e)
{
    SqlConnection connection = null;
    SqlCommand command = null;

    try
    {
        string connectionString = ConfigurationManager.ConnectionStrings["SurveyFdDBConnString"].ConnectionString;
        connection = new SqlConnection(connectionString);
        connection.Open();

        string sql = "Insert into Survey (SurveyID, SurveyName, CreatedBy, DateCreated, Anonymous) Values " + "(@SurveyID, @SurveyName, @CreatedBy, @DateCreated, @Anonymous)";
        command = new SqlCommand(sql, connection);
        command.Parameters.AddWithValue("@SurveyID", txtBoxSurveyID.Text);
        command.Parameters.AddWithValue("@SurveyName", txtBoxSurveyName.Text);
        command.Parameters.AddWithValue("@CreatedBy", (string)Session["UserID"]);
        command.Parameters.AddWithValue("@DateCreated", DateTime.Now);
        command.Parameters.AddWithValue("@Anonymous", txtBoxAnonymous.Text);

        int rowCount = command.ExecuteNonQuery();

        if (rowCount != 0)
        {
            Session["SurveyID"] = txtBoxSurveyID.Text;
            Response.Write("Survey created successfully.<br/>");
            Response.Redirect("~/Admin/Select.aspx");
        }
    }

    catch (Exception ex)
    {
        Response.Write("Error: " + ex.Message);
    }

    finally
    {
        connection.Close();
        txtBoxSurveyID.Text = string.Empty;
        txtBoxSurveyName.Text = string.Empty;
        txtBoxAnonymous.Text = string.Empty;
    }
}

Solution

You can use a combined key for the primary key. In one column the alphanumeric code and in the other the number. In the integer column you can set as identity and sql does the job for you.

To set the combined key. Create the two columns, one integer and one varchar. Select one on the columns with shift down select the second and then select the button of the key in the menu to set them as the key.

Then go to properties > Identity column and select from the dropdown the integer column.

Combined key example after you select the key in the menu you will see both column with a key

enter image description here

Select column as the identity example

enter image description here