database updating and sorting

Question

0.00/5 (No votes)

See more:

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

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 third record of city chennai to agra

then the database will look like this

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

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

[edit]Code blocks added, but the lack of formatted code is all the OP - OriginalGriff[/edit]

Posted 11-Sep-11 21:18pm

M.Ravibalaji

Updated 11-Sep-11 21:20pm

OriginalGriff

v2

Add a Solution

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)

OriginalGriff · Answer 1 · 2011-09-11T21:28:00

First things first. Format your code. Indentation makes it so much easier for us to read. Use the "Improve question" widget to edit your question and provide formatted code within the pre tags.

Secondly, use parametrized queries. They make your code again easier to read, and the protect against SQL Injection attacks:

C#

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) + ...
OleDbCommand cmd_empty = new OleDbCommand(query_empty, this.gMain.openConn);
int i1 = cmd_empty.ExecuteNonQuery();

Becomes

C#

query_empty = "UPDATE customer SET category=@CAT, code=@COD, serial_no='T' ,title=@TIT, ...
OleDbCommand cmd_empty = new OleDbCommand(query_empty, this.gMain.openConn);
cmd_empty.Parameters.AddWithValue("@CAT", NJS_Helper.FormatStringforDB(this.lblcategoryselected.Text));
cmd_empty.Paramaters.AddWithValue("@COD", NJS_Helper.FormatStringforDB(lblcategory_code.Text));
...
int i1 = cmd_empty.ExecuteNonQuery();

Finally, what are you actually trying to achieve? Because your method does seem rather ass-backwards and clumsy. I don't know why you want to do it this way, because I am not sure exactly what you are trying to do in the first place, but I suspect there is a much, much easier way to do it.

Mustafa Ismail Mustafa · Answer 2 · 2011-09-11T21:34:00

Solution 2

First of all, why on earth would you still be using query strings like this? You should have preset stored procedures. 1. They're faster because SQL Server would have their execution plans ready and 2. are not insecure like Johnny Tables[^].

Also, go the extra mile and specify the records you want rather than use "*"; "*" forces the DB to do a lookup prior to bringing out the field names, it has to list it for itself.

And finally, how can you debug your code 2 minutes after writing that jumble?!! I can barely read it. Trust me, put some order in your life, spaces, tabs and new lines are super cheap but they make for infinitely readable code.

One other note, when reading data from a textbox, unless its masked, I'd suggest you use .trim() on the data, this way you can make sure you've removed any preceding or trailing spaces.

Your Update statement seems fine, again take in to account the points I have mentioned above.

Why are you checking for the number of records affected?

Also, the serial number is unaffected, what are you trying to achieve?

I really want to help, I just don't understand exactly what you need.

Posted 11-Sep-11 21:34pm

Mustafa Ismail Mustafa

Comments

M.Ravibalaji 12-Sep-11 3:40am

you have not understood the question correctly if a Dealer has moves from chennai to agra then we are changing that dealers city to agra in that case the serial number has to change because the city is changed from chennai to agra ... agra goes on top so the serial number has to change accordingly please check the structure i have given in the question and more than that i am using access database

Mustafa Ismail Mustafa 12-Sep-11 3:48am

But the serial number was already 3!

I'll assume that that was a typing mistake and what you wanted was to set it N + 1, where N is the current highest serial number value for that particular city, correct? Also, does that mean you want to change the values of ALL the other serial numbers of the previous city so you wouldn't have a gap? For example, Chennai has 1,2,3 and Agra has 1,2,3,4, Agra 3 moves to Chennai, Chennai now has 1,2,3,4 and Agra now has 1,2,4, do you want to update the 4 as well (and any trailing serial numbers) to reflect the correct order or it doesn't really matter?

M.Ravibalaji 12-Sep-11 3:59am

ya now you understood the question now agra should be 1,2,3 and chennai should follow on with 4,5,6,7 this i how it should be . if another dealer's city is edited to Bihar then agra is 1,2,3 Bihar is 4 and chennai is 5,6,7,8
give me some idea or code help me only you understood my problem

Mustafa Ismail Mustafa 12-Sep-11 8:35am

Hmmm.. CTE is the only way to go. But this would be very bad on large tables, say 10,000 or more records. I do not think I would exaggerate when I would say that on a humble machine, the entire server would grind to halt until the process is done. Have you considered simply grabbing them without the need for a serial number? Or is there a specific need for the serial number? You could create a non-serial identifier.

Mustafa Ismail Mustafa · Answer 3 · 2011-09-11T21:53:00

For the first part:

SQL

(This is in SP format, you can alter it to query string at your own discretion)

<pre lang="SQL">
DECLARE @serialNo int = (SELECT MAX serial_no FROM TableThatHasDetails WHERE condition1 = @condition1) + 1

UPDATE TableThatHasDetails
SET
   Category = @Category, 
   Code = @Code
   Serial_no = @serialNo
   City = @City
WHERE
   Condition1 = @Condition1
   AND Condition2 = @Condition2

The second part is rather complicated and it would require use of Common Table Expressions.

database updating and sorting

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0