Record Set Help

Discussion in 'ASP General' started by Mangler, Jan 8, 2009.

  1. Mangler

    Mangler Guest

    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?
     
    Mangler, Jan 8, 2009
    #1
    1. Advertising

  2. Mangler

    Steve Guest

    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 <> 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?
     
    Steve, Jan 8, 2009
    #2
    1. Advertising

  3. Mangler

    Mangler Guest

    On Jan 8, 11:47 am, 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?)  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.
     
    Mangler, Jan 8, 2009
    #3
  4. Mangler

    Steve Guest

    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 <> wrote:
    > On Jan 8, 11:47 am, 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?)  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 -
     
    Steve, Jan 8, 2009
    #4
  5. Mangler

    Mangler Guest

    On Jan 8, 12:22 pm, Steve <> 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 <> wrote:
    >
    >
    >
    > > On Jan 8, 11:47 am, 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?)  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
     
    Mangler, Jan 8, 2009
    #5
  6. Mangler

    Mangler Guest

    Opps! I just figured out what I was doing wrong, thanks for your help
    on this.
     
    Mangler, Jan 8, 2009
    #6
  7. Mangler

    Bob Barrows Guest

    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/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&selm=

    http://groups.google.com/groups?hl=...=1&selm=
    *****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"
     
    Bob Barrows, Jan 9, 2009
    #7
  8. Mangler

    Bob Barrows Guest

    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"
     
    Bob Barrows, Jan 9, 2009
    #8
  9. Mangler

    Bob Barrows Guest

    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"
     
    Bob Barrows, Jan 9, 2009
    #9
  10. Mangler

    Bob Barrows Guest

    Re: Re:Record Set Help

    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
     
    Bob Barrows, Sep 14, 2009
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?SnVzdGlu?=

    Retrieving Record Key while creating the record.

    =?Utf-8?B?SnVzdGlu?=, Oct 4, 2004, in forum: ASP .Net
    Replies:
    4
    Views:
    664
    =?Utf-8?B?SnVzdGlu?=
    Oct 5, 2004
  2. André
    Replies:
    0
    Views:
    3,780
    André
    Jun 25, 2006
  3. MRW
    Replies:
    0
    Views:
    398
  4. THurkmans
    Replies:
    2
    Views:
    623
  5. Maximus
    Replies:
    2
    Views:
    207
    Bob Barrows [MVP]
    Apr 12, 2007
Loading...

Share This Page