none
How to Create Autogenerate ID in C# ?

    Question

  • I have 1 textbox i wanna to make my id column from my table Art to autogenerate +1 evrytime by checking whats is curently last id on table ..

    here is my curently code :

    using System;
    using System.Linq;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlServerCe;
    
    namespace SmartDeviceProject1
    {
        public partial class artadd : Form
        {
            public artadd()
            {
                InitializeComponent();
            }
            SqlCeConnection cn = new SqlCeConnection("Data Source=\\Program Files\\SmartDeviceProject1\\Ege.sdf");
            SqlCeCommand cmd;
    
            private void artadd_Load(object sender, EventArgs e)
            {
                // TODO: This line of code loads data into the 'egeDataSet.Sub_Gr' table. You can move, or remove it, as needed.
                this.sub_GrTableAdapter.Fill(this.egeDataSet.Sub_Gr);
                // TODO: This line of code loads data into the 'egeDataSet.Group' table. You can move, or remove it, as needed.
                this.groupTableAdapter.Fill(this.egeDataSet.Group);
                // TODO: This line of code loads data into the 'egeDataSet.MR' table. You can move, or remove it, as needed.
                this.mRTableAdapter.Fill(this.egeDataSet.MR);
    
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                cn.Open();
                cmd = new SqlCeCommand("INSERT INTO Art(id,name,buy_price,sell_price,mr,group_id,subgroup_id) VALUES(@id,@name,@buy_price,@sell_price,@mr,@group_id,@subgroup_id)", cn);
                cmd.Parameters.Add("@id", textBox1.Text);
                cmd.Parameters.AddWithValue("@name", textBox2.Text);
                cmd.Parameters.AddWithValue("@buy_price", textBox3.Text);
                cmd.Parameters.AddWithValue("@sell_price", textBox4.Text);
                cmd.Parameters.AddWithValue("@mr", comboBox1.Text.Trim());
                cmd.Parameters.AddWithValue("@group_id", comboBox2.Text.Trim());
                cmd.Parameters.AddWithValue("@subgroup_id", comboBox3.Text.Trim());
    
                try
                {
                    int affectedRows = cmd.ExecuteNonQuery();
                    if (affectedRows > 0)
                    {
                        MessageBox.Show("Insert Successfull");
                        cn.Close();
                    }
                    else
                    {
                        MessageBox.Show("Insert Failed");
                        cn.Close();
                    }
                }
                catch (Exception ex)
                {
    
                    MessageBox.Show(ex.Message);
    
                }
            }
    
        }
    }

    Sunday, December 09, 2012 10:36 PM

Answers

All replies

  • Maybe you can create some counter that will raise by 1 every time you click on the button for example. After it is raised by one you can insert the new value in the table. Tell me if that is what you want.

    Microsoft Student Parnter Microsoft Technology Associate

    Sunday, December 09, 2012 11:22 PM
  • You should set up your id column as an IDENTITY column in your SqlCE database, which automatically increments the value when you insert a row:

    http://msdn.microsoft.com/en-us/library/ms174639(v=sql.100).aspx


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    • Marked as answer by mastercho Monday, December 10, 2012 12:25 AM
    Monday, December 10, 2012 12:02 AM
    Moderator
  • Oh, one more thing ... if you need to get that new ID back after you've inserted the row into your database table, then there's more  you need to do. Normally, with a SQL Server database, I suggest using SCOPE_IDENTITY() to get the newly added ID. However, SQL CE doesn't support some of the same things as the full SQL Server (and SCOPE_IDENTITY() is one thing not supported) , so you have to use @@IDENTITY instead. See the reply that was marked as an answer in the following thread:

    http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/1d1d3267-dc29-470b-bb20-00487a39bc87/


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Monday, December 10, 2012 12:09 AM
    Moderator
  • You should set up your id column as an IDENTITY column in your SqlCE database, which automatically increments the value when you insert a row:

    http://msdn.microsoft.com/en-us/library/ms174639(v=sql.100).aspx


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    thats help me thanks but works only for ID columt when i try to sent for quantity_on_stock i get this error 

    http://i.imgur.com/e0kOg.png

    Monday, December 10, 2012 12:28 AM
  • Oh, I thought you were talking about PK (Primary Key) ids. You don't want an IDENTITY column for something like a quantity ... and why would you want that? You need to add or subtract to calculate the new quantity, not be auto-incremented!


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Monday, December 10, 2012 12:41 AM
    Moderator
  • Oh, I thought you were talking about PK (Primary Key) ids. You don't want an IDENTITY column for something like a quantity ... and why would you want that? You need to add or subtract to calculate the new quantity, not be auto-incremented!


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    well hehe this is my boss idea when add some articule in my database to add automaticly +10 on quantity when sell somthing to remove -1 from qunatity column :D 
    Monday, December 10, 2012 12:46 AM
  • With normal SQL Server, I would suggest using INSERT/UPDATE triggers to automatically take care of what you're wanting to do. However, SQL CE doesn't support triggers. You'll have to write some C# code to take care of that prior to updating the database.


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Monday, December 10, 2012 12:58 AM
    Moderator