CODEWITHSUNDEEP
c#databasems-access
Gali
Gali

Reputation: 14953

how to get the last record number after inserting record to database in access

i have database in access with auto increase field (ID).

i insert record like this (in C#)

SQL = "insert into TermNumTbl (DeviceID,IP) values ('" + DeviceID + "','" + DeviceIP + "') ";
OleDbCommand Cmd = new OleDbCommand(SQL, Conn);
Cmd.ExecuteNonQuery();
Cmd.Dispose();
Conn.Close();

how to get the last inserting number ?

i dont want to run new query i know that in sql there is something like SELECT @@IDENTITY

but i dont know how to use it

thanks in advance

Upvotes: 11

Views: 22178

Answers (6)

Vishnu Patel
Vishnu Patel

Reputation: 1

Simple,

What we do in excel for copy text in above cell?

Yes, just ctrl+" combination, and yes, it's work in MS ACCESS also.

You can use above key stroke combination for copy above records field text, just make sure if you have duplicate verification applied or edit field data before move next field.

If you aspects some more validation or any extraordinary then keep searching stack overflow.

Upvotes: -2

Anthony Griggs
Anthony Griggs

Reputation: 1641

Using @Lee.J.Baxter 's method (Which was great as the others id not work for me!) I escaped the Extension Method and just added it inline within the form itself: 

OleDbConnection con = new OleDbConnection(string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='{0}'", DBPath));
OleDbCommand cmd = con.CreateCommand();
con.Open();
cmd.CommandText = string.Format("INSERT INTO Tasks (TaskName, Task, CreatedBy, CreatedByEmail, CreatedDate, EmailTo, EmailCC) VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}')", subject, ConvertHtmlToRtf(htmlBody), fromName, fromEmail, sentOn, emailTo, emailCC);
cmd.Connection = con;
cmd.ExecuteScalar();
using (OleDbCommand command = new OleDbCommand("SELECT @@IDENTITY;", con))
{
    ReturnIDCast =(int)command.ExecuteScalar();
}

NOTE: In most cases you should use Parameters instead of the string.Format() method I used here. I just did so this time as it was quicker and my insertion values are not coming from a user's input so it should be safe.

Upvotes: 0

Kevin
Kevin

Reputation: 1

 query = "Insert Into jobs (jobname,daterecieved,custid) Values ('" & ProjectNAme & "','" & FormatDateTime(Now, DateFormat.ShortDate) & "'," & Me.CustomerID.EditValue & ");"'Select Scope_Identity()"
        ' Using cn As New SqlConnection(connect)

          Using cmd As New OleDb.OleDbCommand(query, cnPTA)
                cmd.Parameters.AddWithValue("@CategoryName", OleDb.OleDbType.Integer)
                If cnPTA.State = ConnectionState.Closed Then cnPTA.Open()
                ID = cmd.ExecuteNonQuery
          End Using

Upvotes: 0

Lee.J.Baxter
Lee.J.Baxter

Reputation: 555

I guess you could even write an extension method for OleDbConnection...

public static int GetLatestAutonumber(
    this OleDbConnection connection)
{
    using (OleDbCommand command = new OleDbCommand("SELECT @@IDENTITY;", connection))
    {
        return (int)command.ExecuteScalar();
    }
}

Upvotes: 5

Cesar BA
Cesar BA

Reputation: 146

I like more indicate the type of command is very similar to the good solution provided by Pranay Rana

using (OleDbCommand cmd = new OleDbCommand())
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = sql_Insert;
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = sql_obtainID;
                        resultado = (int)comando.ExecuteScalar();
                    }

Upvotes: 0

Pranay Rana
Pranay Rana

Reputation: 176886

More about this : Getting the identity of the most recently added record

The Jet 4.0 provider supports @@Identity

string query = "Insert Into Categories (CategoryName) Values (?)";
string query2 = "Select @@Identity";
int ID;
string connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|Northwind.mdb";
using (OleDbConnection conn = new OleDbConnection(connect))
{
  using (OleDbCommand cmd = new OleDbCommand(query, conn))

  {
    cmd.Parameters.AddWithValue("", Category.Text);
    conn.Open();
    cmd.ExecuteNonQuery();
    cmd.CommandText = query2;
    ID = (int)cmd.ExecuteScalar();
  }
}

Upvotes: 15

Related Questions