I have the database like this
Category code serial_no city
Dealer DLR/1 1 Agra
Dealer DLR/1 2 Agra
Dealer DLR/1 3 Chennai
Dealer DLR/1 4 Chennai
Dealer DLR/1 5 Delhi
Dealer DLR/1 6 Delhi
Now i want to update the second record of city Agra
then the database should look like this
Category code serial_no city
Dealer DLR/1 1 Agra
Dealer DLR/1 2 Chennai
Dealer DLR/1 3 Chennai
Dealer DLR/1 4 Delhi
Dealer DLR/1 5 Delhi
Dealer DLR/1 6 Delhi
when i update my city my serial no should also change accordingly, every thing should be done with the coding
it should be sorted by city,serial_no
please any help me.....
Code from comment:
string query_empty = String.Empty;
query_empty = "UPDATE customer SET category='" + NJS_Helper.FormatStringforDB(this.lblcategoryselected.Text) + "', code='" + NJS_Helper.FormatStringforDB(lblcategory_code.Text) + "', serial_no='" + String.Empty + "')";
OleDbCommand cmd_empty = new OleDbCommand(query_empty, this.gMain.openConn);
int i1 = cmd_empty.ExecuteNonQuery();
if (i1 > 0)
{
string query_sort = String.Empty;
query_sort = "SELECT * FROM customer WHERE (category='" + NJS_Helper.FormatStringforDB(this.lblcategoryselected.Text) + "') ORDER BY city,serial_no";
OleDbDataAdapter ad = new OleDbDataAdapter(query_sort, this.gMain.openConn);
DataSet ds = new DataSet();
ad.Fill(ds, "results");
foreach (DataRow row in ds.Tables["results"].Rows)
{
arr_serial.Add(row["serial_no"].ToString());
}
foreach (string currentitem in arr_serial)
{
if (currentitem.Equals(""))
{
current_index = arr_serial.IndexOf(currentitem);
}
}
int current_index_new = current_index + 1;
string query_update_new = String.Empty;
for (int i = arr_serial.Count - 1; i >= current_index_new; i--)
{
int serialno_new = i + 1;
int serialno_old = i;
query_update_new = "UPDATE customer SET serial_no='" + serialno_new + "' WHERE (category='" + this.lblcategoryselected.Text + "' AND serial_no='" + serialno_old + "')";
OleDbCommand cmd_update_new = new OleDbCommand(query_update_new, this.gMain.openConn);
cmd_update_new.ExecuteNonQuery();
}
string query_update = String.Empty;
query_update = "UPDATE customer SET serial_no='" + current_index_new + "' WHERE (category='" + this.lblcategoryselected.Text + "' AND serial_no='" + String.Empty + "')";
OleDbCommand cmd_update = new OleDbCommand(query_update, this.gMain.openConn);
cmd_update.ExecuteNonQuery();
this how i am updating now .. First i update an empty string for the city changed and then finding that empty string and then incrementing the rest of the records and the updating the empty string
the code below first inserts a temp variable T to that serial number after that i am selecting the records based on city now i get it in order now i want to make the serial numbers in order
<pre lang="text">Category code serial_no city
Dealer DLR/1 1 Agra
Dealer DLR/1 2 Agra
Dealer DLR/1 3 Chennai
Dealer DLR/1 4 Chennai
Dealer DLR/1 5 Delhi
Dealer DLR/1 6 Delhi</pre>
Now i want to update the third record of city chennai to agra
then the database will look like this
<pre lang="text">Category code serial_no city
Dealer DLR/1 1 Agra
Dealer DLR/1 2 Agra
Dealer DLR/1 T Agra
Dealer DLR/1 4 Chennai
Dealer DLR/1 5 Delhi
Dealer DLR/1 6 Delhi</pre>
now i want to order my serial no as 1,2,3,4,5,6 the T should be 3
i tried this code
string query_empty = String.Empty;
query_empty = "UPDATE customer SET category='" + NJS_Helper.FormatStringforDB(this.lblcategoryselected.Text) + "', code='" + NJS_Helper.FormatStringforDB(lblcategory_code.Text) + "', serial_no='T' ,title='" + NJS_Helper.FormatStringforDB(this.txt_title.Text) + "',customer_name='" + NJS_Helper.FormatStringforDB(this.txt_name.Text) + "',company='" + NJS_Helper.FormatStringforDB(this.txt_company.Text) + "',department='" + NJS_Helper.FormatStringforDB(this.txt_department.Text) + "',address1='" + NJS_Helper.FormatStringforDB(this.txt_address1.Text) + "',address2='" + NJS_Helper.FormatStringforDB(this.txt_address2.Text) + "',address3='" + NJS_Helper.FormatStringforDB(this.txt_address3.Text) + "',city='" + NJS_Helper.FormatStringforDB(this.txt_city.Text) + "',state='" + NJS_Helper.FormatStringforDB(this.txt_state.Text) + "',pincode='" + NJS_Helper.FormatStringforDB(this.txt_pincode.Text) + "',country='" + NJS_Helper.FormatStringforDB(this.txt_country.Text) + "',phone='" + NJS_Helper.FormatStringforDB(this.txt_phone.Text) + "',mobile='" + NJS_Helper.FormatStringforDB(this.txt_mobile.Text) + "',fax='" + NJS_Helper.FormatStringforDB(this.txt_fax.Text) + "',email_id='" + NJS_Helper.FormatStringforDB(this.txt_email.Text) + "',website='" + NJS_Helper.FormatStringforDB(this.txt_website.Text) + "',servicetax_no='" + NJS_Helper.FormatStringforDB(txt_servicetax_no.Text) + "',tin='" + NJS_Helper.FormatStringforDB(this.txt_tin.Text) + "',pan='" + NJS_Helper.FormatStringforDB(this.txt_pan.Text) + "',notes='" + NJS_Helper.FormatStringforDB(this.txt_notes.Text) + "' WHERE (category='" + NJS_Helper.FormatStringforDB(this.lblcategoryselected.Text) + "' AND code='" + NJS_Helper.FormatStringforDB(this.lblcategory_code.Text) + "' AND serial_no='" + NJS_Helper.FormatStringforDB(this.lbl_serialno.Text) + "' )";
OleDbCommand cmd_empty = new OleDbCommand(query_empty, this.gMain.openConn);
int i1 = cmd_empty.ExecuteNonQuery();
if (i1 > 0)
{
string query_sort = String.Empty;
query_sort = "SELECT * FROM customer WHERE (category='" + NJS_Helper.FormatStringforDB(this.lblcategoryselected.Text) + "') ORDER BY city";
OleDbDataAdapter ad = new OleDbDataAdapter(query_sort, this.gMain.openConn);
DataSet ds = new DataSet();
ad.Fill(ds, "results");
foreach (DataRow row in ds.Tables["results"].Rows)
{
arr_city.Add(row["city"].ToString());
arr_serial.Add(row["serial_no"].ToString());
}
for (int i = 0; i < arr_city.Count; i++)
{
int count = i + 1;
string query_update_new = String.Empty;
query_update_new = "UPDATE customer SET serial_no='" + count + "' WHERE (category='" + this.lblcategoryselected.Text + "' AND city='" + arr_city[i] + "')";
OleDbCommand cmd_update_new = new OleDbCommand(query_update_new, this.gMain.openConn);
cmd_update_new.ExecuteNonQuery();
}
please help me .........