Displaying the values of listview in textboxes

Question

I'm creating an application in Visual Studio 2010 C# and MySQL where the user can add, edit, view an employee. I already done with adding and viewing part. However I'm little confused in editing. I have this listView in my form where it displays all the employee added to the database. What I want is that whenever the user will select an employee and click edit button I want the values saved in the database to show in the corresponding textboxes below the listView. Can someone give me any idea how to do this? Please

Screenshot: enter image description here

Code for listView:

private void getEmployee()
    {
        listViewEmployee.Items.Clear();
        string cmd = "select employee_number, employee_lastname, employee_firstname, employee_middlename, employee_position, employee_datehired from employee";
        DBConn db = new DBConn();
        DataTable tbl = db.retrieveRecord(cmd);
        foreach (DataRow row in tbl.Rows)
        {
            ListViewItem lv = new ListViewItem(row[0].ToString());
            lv.SubItems.Add(row[1].ToString() + ", " + row[2].ToString() + " " + row[3].ToString());
            lv.SubItems.Add(row[4].ToString());
            lv.SubItems.Add(row[5].ToString());
            listViewEmployee.Items.Add(lv);
        }
    }

    private void textBoxSearchEmployee_TextChanged(object sender, EventArgs e)
    {
        string cmd = "SELECT employee_number, employee_lastname, employee_firstname, employee_middlename, employee_position, employee_datehired FROM employee where employee_lastname Like '" + textBoxSearchEmployee.Text + "%'";
        listViewEmployee.Items.Clear();
        DBConn db = new DBConn();
        DataTable tbl = db.retrieveRecord(cmd);
        foreach (DataRow row in tbl.Rows)
        {
            ListViewItem lv = new ListViewItem(row[0].ToString());
            lv.SubItems.Add(row[1].ToString() + ", " + row[2].ToString() + " " + row[3].ToString());
            lv.SubItems.Add(row[4].ToString());
            lv.SubItems.Add(row[5].ToString());
            listViewEmployee.Items.Add(lv);
        }
    }

Provide some relevant code please. Your question is broad. When you say "I already done with adding and viewing part", do you mean you are already able to populate the textboxes above with data? — Alex R., Aug 01 '11 at 02:58
Thanks for the edit. However, how does the `employee` table look like? Does it contain all those info you need? Or is your database normalized enough such that the rest of those info are located in some other table? — Alex R., Aug 01 '11 at 03:20
The table will only display some of important details like Employee Number, Employee Name, Position, and Date Hired. But when I click the edit button I want all the data in the database to be displayed in the textbox. Is this possible? — sean, Aug 01 '11 at 03:28
So where are the _other_ data (e.g. Tax Number, SSS Number, etc.) persisted? In another table? — Alex R., Aug 01 '11 at 03:35
It is only in the database. Should I need to include all the items in the table in order for me to achieve what I want? — sean, Aug 01 '11 at 03:44

user807566 · Answer 1 · 2011-08-01T04:10:58.330

When the user presses the "edit" button...

Retrieve the selected employee
Use a SELECT to get the selected employee's information
Populate the text boxes with the selected employee's information

For example,

String employee = listViewEmployee.Text;
String cmd = "SELECT * FROM employee WHERE employee_lastname='" + employee + "'";
DBConn db = new DBConn();
DataTable tbl = db.retrieveRecord(cmd);
txtLastName.Text = tbl.Rows[0][0];
// ... 
// etc.

Note: It's a bad idea to concatenate values into a SQL query because if the values are malicious, a different query could be executed. For example, if employee had the value of x' OR '1'='1; DROP TABLE employee; -- or something along those lines, then the employee table could be dropped. The way around this is using Stored Procedures or parameterized queries.

@Alex R.: Not really. The employee names come from a listbox whose values are populated by the OP. Nonetheless, it was example code, and that is how he's been making his queries. — user807566, Aug 01 '11 at 04:03

score 2 · Accepted Answer · edited May 23 '17 at 12:11

It seems to me you are able to populate the listView only.

A few pointers:

1) The way you're writing your SQL statement now is prone to SQL Injection. Use parameters in your SQL commands instead of directly concatenating the variables to your query. See this question for an example on how to do it.

2) Depending on where your other relevant data is located (i.e. if your database is normalized), you might have to do a join in your query.

string sqlQuery = "SELECT * FROM employee 
JOIN other_employee_data_table on other_employee_data_table.employeeID = employee.ID
WHERE employee.employee_lastname LIKE @employee_lastname +'%'"

But if your employee table contains all the data, then no need to do a join. Just get all the relevant information from that table.

3) Once you got all the information you need, it's just a matter of reading those data and assigning them to their respective fields.

Pseudo Code:

using (MySqlConnection connection = new MySqlConnection(connectionString))
{
   connection.Open(); 
   MySqlCommand command = connection.CreateCommand();
   command.CommandText = sqlQuery;
   command.CommandType = System.Data.CommandType.Text;
   // add parameters in this line
   using (MySqlDataReader reader = command.ExecuteReader())
   {  
      while (reader.Read()) 
      {     
         // iterate in each row
         for (int i = 0; i < reader.FieldCount; i++)
         {
            // iterate each column using reader.GetValue(i)
         }
      }
   }
}

Displaying the values of listview in textboxes

2 Answers2