none
Sql query and Dropdownlist RRS feed

  • Question

  • I have a query that grabs a variable depending on what a user picks in a Dropdownlist.

    The Query picks up these two items 

    Computer Name                 GUID

    Unknown                           530E5E84-E389-7A96-E578-E49C1B7F3621

    WorkstationName1             DC3438FF-5C05-11E5-B6C6-E014E402E0FF

    WorkstationName2             DC3438FF-5C05-11E5-B6C6-E014E401234d

    the Dropdownlist then gets the Computer Name ONLY. User can pick the Workstation name and then runs another Query base off of that Workstation Name.

    BUT is it possible that if a user picks a "unknown"  pc,  that the GUID is what is returned so that my query can use that GUID instead  vs the actual Workstation name

    This is what i have so far

    Try
                    If conn.State = 0 Then
                        conn.Open()
                        Form1.Label5.Text = "Connected to SQL Server database.  Select a Workstation"
                        Form1.Label5.ForeColor = Color.SeaGreen
                    ElseIf conn.State = 1 Then
                    End If
                Catch ex As Exception
                    Form1.Label5.Text = "[ERROR} Could not connect to SQL Server database!: " & ex.Message
                End Try
                If Form1.CheckBox1.CheckState = CheckState.Checked Then
                    Err = "0"
                Else
                    Err = "99999999"
                End If
             
                Dim strQ As String = String.Empty
                strQ = "USE " & SCCMDB & " Select Distinct Name0 AS 'Workstation Name',
                  SMBIOS_GUID0 as 'GUID'
                    from vSMS_TaskSequenceExecutionStatus tes
                    inner join v_R_System sys on tes.ResourceID = sys.ResourceID
                    inner join v_TaskSequencePackage tsp on tes.PackageID = tsp.PackageID
                    where tsp.Name = '" & TSQName & "'
                    and DATEDIFF(hour,ExecutionTime,GETDATE()) <  '" & Interval & "'
                    and ExitCode <> '" & Err & "'
                    ORDER BY Name0 Desc"
    
                Using connection As SqlConnection = conn
    
                    Using comm As SqlCommand = New SqlCommand(strQ, conn)
                       
                        Dim rs As SqlDataReader = comm.ExecuteReader
                        Dim dt As DataTable = New DataTable
                        dt.Load(rs)
                        
                        Form1.ComboBox2.ValueMember = "Name0"
                        Form1.ComboBox2.DisplayMember = "Workstation Name"
    
                        Form1.ComboBox2.DataSource = dt
    
                    End Using
                End Using
              
    
                If conn.State = 0 Then
                ElseIf conn.State = 1 Then
                    conn.Close()
                    Form1.Label5.Text = "Disconnected from SQL Server database."
                End If


    • Edited by Radizero Friday, February 16, 2018 6:56 PM
    Friday, February 16, 2018 6:54 PM

Answers

  • Just in case anyone runs into this question . 

    The resolution is to use the 

    You should use the property Value not ValueMember

    Dim valueMemberValue = cmbCategory.SelectedValue.ToString()

    The property ValueMember it is just the name of the column from which you want to retrieve values while DiplayMember is the name of the column from which you want to populate the items collection.

    So, setting the ValueMember, you tell the ComboBox to update its Value property when the user select a different item from the Items collection. The combo looks at the corresponding value present in the same row of the item selected but in the column named through the ValueMember property

    • Marked as answer by Radizero Sunday, February 18, 2018 7:52 PM
    Sunday, February 18, 2018 7:52 PM

All replies

  • Not sure what you want but off topic please consider using parameters rather than string concatenation for your SELECT statement e.g.

    Dim dt As New DataTable
    
    Dim strQ As String = "USE " & SCCMDB & " Select Distinct Name0 AS 'Workstation Name',
            SMBIOS_GUID0 as 'GUID'
            from vSMS_TaskSequenceExecutionStatus tes
            inner join v_R_System sys on tes.ResourceID = sys.ResourceID
            inner join v_TaskSequencePackage tsp on tes.PackageID = tsp.PackageID
            where tsp.Name = @TSQName 
            and DATEDIFF(hour,ExecutionTime,GETDATE()) <  @Interval
            and ExitCode <> @Err 
            ORDER BY Name0 Desc"
    
    Using cn As New SqlConnection With {.ConnectionString = "TODO"}
        Using cmd As New SqlCommand With {.Connection = cn, .CommandText = strQ}
            cmd.Parameters.AddWithValue("@TSQName", TSQName)
            cmd.Parameters.AddWithValue("@Interval", Interval)
            cmd.Parameters.AddWithValue("@Err", Err)
            cn.Open()
            dt.Load(cmd.ExecuteReader)
    
        End Using
    End Using


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, February 16, 2018 7:04 PM
    Moderator
  • Just in case anyone runs into this question . 

    The resolution is to use the 

    You should use the property Value not ValueMember

    Dim valueMemberValue = cmbCategory.SelectedValue.ToString()

    The property ValueMember it is just the name of the column from which you want to retrieve values while DiplayMember is the name of the column from which you want to populate the items collection.

    So, setting the ValueMember, you tell the ComboBox to update its Value property when the user select a different item from the Items collection. The combo looks at the corresponding value present in the same row of the item selected but in the column named through the ValueMember property

    • Marked as answer by Radizero Sunday, February 18, 2018 7:52 PM
    Sunday, February 18, 2018 7:52 PM