multi sql table update with listview items

Question

0.00/5 (No votes)

See more:

i have sql table 1 that contains itemcode - itemcount - tablename
and my windows form1 contains listview1 and button1

what i'm trying to do is that when i click on button1 all listview1 SubItems[1] values in the column to update from tables names in SubItems[2] values where itemcode values are SubItems[0]

i tried the following code but it did what it should do but for the first row only not the rest:

C#

foreach (ListViewItem item in listView1.Items)
{
item.Selected = true;
}

if (cn.State == ConnectionState.Closed) cn.Open();

cm = new SqlCommand();

cm.Connection = cn;

ListViewItem lvi1 = listView1.SelectedItems[0];

string tableName = lvi1.SubItems[2].Text;

string itemcode1 = lvi1.SubItems[0].Text;

string itemcode2 = lvi1.SubItems[1].Text;

string sql = "UPDATE " + tableName + " SET [itemcount]= " + itemcode2 + " WHERE [itemcode]= " + itemcode1 + " AND [itemcount] IS NOT NULL";

cm.CommandText = sql;

cm.ExecuteNonQuery();

Posted 17-Oct-15 10:13am

Ahmed Zoeil

Updated 17-Oct-15 20:47pm

Michael_Davies

v2

Add a Solution

Comments

Michael_Davies 18-Oct-15 2:46am

Why set the listview items as selected, just use the loop to execute the updates.

Have you set MultiSelect = true for your listview, if not only one item can be selected and selecting an item deselects the current selected item.

Ahmed Zoeil 18-Oct-15 3:10am

thanks Michael_Davies for your reply

can modify my code and show me how to make the loop in code ?

i'm stuck for 2 days and will appreciate your help

Michael_Davies 18-Oct-15 3:12am

Making sure the listview has multiselect=true will make your code work as it stands.

Ahmed Zoeil 18-Oct-15 3:17am

can I minus the values in itemcount in listview1 from the values in the tables before update and update itemcode with the result ?

Ahmed Zoeil 18-Oct-15 3:19am

and by the way .. multiselect is already set to true

Michael_Davies 18-Oct-15 3:20am

Clarify the question, I do not understand what you mean, you probably can...

Ahmed Zoeil 18-Oct-15 3:27am

I mean :

if listview1 items in form contains the following :

listview1 : itemcode itemcount tablename
1 15 testtbl1
2 20 testtbl2

and as u see if u go to testtbl1 u will find the following :

testtbl1 : itemcode itemcount tablename
1 50 testtbl1

and testtbl2 : itemcode itemcount tablename
2 40 testtbl2

I want when user click button1 it should minus what's in every row in itemcount column from it's table to gives the following result :

testtbl1 : itemcode itemcount tablename
1 35 testtbl1

and testtbl2 : itemcode itemcount tablename
2 20 testtbl2

Michael_Davies 18-Oct-15 3:34am

Simply use maths in your SQL

string sql = "UPDATE " + item.SubItems[2].Text + " SET [itemcount]= [itemcount] - " + item.SubItems[1].Text + " WHERE [itemcode]= " + item.SubItems[0].Text + " AND [itemcount] IS NOT NULL";

Caveat: unpredictable results, you do not perform any testing on your input strings, I can not see how you load the listview and if you load it with the current value in itemcount then you will zero it on the first update...

1 solution

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)

Michael_Davies · Accepted Answer · 2015-10-17T21:18:00

Solution 1

I would recommend you use SQL parameter instead of building an SQL string but...As requested:

C#

if (cn.State == ConnectionState.Closed) cn.Open();
 
cm = new SqlCommand();
 
cm.Connection = cn;

foreach (ListViewItem item in listView1.Items)
{
   string sql = "UPDATE " + item.SubItems[2].Text + " SET [itemcount]= " + item.SubItems[1].Text + " WHERE [itemcode]= " + item.SubItems[0].Text + " AND [itemcount] IS NOT NULL";
 
   cm.CommandText = sql;
   cm.ExecuteNonQuery();
}

Posted 17-Oct-15 21:18pm

Michael_Davies

Comments

Ahmed Zoeil 18-Oct-15 5:30am

thank u so so much Michael_Davies ... u made my day

Ahmed Zoeil 18-Oct-15 13:35pm

@Michael_Davies

may I ask what is the difference between doing it like this or using SQL parameters and can u show me how to do it using SQL parameters plz