数据库记录更新 [英] database records updation

问题描述

我有这样的数据库

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

当我更新城市时，我的序列号也应作相应更改，每件事都应使用编码完成

应该按城市，serial_no
排序
请任何帮助我.....

注释中的代码:

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

这就是我现在要更新的方式..首先，我为更改的城市更新一个空字符串，然后找到该空字符串，然后递增其余记录并更新空字符串



下面的代码首先在该序列号中插入一个临时变量T，之后我要根据城市选择记录.现在我按顺序获取它.现在我要按顺序进行序列号

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>

现在我想订购序列号为1,2,3,4,5,6的T应该是3

我尝试过此代码

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

推荐答案

您需要在表中包括主键

查看您的查询:
仅在SELECT语句中使用CITY字段，而不在任何UPDATE语句中使用.将city语句添加到适当的UPDATE语句中

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

要考虑的几件事:

• 在表中维护排序值是非常不好的做法.这样的值只会定义一个顺序，如果您想将顺序更改为其他顺序(例如在用户界面中)，会发生什么情况.
• 与其存储排序值，不如存储所有可以使用的必要数据对行进行排序.如果需要，请相应地调整ORDER BY子句.
• 在SELECT语句中处理排序时，您所问的实际问题将完全消失.更改城市时，只需更改它，而无需进行任何其他修改(例如serial_no).这将大大简化代码集会
• 使用参数: ^ ].使用参数将帮助您避免SQL注入，并避免与数据类型相关的问题.例如，考虑如果用户在this.lblcategoryselected.Text
• 中使用适当的数据类型写入"Some''City"，会发生什么情况.根据您的代码，看起来serial_no 是varchar ，但是您将其用于数字.

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.

这篇关于数据库记录更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！