0

For some reason i don't want to use the auto-incrementing value of SQL Server and i want to make my own incrementing value for my Database IDs.

What Im trying to do is, for example i will add a Product. Im going to take the last value of the last ProductID by MAX query and add 1 then i will insert that value as the ProductID for the current product.

My code is something like this

    private static int x;
   private static int y = 1;
   private static int z;
   private static int ID;

    private void AddnewProduct_Click(object sender, EventArgs e)
    {
       x = ID;
       z = x + y;
       lblProductIDnew.Text = Convert.ToString(z);

    }
    private void AddProduct_Load(object sender, EventArgs e)
    {
        crud.FillDataGrid("Select * From IDs", ref dgvIDs);    

        using (SqlConnection connection = new SqlConnection("Data Source=DESKTOP-MQKIBSK\\SQLEXPRESS;Initial Catalog=MARISCHELLdatabase;Integrated Security=True"))
        {
            connection.Open();
            string query = "SELECT MAX(ProductID) FROM IDs";
            SqlCommand cmd = new SqlCommand(query, connection);
            ID = (int)cmd.ExecuteScalar();
            lblProductID.Text = ID.ToString();
        }
    }

Is there an easier way to do this? Thanks everyone

UPDATE: The reason i don't want to use SQL auto-increment is when you Send a data to another database(archive) and when you want to restore it, you can't, because you cant enter a value to that identity column. thus the productID will change everytime you send a data to archive and restore it. And setting the identity on and off is not working. if there is a work-around here i will gladly use it. Thanks again everyone

FutureDev
  • 145
  • 3
  • 3
  • 19
  • 3
    `"Is there an easier way to do this?"` - Well, there's the identity feature that's built into SQL Server. What's the reason that you won't use that? – David Jan 30 '17 at 18:12
  • 3
    `For some reason...` the only reason to roll your own is if you want it to fail sooner or later. The way it is presented, it isnt incremented and may very well be stale by time you try to use it. – Ňɏssa Pøngjǣrdenlarp Jan 30 '17 at 18:12
  • 3
    What happens if two people SELECT MAX at the same time? IDENTITY columns are provided to to this for you and are quite flexible, use the right tool for the job. – Alex K. Jan 30 '17 at 18:13
  • Identity Columns use the built in locking mechanism of sql server. – Ross Bush Jan 30 '17 at 18:14
  • 2
    For many reasons you should use the built in identity column. – Damian Galletini Jan 30 '17 at 18:15
  • You need to test whatever you do extensively for race conditions. Rolling your own is generally a stupid move as it is difficult to get something that will not break down and destroy data integrity at some point. What you have will not work unless you can guarantee only one user for the systems will be logged in at a time. – HLGEM Jan 30 '17 at 18:18
  • Is this a homework question by any chance? – aleppke Jan 30 '17 at 18:23
  • My problem with the auto-increment is when you Send a data to another database(archive) and when you want to restore it, you can't, because you cant enter a value to that identity column. thus the productID will change everytime you send a data to archive and restore it. – FutureDev Jan 30 '17 at 18:32
  • No sir its for our thesis system. – FutureDev Jan 30 '17 at 18:32
  • 1
    @FutureDev: `"because you cant enter a value to that identity column"` - Incorrect: http://stackoverflow.com/questions/7063501/how-to-turn-identity-insert-on-and-off-using-sql-server-2008 – David Jan 30 '17 at 18:34
  • 1
    @FutureDev: Regarding your update to the question... `"And setting the identity on and off is not working."` - *That* sounds like a specific problem with which Stack Overflow can help. Currently this question reads as, "I tried to do something, and it didn't work for some reason. So instead of trying to correct the problem I gave up and now want to do the *completely wrong thing*. What's a good way to do this completely wrong thing?" There's no sensible answer to that. We *can*, however, help with the actual problem you encountered. – David Jan 30 '17 at 18:39
  • Thanks for all the feedback. ill try to fix my problem with SQL auto-increment with sir David's link for now. – FutureDev Jan 30 '17 at 18:43
  • @David i got it to work sir. thank you so much and to everyone – FutureDev Jan 30 '17 at 19:14

0 Answers0