Situation: SerialNumber has duplicate rows and I need to select the latest records and get what is the Status value (either it PASS or FAIL does not matter).
SerialNumber must be check based on the previous StationNumber. Example: I want to get the Status value of the latest record SerialNumber 197601234512345 in Station 2, so value StationNumber = @Station in the command line below is actually “Station 1”
Scenario: SerialNumber can either be pass or fail in Station 1, then SerialNumber exp:197601234512345 is brought forward to Station 2. In Station 2, need to check what latest Status of that SerialNumber exp:197601234512345 has in the previous StationNumber (Station 1).
Example table in the database db:
| ID | SerialNumber | Date | Time | Date_Time | Status | StationNumber |
|---|---|---|---|---|---|---|
| 213939 | 197601234512344 | 2021-04-28 | 11.53 AM | 2021-04-28 11:53:00 | PASS | Station 1 |
| 213940 | 197601234512345 | 2021-04-28 | 11.54 AM | 2021-04-28 11:54:00 | FAIL | Station 1 |
| 213941 | 197601234512345 | 2021-04-28 | 12.11 PM | 2021-04-28 14:30:00 | FAIL | Station 2 |
| 213942 | 197601234512345 | 2021-04-28 | 12.11 PM | 2021-04-28 14:31:00 | FAIL | Station 2 |
| 213943 | 197601234512344 | 2021-04-28 | 12.00 PM | 2021-04-28 14:35:00 | FAIL | Station 3 |
| 213944 | 197601234512344 | 2021-04-28 | 12.05 PM | 2021-04-28 14:46:00 | FAIL | Station 3 |
| 213945 | 197601234512344 | 2021-04-28 | 12.10 PM | 2021-04-28 14:47:00 | PASS | Station 3 |
Note: All datatype for the columns are VARCHAR(45)
So far, the SQL commands that I have tried has NO Error, but the MySqlreader does NOT Execute the ExecuteReader() which hold the SQL command. How do I know it? By doing the test display label on Label13.Text in Update 1: C# code, At the front-end system it display till Label13.Text = “Check 3”; then direct gives me Label13.Text = “Bad Output”;
Below here are my five tried sql commands. But none of it works hurm =( :
Select Status from db.station where SerialNumber = @SN and StationNumber = @Station and MAX(CAST(Date_Time as DateTime)) or Select Status from db.station where SerialNumber = @SN and StationNumber = @Station and MAX(CAST(Date_Time as DateTime)) Group By Status or Select Top Status from db.station where SerialNumber = @SN and StationNumber = @Station and MAX(CAST(Date_Time as DateTime)) or Select Status from db.station where SerialNumber = @SN and StationNumber = @Station Oder By Status desc limit 1 or Select a.Status from db.station INNER JOIN (Select SerialNumber, Status from db.station where SerialNumber = @SN and StationNumber = @Station and MAX(CAST(Date_Time as DateTime))group by SerialNumber) b on a.Status = b.Status
Update 1: C# code on 2021-04-29
if (textbox1.Text != "")
{
con.Open();
Label13.Text = "Check 1";
//new command
MySqlCommand cmdd = new MySqlCommand("Select SerialNumber, Status from db.station where SerialNumber = @SN and StationNumber = @Station order by CAST(Date_Time as DateTime) desc limit 1 ", con);
Label13.Text = "Check 2";
cmdd.Parameters.AddWithValue("@SN", textbox1.Text);
cmdd.Parameters.AddWithValue("@Station", "Station 1");
Label13.Text = "Check 3";
MySqlDataReader read = cmdd.ExecuteReader();
Label13.Text = "Check 4";
if (read.HasRows)
{
Label13.Text = "Check 5";
while (read.Read())
{
status = read.GetValue(1).ToString();
Label13.Text = status;
if (status == "PASS")
{
con.Close();
PreChecking.Text = " Checked : Serial Number passed in previous Station 1";
PreChecking.ForeColor = Color.Green;
YourText1 = "hi";
////catch WO and CASING SN should be in here
if (textbox1.Text != "")
{
//Auto Capture the Work Order when Entering the serial Number
con.Open();
MySqlCommand cmd = new MySqlCommand("Select WorkOrder from db.workorder where @Text between coalesce (StartRange, @Text) and Coalesce (EndRange, @Text)", con);
cmd.Parameters.AddWithValue("@Text", textbox1.Text);
MySqlDataReader sdr = cmd.ExecuteReader();
//if the serial number is in the work order range, it will display the work order, else display error message
if (sdr.HasRows)
{
while (sdr.Read())
{
TextBox3.Text = sdr.GetValue(0).ToString();
label12.Text = " Checked: Valid W/O for Serial Number";
label12.ForeColor = Color.Green;
DropDownList3.Focus();
Button1.Enabled = true;
Button1.Focus();
//**Close pop-up message as requested by Engineers**//
//Page.ClientScript.RegisterStartupScript(this.GetType(), "popupscript", "alert(' Done checking Board Serial Number and validity W/O ');", true);
}
}
else
{
TextBox3.Text = "";
textbox1.Focus();
label12.Text = "Alert: Invalid W/O for Serial Number";
YourText4 = "Hi";
label12.ForeColor = Color.Red;
Button1.Enabled = false;
Page.ClientScript.RegisterStartupScript(this.GetType(), "popupscript", "alert(' 1 Please aware of Invalid W/O for current scan Serial Number');", true);
}
con.Close();
}
}
else if (status == "FAIL")
{
PreChecking.Text = " Alert: Serial Number not pass in previous Station 1";
PreChecking.ForeColor = Color.Red;
label12.Text = string.Empty;
DropDownList2.Enabled = false;
DropDownList3.Enabled = false;
Button1.Enabled = false;
Page.ClientScript.RegisterStartupScript(this.GetType(), "popupscript", "alert(' Serial NUmber cannot pass this station');", true);
}
}
}
else
{
Label13.Text = "Bad Output";
}
}
Hope anyone can help and spot the mistake or have a solution. Thank you.
Advertisement
Answer
Solution: my situation kinda relate to situation that happen in enter link description here
so, my new SQL command
SELECT a.ID, a.Date_Time, a.Status FROM db.station a, (SELECT MAX(ID) as id FROM db.station WHERE SerialNumber = @SN Group by StationNumber = @Station) b WHERE a.ID = b.id
I test to insert Serial Number 197601234512345 in Station 3 finally, it gets the latest row from the previous Station (Station 2)
| ID | SerialNumber | Date | Time | Date_Time | Status | StationNumber |
|---|---|---|---|---|---|---|
| 213942 | 197601234512345 | 2021-04-28 | 12.11 PM | 2021-04-28 14:31:00 | PASS | Station 2 |
