update row with certain text in more than one field sql

Question

i have a table has the following columns :
name - id - phone - position

and a listview that allow user to choose an entire row only
and i want a button click event after choosing a row from the listview to update fields : id - position .. only to a certain text to be both as an examplde : not available

Vulpes · Answer

Well to find the record you'll need an additional parameter - the original username - before it's set to Not available: string sql = UPDATE usertable SET [username]=@username [fillingcode]=@fillingcode [dateassigned]=@dateassigned [branch]=@branch [department]=@department [agency]=@agency [computername]=@computername [lapmodel]=@lapmodel [lapserial]=@lapserial [assetnumber]=@assetnumber [os]=@os [winlicense]=@winlicense [office]=@office [officelicense]=@officelicense [hddsize]=@hddsize [processor]=@processor [ram]=@ram [macadress]=@macadress [ipadress]=@ipadress WHERE [username]= @originalusername; // change WHERE to look for original username cm.CommandText = sql; cm.Parameters.Clear(); cm.Parameters.AddWithValue(@username username); cm.Parameters.AddWithValue(@originalusername textBox1.Text); // insert new parameter cm.Parameters.AddWithValue(@fillingcode fillingcode);

Vulpes · Answer

OK these two lines: int id = int.Parse(lvi.SubItems[1].Text); string position = lvi.SubItems[3].Text; need changing to: string id = not available; string position = not available; Note that this will only work if the 'id' column in the database is of a string type such as varchar.

Vulpes · Answer

No problem try instead: private void listView1_Leave(object sender EventArgs e) { if (!button1.Focused && !button2.Focused && !button3.Focused) { button1.Enabled = false; button2.Enabled = false; button3.Enabled = false; } }

Vulpes · Answer

OK I think I'm clear now :) To duplicate the selected record you'll need to copy the subitems of the selected item back out of the listview and insert them (apart from the 3 columns you want to be null) into the datatable. The code to do that will be: string sql = INSERT INTO usertable([username][fillingcode][dateassigned][branch][department][agency][computername][lapmodel][lapserial][assetnumber][os][winlicense][office][officelicense][hddsize][processor][ram][macadress][ipadress])VALUES(@username@fillingcode@dateassigned@branch@department@agency@computername@os@winlicense@office@officelicense@hddsize@processor@ram@macadress@ipadress); cm.CommandText = sql; cm.Parameters.Clear(); ListViewItem lvi = listView1.SelectedItems[0]; cm.Parameters.AddWithValue(@username lvi.Text); cm.Parameters.AddWithValue(@fillingcode lvi.SubItems[1].Text); cm.Parameters.AddWithValue(@dateassigned lvi.SubItems[2].Text); cm.Parameters.AddWithValue(@branch lvi.SubItems[3].Text); cm.Parameters.AddWithValue(@department lvi.SubItems[4].Text); cm.Parameters.AddWithValue(@agency lvi.SubItems[5].Text); cm.Parameters.AddWithValue(@computername lvi.SubItems[6].Text); cm.Parameters.AddWithValue(@os lvi.SubItems[10].Text); cm.Parameters.AddWithValue(@winlicense lvi.SubItems[11].Text); cm.Parameters.AddWithValue(@office lvi.SubItems[12].Text); ; cm.Parameters.AddWithValue(@officelicense lvi.SubItems[13].Text); cm.Parameters.AddWithValue(@hddsize lvi.SubItems[14].Text); cm.Parameters.AddWithValue(@processor lvi.SubItems[15].Text); cm.Parameters.AddWithValue(@ram lvi.SubItems[16].Text); cm.Parameters.AddWithValue(@macadress lvi.SubItems[17].Text); cm.Parameters.AddWithValue(@ipadress lvi.SubItems[18].Text); cm.ExecuteNonQuery(); When doing the update you'll then need to specify the record for which lapmodel (say) is not null so you don't update the duplicate as well: string sql = UPDATE usertable SET [username]=@username [fillingcode]=@fillingcode [dateassigned]=@dateassigned [branch]=@branch [department]=@department [agency]=@agency [computername]=@computername [os]=@os [winlicense]=@winlicense [office]=@office [officelicense]=@officelicense [hddsize]=@hddsize [processor]=@processor [ram]=@ram [macadress]=@macadress [ipadress]=@ipadress WHERE [username]=@originalusername AND [lapmodel] IS NOT NULL ;

Vulpes · Answer

Sorry I'm not clear what the problem is. Are you saying that the button you're pressing to update the database (button2) is on a different form to the listview itself?

Vulpes · Answer

I wouldn't take this code too literally as I've had to make a number of assumptions and can't really test it but it'll be something along these lines. As in your previous thread I've assumed that 'conn''com' and 'sql' are defined at form level: private void button2_Click(object sender EventArgs e { if (listView1.SelectedItems.Count > 0) { ListViewItem lvi = listView1.SelectedItems[0]; string name = lvi.Text; int id = int.Parse(lvi.SubItems[1].Text); string position = lvi.SubItems[3].Text; conn.Open(); com.Connection = conn; sql = UPDATE someTable SET [id]=@id [position]=@position WHERE [name]=@name; com.CommandText = sql; com.Parameters.Clear(); com.Parameters.AddWithValue(@name name); com.Parameters.AddWithValue(@id id); com.Parameters.AddWithValue(@position position); com.ExecuteNonQuery(); MessageBox.Show(Updated Successfully ..); conn.Close(); } else { MessageBox.Show(No row has been selected); } }

Punar Rajput · Answer

Test reply by author

Vulpes · Answer

continue is only used in loops. If you replace continue with return then the method will skip the remaining conditions and return immediately. Is that what you're trying to do here?

mind controll · Answer

not exactly  I want else statement skip it's if statement if the condition didn't met ( which is cobobox is visible ) and move to the next code in button1 click event and if it met the if statement execute code in if statement and also continue the rest of the code  I tried something like this but got redlines under continue; if (!comboBox1.Visible) { // do something } else { continue; } if (!comboBox2.Visible) { // do something } else { continue; } if (!comboBox3.Visible) { // do something } else { continue; }

Vulpes · Answer

Do you mean like this: if (!comboBox1.Visible) { // do something } else { // do something else } if (!comboBox2.Visible) { // do something } else { // do something else } if (!comboBox3.Visible) { // do something } else { // do something else }

mind controll · Answer

now I need to loop the button action I need it to do the following loop if (!comboBox1.Visible) { // do something } (if wasn't visible skip and continue to next one and if visible do the action and move to next) if (!comboBox2.Visible) { // do something } (if wasn't visible skip and continue to next one and if visible do the action and move to next) if (!comboBox3.Visible) { // do something }

Vulpes · Answer

It should fire when nothing is selected in the combobox which is what you said (or what I understood you to say) you wanted originally. You now seem to be saying that you want it to fire when the display member changes because either a different member is selected or because no member is selected any longer. If that's the case then the SelectedIndexChanged handler for comboBox1 would need to be changed to: private void comboBox1_SelectedIndexChanged(object sender EventArgs e) { comboBox1.Visible = false; comboBox4.Visible = true; } and the handlers for comboBox2 and comboBox3 would need similar changes. However if you've somehow written the code so that the display members for comboBox2 and comboBox3 change automatically when the display member for comboBox1 changes then this will inevitably means that all three will be hidden together.

mind controll · Answer

it still doesn't fire when the display member changed combobox123 show values from table1 column123 when u select different item from combobox1 the displaymember changes in combobox23 I need it to be fired if the display member changes even if the user didn't select it by himself

Vulpes · Answer

If the user deletes the contents of the TextBox part of the ComboBox then that will set the Text ptoperty to  and the SelectedIndex property to -1 which will cause the SelectedIndexChanged event to fire. To do what you want the code for the eventhandlers will need to be as follows: private void comboBox1_SelectedIndexChanged(object sender EventArgs e) { if (comboBox1.SelectedIndex == -1) { comboBox1.Visible = false; comboBox4.Visible = true; } } private void comboBox2_SelectedIndexChanged(object sender EventArgs e) { if (comboBox2.SelectedIndex == -1) { comboBox2.Visible = false; comboBox5.Visible = true; } } private void comboBox3_SelectedIndexChanged(object sender EventArgs e) { if (comboBox3.SelectedIndex == -1) { comboBox3.Visible = false; comboBox6.Visible = true; } } I don't know what other action you have in mind but the code for button1's Click handler will be on the following lines: private void button1_Click(object sender EventArgs e) { if (!comboBox1.Visible) { // do something } if (!comboBox2.Visible) { // do something } if (!comboBox3.Visible) { // do something } if (!comboBox4.Visible) { // do something } if (!comboBox5.Visible) { // do something } if (!comboBox6.Visible) { // do something } }

mind controll · Answer

no it was modified before .. the user modified one of the 3 values to null and the other 2 aren't null

Vulpes · Answer

What do you mean when you say that a combobox has a value of null? Do you mean that: 1. It has no items because there were no rows in the DataTable to which it's bound; or 2. No value is selected because your query didn't produce a value which corresponds to an item in the combobox; or 3. Something else?

mind controll · Answer

any update ? kindly i'm waiting for your answer

mind controll · Answer

hey bro I have combobox1  combobox2 combobox3 combobox4 combobox5 combobox6 and button1 the comboboxes are getting their data from sql tables combobox4 combobox5 combobox6 are hidden by default combobox1  combobox2 combobox3 disabled by default I want combobox1 to hide itself if it's value is null and show the hidden combobox4 and do nothing if it's value isn't null and combobox2 to hide itself if it's value is null and show the hidden combobox5 and do nothing if it's value isn't null and combobox3 to hide itself if it's value is null and show the hidden combobox6 and do nothing if it's value isn't null and the event of button1 check which 3 comboboxes are hidden and then do other action thanks in advance

mind controll · Answer

not exactly what I wanted listview1 leave event only do the following : button1.Enabled = false ; button2.Enabled = false ; button3.Enabled = false ; I want to make an exception for that  if any of button1  button2 and button3 are the ones going to be clicked when leave ( like only if leaving listview1 directly to any of 3 buttons ) they stay enabled all 3 to do their events normally

Vulpes · Answer

Just change the code in the Leave handler to something like this: private void listView1_Leave(object sender EventArgs e) { if (!button1.Focused) button1.Enabled = false; if (!button2.Focused) button2.Enabled = false; if (!button3.Focused) button3.Enabled = false; }

mind controll · Answer

hey bro I listview1 and 3 buttons  button1button2 and button3 I have set leave event to disable the 3 buttons when leaving listview1 but I want to make an exception for the leave event of the listview1 I want it to keep disabling the 3 buttons except if I leave directly to any of the 3 buttons to click any of them thanks in advance

mind controll · Answer

thanks bro working perfectly

Vulpes · Answer

You seem to have a bit of code missing. I've added it (highlighted) below and made a couple of other minor changes: if (conn.State == ConnectionState.Closed) { conn.Open(); } com.Connection = conn; sql = SELECT COUNT(*) FROM lapdev WHERE [username] = @username; com.CommandText = sql; com.Parameters.Clear(); com.Parameters.AddWithValue(@username comboBox8.Text); int numRecords1 = (int)com.ExecuteScalar(); if (numRecords1 > 0) { MessageBox.Show(user already have laptop comboBox8.Text); return; } sql = SELECT COUNT(*) FROM lapdev WHERE [lapserial] = @lapserial; com.CommandText = sql; com.Parameters.Clear(); com.Parameters.AddWithValue(@lapserial comboBox21.Text); int numRecords2 = (int)com.ExecuteScalar(); if ( numRecords2 == 0) { sql = INSERT INTO lapdev([username][fillingcode][dateassigned][branch][department][agency][computername][lapmodel][lapserial][assetnumber][os][winlicense][office][officelicense][hddsize][processor][ram][macadress][ipadress])VALUES(@username@fillingcode@dateassigned@branch@department@agency@computername@lapmodel@lapserial@assetnumber@os@winlicense@office@officelicense@hddsize@processor@ram@macadress@ipadress); com.CommandText = sql; com.Parameters.Clear(); com.Parameters.AddWithValue(@username comboBox8.Text); com.Parameters.AddWithValue(@fillingcode comboBox7.Text); com.Parameters.AddWithValue(@dateassigned dateTimePicker1.Text); com.Parameters.AddWithValue(@branch comboBox2.Text); com.Parameters.AddWithValue(@department comboBox1.Text); com.Parameters.AddWithValue(@agency comboBox3.Text); com.Parameters.AddWithValue(@computername comboBox21.Text); com.Parameters.AddWithValue(@lapmodel comboBox13.Text); com.Parameters.AddWithValue(@lapserial comboBox12.Text); com.Parameters.AddWithValue(@assetnumber comboBox11.Text); com.Parameters.AddWithValue(@os comboBox10.Text); com.Parameters.AddWithValue(@winlicense comboBox17.Text); com.Parameters.AddWithValue(@office comboBox9.Text); ; com.Parameters.AddWithValue(@officelicense comboBox16.Text); com.Parameters.AddWithValue(@hddsize comboBox15.Text); com.Parameters.AddWithValue(@processor comboBox14.Text); com.Parameters.AddWithValue(@ram comboBox20.Text); com.Parameters.AddWithValue(@macadress comboBox19.Text); com.Parameters.AddWithValue(@ipadress comboBox18.Text); com.ExecuteNonQuery(); // com.Parameters.Clear(); // superfluous needs to be removed sql = INSERT INTO lapdevhis([username][fillingcode][dateassigned][branch][department][agency][computername][lapmodel][lapserial][assetnumber][os][winlicense][office][officelicense][hddsize][processor][ram][macadress][ipadress])VALUES(@username@fillingcode@dateassigned@branch@department@agency@computername@lapmodel@lapserial@assetnumber@os@winlicense@office@officelicense@hddsize@processor@ram@macadress@ipadress); com.CommandText = sql; com.Parameters.Clear(); com.Parameters.AddWithValue(@username comboBox8.Text); com.Parameters.AddWithValue(@fillingcode comboBox7.Text); com.Parameters.AddWithValue(@dateassigned dateTimePicker1.Text); com.Parameters.AddWithValue(@branch comboBox2.Text); com.Parameters.AddWithValue(@department comboBox1.Text); com.Parameters.AddWithValue(@agency comboBox3.Text); com.Parameters.AddWithValue(@computername comboBox21.Text); com.Parameters.AddWithValue(@lapmodel comboBox13.Text); com.Parameters.AddWithValue(@lapserial comboBox12.Text); com.Parameters.AddWithValue(@assetnumber comboBox11.Text); com.Parameters.AddWithValue(@os comboBox10.Text); com.Parameters.AddWithValue(@winlicense comboBox17.Text); com.Parameters.AddWithValue(@office comboBox9.Text); ; com.Parameters.AddWithValue(@officelicense comboBox16.Text); com.Parameters.AddWithValue(@hddsize comboBox15.Text); com.Parameters.AddWithValue(@processor comboBox14.Text); com.Parameters.AddWithValue(@ram comboBox20.Text); com.Parameters.AddWithValue(@macadress comboBox19.Text); com.Parameters.AddWithValue(@ipadress comboBox18.Text); com.ExecuteNonQuery(); MessageBox.Show(Created Successfully ..); // conn.Close(); // move to end } else { // MessageBox.Show(user already have laptop comboBox8.Text); // not needed now MessageBox.Show(Already Assigned To Another User comboBox12.Text); } conn.Close(); // moved to end so always closed A couple of other points; 1. You might want to consider replacing combobox.Text with combobox.SelectedItem.ToString() throughout the code in case the edit box doesn't contain the selected item. 2. Some of the parameters look like they might not be strings. For example dateassigned looks like it might be a DateTime. If so use dateTimePicker1.Value rather than Text.

mind controll · Answer

dear bro I use the following in button action to insert data in a table and if the value in comboBox8 already exist in the table break and give message  also comboBox12 and give different message  everything Is perfect except checking if the 2 values exist and the message part doesn't work properly plz help if (conn.State == ConnectionState.Closed) { conn.Open(); } com.Connection = conn; sql = SELECT COUNT(*) FROM lapdev WHERE [username] = @username; com.CommandText = sql; com.Parameters.Clear(); com.Parameters.AddWithValue(@username comboBox8.Text); sql = SELECT COUNT(*) FROM lapdev WHERE [lapserial] = @lapserial; com.CommandText = sql; com.Parameters.Clear(); com.Parameters.AddWithValue(@lapserial comboBox21.Text); int numRecords = (int)com.ExecuteScalar(); if (numRecords == 0) { sql = INSERT INTO lapdev([username][fillingcode][dateassigned][branch][department][agency][computername][lapmodel][lapserial][assetnumber][os][winlicense][office][officelicense][hddsize][processor][ram][macadress][ipadress])VALUES(@username@fillingcode@dateassigned@branch@department@agency@computername@lapmodel@lapserial@assetnumber@os@winlicense@office@officelicense@hddsize@processor@ram@macadress@ipadress); com.CommandText = sql; com.Parameters.Clear(); com.Parameters.AddWithValue(@username comboBox8.Text); com.Parameters.AddWithValue(@fillingcode comboBox7.Text); com.Parameters.AddWithValue(@dateassigned dateTimePicker1.Text); com.Parameters.AddWithValue(@branch comboBox2.Text); com.Parameters.AddWithValue(@department comboBox1.Text); com.Parameters.AddWithValue(@agency comboBox3.Text); com.Parameters.AddWithValue(@computername comboBox21.Text); com.Parameters.AddWithValue(@lapmodel comboBox13.Text); com.Parameters.AddWithValue(@lapserial comboBox12.Text); com.Parameters.AddWithValue(@assetnumber comboBox11.Text); com.Parameters.AddWithValue(@os comboBox10.Text); com.Parameters.AddWithValue(@winlicense comboBox17.Text); com.Parameters.AddWithValue(@office comboBox9.Text); ; com.Parameters.AddWithValue(@officelicense comboBox16.Text); com.Parameters.AddWithValue(@hddsize comboBox15.Text); com.Parameters.AddWithValue(@processor comboBox14.Text); com.Parameters.AddWithValue(@ram comboBox20.Text); com.Parameters.AddWithValue(@macadress comboBox19.Text); com.Parameters.AddWithValue(@ipadress comboBox18.Text); com.ExecuteNonQuery(); com.Parameters.Clear(); sql = INSERT INTO lapdevhis([username][fillingcode][dateassigned][branch][department][agency][computername][lapmodel][lapserial][assetnumber][os][winlicense][office][officelicense][hddsize][processor][ram][macadress][ipadress])VALUES(@username@fillingcode@dateassigned@branch@department@agency@computername@lapmodel@lapserial@assetnumber@os@winlicense@office@officelicense@hddsize@processor@ram@macadress@ipadress); com.CommandText = sql; com.Parameters.Clear(); com.Parameters.AddWithValue(@username comboBox8.Text); com.Parameters.AddWithValue(@fillingcode comboBox7.Text); com.Parameters.AddWithValue(@dateassigned dateTimePicker1.Text); com.Parameters.AddWithValue(@branch comboBox2.Text); com.Parameters.AddWithValue(@department comboBox1.Text); com.Parameters.AddWithValue(@agency comboBox3.Text); com.Parameters.AddWithValue(@computername comboBox21.Text); com.Parameters.AddWithValue(@lapmodel comboBox13.Text); com.Parameters.AddWithValue(@lapserial comboBox12.Text); com.Parameters.AddWithValue(@assetnumber comboBox11.Text); com.Parameters.AddWithValue(@os comboBox10.Text); com.Parameters.AddWithValue(@winlicense comboBox17.Text); com.Parameters.AddWithValue(@office comboBox9.Text); ; com.Parameters.AddWithValue(@officelicense comboBox16.Text); com.Parameters.AddWithValue(@hddsize comboBox15.Text); com.Parameters.AddWithValue(@processor comboBox14.Text); com.Parameters.AddWithValue(@ram comboBox20.Text); com.Parameters.AddWithValue(@macadress comboBox19.Text); com.Parameters.AddWithValue(@ipadress comboBox18.Text); com.ExecuteNonQuery(); MessageBox.Show(Created Successfully ..); conn.Close(); } else { MessageBox.Show(user already have laptop comboBox8.Text); MessageBox.Show(Already Assigned To Another User comboBox12.Text); }

mind controll · Answer

working perfectly as it should thanks bro

Vulpes · Answer

As well as the Enter and Leave events for the DGV try handling the Click event for the form itself on the following lines: private void Form1_Click(object sender EventArgs e) { if (this.ActiveControl == dataGridView1) { button1.Enabled = false; // etc textBox1.Focus(); // set focus to some other non-button control } }

mind controll · Answer

dear bro following ur instructions was correct but the buttons get disabled only when I click on another control such as clicking inside a textbox i need to make the buttons to be disabled when the user click on anywhere on the form other than the selected row in the gridview thanks

Vulpes · Answer

I'd handle the datagridview's Enter and Leave events which fire when the input focus enters or leaves the DGV respectively. In the Enter event handler you can enable the buttons and in the Leave event handler you can disable them.

mind controll · Answer

I have set the buttons to be enabled only when I click on a row in the gridview and it works perfectly but when I click on anything else outside the gridview the row still focused and the buttons are enabled still how can I eable the buttons only when I click on the row and when I click on anything other than the row in the gridview it loses focus and disable the buttons again until I clock on a row in the gridview again

Vulpes · Answer

Well the only columns which are obviously derived from a listview are branch and lapserial though both are being updated from lvi.SubItems[4].Text. So on the face of it it's just a matter of replacing this expression with not available for whichever one is selected. If that's what you're already doing then in what way is it not working?

mind controll · Answer

hey bro I used this code to update only the field that I select in a row showed on the listview with a specific word ( not available ) but it didn't work please help string sql = INSERT INTO usertablehis([username][fillingcode][dateassigned][branch][department][agency][computername][lapmodel][lapserial][assetnumber][os][winlicense][office][officelicense][hddsize][processor][ram][macadress][ipadress]) VALUES(@username@fillingcode@dateassigned@branch@department@agency @computername@lapmodel@lapserial@assetnumber@os@winlicense@office @officelicense@hddsize@processor@ram@macadress@ipadress); cm.CommandText = sql; cm.Parameters.Clear(); cm.Parameters.AddWithValue(@username username); cm.Parameters.AddWithValue(@originalusername textBox1.Text); cm.Parameters.AddWithValue(@fillingcode fillingcode); cm.Parameters.AddWithValue(@dateassigned DateTime.Today); cm.Parameters.AddWithValue(@branch lvi.SubItems[4].Text); cm.Parameters.AddWithValue(@department department); cm.Parameters.AddWithValue(@agency agency); cm.Parameters.AddWithValue(@computername computername); cm.Parameters.AddWithValue(@lapmodel lapmodel); cm.Parameters.AddWithValue(@lapserial lvi.SubItems[4].Text); cm.Parameters.AddWithValue(@assetnumber assetnumber); cm.Parameters.AddWithValue(@os os); cm.Parameters.AddWithValue(@winlicense winlicense); cm.Parameters.AddWithValue(@office office); ; cm.Parameters.AddWithValue(@officelicense officelicense); cm.Parameters.AddWithValue(@hddsize hddsize); cm.Parameters.AddWithValue(@processor processor); cm.Parameters.AddWithValue(@ram ram); cm.Parameters.AddWithValue(@macadress macadress); cm.Parameters.AddWithValue(@ipadress ipadress); cm.ExecuteNonQuery();

mind controll · Answer

thanks bro working fine u are the best

Vulpes · Answer

You didn't say what the error was but the first thing that strikes me is that the syntax of your INSERT command is wrong. It should be: string sql = INSERT INTO usertablehis([username][fillingcode][dateassigned][branch][department][agency][computername][lapmodel][lapserial][assetnumber][os][winlicense][office][officelicense][hddsize][processor][ram][macadress][ipadress]) VALUES(@username@fillingcode@dateassigned@branch@department@agency @computername@lapmodel@lapserial@assetnumber@os@winlicense@office @officelicense@hddsize@processor@ram@macadress@ipadress);

mind controll · Answer

thanks a million bro the first step and the second step are working perfectly now the third step which is inserting the row after updated with the second step gives me error u can say it's the same like step one which is duplicating the row before update but this step is duplicating it in another identical table after update : string sql = INSERT INTO usertablehis SET [username]=@username [fillingcode]=@fillingcode [dateassigned]=@dateassigned [branch]=@branch [department]=@department [agency]=@agency [computername]=@computername [lapmodel]=@lapmodel [lapserial]=@lapserial [assetnumber]=@assetnumber [os]=@os [winlicense]=@winlicense [office]=@office [officelicense]=@officelicense [hddsize]=@hddsize [processor]=@processor [ram]=@ram [macadress]=@macadress [ipadress]=@ipadress WHERE [username]=@originalusername; cm.CommandText = sql; cm.Parameters.Clear(); cm.Parameters.AddWithValue(@username username); cm.Parameters.AddWithValue(@originalusername textBox1.Text); cm.Parameters.AddWithValue(@fillingcode fillingcode); cm.Parameters.AddWithValue(@dateassigned DateTime.Today); cm.Parameters.AddWithValue(@branch lvi.SubItems[4].Text); cm.Parameters.AddWithValue(@department department); cm.Parameters.AddWithValue(@agency agency); cm.Parameters.AddWithValue(@computername computername); cm.Parameters.AddWithValue(@lapmodel lapmodel); cm.Parameters.AddWithValue(@lapserial lvi.SubItems[4].Text); cm.Parameters.AddWithValue(@assetnumber assetnumber); cm.Parameters.AddWithValue(@os os); cm.Parameters.AddWithValue(@winlicense winlicense); cm.Parameters.AddWithValue(@office office); ; cm.Parameters.AddWithValue(@officelicense officelicense); cm.Parameters.AddWithValue(@hddsize hddsize); cm.Parameters.AddWithValue(@processor processor); cm.Parameters.AddWithValue(@ram ram); cm.Parameters.AddWithValue(@macadress macadress); cm.Parameters.AddWithValue(@ipadress ipadress); cm.ExecuteNonQuery();

mind controll · Answer

yes I want the duplication before update I will tell u about the steps of the form so u can see the whole image : 1- the user inserts the username in textbox3 and click search button 2- the result of the search appears in listview3 and until now button11 is disabled 3- when user click on a row in listview3 the button11 be enabled 4- when user clicks button11 and this is the part that I'm asking about  button11 should execute 3 steps first  duplicate the selected row in listview3 in the table usertable then update one of the rows and leave the other one not changed then insert the updated row in the identical table which is usertablehis I guess the problem is here : ListViewItem lvi = listView1.SelectedItems[0]; string username = Stock ; string fillingcode = Stock ; string dateassigned = Stock ; string branch = Stock ; string department = Stock ; string agency = Stock ; string computername = Stock ; string lapmodel = lvi.Text; string lapserial = lvi.Text; string assetnumber = lvi.Text; string os = Stock ; string winlicense = Stock ; string office = Stock ; string officelicense = Stock ; string hddsize = Stock ; string processor = Stock ; string ram = Stock ; string macadress = Stock ; string ipadress = Stock ; or when the update procedure update the 2 rows  the one that was duplicated before update and the original one that needs to be updated alone I hope this gets to u clear bro thanks for your patience

Vulpes · Answer

Sorry I'm not following this at all :( You say that you want the row duplicating before it's updated. But you haven't updated anything at the point where I suggested calling cm.ExecuteNonQuery again - you've just inserted a new record?

mind controll · Answer

thanks bro for your answer but the problem is  the first step which is duplication of the row in the same table before update  it duplicate but with the row after update not before the second step which is updating with word ( stock ) works perfect the third step which is inserting the row after update in an identical table works but except mistakes in 3 fields the big deal now is the first step if it is solved the third one will be easy to solve

Vulpes · Answer

Well on the insertion of the duplicate row it appears that no values were being set for those 3 columns in the first insertion so it should suffice just to call cm.ExecuteNonQuery() again: cm.ExecuteNonQuery();// so i want here to insert the row again etc cm.ExecuteNonQuery(); // call this again string sql = UPDATE etc ; On the insertion of the row into the 'usertablethis' table I've just noticed that you're already inserting a row into that table anyway. If you don't want to duplicate this row but just want to change the @dateassigned parameter it should just be a matter of changing this line: cm.Parameters.AddWithValue(@dateassigned dateassigned); to this: cm.Parameters.AddWithValue(@dateassigned DateTime.Today ); If you do want to duplicate the row after making the change then after the first cm.ExecuteNonQuery() you'll need: cm.Parameters[@dateassigned].Value = DateTime.Today; cm.ExecuteNonQuery(); // call it again

mind controll · Answer

1- no there's no priary keys .. no unique 2- the values are : @lapmodel - @lapserial - @assetnumber 3- yes bro the table exists and it's name is usertablehis

Vulpes · Answer

Some preliminary questions here: 1. Regarding the duplicate row are there any columns which are either primary keys or otherwise unique? 2. Which 3 columns do you want to be empty? 3. Am I right in thinking that the identical table has already been created and so doesn't need to be generated in code?

mind controll · Answer

heeey bro here is my code I wrote comments in the code about what I want it to do to make it easier for u using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.SqlClient; namespace Inventory_Manager_Pro { public partial class Unassign : Form { public SqlConnection cn = new SqlConnection ( Data Source=10.10.1.23;Initial Catalog=MYDB;Persist Security Info=True;User ID=sa;Password=farespila010A@;Encrypt=False ); SqlCommand cm = new SqlCommand (); public Unassign() { InitializeComponent(); } private void populate() { listView1.Items.Clear(); if (textBox1.Text ==  ) cm = new SqlCommand ( SELECT * FROM usertable  cn); else cm = new SqlCommand ( SELECT * FROM usertable WHERE username=' + textBox1.Text + '  cn); try { SqlDataReader dr = cm.ExecuteReader(); while (dr.Read()) { ListViewItem it = new ListViewItem (dr[ username ].ToString()); it.SubItems.Add(dr[ fillingcode ].ToString()); it.SubItems.Add(dr[ dateassigned ].ToString()); it.SubItems.Add(dr[ branch ].ToString()); it.SubItems.Add(dr[ department ].ToString()); it.SubItems.Add(dr[ agency ].ToString()); it.SubItems.Add(dr[ computername ].ToString()); it.SubItems.Add(dr[ lapmodel ].ToString()); it.SubItems.Add(dr[ lapserial ].ToString()); it.SubItems.Add(dr[ assetnumber ].ToString()); it.SubItems.Add(dr[ os ].ToString()); it.SubItems.Add(dr[ winlicense ].ToString()); it.SubItems.Add(dr[ office ].ToString()); it.SubItems.Add(dr[ officelicense ].ToString()); it.SubItems.Add(dr[ hddsize ].ToString()); it.SubItems.Add(dr[ processor ].ToString()); it.SubItems.Add(dr[ ram ].ToString()); it.SubItems.Add(dr[ macadress ].ToString()); it.SubItems.Add(dr[ ipadress ].ToString()); listView1.Items.Add(it); } dr.Close(); dr.Dispose(); } catch ( Exception ex) { MessageBox .Show(ex.Message Application .ProductName MessageBoxButtons .OK MessageBoxIcon .Error); } } private void Unassign_Shown( object sender EventArgs e) { try { cn.Open(); } catch ( SqlException ex) { MessageBox .Show(ex.Message Application .ProductName MessageBoxButtons .OK MessageBoxIcon .Error); Application .ExitThread(); } } private void listView1_SelectedIndexChanged( object sender EventArgs e) { if (listView1.SelectedItems.Count > 0) { button11.Enabled = true ; } else { button11.Enabled = false ; } } private void button11_Click( object sender EventArgs e) { if (listView1.SelectedItems.Count > 0) { ListViewItem lvi = listView1.SelectedItems[0]; string username = Stock ; string fillingcode = Stock ; string dateassigned = Stock ; string branch = Stock ; string department = Stock ; string agency = Stock ; string computername = Stock ; string lapmodel = lvi.Text; string lapserial = lvi.Text; string assetnumber = lvi.Text; string os = Stock ; string winlicense = Stock ; string office = Stock ; string officelicense = Stock ; string hddsize = Stock ; string processor = Stock ; string ram = Stock ; string macadress = Stock ; string ipadress = Stock ; if (cn.State == ConnectionState .Closed) cn.Open(); cm = new SqlCommand (); cm.Connection = cn; string sql = INSERT INTO usertable([username][fillingcode][dateassigned][branch][department][agency][computername][lapmodel][lapserial][assetnumber][os][winlicense][office][officelicense][hddsize][processor][ram][macadress][ipadress])VALUES(@username@fillingcode@dateassigned@branch@department@agency@computername@os@winlicense@office@officelicense@hddsize@processor@ram@macadress@ipadress) ; cm.CommandText = sql; cm.Parameters.Clear(); cm.Parameters.AddWithValue( @username  lvi.Text); cm.Parameters.AddWithValue( @fillingcode  lvi.Text); cm.Parameters.AddWithValue( @dateassigned  lvi.Text); cm.Parameters.AddWithValue( @branch  lvi.Text); cm.Parameters.AddWithValue( @department  lvi.Text); cm.Parameters.AddWithValue( @agency  lvi.Text); cm.Parameters.AddWithValue( @computername  lvi.Text); cm.Parameters.AddWithValue( @os  lvi.Text); cm.Parameters.AddWithValue( @winlicense  lvi.Text); cm.Parameters.AddWithValue( @office  lvi.Text); ; cm.Parameters.AddWithValue( @officelicense  lvi.Text); cm.Parameters.AddWithValue( @hddsize  lvi.Text); cm.Parameters.AddWithValue( @processor  lvi.Text); cm.Parameters.AddWithValue( @ram  lvi.Text); cm.Parameters.AddWithValue( @macadress  lvi.Text); cm.Parameters.AddWithValue( @ipadress  lvi.Text); cm.ExecuteNonQuery(); // so i want here to insert the row again like duplicating it before updating the row and this duplicating will be without 3 values will keep them empty string sql = UPDATE usertable SET [username]=@username [fillingcode]=@fillingcode [dateassigned]=@dateassigned [branch]=@branch [department]=@department [agency]=@agency [computername]=@computername [os]=@os [winlicense]=@winlicense [office]=@office [officelicense]=@officelicense [hddsize]=@hddsize [processor]=@processor [ram]=@ram [macadress]=@macadress [ipadress]=@ipadress WHERE [username]=@originalusername ; cm.CommandText = sql; cm.Parameters.Clear(); cm.Parameters.AddWithValue( @username  username); cm.Parameters.AddWithValue( @originalusername  textBox1.Text); cm.Parameters.AddWithValue( @fillingcode  fillingcode); cm.Parameters.AddWithValue( @dateassigned  dateassigned); cm.Parameters.AddWithValue( @branch  branch); cm.Parameters.AddWithValue( @department  department); cm.Parameters.AddWithValue( @agency  agency); cm.Parameters.AddWithValue( @computername  computername); cm.Parameters.AddWithValue( @os  os); cm.Parameters.AddWithValue( @winlicense  winlicense); cm.Parameters.AddWithValue( @office  office); ; cm.Parameters.AddWithValue( @officelicense  officelicense); cm.Parameters.AddWithValue( @hddsize  hddsize); cm.Parameters.AddWithValue( @processor  processor); cm.Parameters.AddWithValue( @ram  ram); cm.Parameters.AddWithValue( @macadress  macadress); cm.Parameters.AddWithValue( @ipadress  ipadress); cm.ExecuteNonQuery(); // this is the update with word ( not avaliable ) that is working perfectlly after u helped me with string sql = INSERT INTO usertablehis SET [username]=@username [fillingcode]=@fillingcode [dateassigned]=@dateassigned [branch]=@branch [department]=@department [agency]=@agency [computername]=@computername [os]=@os [winlicense]=@winlicense [office]=@office [officelicense]=@officelicense [hddsize]=@hddsize [processor]=@processor [ram]=@ram [macadress]=@macadress [ipadress]=@ipadress WHERE [username]=@originalusername ; cm.CommandText = sql; cm.Parameters.Clear(); cm.Parameters.AddWithValue( @username  username); cm.Parameters.AddWithValue( @originalusername  textBox1.Text); cm.Parameters.AddWithValue( @fillingcode  fillingcode); cm.Parameters.AddWithValue( @dateassigned  dateassigned); cm.Parameters.AddWithValue( @branch  branch); cm.Parameters.AddWithValue( @department  department); cm.Parameters.AddWithValue( @agency  agency); cm.Parameters.AddWithValue( @computername  computername); cm.Parameters.AddWithValue( @os  os); cm.Parameters.AddWithValue( @winlicense  winlicense); cm.Parameters.AddWithValue( @office  office); ; cm.Parameters.AddWithValue( @officelicense  officelicense); cm.Parameters.AddWithValue( @hddsize  hddsize); cm.Parameters.AddWithValue( @processor  processor); cm.Parameters.AddWithValue( @ram  ram); cm.Parameters.AddWithValue( @macadress  macadress); cm.Parameters.AddWithValue( @ipadress  ipadress); cm.ExecuteNonQuery(); //here i want to insert the row after it get updated with the word ( not avaliable ) to an identical table with the same columns names but change the value of @dateassigned that will be added to that identical table to today date try { cm.ExecuteNonQuery(); MessageBox .Show( Updated Successfully .. ); } catch ( Exception ex) { MessageBox .Show(ex.Message Application .ProductName MessageBoxButtons .OK MessageBoxIcon .Error); } finally { cm.Dispose(); listView1.Items.Clear(); textBox1.Clear(); cn.Close(); } } else { MessageBox .Show( No row has been selected ); } } } }

Vulpes · Answer

Can you post the latest version of your code please.

mind controll · Answer

where are u bro ?

mind controll · Answer

I have another problem instead of only updating fields in listview1 with word ( not available ) I want to first insert the row fields again with the same value in the same table ( usertable ) like duplicating it before it gets updated later with the word ( not available ) and last  insert the updated row in an identical table with same columns names called ( usertablehis ) please help

Vulpes · Answer

I usually get there in the end ;)

mind controll · Answer

thank you a million for your help and support u been a good mentor :)

Vulpes · Answer

I notice that you've got all 3 of them set to lvi.Text. If you don't want them updating then simply exclude them altogether from the UPDATE command: string sql = UPDATE usertable SET [username]=@username [fillingcode]=@fillingcode [dateassigned]=@dateassigned [branch]=@branch [department]=@department [agency]=@agency [computername]=@computername [os]=@os [winlicense]=@winlicense [office]=@office [officelicense]=@officelicense [hddsize]=@hddsize [processor]=@processor [ram]=@ram [macadress]=@macadress [ipadress]=@ipadress WHERE [username]=@originalusername;

mind controll · Answer

it was almost working but it updated the 3 fields of the row that I wanted to remain the same to the username that the user entered in textbox1

mind controll · Answer

my table doesn't have a primary key i will tell u how my app works the user enters the username in textbox1 and clicks a search button and the matched result appears in listview1 and user selects the row in result then button11 set to enabled so far i could make it right now when user clicks button11 should change all the fields to not avaliable except 3 fields stay the same lapmodel lapserial assetnumber what is my mistake if u plz

Vulpes · Answer

Just noticed that you're trying to update a record whose username column is not available' (i.e. the WHERE clause) which presumably doesn't exist to start with and so the record is not therefore getting updated. What's your primary key here?

mind controll · Answer

I changed the typo mistake and I got the message box and no errors but it didn't change anything in the fields they still the same and I checked the table on the sql server and still the same

Vulpes · Answer

It looks like the error is caused by a typo: in the UPDATE command 'ipdaress 'should be 'ipadress'. string sql = UPDATE usertable SET [username]=@username [fillingcode]=@fillingcode [dateassigned]=@dateassigned [branch]=@branch [department]=@department [agency]=@agency [computername]=@computername [lapmodel]=@lapmodel [lapserial]=@lapserial [assetnumber]=@assetnumber [os]=@os [winlicense]=@winlicense [office]=@office [officelicense]=@officelicense [hddsize]=@hddsize [processor]=@processor [ram]=@ram [macadress]=@macadress [ipadress]=@ipadress WHERE [username]=@username;

mind controll · Answer

well .. the column is nvarchar(50) so i will not cause any more brain headache for you .. i been using the code u give me on a dummy app and table so that i can know how to do it the error message was about @ipdaress but here's my whole form code hope u can help using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.SqlClient; namespace usersapp { public partial class change : Form { public SqlConnection cn = new SqlConnection(Data Source=10.10.1.23;Initial Catalog=userDB;Persist Security Info=True;User ID=sa;Password=123456;Encrypt=False); SqlCommand cm = new SqlCommand(); public change() { InitializeComponent(); } private void populate() { listView1.Items.Clear(); if(textBox1.Text == ) cm = new SqlCommand(SELECT * FROM usertable cn); else cm = new SqlCommand(SELECT * FROM usertable WHERE username=' + textBox1.Text + ' cn); try { SqlDataReader dr = cm.ExecuteReader(); while (dr.Read()) { ListViewItem it = new ListViewItem(dr[username].ToString()); it.SubItems.Add(dr[fillingcode].ToString()); it.SubItems.Add(dr[dateassigned].ToString()); it.SubItems.Add(dr[branch].ToString()); it.SubItems.Add(dr[department].ToString()); it.SubItems.Add(dr[agency].ToString()); it.SubItems.Add(dr[computername].ToString()); it.SubItems.Add(dr[lapmodel].ToString()); it.SubItems.Add(dr[lapserial].ToString()); it.SubItems.Add(dr[assetnumber].ToString()); it.SubItems.Add(dr[os].ToString()); it.SubItems.Add(dr[winlicense].ToString()); it.SubItems.Add(dr[office].ToString()); it.SubItems.Add(dr[officelicense].ToString()); it.SubItems.Add(dr[hddsize].ToString()); it.SubItems.Add(dr[processor].ToString()); it.SubItems.Add(dr[ram].ToString()); it.SubItems.Add(dr[macadress].ToString()); it.SubItems.Add(dr[ipadress].ToString()); listView1.Items.Add(it); } dr.Close(); dr.Dispose(); } catch (Exception ex) { MessageBox.Show(ex.Message Application.ProductName MessageBoxButtons.OK MessageBoxIcon.Error); } //end laptop private void listView1_SelectedIndexChanged(object sender EventArgs e) { if (listView1.SelectedItems.Count > 0) { button1.Enabled = true; } else { button1.Enabled = false ; } } private void button11_Click(object sender EventArgs e) { if (listView1.SelectedItems.Count > 0) { ListViewItem lvi = listView1.SelectedItems[0]; string username = not available; string fillingcode = not available; string dateassigned = not available; string branch = not available; string department = not available; string agency = not available; string computername = not available; string lapmodel = lvi.Text; string lapserial = lvi.Text; string assetnumber = lvi.Text; string os = not available; string winlicense = not available; string office = not available; string officelicense = not available; string hddsize = not available; string processor = not available; string ram = not available; string macadress = not available; string ipadress = not available; if (cn.State == ConnectionState.Closed) cn.Open(); cm = new SqlCommand(); cm.Connection = cn; string sql = UPDATE usertable SET [username]=@username [fillingcode]=@fillingcode [dateassigned]=@dateassigned [branch]=@branch [department]=@department [agency]=@agency [computername]=@computername [lapmodel]=@lapmodel [lapserial]=@lapserial [assetnumber]=@assetnumber [os]=@os [winlicense]=@winlicense [office]=@office [officelicense]=@officelicense [hddsize]=@hddsize [processor]=@processor [ram]=@ram [macadress]=@macadress [ipdaress]=@ipdaress WHERE [username]=@username; cm.CommandText = sql; cm.Parameters.Clear(); cm.Parameters.AddWithValue(@username username); cm.Parameters.AddWithValue(@fillingcode fillingcode); cm.Parameters.AddWithValue(@dateassigned dateassigned); cm.Parameters.AddWithValue(@branch branch); cm.Parameters.AddWithValue(@department department); cm.Parameters.AddWithValue(@agency agency); cm.Parameters.AddWithValue(@computername computername); cm.Parameters.AddWithValue(@lapmodel lapmodel); cm.Parameters.AddWithValue(@lapserial lapserial); cm.Parameters.AddWithValue(@assetnumber assetnumber); cm.Parameters.AddWithValue(@os os); cm.Parameters.AddWithValue(@winlicense winlicense); cm.Parameters.AddWithValue(@office office); ; cm.Parameters.AddWithValue(@officelicense officelicense); cm.Parameters.AddWithValue(@hddsize hddsize); cm.Parameters.AddWithValue(@processor processor); cm.Parameters.AddWithValue(@ram ram); cm.Parameters.AddWithValue(@macadress macadress); cm.Parameters.AddWithValue(@ipadress ipadress); try { cm.ExecuteNonQuery(); MessageBox.Show(Updated Successfully ..); } catch (Exception ex) { MessageBox.Show(ex.Message Application.ProductName MessageBoxButtons.OK MessageBoxIcon.Error); } finally { cm.Dispose(); cn.Close(); } } else { MessageBox.Show(No row has been selected); } } } }

Vulpes · Answer

That error almost certainly means that the 'id' column isn't a varchar or other string type in the database. If it's an int then you won't be able to set it to not available' but will need to set it instead to some impossible value such as 0 or -1: int id = 0; // say

mind controll · Answer

I get error message : you must declare the scalar variable @id

Vulpes · Answer

Ah I see what you mean now :) Here's some revised code: private void button2_Click(object sender EventArgs e { if (listView1.SelectedItems.Count > 0) { ListViewItem lvi = listView1.SelectedItems[0]; string name = lvi.Text; string id = not available; string position = not available; if (cn.State == ConnectionState.Closed) cn.Open(); cm = new SqlCommand(); cm.Connection = cn; string sql = UPDATE someTable SET [id]=@id [position]=@position WHERE [name]=@name; cm.CommandText = sql; cm.Parameters.Clear(); cm.Parameters.AddWithValue(@name name); cm.Parameters.AddWithValue(@id id); cm.Parameters.AddWithValue(@position position); try { cm.ExecuteNonQuery(); MessageBox.Show(Updated Successfully ..); } catch (Exception ex) { MessageBox.Show(ex.Message Application.ProductName MessageBoxButtons.OK MessageBoxIcon.Error); } finally { cm.Dispose(); cn.Close(); } } else { MessageBox.Show(No row has been selected); } }

mind controll · Answer

no my friend what i'm saying that the other codes u have seen in my other posts are on another form and this one is different u assumed about conn and com .. so i tried to show u what i use i'm showing u my code and asking about the button click event how it will look like according to this code i showed u

mind controll · Answer

no this code was in another form in this form i use this : for the form level i use this : public partial class Unassign : Form { public SqlConnection cn = new SqlConnection(Data Source=10.10.1.23;Initial Catalog=userDB;Persist Security Info=True;User ID=sa;Password=123456;Encrypt=False); SqlCommand cm = new SqlCommand(); for the listview i use this : private void populate() { listView1.Items.Clear(); if(textBox1.Text == ) cm = new SqlCommand(SELECT * FROM usertable cn); else cm = new SqlCommand(SELECT * FROM usertable WHERE name=' + textBox1.Text + ' cn); try { SqlDataReader dr = cm.ExecuteReader(); while (dr.Read()) { ListViewItem it = new ListViewItem(dr[name].ToString()); it.SubItems.Add(dr[id].ToString()); it.SubItems.Add(dr[phone].ToString()); it.SubItems.Add(dr[position].ToString()); listView1.Items.Add(it); } dr.Close(); dr.Dispose(); } catch (Exception ex) { MessageBox.Show(ex.Message Application.ProductName MessageBoxButtons.OK MessageBoxIcon.Error); } how would the button click event will be then sorry and thanks in advance

mind controll · Answer

there's a missing thing the 2 fields id - position .. i want them to be updated both with a specific word : not avaliable when i click a button after selecting the desired row and the other 2 fields stay as they are