SQL Question

Display Another Data from Other Field if the Data is Empty

Hello Everyone Good Afternoon,

I have 5 Fields in MySQL and there are:


I cant explain it using proper words but I hope you will understand it by letting me show to you the table.

|12345678|Ketchup |12.00 |0.00 |PC |1.00 |12.00|
|67891111|Soy Sauce |0.00 |12.00 |PC |1.00 |12.00|

Now you see it, I hope you can understand it now.
Here is my Question, I have a Code that will transfer the Data above into a Datagridview but theres a twist and that twist is the Question.

Here it is:

How can I Display the Data like this; If ApprovedQty is 0.00 then the Data in the OrderQty will display but if ApprovedQty is not 0.00 regardless of what data is in the OrderQty the ApprovedQty will be Show.

Something like this.

Based on the Table above

|12345678|Ketchup |12.00 |PC |1.00 |12.00|
|67891111|Soy Sauce |12.00 |PC |1.00 |12.00|

Here is my code but it only shows the Approved Qty

Private Sub loadfinalpurch1()
Dim con1 As MySqlConnection = New MySqlConnection("server=localhost;userid=root;password=admin1950;database=inventory")
Dim sql1 As MySqlCommand = New MySqlCommand("Select ItemCode,Description,ApprovedQty,UoM,UnitPrice,Total from final_purch where PRnumber = '" & Label2.Text & "' and Added is NULL OR Added ='';", con1)
Dim ds1 As DataSet = New DataSet
Dim adapter1 As MySqlDataAdapter = New MySqlDataAdapter
adapter1.SelectCommand = sql1
adapter1.Fill(ds1, "MyTable")
DataGridView1.DataSource = ds1.Tables(0)
With DataGridView1
.RowHeadersVisible = False
.Columns(0).HeaderCell.Value = "Item Code"
.Columns(1).HeaderCell.Value = "Description"
.Columns(2).HeaderCell.Value = "Order Qty"
.Columns(3).HeaderCell.Value = "UOM"
.Columns(4).HeaderCell.Value = "Unit Price"
.Columns(5).HeaderCell.Value = "Total Amount"
End With
DataGridView1.Columns.Item(0).Width = 90
DataGridView1.Columns.Item(1).Width = 200
DataGridView1.Columns.Item(2).Width = 90
DataGridView1.Columns.Item(3).Width = 90
DataGridView1.Columns.Item(4).Width = 100
DataGridView1.Columns.Item(5).Width = 100
DataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomCenter
Dim checkBoxColumn As New DataGridViewCheckBoxColumn()
checkBoxColumn.HeaderText = "Tag"
checkBoxColumn.Width = 30
checkBoxColumn.Name = "checkBoxColumn"
DataGridView1.Columns.Insert(0, checkBoxColumn)

End Sub

I hope you get my point.Do I have something to do with the MYSQL Command? with My Code? I don`t know how to achieve this.

TYSM For Future Help

Answer Source

You don't do that condition anywhere, you could do it on your code or directly changing your query:

Select ItemCode,
IF(ApprovedQty = 0, OrderQty, ApprovedQty) as myApprovedQty,
Total from final_purch where PRnumber = '" & Label2.Text & "' and Added is NULL OR  Added ='';", con1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download