increment ID automatically by adding +1

Sulieman Alkatib

I'm developing a simple application to insert values to ms access database but I need to make event ID as automated field by get maximum value from the column and add +1 to insert the new value

OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\zoom\Desktop\TRAVIL IN OMAN PROJECT - Copy\WebApplication1\WebApplication1\App_Data\travilinoman.MDB.mdb");
con.Open();
OleDbCommand cmd = new OleDbCommand("select MAX(ID)+1 from event", con);
OleDbDataReader dr = cmd.ExecuteReader();
TextBox1.Text = dr.ToString();
con.Close();

the textboxis showing this result : System.Data.OleDb.OleDbDataReader

how can I convertit to the acctual value (int) ???

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Gupta9y ago
Accepted answer
If you have only one result then its better to use ExecuteScalar() function.
OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\zoom\Desktop\TRAVIL IN OMAN PROJECT - Copy\WebApplication1\WebApplication1\App_Data\travilinoman.MDB.mdb");
       con.Open();
       OleDbCommand cmd = new OleDbCommand("select MAX(Nz(ID,0))+1 from event", con);
       TextBox1.Text = Convert.ToString(cmd.ExecuteScalar());
           con.Close();
We are using Nz function here, when ID is null then it will return 0 instead of null
Sulieman Alkatib9y ago
work well

thanks master Amit
con.Open();
      OleDbCommand cmd = new OleDbCommand("select MAX(Nz(ID,0))+1 from event", con);
      TextBox1.Text = Convert.ToString(cmd.ExecuteScalar());
          con.Close();
Khaja Moizuddin9y ago
Why dont you use identity column to increment the row number by 1 instead of doing it from Code.
Sulieman Alkatib9y ago
An exception of type 'System.InvalidOperationException' occurred in System.Data.dll but was not handled in user code
Additional information: No data exists for the row/column.

not working

I have this code on VB.net but I could not convert it to C
Dim conn As New SqlConnection("Data Source=xp-pc;Initial Catalog=HR;Integrated Security=True")
Dim cmd As New SqlCommand("select MAX(LID)+1 from Letters ", conn)

Dim sql As String = "select MAX(LID)+1 from Letters "

conn.Open()

Dim lid As Int32 = Convert.ToInt32(cmd.ExecuteScalar)
conn.Close()
TextBox1.Text = lid
Bharath Peggem9y ago
Hi Sulieman,

Try this

TextBox1.Text = dr[0];

Thanks,
Bharath Peggem