counting row and increment primary key with the next row

hi,

I think, count might give you a wrong result. for example some Ids are skipped by any reason
like IDs 1,2,3 ,5,6, here you gets count 5 and your next number is count+1 = 6 but it logically wrong. in this case you may use Max instead of count.

also for the finding next number you may use ++I.

hope this will help you. 

Thanks for your Reply:

I was thinking maybe I could count records (rows) in a table  then assign the value to "i" ...
when the progrm runs i = count + 1 . or i++ . to get primary key for the next record.


ID = i
 


 cmd = new SqlCommand("Insert into Records(         ID           , Faculty, Intake, Location, Years, University, Fees, Courses) values('" +        i        + "','" + DropDownList5.SelectedItem.Text + "','" + DropDownList2.SelectedItem.Text + "','" + DropDownList4.SelectedItem.Text + "','" + DropDownList6.SelectedItem.Text + "','" + DropDownList7.SelectedItem.Text + "','" + TextBox1.Text + "','" + TextBox2.Text + "')", sc);       

____________________________________________________________________


I am not pretty sure how to How to auto - increment   primary key , so i thought calculating will work for me but it seems like I am missing lots of things.     

you will have done it in many ways,

1.) You should use auto increment on primary key column and set indentity keywords on a primary key column inside a table

2) if you want  using data type uniqueidentifier  on primary key column and going to the column property and click on Default Value or Binding and set (newid()) 


3.) In you code.

 
sc.Open();
            cmd = new SqlCommand("SELECT COUNT (*) FROM Records", sc);




            Int32 count = (Int32)cmd.ExecuteScalar();


            if (count >= 0)


                for (int i = 1; i < count; i++)            
              cmd = new SqlCommand("Insert into Records(Num, Faculty, Intake, Location, Years, University, Fees, Courses) values('" +(count+i)+ "','" + DropDownList5.SelectedItem.Text + "','" + DropDownList2.SelectedItem.Text + "','" + DropDownList4.SelectedItem.Text + "','" + DropDownList6.SelectedItem.Text + "','" + DropDownList7.SelectedItem.Text + "','" + TextBox1.Text + "','" + TextBox2.Text + "')", sc);
               sc.Close();

try
{
 sc.Open();
 string s ="SELECT COUNT (*) FROM Records";
 cmd = new SQLCommand(s,sc);
 int count = convert.Toint32(cmd.executeScalar());
 if(count>0)
{
 // you cannot fire insert query here. This is wrong.
 //And you have closed the connection before executing the query ???
}
}
--

Give me the logic that you need to set.