Sql command for autogenerate columns

Question

0.00/5 (No votes)

See more:

How generate column id automaticaly whnever data goes to insert into it?
Ex. id,name,address etc
I am going to put name and address but id should be generate auto How in asp.net /c#

Posted 18-Oct-12 5:59am

Harshal Solao

Add a Solution

Comments

[no name] 18-Oct-12 12:02pm

Why don't you just do that in SQL Server?

joshrduncan2012 18-Oct-12 12:04pm

I think you are referring to the table schema in SQL Server. If you open that up and set a field to be int. Further options should allow you to say "Identity" as true and "Identity Increment" and "Identity Seed" should auto-populate with 1. That will auto-increment an "ID" field. If that's not what you are looking for, can you elaborate on what I'm missing?

Harshal Solao 18-Oct-12 12:21pm

whats the meaning of "Identity Seed" & what should be the above field value if my id start from 1001 & increment by 1 ?

joshrduncan2012 18-Oct-12 12:29pm

I think Identity Seed should be your 1001 value. Leave increment at 1. That should do the trick that you are looking for.

3 solutions

Solution 1

Hey, use this code. This will generate id and password when you click the button in textbox3 and textbox4

I used this code. Maybe this will help you.

C#

protected void Button1_Click(object sender, EventArgs e)//BUTTON TOGENERATE ID AS ROPDM0,ROPDM1..... AND PASSWORD AS NAME+RANDOM NUMBER
{
   if (TextBox1.Text == "")//YOUR TEXT BOX IN WHICH WE ENTER NAME
   {
      cid = "";
   }
   else
   {
      con.Open();
      SqlCommand cmd = new SqlCommand("select COUNT(CUSTOMER_NAME) from customer", con);//I M USING A CUSTOMER TABLE TO GENERATE ID AND PASSWORD
      int r = Convert.ToInt32(cmd.ExecuteScalar());
      if (r > 0)
      {
         string n = TextBox1.Text;
         string pas = GenerateNumber();
         TextBox4.Text = n + pas;//FOR PASSWORD PLEAES USE YOUR TEXTBOX IN WHICH YOU WANT TO GENERATE PASSWORD
               
         SqlDataAdapter da = new SqlDataAdapter("select distinct(CUSTOMER_ID) from customer", con);
         DataTable dt = new DataTable();
         da.Fill(dt);
         var query = from row in dt.AsEnumerable() select row["CUSTOMER_ID"].ToString();
         string[] ides = query.ToArray();
         int rt = 0;
         int m = 0;
         foreach (string s in ides)
         {
            int l2 = s.Length;
            if (l2 > m){ m = l2; }
            foreach (string s in ides)
            {
               int l3 = s.Length;
               if (l3 == m)
               {
                  string hc = "";
                  for (int i = 5; i < l3; i++){ hc = hc + s[i]; }
                  int hh = Convert.ToInt32(hc);
                  rt = hh + 1;
               }
            }
            TextBox3.Text = "ROPDM" + rt.ToString();//THIS WILL SHOW YOUR ID            
         }
         else
         {
            cid = "ROPDM" + "0";//WHEN THERE IS NO RECORD IN TABLE
            TextBox3.Text = cid;//ID
            string pas = GenerateNumber();
            string n = TextBox1.Text;
            TextBox4.Text = n + pas;//PASSWORD            
         }
         con.Close();
      }
  }

Posted 18-Oct-12 6:19am

vinu2528

Updated 18-Oct-12 6:30am

v2

Comments

[no name] 18-Oct-12 12:23pm

Why are you screaming at the poor guy?

vinu2528 18-Oct-12 12:24pm

THE GenerateNumber function:

public string GenerateNumber()
{
Random random = new Random();
string r = "";
int i;
for (i = 1; i < 5; i++)
{
r += random.Next(0, 9).ToString();
}
return r;
}

fjdiewornncalwe 18-Oct-12 12:32pm

My vote of 1. This is a horrible implementation just full of problems.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

milenalukic · Accepted Answer · 2012-10-18T07:15:00

Hi,

First add in ID column to your database if you have not already done so. Make this the primary key by clicking the key icon. Then in the properties section at the bottom find identity and set it to yes. The seed is the starting number you would like. for example if you want your first id to be 1000 put that in the seed. the default is 1. Also in the increment put in the number you want your id to increase by with every entry. Default is 1 again. so this will increase your id from say 1000,1001,1002 etc.

When you save data to the table the id is created automatically and you do not need to do anything to calculate this yourself.

If you require to show the id on screen you will need to create a stored procedure to do the update and return @@identity which will be the id value of the last created record.

SQL

Create Procedure InsertUser
    @Name varchar(50),
    @address varchar (50)
as
begin

insert into MyUserTable(name, address)
values(@name,@address)

Return @@identity
End

Maciej Los · Accepted Answer · 2012-10-18T07:25:00

If you use SQL server, ASP.NET/C# has nothing to do with it ;)

Please, see these and related topics:
http://www.w3schools.com/sql/sql_autoincrement.asp[^]
http://msdn.microsoft.com/en-us/library/ms186775.aspx[^]
http://social.msdn.microsoft.com/Forums/en-AU/csharpgeneral/thread/e6946a5f-8b29-4932-bbf8-577eac374ca2[^]

Sql command for autogenerate columns

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0