使用Excel列数据创建查询数据库的sql语句 [英] Using Excel column data to create sql statement that queries database

问题描述

我正在寻找任何有关如何阅读excel中包含500个user_id的列的建议，并查询数据库以在WPF应用程序中显示结果。用户可以拥有或租用，因此SQL将如下所示：

I am looking for any advice on how i can read a single column in excel that contains 500 user_id's and query a database to display results in a WPF application. A user can own or rent so the SQL would look like;

SELECT * FROM users WHERE own= 'user_id' or rent= 'user_id'

这对一个用户来说很好，但是我想读取每个user_id并将其连接到SQL语句从数据库中拉出所有结果。任何一个有这样做的简单方法？

This is fine for one user but i want to read each user_id and concatenate it to the SQL statement to pull out all results from the database. Any one have any easy way of doing this?

推荐答案

根据需要更换范围，加入联盟中的brettdj href =https://stackoverflow.com/questions/8482759/simple-vba-array-join-not-working>简单的VBA数组加入不起作用

Replace the range as necessary, credit to brettdj on the join - Simple VBA array join not working

Sub test()

    Dim strQuery As String
    Dim strVals As String

    Dim rngTarget As Range
    Set rntTarget = Range("A1:A7")

    Dim varArr
    Dim lngRow As Long
    Dim myArray()
    varArr = rntTarget.Value2

    ReDim myArray(1 To UBound(varArr, 1))

    For lngRow = 1 To UBound(varArr, 1)
        myArray(lngRow) = varArr(lngRow, 1)
    Next

    strVals = "('" & Join$(myArray, "','") & "') "

    strQuery = "SELECT * FROM users WHERE own in " _
        & strVals & "or rent in " & strVals


End Sub

这篇关于使用Excel列数据创建查询数据库的sql语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！