«

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?

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

On Jan 8, 11:13*am, Mangler <webmas...@repairresource.com> wrote:
> 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?

On Jan 8, 11:47*am, Steve <gom...@infinity-software.com> wrote:
> 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.

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

On Jan 8, 12:12*pm, Mangler <webmas...@repairresource.com> wrote:
> On Jan 8, 11:47*am, Steve <gom...@infinity-software.com> wrote:
>
>
>
> > 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.- Hide quoted text -
>
> - Show quoted text -

On Jan 8, 12:22*pm, Steve <gom...@infinity-software.com> wrote:
> Is the "sku" field in the database an integer? If not, are you using
> the code that I sent or your original code?
>
> On Jan 8, 12:12*pm, Mangler <webmas...@repairresource.com> wrote:
>
>
>
> > On Jan 8, 11:47*am, Steve <gom...@infinity-software.com> wrote:
>
> > > 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.- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

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

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

Steve wrote:
> 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/...e36562fee7804e

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

Access:
http://www.google.com/groups?hl=en&l...TNGP12.phx.gbl

http://groups.google.com/groups?hl=e...tngp13.phx.gbl
*****end canned
reply********************************************* ***********


--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Mangler wrote:
> 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.

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Mangler wrote:
> 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
Lummert" <> , who provided it:

> 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 ----]



--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Ivan wrote:
> 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?

--
HTH,
Bob Barrows