show the ID and ad 1

Question

4.00/5 (1 vote)

See more:

I have an application, in the main form a button "AD NEW".

When I click the button, a new form is open, and on a textbox I want to show the that ID+1:

I use max() method.

C#

private void GetNewId()
        {
            int newID = 0;
            Program.Connection.CommandText = "SELECT MAX(ContractId) AS ContractId FROM Contracts";

            DataTable Table = new DataTable();
            Program.Connection.FillDataTable(Table, true);
            
            newID = Convert.ToInt32(Table.Rows[0]["ContractId"]);
            newID++;
            txtContractNumar.Text = newID.ToString();
        }

I'm looking for a better way. I search over the internet and I saw the method max() is not so good.

I'm using Access, but if possible I'm searching a method that work in mysql, ms sql, and access, if not a method for access.

Posted 29-Mar-11 14:37pm

aciobanita

Updated 29-Mar-11 14:49pm

Henry Minute

v2

Add a Solution

2 solutions

Solution 2

You do not ever do this in production code. In a multi-user system, your code WILL generate the same ContractId on multiple clients if they run this code at the same time (and they WILL!).

You let the database assign identity values in autoincrementing columns and use the databases @@SCOPE_IDENTITY equivilent to get back what that Id value is for that record.

Posted 29-Mar-11 17:22pm

Dave Kreskowiak

Comments

aciobanita 29-Mar-11 23:26pm

I try touse @@SCOPE_IDENTITY but in database access not work.

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)

Henry Minute · Accepted Answer · 2011-03-29T15:24:00

I do not know of one command/function (although see below) that will work for Access, MySql and SqlServer.

My Sql uses LAST_INSERT_ID() or @@IDENTITY (although there are reports that this is broken), Sql Server uses SCOPE_IDENTITY() or IDENT_CURRENT('table') (depending on the circumstances) or @@IDENTITY although once again this is deprecated (and possibly broken) and according to this article[^] (scroll down to the 'Access' heading) JET/OLEDB now also supports @@IDENTITY

This, from MSDN[^] (See the Remarks Section) should help you decide which to use for Sql Server.

So, provided you can confirm for yourself that @@IDENTITY works for all the systems you want to use, you might be able to use that.

Otherwise something like:

C#

public enum DBSystem
{
  Access,
  MySql,
  SqlServer
}

public string GetNextIDString(DBSystem system)
{
  string nextIDString = "SELECT ";
  switch (system)
  {
    case DBSystem.Access:
      nextIDString += "@@IDENTITY .............";  // the ...... represents the rest of the syntax
      break;
    case DBSystem.MySql
      nextIDString += "LAST_INSERT_ID(Contracts) .............";  // the ...... represents the rest of the syntax
      break;
    case DBSystem.SqlServer:
      nextIDString += "@@IDENTITY .............";  // the ...... represents the rest of the syntax
      // OR
      nextIDString += "SCOPE_IDENTITY .............";  // the ...... represents the rest of the syntax
      nextIDString += "IDENT_CURRENT(Contracts) .............";  // the ...... represents the rest of the syntax

      break;
  }

  return nextIDString;
}

I hope that some of this helps.