Check if x is IN columna,columnb

Question

I am building a Reports page and want to be able to have a dropdownlist that pulls clients from an sql database based on the funding source selected in another drop down menu. Clients can have multiple funding sources. The table has three columns: ClientID, FundingSource1 and FundingSource2.

I need to set up an SQL query that:

Select clientID
FROM clientTable
WHERE fundingvariable IN (@fundingsource1, @fundingsource2)

So that I can select a fundingvariable in a drop down menu and have the report return all clients with that funding variable regardless of whether that variable is in the first or second fundingsource column.

Obviously the Select statment won't recognize a variable declared in VB. How do I identify a non-specific variable in an SQL statement so i can then call the Method for the dependent dropdownlist?

I am not seeing a reason why you couldn't do `WHERE fundingvariable = fundingsource1 or fundingvariable = fundingsource2` — Jacob H, Sep 27 '17 at 19:11
It would help if you would show an attempt at what you are trying to accomplish... — Trevor, Sep 27 '17 at 19:17
Possible duplicate of [how to pass parameters to query in SQL (Excel)](https://stackoverflow.com/questions/5434768/how-to-pass-parameters-to-query-in-sql-excel) — Degan, Sep 27 '17 at 19:44
@Degan that link is irrelevant, where did the OP say anything about Excel? — Trevor, Sep 27 '17 at 19:45
Possible duplicate of [How do I create a parameterized SQL query? Why Should I?](https://stackoverflow.com/questions/542510/how-do-i-create-a-parameterized-sql-query-why-should-i) — Andrew O'Brien, Sep 27 '17 at 20:19
What you have works (without the @) if your question is how you can match your variable against multiple columns. I tested this with data: `SELECT clientID FROM clientTable WHERE "credit" IN (fundingSource1, fundingSource2)` where I had either `bank`, `credit`, or random strings in the two columns. If you are asking how to pass the variable to fundingVariable, that's a different question. — Chris Strickland, Sep 29 '17 at 07:02
yes, that is my question! How do I pass a variable to the funding variable, and how do I demarcate it in the SQL statement to create the getby method? — Anna, Sep 29 '17 at 14:27

score 0 · Answer 1 · answered Sep 28 '17 at 00:06

In your SQL statement you cannot select only ClientID. Select is for you column in the table. if you have the 2 columns fundingsource1 and 2 and the clientid and you want to see if clientid is those 2 columns then you write something like this

"SELECT fundingsource1, fundingsource2 WHERE (fundingsource1=@clientid) or (fundingsource2=@clientid)"

if the table has more than 2 columns and you need access to the data in all of them then you can write something like this

 "SELECT * WHERE (fundingsource1=@clientid) or (fundingsource2=@clientid)"

This is a class is use with MS SQL servers. I actually have more functions in the class for stuff I do all the time. I don't like re-writing lines of codes. If you want to understand the newID and execscalar then read about execscalar.

You can use it in your project like this and change the connection string to match yours

sub Something ()
Dim xDB as new dbcontrol
dim SQLQuery as string = "SELECT fundingsource1, fundingsource2 WHERE (fundingsource1=@clientid) or (fundingsource2=@clientid)"
xdb.addparams("@clientid",ClientID)

xdb.execquery(SQLQuery)

end sub

SQL DBControl class

    Imports System.Data.SqlClient

    Public Class DBControl
        ' CREATE YOUR DB CONNECTION
        'Change the datasource
        Public SQLSource As String = "Data Source=someserver\sqlexpress;Integrated Security=True"
        Private DBCon As New SqlConnection(SQLSource)

        ' PREPARE DB COMMAND
        Private DBCmd As SqlCommand

        ' DB DATA
        Public DBDA As SqlDataAdapter
        Public DBDT As DataTable

        ' QUERY PARAMETERS
        Public Params As New List(Of SqlParameter)

        ' QUERY STATISTICS
        Public RecordCount As Integer
        Public Exception As String

        Public Sub ExecQuery(Query As String, Optional ByVal ExecScalar As Boolean = False, Optional ByRef NewID As Long = -1)
            ' RESET QUERY STATS
            RecordCount = 0
            Exception = ""

            Try
                ' OPEN A CONNECTION
                DBCon.Open()

                ' CREATE DB COMMAND
                DBCmd = New SqlCommand(Query, DBCon)

                ' LOAD PARAMS INTO DB COMMAND
                Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))

                ' CLEAR PARAMS LIST
                Params.Clear()

                ' EXECUTE COMMAND & FILL DATATABLE
                If ExecScalar = True Then
                    NewID = DBCmd.ExecuteScalar()
                End If
                DBDT = New DataTable
                DBDA = New SqlDataAdapter(DBCmd)
                RecordCount = DBDA.Fill(DBDT)
            Catch ex As Exception
                Exception = ex.Message
            End Try


            ' CLOSE YOUR CONNECTION
            If DBCon.State = ConnectionState.Open Then DBCon.Close()
        End Sub

        ' INCLUDE QUERY & COMMAND PARAMETERS
        Public Sub AddParam(Name As String, Value As Object)
            Dim NewParam As New SqlParameter(Name, Value)
            Params.Add(NewParam)
        End Sub
    End Class

Check if x is IN columna,columnb

1 Answers1