using variable number of search strings with VBA

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

using variable number of search strings with VBA

Author

Topic

robert_645
Starting Member

4 Posts

Posted - 2015-03-05 : 09:25:48

I have created a kind of search function in my Excel sheet where the user can select multiple items from different listboxes.
To save the items I use an array. So in one case only 1 items off the array is filled, but another time maybe 3 items are filled in the array.

I use the array to search in a SQL database (this is string rva(i) in the code).
But when the array is empty it results in an error.

Who can help me with the select code for SQL?
This is what I have so far. (the rule at the bottom won't work also )


rc.Open "SELECT [Datum],[RvA_Nr], [RvA_Letter], [Afdeling], [EVAL], [Matrix], [Component], [AS3000], [AP04], [Rec] FROM dbo.QHSE_2ndline_history " _
        & "WHERE [Afdeling] = '" & afd & "' AND [Datum] >= '" & datum1 & "' AND [Datum] <= '" & datum2 & "' AND " _
        & "CASE WHEN '" & rva(1) & "' <> '' then [RvA_Nr] = '" & rva(1) & "' END AND" _
        & "([Matrix] LIKE '%" & matrix(1) & "%' OR [Matrix] LIKE '%" & matrix(2) & "%' OR [Matrix] LIKE '%" & matrix(3) & "%' OR [Matrix] LIKE '%" & matrix(4) & "%' OR [Matrix] LIKE '%" & matrix(5) & "%' OR [Matrix] LIKE '%" & matrix(6) & "%' OR [Matrix] LIKE '%" & matrix(7) & "%' OR [Matrix] LIKE '%" & matrix(8) & "%' OR [Matrix] LIKE '%" & matrix(9) & "%' OR [Matrix] LIKE '%" & matrix(10) & "%') AND " _
        & "([Component] LIKE '%" & comp(1) & "%' OR [Component] LIKE '%" & comp(2) & "%' OR [Component] LIKE '%" & comp(3) & "%' OR [Component] LIKE '%" & comp(4) & "%' OR [Component] LIKE '%" & comp(5) & "%' OR [Component] LIKE '%" & comp(6) & "%' OR [Component] LIKE '%" & comp(7) & "%' OR [Component] LIKE '%" & comp(8) & "%' OR [Component] LIKE '%" & comp(9) & "%' OR [Component] LIKE '%" & comp(10) & "%') " _
        & "ORDER BY [Datum] desc", con


GoTo verder2

'        & "([RvA_Nr] = '" & rva(1) & "' OR [RvA_Nr] = '" & rva(2) & "' OR [RvA_Nr] = '" & rva(3) & "' OR [RvA_Nr] = '" & rva(4) & "' OR [RvA_Nr] = '" & rva(5) & "' OR [RvA_Nr] = '" & rva(6) & "' OR [RvA_Nr] = '" & rva(7) & "' OR [RvA_Nr] = '" & rva(8) & "' OR [RvA_Nr] = '" & rva(9) & "' OR [RvA_Nr] = '" & rva(10) & "') AND " _

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-05 : 12:30:48

Your code is vulnerable to SQL injection as it's concatenating the query together. Instead you need to start using parameterized queries.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources