Struggling to find an answer on this one. I have a Windows app, on which an operator scans a barcode and the app returns some information based on the barcode from an SQL Database. This then adds a row to a DataGridView control.
I’ve been asked to make a change – On the same form is a combo box control with 4 statically configured values. What I need to have happen is when the operator scans the barcode and the new row is added to the DGV control, the last column in the DGV needs to be filled with the selected item of the CB control.
Image A – How the DGV looks after scanning a barcode presently:
Image B – What I’m wanting to achieve – Notice the TestPanel column text matches the Panel combo box contents:
Most of the answers I found when searching suggested adding the data to the datatable before the DGV is filled – I’ve tried a few suggestions across the internet with no joy. Not sure if that’s just the wrong solution or I’m doing something wrong.
I also gave the below a try but thinking it does not work because the DGV is populated via a Datatable.
For Each dr As DataRow In GetResults_Meth().Rows
dgScanned.Rows.Insert(0, dr.ItemArray)
Dim test As String = cbPanel.SelectedItem
dgScanned.Columns(8).ToString().Replace(" ", test)
Next dr
EDIT: Sorry for the lack of information. There are are 9 columns in the DGV with one hidden, 8 of the coulumns are populated by the SQL query:
Public Sub frmLASend_Load(sender As Object, e As EventArgs) Handles MyBase.Load
dgScanned.ColumnCount = 9
dgScanned.Columns(0).Name = "Request ID"
dgScanned.Columns(1).Name = "GivenName"
dgScanned.Columns(2).Name = "Surname"
dgScanned.Columns(3).Name = "DOB"
dgScanned.Columns(4).Name = "Sex"
dgScanned.Columns(5).Name = "Specimen"
dgScanned.Columns(6).Name = "Referral Date"
dgScanned.Columns(7).Name = "IgnoreMe"
dgScanned.Columns(7).Visible = False
dgScanned.Columns(8).Name = "TestPanel"
cbPanel.Items.Add("Amino Acids")
cbPanel.Items.Add("MACS")
cbPanel.Items.Add("Methylation")
cbPanel.Items.Add("Organic Acids")
cbPanel.SelectedItem = "Amino Acids"
Each barcode scan only produces one result from the database and hence only adds a single row to the DGV. So the operator will scan all barcodes relating to the task which will appear in the DGV.
SQL Query:
Private Function GetResults_Meth() As DataTable
Dim dtResults_Meth As New DataTable
Dim connString As String = "Data Source=NP-SQL03\SQL2019;Initial Catalog=asl;User Id=aslmanager;Password=!_asl1_!"
Using conn As New SqlConnection(connString)
Using cmd As New SqlCommand("
SELECT DISTINCT requests.request_id AS 'Request ID', patients.first_name AS GivenName, patients.surname AS Surname,
concat(patients.day_birth, '/', patients.month_birth, '/', patients.year_birth) AS DOB, patients.sex AS 'Sex',
specimens.specimen AS 'Specimen', referral_details.referral_dt AS 'Referral Date', labs.lab_name
FROM
{ oj (((((((asl.dbo.requests requests INNER JOIN asl.dbo.requests_services requests_services ON
requests.request_id = requests_services.request_id)
INNER JOIN asl.dbo.referral_details referral_details ON
requests.request_id = referral_details.referring_request_id)
INNER JOIN asl.dbo.patients patients ON
requests.patient_id = patients.patient_id)
INNER JOIN asl.dbo.specimens specimens ON
requests_services.specimen_id = specimens.specimen_id)
INNER JOIN asl.dbo.referral_details_services referral_details_services ON
requests_services.request_id = referral_details_services.request_id AND
requests_services.service_id = referral_details_services.service_id)
INNER JOIN asl.dbo.labs labs ON referral_details.to_lab_id = labs.lab_id)
INNER JOIN asl.dbo.services services ON referral_details_services.service_id = services.service_id)
LEFT OUTER JOIN asl.dbo.addresses addresses ON labs.lab_address_id = addresses.address_id}
WHERE
referral_details.referral_dt >= @StartDate AND
referral_details.referral_dt <= @EndDate AND
labs.lab_id = '82' AND
requests.request_id = @LabID AND
services.service_abbrev IN ('SAMe', 'SAHe', 'THF', 'Folinic', '5MTHF', 'GluthR', 'Gluthox')
GROUP BY
requests.request_id, patients.first_name, patients.surname,
patients.month_birth, patients.day_birth, patients.year_birth,
patients.sex, services.service_abbrev, specimens.specimen,
referral_details.referral_dt, requests.collection_dt, labs.lab_name
ORDER BY
labs.lab_name ASC,
requests.request_id ASC
", conn)
cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = dateFrom.Value
cmd.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = DateTo.Value
cmd.Parameters.Add("@LabID", SqlDbType.Int).Value = txtBarcode.Text
cmd.CommandTimeout = 0
Try
conn.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader()
dtResults_Meth.Load(reader)
Process:
If dtResults_Meth.Rows.Count = 0 Then
MessageBox.Show("This Lab ID cannot be added to the manifest because it is not in the referrals table or is not being referred for this panel. Please resolve and try again.", "No Referral", MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
Return dtResults_Meth
End If
Catch Ex As Exception
MessageBox.Show("Unable to connect to SQL Server. Please check your network connection and if required, report the issue to IT." & vbCrLf & vbCrLf & Ex.Message, "No Connection!", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Using
End Using
End Function
Trigger (carriage return after the barcode):
Private Sub txtBarcode_KeyDown(sender As Object, e As KeyEventArgs) Handles txtBarcode.KeyDown
If e.KeyCode = Keys.Enter Then
If txtBarcode.Text.Length < 7 Or txtBarcode.Text.Length > 7 Then
My.Computer.Audio.Play("specierror.wav")
MessageBox.Show("Scanned Lab ID is invalid. Please try again.", "Invalid Lab ID", MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
If cbPanel.SelectedItem = "Methylation" Then
If GetResults_Meth() Is Nothing Then
Else
For Each dr As DataRow In GetResults_Meth().Rows
'Ignore the below. Was tinkering
dgScanned.Rows.Insert(0, dr.ItemArray)
Dim test As String = cbPanel.SelectedItem
dgScanned.Columns(8).ToString().Replace(" ", test)
Next dr
txtBarcode.Text = ""
txtBarcode.Focus()
End If
ElseIf cbPanel.SelectedItem = "Organic Acids" Then
If GetResults_OrgA() Is Nothing Then
Else
For Each dr As DataRow In GetResults_OrgA().Rows
dgScanned.Rows.Insert(0, dr.ItemArray)
Next dr
txtBarcode.Text = ""
txtBarcode.Focus()
End If
ElseIf cbPanel.SelectedItem = "MACS" Then
If GetResults_MACS() Is Nothing Then
Else
For Each dr As DataRow In GetResults_MACS().Rows
dgScanned.Rows.Insert(0, dr.ItemArray)
Next dr
txtBarcode.Text = ""
txtBarcode.Focus()
End If
End If
End If
End If
End Sub
>Solution :
It seems like the crux of the issue is that you’re populating the grid from the DataTable, then you getting some data back from the grid and trying to change, then failing to put the result back in the grid. The obvious solution is to modify the data in the DataTable or even putting the actual data you want in the DataTable in the first place.
I don’t know how that GetResults_Meth is written but one option is to put the ComboBox value directly into your query, so it gets added to the DataTable at the outset, e.g.
Dim sql = "SELECT Column1, @Column2 AS Column2 FROM SomeTable"
'...
myCommand.Parameters.Add("@Column2", SqlDbType.VarChar, 50).Value = myComboBox.Text
The text from the ComboBox will now be in every row of the result set of the query.
The other option is to modify the data in the DataRow first, then add the data from the DataRow to the grid. Going from your own code:
For Each dr As DataRow In GetResults_Meth().Rows
dr(8) = cbPanel.Text
dgScanned.Rows.Insert(0, dr.ItemArray)
Next dr
Based on your images though, it looks like you want column index 7, not 8.