.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Forum >> C# >> Post New QuestionBookmark and Share Subscribe to Forum

How to create an alpha numeric id in c# using ms-access?

Posted By: Sameer     Posted Date: April 05, 2010    Points:2   Category :C#
I have table called emp, which contains three columns
Empid,Empname,salary etc.

I want to insert the empid as auto generated id through program.


Any help appreciated.

Responses
Author: Dilip             
Posted Date: April 05, 2010     Points: 5   

It actually depends on what should be the logic of you empid creation. We used the below link to create unique ids for our Strategic Partners.

I would suggest to make your autogenerated column(empid in this case) as integer rather than string. The reason behind, your empid must the primary key on which indexing takes palce for better search. If you intend to use the alphanumeric characters in Primary key, then empid should be string. I hope you must be aware that string column are not good for indexing.

So I would suggest you to create the empid as identity column with integer datatype as below

alter table YourTable add ID int identity(start, increment)

and for the presentation purpose use some alphanumeric characters.

Do check the link below

http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

Regards
Dilip
Author: Lalij Mer             
Posted Date: May 01, 2012     Points: 20   

Hi,,

Try this code sample

protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlcon"].ToString());
try
{
string ID = GenerateId("emp", "empid", 10, "EMP");
SqlCommand cmd = new SqlCommand("insert into emp values('" + ID + "','" + TextBox1.Text + "','" + TextBox2.Text + "')", con);
cmd.CommandType = CommandType.Text;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}

public string GenerateId(string TableName, string ColumnName, int ColumnLength, string Prefix)
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlcon"].ToString());
string Query, Id;
int PrefixLength, PadLength;
PrefixLength = Convert.ToInt32(Prefix.Length);
PadLength = ColumnLength - PrefixLength;
Query = "SELECT '" + Prefix + "' + REPLACE(STR(MAX(CAST(SUBSTRING(" + ColumnName + "," + Convert.ToString(PrefixLength + 1) + "," + PadLength + ") AS INTEGER))+1," + PadLength + "),' ',0) FROM " + TableName;

SqlCommand com = new SqlCommand(Query, con);
con.Open();
if (com.ExecuteScalar().ToString() == "")
{
Id = Prefix;
for (int i = 1; i <= PadLength - 1; i++)
{
Id += "0";
}
Id += "1";
}
else
{
Id = Convert.ToString(com.ExecuteScalar());
}
con.Close();
return Id;
}


Please Check my answer if you help....
Thank You...

Lalit.


Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend