locked
Compare dataset to a form combo box RRS feed

  • Question

  • I have the following query in a dataset that I would like to compare to a combo box value and not have the static text, LD SPOILER PACKAGING. 

    Any help would be greatly appreciated

    SELECT        PR_EMP.EMPNO, PR_EMP.MIDDLE_NAME, PR_EMP.FIRST_NAME, PR_EMP.LAST_NAME, TA_LABOR.TIME_IN, TA_LABOR.SOURCE, TA_LABOR.SOURCE_ID, TA_LABOR.TIME_OUT, WORK_CENTER.EQNO,
                             WORK_CENTER.CNTR_DESC
    FROM            TA_LABOR TA_LABOR, PR_EMP PR_EMP, WORK_CENTER WORK_CENTER
    WHERE        TA_LABOR.PR_EMP_ID = PR_EMP.ID AND TA_LABOR.WORK_CENTER_ID = WORK_CENTER.ID AND (TA_LABOR.TIME_IN IS NOT NULL) AND (TA_LABOR.TIME_OUT IS NULL) AND
                             (WORK_CENTER.CNTR_DESC = 'LD SPOILER PACKAGING')
    ORDER BY PR_EMP.FIRST_NAME

    Monday, April 6, 2020 5:34 PM

All replies

  • Here is a pattern to use for SQL-Server, if using another database such as MS-Access swap out SqlConnection for OleDbConnection and SqlCommand with OleDbCommand etc.

    Now pass the value of the comboBox e.g. Dim SomeTable = GetData(SomeComboBox.Text)

    Public Function GetData(Value As String) As DataTable
        Dim dataTable As New DataTable
    
        Using cn As New SqlConnection With {.ConnectionString = "Your connection string goes here"}
            Using cmd As New SqlCommand With {.Connection = cn}
                cmd.CommandText = <SQL>
                                    SELECT  PR_EMP.EMPNO, PR_EMP.MIDDLE_NAME, PR_EMP.FIRST_NAME, 
                                            PR_EMP.LAST_NAME, TA_LABOR.TIME_IN, TA_LABOR.SOURCE, 
                                            TA_LABOR.SOURCE_ID, TA_LABOR.TIME_OUT, WORK_CENTER.EQNO,
                                            WORK_CENTER.CNTR_DESC
                                    FROM    TA_LABOR TA_LABOR, PR_EMP PR_EMP, WORK_CENTER WORK_CENTER
                                    WHERE   TA_LABOR.PR_EMP_ID = PR_EMP.ID AND TA_LABOR.WORK_CENTER_ID = WORK_CENTER.ID AND 
                                                (TA_LABOR.TIME_IN IS NOT NULL) AND (TA_LABOR.TIME_OUT IS NULL) AND
                                                (WORK_CENTER.CNTR_DESC = @CNTR_DESC)
                                    ORDER BY PR_EMP.FIRST_NAME
                                  </SQL>.Value
    
                cmd.Parameters.AddWithValue("@CNTR_DESC", Value)
    
                cn.Open()
    
                dataTable.Load(cmd.ExecuteReader())
            End Using
        End Using
    
        Return dataTable
    
    End Function
    Edit: Some people like to use ? as a place holder for MS-Access as MS-Access parameters are ordinal positional while SQL-Server are named parameters, named parameters can be used with Access but are still ordinal.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange



    Monday, April 6, 2020 7:04 PM
  • Be aware with OLEDB are no "named parameters". You can use a description for a parameter.

    With OleDB that description does nothing, it does not even have to be unique, you can use ? but as well always a number to give its presence in the ordinal sequence as it will be used (it does not set the sequence).



    Success
    Cor



    Monday, April 6, 2020 7:16 PM