increment ID automatically by adding +1

Question

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) ??? 

Answer 1

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

 

 

Answer 2

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();

Answer 3

Why dont you use identity column to increment the row number by 1 instead of doing it from Code.

Answer 4

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

Answer 5

Hi Sulieman,

 

Try this 

 

   TextBox1.Text = dr[0];

 

Thanks,

Bharath Peggem