Record Set Help

[Mangler]

New at this so bare with me please....

I have a recordset on a page like so :

<%
Dim Recordset3__varT
Recordset3__varT = "0"
If (Request("textfield") <> "") Then
Recordset3__varT = Request("textfield")
End If
%>
<%
Dim Recordset3
Dim Recordset3_cmd
Dim Recordset3_numRows

Set Recordset3_cmd = Server.CreateObject ("ADODB.Command")
Recordset3_cmd.ActiveConnection = MM_conLogistics_STRING
Recordset3_cmd.CommandText = "SELECT sku, quantity FROM partsinventory
WHERE sku in (?)"
Recordset3_cmd.Prepared = true
Recordset3_cmd.Parameters.Append Recordset3_cmd.CreateParameter
("param1", 200, 1, 50, Recordset3__varT) ' adVarChar

Set Recordset3 = Recordset3_cmd.Execute
Recordset3_numRows = 0
%>

When the variable is something like "test" it works great. However it
is possible for the variable to look like this "test,test1" ( without
the quotes ). That explains the sql "in" operator in the query. This
is where i am having trouble. I know that in SQL to use "in", the
query should look like :

WHERE sku in ('test','test1')

So i modified the variable to do that like so :

<%
Dim Recordset3__varT
Recordset3__varT = "0"
If (Request("textfield") <> "") Then
Recordset3__varT = Replace("('" & Request("textfield"),",","','")&
"')"
End If
%>

Which when I do Request("Recordset3__varT") on the page it displays it
just as I need it. However, it is not working.

What am I doing wrong here? Can someone help me get this working?

 

[Steve]

I believe this does what you're trying to do. I tried to simplify
your code a bit and get rid of the command object. (I wasn't sure why
you were using it other than trying to get agay from instantiating a
connection object?) This should work for you although I have not
executed it, so there may be errors. It's also been a while since
I've written classic ASP.

<%
Dim Recordset3
Dim Recordset3__varT
Dim objAdoConn

Recordset3__varT = "0"
If (Request("textfield") <> "") Then
Recordset3__varT = "'" & Replace(Request("textfield"), ",", "','") &
"'"
End If

Set objAdoConn = Server.CreateObject("ADODB.Connection")
objAdoConn.Open MM_conLogistics_STRING

Set Recordset3 = Server.CreateObject("ADODB.RecordSet")
Recordset3.Open "SELECT sku, quantity FROM partsinventory WHERE sku in
(" & Recordset3__varT & ")", objAdoConn, 3, 3

%>

You can clean it up a bit by getting rid of the connection I added,
but I wanted to show the complete code.

Let me know if this works for you.

-Steve

 

[Mangler]

I believe this does what you're trying to do.  I tried to simplify
your code a bit and get rid of the command object. (I wasn't sure why
you were using it other than trying to get agay from instantiating a
connection object?)  This should work for you although I have not
executed it, so there may be errors.  It's also been a while since
I've written classic ASP.  

<%
Dim Recordset3
Dim Recordset3__varT
Dim objAdoConn

Recordset3__varT = "0"
If (Request("textfield") <> "") Then
  Recordset3__varT = "'" & Replace(Request("textfield"), ",", "','") &
"'"
End If

Set objAdoConn = Server.CreateObject("ADODB.Connection")
objAdoConn.Open MM_conLogistics_STRING

Set Recordset3 = Server.CreateObject("ADODB.RecordSet")
Recordset3.Open "SELECT sku, quantity FROM partsinventory WHERE sku in
(" & Recordset3__varT & ")", objAdoConn, 3, 3

%>

Almost there I believe, I am getting this error:

Microsoft OLE DB Provider for SQL Server error '80040e07'

Conversion failed when converting the varchar value 'testRR' to data
type int.

 

[Steve]

Is the "sku" field in the database an integer? If not, are you using
the code that I sent or your original code?

 

[Mangler]

Is the "sku" field in the database an integer? If not, are you using
the code that I sent or your original code?







- Show quoted text -

I am using the code you gave and the datatype in the DB is VARCHAR

 

[Mangler]

Opps! I just figured out what I was doing wrong, thanks for your help
on this.

 

[Bob Barrows]

I believe this does what you're trying to do. I tried to simplify
your code a bit and get rid of the command object. (I wasn't sure why
you were using it other than trying to get agay from instantiating a
connection object?)

Oh great. We finally get Mangler away from painting a huge sign on his
website that says "Please hack me, I'm vulnerable to sql injection" and you
put him back on it.
Here, read this:
*****canned reply***********************************************************
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:

Access:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]
*****end canned
reply********************************************************

 

[Bob Barrows]

Recordset3_cmd.ActiveConnection = MM_conLogistics_STRING

Bad. Always use an explicit Connection object:
Set cn = createobject("adodb.connection")
cn.open MM_conLogistics_STRING
set Recordset3_cmd.ActiveConnection = cn

Recordset3_cmd.CommandText = "SELECT sku, quantity FROM partsinventory
WHERE sku in (?)"

Uh-oh, I think I know where you're going here ...

Recordset3_cmd.Prepared = true
Recordset3_cmd.Parameters.Append Recordset3_cmd.CreateParameter
("param1", 200, 1, 50, Recordset3__varT) ' adVarChar

Set Recordset3 = Recordset3_cmd.Execute
Recordset3_numRows = 0
%>

When the variable is something like "test" it works great. However it
is possible for the variable to look like this "test,test1"

Doh! I knew it!!
This just is not possible. The parameter is treated as a single string. In
this case it's a single string that contains a comma. There is no way the
query engine is coing to treat it as a comma-delimited list. Now, many
people would be tempted at this point to throw up their hands and say "ok
hackers, come get me. I have to use dynamic sql for this", but I'm hoping
you're made of sterner stuff. Let me know what database you are using so I
can give you some alternatives to using dynamic sql for this.

 

[Bob Barrows]

New at this so bare with me please....

Ah, I see from the error message in your subsequent post that you are using
SQL Server.
There are several ways to accomplish what you're after:

1) use charindex to search for the values:
WHERE CHARINDEX(intPK,@strArgs) > 0

Of course, this will force a table scan, but performance may be adequate for
your needs. You will also need to code delimiters into the variable and the
statement, to keep '4' from being found in this list: '3','34','44'.
WHERE CHARINDEX(',' + TRIM(intPK) + ',', ',' + @strArgs + ',') > 0

You can use any delimiter you want. For your sql statement, it would look
like this:

Recordset3_cmd.CommandText = _
"SELECT sku, quantity FROM partsinventory " & _
WHERE CHARINDEX(',' + TRIM(sku) + ',', ',' + ? + ',') > 0"

2) Thanks to Michael Walsh, here's yet another way:

" ... WHERE ( ',' + ? + ',' ) LIKE ('%,' + sku + ',%' )"

There's a few more alternatives that are maybe too advanced for your tastes
but just in case:
Here's a third way (only works with SS2000 and above) - thanks to "Robert

you could use xml, too:

create table tblExample(intPK int)
insert tblExample values(3)
insert tblExample values(56)
insert tblExample values(34)
insert tblExample values(300)
insert tblExample values(301)
go

create proc [tmp] as begin
declare @strArgs varchar(200), @hdoc int
set @strArgs='<r><n v="3"/><n v="56"/><n v="34"/><n v="300"/></r>'

exec sp_xml_preparedocument @hdoc output, @strArgs

select * from tblExample
where intPK in(
select v from openxml(
@hDoc, '/r/n',1
)
with(v int)
)

exec sp_xml_removedocument @hDoc
end
go

exec tmp
go

drop procedure tmp
drop table tblExample
go

For SQL 2000, you can use a UDF (thanks to Marc Litchfield):

.... it's
pretty easy to write a UDF to parse a comma-delimited string and return a
table variable (this is called a table-valued function), so you could do
something like this:

SELECT a.IdentityID
FROM tblExample ex
JOIN udfSplitInt(@strArgs) a ON a.Element = ex.intPK

Which would return the position of the value of ex.intPK within the
comma-delimited list (@strArgs). Here's a possible implementation of
udfSplitInt:

[---- Begin SQL ----]
-- Function: udfSplitInt
-- Description: Returns a table variable from a string containing a
-- delimited list of integers
-- Author: Marc Litchfield, 09/05/01
--
CREATE FUNCTION udfSplitInt
(
@vchList varchar(8000) = '',
@vchDelimiter varchar(5) = ','
)
RETURNS @tblList TABLE (
IdentityID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
Element int NULL )
AS
BEGIN

DECLARE @intCurIdx int,
@intLastIdx int,
@intListLen int,
@vchValue varchar(10)

SELECT @intCurIdx = 1,
@intLastIdx = 1,
@intListLen = LEN(@vchList)

WHILE ( @intCurIdx BETWEEN 1 AND @intListLen )
BEGIN
SELECT @intCurIdx = CHARINDEX(@vchDelimiter,@vchList,@intLastIdx),
@intCurIdx = CASE WHEN @intCurIdx = 0
THEN @intListLen + 1 ELSE @intCurIdx END,
@vchValue = LEFT(SUBSTRING(@vchList,@intLastIdx,
@intCurIdx - @intLastIdx),10),
@vchValue = REPLACE(CASE WHEN ISNUMERIC(@vchValue) = 0
THEN NULL ELSE @vchValue
END,',','')

INSERT @tblList ( Element )
SELECT @vchValue

SET @intLastIdx = @intCurIdx + 1
END

RETURN
END
[---- End SQL ----]

 

[Bob Barrows]

Hello,
I have the same problem (the IN operator doesn't work when using
CreateParameter) but with Access database
I tried:

" ... WHERE ( ',' + ? + ',' ) LIKE ('%,' + sku + ',%' )"

but doesn't work properly: when passing "sku" with multiple ids ("5,
7, 9" etc..) for having batch operations (toggle multiple records,
delete them, etc..), only the first record get processed, not the
others

Set cmdToggle = Server.CreateObject ("ADODB.Command")
cmdToggle.ActiveConnection = MM_connIWF_STRING
cmdToggle.CommandText = "UPDATE RECORDSET SET RS_ACTIVE = NOT
RS_ACTIVE WHERE (',' & ? & ',') LIKE ('%,' & RS_ID & ',%' )"
cmdToggle.Parameters.Append cmdToggle.CreateParameter("iData", 202,
1, 250, Request("iData")) ' adVarWChar

Have you previously verified that Request("iData") contains what you
expect it to contain?

Does this work when you use dynamic sql instead of parameters?