database records updation

Question

5.00/5 (1 vote)

See more:

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:

C#

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();
                        }
                        //update empty string with new serial no
                        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

XML

<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

C#

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 .........

Posted 9-Sep-11 0:47am

M.Ravibalaji

Updated 11-Sep-11 21:25pm

Dalek Dave

v5

Add a Solution

Comments

Herman<T>.Instance 9-Sep-11 6:48am

show your current update statement!

M.Ravibalaji 9-Sep-11 9:17am

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();
}
//update empty string with new serial no
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

Pravin Patil, Mumbai 9-Sep-11 6:49am

On what basis you want to update the city...?

Dalek Dave 12-Sep-11 3:25am

Edited for final Code Block.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Syed Salman Raza Zaidi · Answer 1 · 2011-09-09T00:55:00

Solution 1

You'll need to include a Primary key in your table

Posted 9-Sep-11 0:55am

Syed Salman Raza Zaidi

Comments

M.Ravibalaji 9-Sep-11 7:05am

this should be don without setting the primary key because your records will be changing based on the city and the serial number will also change accordingly

Herman<T>.Instance · Answer 2 · 2011-09-09T03:39:00

Solution 3

looking at your query:
only in the SELECT statement the CITY field is used, not in any UPDATE statement. Add the city statement to the appropriate UPDATE statement

Posted 9-Sep-11 3:39am

Herman<T>.Instance

Wendelius · Answer 3 · 2011-09-09T04:37:00

Few things to consider:

It is a very bad practice to maintain an ordering value in a table. Such value would define only one order and what happens if you want to change the ordering to something else, for example in your UI.
Instead of storing the ordering value, store all the necessary data that can be used to order the rows. Adjust you ORDER BY clause accordingly if needed.
When you handle the ordering in the SELECT statement the actual problem you have asked is vanished, completely. When you change the city, you just change it, no need to any additional modifications (like the serial_no). This would simplify your code rally much
Instead of using literals in your sql statement, use parameters: OleDbParameter[^]. Using parameters will help you to be safe from SQL injections and to avoid data type related problems. For example, consider what happens if the user writes "Some'City" in the this.lblcategoryselected.Text
Use proper data types in the database. Based on your code it looks like the serial_no is varchar but you use it for numbers.