//Function to refresh part data from the database
private void dbRefresh() {
lstParts.Items.Clear();
conn.connect();
try {
//MessageBox.Show("Connection success!");
string stm = "SELECT * FROM parts";
MySqlCommand cmd = new MySqlCommand(stm, conn.conn);
conn.rdr = cmd.ExecuteReader();
partlist.Clear();
clearInput();
while (conn.rdr.Read()) {
part temp = new part();
temp.id = conn.rdr.GetInt32(0);
temp.name = conn.rdr.GetString(1);
temp.msrp = conn.rdr.GetDecimal(2);
temp.inv = conn.rdr.GetDecimal(3);
temp.wt = conn.rdr.GetDecimal(4);
temp.desc = conn.rdr.GetString(5);
partlist.Add(temp);
}
displist();
} catch (MySql.Data.MySqlClient.MySqlException ex) {
MessageBox.Show(ex.Message);
}
if (conn != null) {
conn.Close();
}
}
private void btnSearch_Click(object sender, EventArgs e) {
conn.connect();
try {
string stm = "SELECT * FROM parts WHERE LOWER(name) LIKE @name AND LOWER(description) LIKE @desc AND msrp >= @min AND msrp <= @max";
MySqlCommand cmd = new MySqlCommand(stm, conn.conn);
cmd.Parameters.AddWithValue("@name", "%" + txtName.Text.ToLower() + "%");
cmd.Parameters.AddWithValue("@desc", "%" + txtDesc.Text.ToLower() + "%");
decimal max;
if (decimal.TryParse(txtMax.Text, out max)) {
if (max <= 0)
max = 999999999;
} else {
max = 999999999;
}
cmd.Parameters.AddWithValue("@max", max.ToString());
cmd.Parameters.AddWithValue("@min", txtMin.Text);
conn.rdr = cmd.ExecuteReader();
fil.active = true;
fil.ids.Clear();
lstResult.Items.Clear();
while (conn.rdr.Read()) {
int id = conn.rdr.GetInt32(0);
part temp = new part();
temp.id = id;
temp.name = conn.rdr.GetString(1);
temp.msrp = conn.rdr.GetDecimal(2);
temp.inv = conn.rdr.GetDecimal(3);
temp.wt = conn.rdr.GetDecimal(4);
temp.desc = conn.rdr.GetString(5);
fil.ids.Add(id);
lstResult.Items.Add(temp.disp());
}
conn.Close();
} catch (Exception ex){
MessageBox.Show(ex.Message);
}
}