Iakovl Iakovl - 2 months ago 11
SQL Question

How to retrive last enty in ACCESS database using C# via OleDB with sql commands

Again me with me form base program (with visual C# 2010)
After getting most of my program to work I started adding some small "extras"

Here is the problem: when I add a new item, I want to open a MessageBox and write the id of the new item.

Table = Item, field = item_id

Tried using:

cmd.CommandText = "SELECT LAST(item_id) FROM Item";
OleDbDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
id = dr["item_id"].ToString();
}


Here is the full function:

public void ItemInsert(string name,string creator,string publishing,string itemType,string genere, string year)
{
string id ="";
cmd.CommandText = "INSERT INTO Item (item_name, creator_name,publishing_name,item_type,genre,year_publication,location) VALUES (@item_name, @creator_name,@publishing_name,@item_type,@genre,@year_publication,@location);";
cmd.Parameters.AddWithValue("@item_name", name);
cmd.Parameters.AddWithValue("@creator_name", creator);
cmd.Parameters.AddWithValue("@publishing_name",publishing);
cmd.Parameters.AddWithValue("@item_type", itemType);
cmd.Parameters.AddWithValue("@genre",genere);
cmd.Parameters.AddWithValue("@year_publication",year);
cmd.Parameters.AddWithValue("@location", 0);//location=0 when in library

con.Open(); // open the connection
cmd.ExecuteNonQuery();
//get item id
cmd.CommandText = "SELECT LAST(item_id) FROM Item";
OleDbDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
id = dr["item_id"].ToString();
}

con.Close();
MessageBox.Show("Item ID : " + id+"","Added new item");
}

Answer

Replace:

cmd.CommandText = "SELECT LAST(item_id) FROM Item";

With:

cmd.CommandText = "SELECT @@IDENTITY";
Comments