Auto Increment No

public partial class Invoice_newinvoice : System.Web.UI.Page
{
   
    connection obj = new connection();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            txt_invoiceno.Text=countinvoiceno().ToString();
            txt_custid.Text=countcustomerid().ToString();
            txt_invoicedate.Text = System.DateTime.Now.ToShortDateString();
        }

    }

    public string countinvoiceno()
    {
        string invno;
        obj.con.Open();
        obj.cmd1 = new SqlCommand("select 'IN'+cast(max(substring(invoiceno,3,3)+1)as varchar(50))from invoicemaster ", obj.con);
        obj.dr = obj.cmd1.ExecuteReader();
        obj.dr.Read();
        if (obj.dr.IsDBNull(0))
        {
            invno="IN1";
        }
        else
        {
            invno=obj.dr[0].ToString();
        }
        return invno;
        obj.con.Close();
    }
    public string countcustomerid()
    {
        obj.con.Close();
        obj.con.Open();
        obj.cmd1 = new SqlCommand("select 'CI'+cast(max(substring(invoiceno,3,3)+1)as varchar(50)) from customers", obj.con);
        string m ;
        obj.dr = obj.cmd1.ExecuteReader();
        obj.dr.Read();
        if (obj.dr.IsDBNull(0))
        {
            m = "CI1";
        }
        else
        {
            m = obj.dr[0].ToString();
        }
        return m;
        obj.con.Close();
    }


    protected void btn_submitinvoice_Click(object sender, EventArgs e)
    {
        obj.con.Open();
        obj.cmd = new SqlCommand("sp_invoiceinsert",obj.con);
        obj.cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, p13;
         
        p1 = new SqlParameter("@invoiceno",txt_invoiceno.Text);
        obj.cmd.Parameters.Add(p1);   
        p2 = new SqlParameter("@invoicedate",txt_invoicedate.Text);
        obj.cmd.Parameters.Add(p2);
        p10 = new SqlParameter("@customerid", txt_custid.Text);
        obj.cmd.Parameters.Add(p10);
        p4 = new SqlParameter("@totalamount",Convert.ToDecimal(txt_totalamt.Text));
        obj.cmd.Parameters.Add(p4);
        p5 = new SqlParameter("@paidamount", Convert.ToDecimal(txt_pamount.Text));
        obj.cmd.Parameters.Add(p5);
        p6 = new SqlParameter("@dueamount", Convert.ToDecimal(txt_damount.Text));
        obj.cmd.Parameters.Add(p6);
        p12 = new SqlParameter("@paymentmode", drdp_paymode.SelectedItem.Text);
        obj.cmd.Parameters.Add(p12);
        p13 = new SqlParameter("@taxtype", drdp_taxtype.SelectedItem.Text);
        obj.cmd.Parameters.Add(p13);
        p7 = new SqlParameter("@firstname",txt_fname.Text);
        obj.cmd.Parameters.Add(p7);       
        p8 = new SqlParameter("@address",txt_addr.Text);
        obj.cmd.Parameters.Add(p8);
        p11 = new SqlParameter("@phone", txt_phone.Text);
        obj.cmd.Parameters.Add(p11); 
        p9 = new SqlParameter("@email", txt_email.Text);
        obj.cmd.Parameters.Add(p9);
        obj.cmd.ExecuteNonQuery();
        obj.con.Close();
        Server.Transfer("/inventory/Invoice/newinvoice.aspx");

}
sql functions:
SUBSTRING(s,start,length):Returns a part of a string from String s starting from start position,where length is the no of chars to be picked...
ex:

SELECT SUBSTRING("HELLO",1,3) OUTPUT:HEL
CONVERTION FUNCTIONS:
Explicitly converts an expression of one datatype to another.. we has two conversion  functions CAST and CONVERT ,both provide similar functionality..
Example:
CAST Syntax:::
CAST(expression AS data_type[(length)])

Select CAST(10.6496 AS INT) output:10
Select CAST(10.6496 AS money) output:10 .3497
CONVERT Syntax
CONVERT(data_type[(length)],expression[,style(optional)])
select CONVERT(int,10.6496)
select CONVERT(Varchar(50),GETDATE())