How to send multiple values to a SQL stored procedure parameter from Visual Basic 6?

Data and Databases
1

I’m having trouble getting my VB6 report to show records. Here’s my stored procedure:

CREATE PROC GetData 
(@region VARCHAR(20),
 @category VARCHAR(20))
AS 
SELECT * FROM MainTable 
WHERE (region = @region) AND category IN (@category)

It works fine in SQL Server when I manually put ‘Business’,‘Personal’ for @category. But when I try to pass a string from VB6, it doesn’t work. In VB6, I have:

categoryList = "'Business','Personal'"

How can I make this work? I need to pass multiple values for the category parameter. Any ideas would be great. Thanks!

2

hey sophia, i had same issue. try this:

change ur stored proc to use table-valued parameter instead. then in vb6, create a temp table with ur categories and pass that. it’s more flexible for multiple values.

sry if explanation’s not great, but it worked 4 me. good luck!

3

I’ve encountered this issue before, and there’s a straightforward solution. The problem lies in how SQL Server interprets the string you’re passing. Instead of treating it as multiple values, it sees it as a single string.

To fix this, you’ll need to modify your stored procedure to use dynamic SQL. Here’s how you can rewrite it:

CREATE PROC GetData 
(@region VARCHAR(20),
 @category VARCHAR(MAX))
AS 
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT * FROM MainTable WHERE region = @region AND category IN (' + @category + ')'
EXEC sp_executesql @sql, N'@region VARCHAR(20)', @region

In your VB6 code, you can then pass the categories like this:

categoryList = "'Business','Personal'"

Now, this should work as expected. Remember to properly sanitize your inputs to prevent SQL injection.