I want to insert records into the sql server 2008 table,using c# asp.net,the column values are all declared as "nvarchar",and the 1st column subid is the primary key.In my code,i retrieve the last coulmn id,increment it and use it as the subid for new record,when i insert a record,it does not get stored sequentially,instead it gets stored in the middle somewhere.So the next time i try to retrieve the last id column value(though i use "order by"),it gives the existing value,which when incremented,gives an error,"duplicate values,not allowed".
this is my code to retrieve the last id value and increment it:
SqlCommand myCommand = new SqlCommand("SELECT TOP 1 subid FROM subjects
ORDER BY subid DESC", con1);
reader = myCommand.ExecuteReader();
if (reader.HasRows == false)
{
Label4.Text = "1";
reader.Close();
}
else
{
while (reader.Read())
{
id = reader[0].ToString();
}
int n = Convert.ToInt32(id);
int j = n + 1;
Label4.Text = j.ToString();
my insert code is:
SqlDataAdapter da = new SqlDataAdapter("SELECT TOP 1 * FROM subjects ORDER BY
subid DESC", con);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet("subjects");
da.Fill(ds, "subjects");
DataRow row = ds.Tables["subjects"].NewRow();
row["subid"] = Label4.Text;
row["dept"] = (string)Session["deptmnt"];
row["yr"] = DropDownList2.SelectedValue;
row["sem"] = DropDownList3.SelectedValue;
row["subname"] = TextBox1.Text;
row["subcode"] = TextBox2.Text;
ds.Tables["subjects"].Rows.Add(row);
da.Update(ds, "subjects");
Label10.Visible = true;
con.Close();
how do i make the value get inserted as the last row,pls help.