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