counting row and increment primary key with the next row

Question

Here is my code: ( It is not working)

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('" +1+ "','" + DropDownList5.SelectedItem.Text + "','" + DropDownList2.SelectedItem.Text + "','" + DropDownList4.SelectedItem.Text + "','" + DropDownList6.SelectedItem.Text + "','" + DropDownList7.SelectedItem.Text + "','" + TextBox1.Text + "','" + TextBox2.Text + "')", sc);

sc.Close();

Jignesh Trivedi · Answer

hi I think count might give you a wrong result. for example some Ids are skipped by any reason like IDs 123 56 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.

John Joe · Answer

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.

gaurav singh · Answer

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

Riddhi Valecha · Answer

try { sc.Open(); string s = SELECT COUNT (*) FROM Records; cmd = new SQLCommand(ssc); 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.