update row with certain text in more than one field sql

mind controll

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

58 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes12y ago
Accepted 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);
Vulpes12y ago
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 controll12y ago
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;
}
Vulpes12y ago
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 controll12y ago
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

}
Vulpes12y ago
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 controll12y ago
it still doesn't fire when the display member changed
combobox1,2,3 show values from table1 column1,2,3

when u select different item from combobox1 the displaymember changes in combobox2,3

I need it to be fired if the display member changes even if the user didn't select it by himself
Vulpes12y ago
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 controll12y ago
no it was modified before .. the user modified one of the 3 values to null and the other 2 aren't null
Vulpes12y ago
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 controll12y ago
any update ?

kindly i'm waiting for your answer
mind controll12y ago

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
Vulpes12y ago
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;
}
}
mind controll12y ago
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
Vulpes12y ago
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 controll12y ago
hey bro

I listview1 and 3 buttons ,, button1,button2 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 controll12y ago
thanks bro

working perfectly
Vulpes12y ago
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 controll12y ago
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 controll12y ago
working perfectly as it should thanks bro
Vulpes12y ago
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 controll12y ago
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
Vulpes12y ago
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 controll12y ago
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
Vulpes12y ago
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 controll12y ago
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 controll12y ago

thanks bro
working fine
u are the best
Vulpes12y ago
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 controll12y ago
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();
Vulpes12y ago
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";
mind controll12y ago
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
Vulpes12y ago
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 controll12y ago
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
Vulpes12y ago
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 controll12y ago
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
Vulpes12y ago
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 controll12y ago
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");

}

}

}

}
Vulpes12y ago
Can you post the latest version of your code please.
mind controll12y ago
where are u bro ?
mind controll12y ago

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
Vulpes12y ago
I usually get there in the end ;)
mind controll12y ago

thank you a million for your help and support

u been a good mentor :)
Vulpes12y ago
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 controll12y ago
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 controll12y ago
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
Vulpes12y ago
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 controll12y ago
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
Vulpes12y ago
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 controll12y ago
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");
}
}

}
}
Vulpes12y ago
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 controll12y ago
I get error message :
you must declare the scalar variable "@id"
Vulpes12y ago
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 controll12y ago

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
Vulpes12y ago
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?
mind controll12y ago
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
Vulpes12y ago
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.
mind controll12y ago
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
Vulpes12y ago
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");
}
}