Automatic number insertion in MySQL with the format "AN-00000000"

Question

My purpose on this is to insert a autonumber for this format AN-00000000 to the db, with the type in db varchar.

my code in windows form is saving a integer number already "10000000".

But I turn on my mind and think if possible that the auto number will be like this AN-00000000 and to save to the db with a character string.

I tried my best to change and apply but suddenly I can not implement because is on the integer part. I am creating the basic system in our company to create a automate membership for the members, in our company we have 4 parts of membership which is Dep, sep, mep and cef, so I turn on my mind that i need to implement the 4 Id with the following to identify what the department they included. like DEP-00000001, SEP-00000001, MEP-0000001 and CEF-00000001.

Can anyone suggest or give their opinion on my code below? Thanks!

DBconnect.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Windows.Forms;
using System.Data;


namespace PAIRDevelopment.Classes
{
    public class DBConnect
    {
        public static string csConnect = "uid=root; database=membership; pooling = false; convert zero datetime=True";
        public static MySqlConnection csCon= new MySqlConnection(Classes.DBConnect.csConnect);

        public MySqlCommand cmdCon = new MySqlCommand();
        public MySqlDataReader reader;

        public void nonQuery(string cmdText)
        {
            cmdCon.Connection = csCon;
            csCon.Open();
            cmdCon.CommandText = cmdText;
            cmdCon.ExecuteNonQuery();
            cmdCon.Dispose();
            csCon.Close();
        }

        public void OPEN(string cmdtext)
        {
            cmdCon.Connection = Classes.DBConnect.csCon;
            Classes.DBConnect.csCon.Open();
            cmdCon.CommandText = cmdtext;
            reader = cmdCon.ExecuteReader();


        }

        public void CLOSE()
        {
            reader.Close();
            cmdCon.Dispose();
            Classes.DBConnect.csCon.Close();
        }





    }

}

Windows Form:

using System;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace PAIRDevelopment
{
    public partial class Pair_Individual : Form
    {
        Classes.DBConnect OpenConCls = new Classes.DBConnect();


        public Pair_Individual()
        {
            InitializeComponent();

            textBox6.MaxLength = 13;

        }


 private void benabled_Click(object sender, EventArgs e)
        {

           OpenConCls.OPEN("SELECT MAX(piId) FROM tblpair_individual_membership");

            while (OpenConCls.reader.Read())
            {
                string pcount = OpenConCls.reader[0].ToString();

                if (pcount.Length == 0)
                {
                    textBox1.Text = "10000000";
                }
                else
                {
                    //int pcount1 = Convert.ToInt32(pcount);
                    //int pcountAdd = pcount1 + 1;

                    int pcount1 = Convert.ToInt32(pcount);
                    int pcountAdd = pcount1 + 1;
                    textBox1.Text = pcountAdd.ToString();
                }

            }

            OpenConCls.CLOSE();

}

Answer 1

int pcount1 = Convert.ToInt32(pcount);

You are probably throwing a FormatException on the above line now if the string contains more than 0-9.

FormatException - value does not consist of an optional sign followed by a sequence of digits (0 through 9).

If you know the first 4 chars will always be "XXX-" (e.g. "MEP-") then why don't you:

int pcount1 = Convert.ToInt32(pcount.Substring(4)); // ######## part
int pcountAdd = pcount1 + 1;                        // ######## + 1
textBox1.Text = String.Format("{0}-{1:D8}", pcount.Substring(0, 4), pcountAdd);

Which will convert only the numeric part, increment it, and then concatenate it with the first part.

Also you can look into the TryParse method. Which will indicate if your parse succeeded.

TryParse - Converts the string representation of a number to its 32-bit signed integer equivalent. A return value indicates whether the operation succeeded.