Populating the SQL IN Values - SQL Server Forums

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

Populating the SQL IN Values

Author

Topic

sourceskill
Starting Member

2 Posts

Posted - 2010-09-10 : 11:59:31

I have a form passing the variables to populate the IN values in SQL. I think I'm close but not having any luck (knowledge is better than luck I know)

PLEASE! Take a look at my code and let me know what I'm doing wrong:

<%
Dim rsAuto__print
rsAuto__print = "1"
For i=1 to (Trim(UCase(Request.QueryString("print"))).Count
If (Trim(UCase(Request.QueryString("print"))) (i) = Trim(UCase(rsAuto.Fields.Item("vin").Value)) Then
rsAuto__print = Trim(UCase(Request.QueryString("print"))
End If
Next
%>
<%
Dim rsAuto
Dim rsAuto_cmd
Dim rsAuto_numRows

Set rsAuto_cmd = Server.CreateObject ("ADODB.Command")
rsAuto_cmd.ActiveConnection = MM_Regal_STRING
rsAuto_cmd.CommandText = "SELECT * FROM AUTODETA as t1 LEFT JOIN MAKES AS t2 ON t1.makesx = t2.makesx WHERE vin IN (?)"
rsAuto_cmd.Prepared = true
rsAuto_cmd.Parameters.Append rsAuto_cmd.CreateParameter("param1", 200, 1, 255, rsAuto__print) ' adVarChar

Set rsAuto = rsAuto_cmd.Execute
rsAuto_numRows = 0
%>

Thank you for any help. I've been trying to get this running for 3 days now :(

Natalie Patterson

Kristen
Test

22859 Posts

Posted - 2010-09-10 : 12:47:34

You can't do this with parameter substitution like:

"SELECT * FROM AUTODETA as t1 LEFT JOIN MAKES AS t2 ON t1.makesx = t2.makesx WHERE vin IN (?)"

you'll need to put the actual values in there. Make sure there are no embedded single quotes (if there are double-them-up), and wrap each with single quotes unless they are numeric values.

"SELECT * FROM AUTODETA as t1 LEFT JOIN MAKES AS t2 ON t1.makesx = t2.makesx WHERE vin IN (" & rsAuto__print & ")"

(assuming you have pre-processed rsAuto__print as per description above).

Hmmm ... you just want [vin] in numbers 1 to [print] ? If so something like this should do:

rsAuto_cmd.CommandText = "SELECT * FROM AUTODETA as t1 LEFT JOIN MAKES AS t2 ON t1.makesx = t2.makesx WHERE vin >= 1 AND vin <= ?"

rsAuto_cmd.Parameters.Append rsAuto_cmd.CreateParameter("param1", 200, 1, 255, print) ' adVarChar

sourceskill
Starting Member

2 Posts

Posted - 2010-09-10 : 14:06:10

Hmmm ... you just want [vin] in numbers 1 to [print] ?
Ummmm.... No I've taken that default value out it's only throwing me off even more.

<%
Dim rsAuto__print
For i=1 to (Trim(UCase(Request.QueryString("print"))).Count
If (Trim(UCase(Request.QueryString("print"))) (i) = Trim(UCase(rsAuto.Fields.Item("vin").Value)) Then
rsAuto__print = Trim(UCase(Request.QueryString("print")) (i)<-- does this need to be part of the value for the count agains the query? or does the count in the For part of the statement take care of all the counting?
End If
Next
%>
<%
Dim rsAuto
Dim rsAuto_cmd
Dim rsAuto_numRows

Set rsAuto_cmd = Server.CreateObject ("ADODB.Command")
rsAuto_cmd.ActiveConnection = MM_Regal_STRING
rsAuto_cmd.CommandText = "SELECT * FROM AUTODETA as t1 LEFT JOIN MAKES AS t2 ON t1.makesx = t2.makesx WHERE vin IN ("& rsAuto__print &")" <--- How will I have the values seperated by the comma using this?
rsAuto_cmd.Prepared = true
rsAuto_cmd.Parameters.Append rsAuto_cmd.CreateParameter("param1", 200, 1, 255, rsAuto__print) ' adVarChar

Set rsAuto = rsAuto_cmd.Execute
rsAuto_numRows = 0
%>

A little insight on why the query and count are needed the variables from the form page previous to the above report page has a list of vehicles that are from the DB and a check box with the DB vin value assigned to the check box. The check box is used to select the vehicles that need to be printed/passed to the report page. The check box name is "print" hence the count. My aim is to get the values from the checked boxes (which are the vin numbers) to match with the vin field in the DB and populate the vaules for the IN statement based on what was checked to generate the vehicles for the report. I don't know if this information helps you help me but I'm going Do-Lolly trying to get this thing running!!!

Cheers Kristen

Natalie Patterson

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-09-13 : 16:40:44

The SQL trick for this is the long parameter list. Here is the skeleton; T-SQL can handle up to 2K parameters

CREATE PROCEDURE Build_VIN_List
(@in_vin_01 VARCHAR(17) = NULL,
@in_vin_02 VARCHAR(17) = NULL,
@in_vin_03 VARCHAR(17) = NULL,
@in_vin_04 VARCHAR(17) = NULL,
..
@in_vin_xx VARCHAR(17) = NULL)
AS
BEGIN

--put the vins into a motorpool
SELECT vin
INTO #local_motorpool
FROM (VALUES (@in_vin_01),
(@in_vin_02),
(@in_vin_03),
(@in_vin_04),
..,
(@in_vin_xx)) AS X(vin)
WHERE vin IS NOT NULL;

/*
add data scrubbing here so you have good data. Might to check that VIN matches the Make code
*/

SELECT * -- do not use * in production code
FROM Autodeta AS A
LEFT OUTER JOIN
Makes AS M
ON A.makesx = M.makesx
WHERE vin
(SELECT vin
FROM #local_motorpool
WHERE vin IS NOT NULL);
END;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources