Take combobox SelectedIndex data and use in SELECT Query - VB.net

Question

I'm a little desperate, hence why I'm here! I'm quite new to programming and have been given an assignment where I need to use a range of SQL queries to generate a simple HTML report table. There is also a user input, with them selecting the ClinicID from the comboBox and clicking a button to generate the report.

Basically, I have a comboBox that I have populated with 'ClinicID', as below. I have also made sure that SelectedIndex is working. I need to somehow use this in the SQL query method which I have also provided below.

Public Class frmReport1
'Set lsData for Clinics table
Dim lsData As List(Of Hashtable)


'On form load
Private Sub frmReport1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    cboClinicID.DropDownStyle = ComboBoxStyle.DropDownList

    'Instantiate new ClinicController object
    Dim cController As ClinicController = New ClinicController

    'Load ClinicID
    lsData = cController.findId()

    For Each clinic In lsData
        cboClinicID.Items.Add(CStr(clinic("ClinicID")))
    Next

End Sub

'Selected Index 
Private Sub cboClinicID_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboClinicID.SelectedIndexChanged
    Dim selectedIndex As Integer = cboClinicID.SelectedIndex
    Dim selectedItem As Object = cboClinicID.SelectedItem

    'Print in debug window
    Debug.Print("Selected clinicID: " & selectedItem.ToString())
    Debug.Print("Selected clinicID index: " & selectedIndex.ToString())

    Dim htData = lsData.Item(selectedIndex)


End Sub

SQL query method - **note, I'm pulling from two different tables:

Where the '?' is, is where I assume I have to work in the 'SelectedItem' but I have no idea how!

Desired result: an html table outputted with these three selected fields.

Public Class ClinicOrderController

Public Const CONNECTION_STRING As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=PharmDB.accdb"

'Dim cController As ClinicController = New ClinicController
'Dim oController As OrderController = New OrderController

Public Function findClinicOrder() As List(Of Hashtable)

    'Instantiates a connection object
    Dim oConnection As OleDbConnection = New OleDbConnection(CONNECTION_STRING)
    'Instantiates a list of hashtables
    Dim lsData As New List(Of Hashtable)

    Try
        Debug.Print("Connection string: " & oConnection.ConnectionString)

        oConnection.Open()
        Dim oCommand As OleDbCommand = New OleDbCommand
        oCommand.Connection = oConnection

        'Stored in the CommandText property of the command object
        'SELECT SQL statement
        oCommand.CommandText = "SELECT clinics.clinic_id, orders.date_ordered, orders.total_price FROM clinics, orders WHERE clinics.clinic_id = orders.clinic_id AND clinics.clinic_id = ? ORDER BY clinics.clinic_id"

        'Compiles the prepared statement
        'oCommand.Prepare()
        'Executes the SQL statement and stores the results in data reader object
        Dim oDataReader = oCommand.ExecuteReader()

        'Process data set in Hashtable
        Dim htTempData As Hashtable
        Do While oDataReader.Read() = True
            htTempData = New Hashtable
            htTempData("ClinicID") = CStr(oDataReader("clinic_id"))
            htTempData("DateOrdered") = CStr(oDataReader("date_ordered"))
            htTempData("OrderTotalPrice") = CStr(oDataReader("total_price"))
            lsData.Add(htTempData)

        Loop

        Debug.Print("The record was found.")

    Catch ex As Exception
        Debug.Print("ERROR:" & ex.Message)
        MsgBox("An error occured!")
    Finally
        oConnection.Close()
    End Try

    'Return list of hashtables to the calling function
    Return lsData

End Function

Really, really appreciate any help here. Ive been struggling with this for more than 8 hours (not joking - I give you permission to laugh)

Answer 1

If i understand you correctly, you want to use your dropdown selected item in your WHERE clause. To achieve that , revise your joining using INNER JOIN with ON then place your filtering in WHERE condition. Hope code below will help.

    SELECT clinics.clinic_id,
      , orders.date_ordered
      , orders.total_price 
    FROM clinics INNER JOIN orders ON clinics.clinic_id = orders.clinic_id
    WHERE clinics.clinic_id = selectedItem.ToString()
    ORDER BY clinics.clinic_id

if the selectedItem.ToString() did not work, you can try SelectedValue

Answer 2

Assuming that clinic_id is a numeric field in the database: (otherwise just surround it with single quotes (''))

string clinicID = cboClinicID.SelectedItem.ToString();
string sql = string.Format(@"SELECT clinics.clinic_id, orders.date_ordered, orders.total_price
                             FROM clinics, orders
                             WHERE clinics.clinic_id = orders.clinic_id
                             AND clinics.clinic_id = {0}
                             ORDER BY clinics.clinic_id", clinicID);

oCommand.CommandText = sql;

You can also do it like this:

string sql = "SELECT clinics.clinic_id, orders.date_ordered, orders.total_price " +
             "FROM clinics, orders " +
             "WHERE clinics.clinic_id = orders.clinic_id " +
             "AND clinics.clinic_id = " + clinicID + " " +
             "ORDER BY clinics.clinic_id";

Answer 3

Please provide code in vb.net

Here is my code, I want to display prize amount from table where class id is condition :

If class id is 3 then prize for that is display in my textbox named txtprize.text and class Id is displayed in list box.

Private Sub listClassID_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles listClassID.SelectedIndexChanged
Dim classIdlist As String
classIdlist = New String(listClassID.SelectedItem.ToString)
Dim strSQL As String = "select [Prize Amount] from Master_Class WHERE [Class ID]  =" & classIdlist
Dim dr As SqlDataReader

Try
    con.Open()
    cmd = New SqlCommand(strSQL, con)
    dr = cmd.ExecuteReader()
    If dr.Item(0) Then
        txtPrize.Text = dr("[Prize Amount]").ToString
    End If

    dr.Close()
    cmd.Dispose()
    con.Close()

Catch ex As Exception
    MsgBox(ex.Message)
    con.Close()
End Try
End Sub