DataGrid - Custom values derived from database values

I have created and loaded a datagrid with values from a database. I have two columns that are directly from the database and one column that is based off a value in the database.
The column derived from the database value essentially is based on the following pseudocode
If Issued_Quantity > 0, field value = Complete ("C")
Else, field value = Incomplete ("I")
Is there a way to incorporate this into the datagrid while still pulling two columns directly from the database?
My current code is as follows:


Dim dr As SqlCeDataReader
Dim dt As DataTable
Dim cmd As SqlCeCommand = New SqlCeCommand(sql, cn)
 
cn.Open()
dr = cmd.ExecuteReader
 
Using dr
dt = New DataTable
dt.Load(dr)
cn.Close()
End Using
 
Return dt
 
dgPickIssue.DataSource = dt

Where the SQL statement is

 

SELECT pi.Requisition_Number as Requisition, i.Location as Location FROM Pick 
as pi INNER JOIN Item as i ON pi.Item_Key = i.Item_Key

The desired end result would look something like this:
Requisition Number | Location | Status
1230957389 | Bay 2 | I
4357892346 | Bay 6 | C
The values in the database for status would be '0' for the first record and a number like '25' for the second.
Thank you in advance for your help!

Dim dr As SqlCeDataReader Dim dt As DataTable Dim cmd As SqlCeCommand = New SqlCeCommand(sql, cn) cn.Open() dr = cmd.ExecuteReader Using dr dt = New DataTable dt.Load(dr) cn.Close() End Using Dim r As DataRow dt.Columns.Add("Status") dt.Columns(3).ReadOnly = False For i = 0 To dt.Rows.Count - 1 r = dt.Rows.Item(i) MsgBox(r.Item(2).ToString) If CInt(r.Item(2)) > 0 Then r.Item(3) = "C" Else r.Item(3) = "I" End If Next dt.Columns.Remove("Issued_Qty") Return dt

1 Reply

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

Laura Ritchey16y ago

I figured out how to do what I asked above. I'm not sure if it is the most efficient way, but it works. I am pulling in all three columns (Requisition_Number, Location and Issued_Qty) into the datagrid. Before the load method is finished, I am adding another column. I calculate the value of that fourth column based off the Issued_Qty, and then delete the Issued_Qty column. The code I used is below: