检查x是否为IN columna，columnb

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.

我正在构建一个Reports页面，希望能够有一个下拉列表，根据在另一个下拉菜单中选择的资金来源从sql数据库中提取客户端。客户可以拥有多个资金来源。该表有三列：ClientID，FundingSource1和FundingSource2。

I need to set up an SQL query that:

我需要设置一个SQL查询：

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?

显然，Select语句不会识别VB中声明的变量。如何在SQL语句中标识非特定变量，以便我可以为依赖下拉列表调用Method？

1 个解决方案

#1

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

在SQL语句中，您不能只选择ClientID。在表格中选择适合您的列。如果您有2列fundingsource1和2以及clientid，并且您想看看clientid是否是那2列，那么你写这样的东西

"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

如果表有超过2列，并且您需要访问所有这些列中的数据，那么您可以编写类似这样的内容

 "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.

这是一个与MS SQL服务器一起使用的类。我实际上在课堂上有更多的功能，我一直都在做。我不喜欢重写代码行。如果你想了解newID和execscalar，那么请阅读有关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

SQL DBControl类

    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

#1

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