using Command to set Parameters and Recordset to retrive the Query

Discussion in 'ASP General' started by Bruno Alexandre, Mar 3, 2004.

  1. Hi guys,

    withou using SP, I want to be able to add a Parameter to the SQL Query and
    retrive the Recordset so I can use the Paging property under the recorset
    object.... how can I do this?

    I'm stuck here.



    Set cnData = server.createObject("ADODB.Command")
    Set rsData = server.createObject("ADODB.RecordSet")
    ' set the page size
    rsData.PageSize = iPSize
    rsData.CursorLocation = adUseClient

    ' open the data
    sSQL = " SELECT * FROM vATSlistaAssistencias " & _
    " WHERE estado = 'ACTIVO' and estadoEsc not in ('FORA SERVICO', 'NAO
    QUER', 'NAO TEM MAQUINA', 'OUTRA 2') and " & _
    " idDistribuidorAssistencia = @idDistAss and localidade like @localidade
    " & _
    " ORDER BY @coluna @ordem"

    with cnData
    .ActiveConnection = sConnCW
    .CommandText = sSQL
    .CommandType = adCmdText

    .Parameters.Append = .CreateParameter("@idDistAss", adInteger,
    adParamInput)
    .Parameters.Append = .CreateParameter("@localidade", adVarChar,
    adParamInput, 100)
    .Parameters.Append = .CreateParameter("@coluna", adVarChar, adParamInput,
    100)
    .Parameters.Append = .CreateParameter("@ordem", adVarChar, adParamInput,
    5)

    .Parameters("@idDistAss") = idDistAssistencia
    .Parameters("@localidade") = sLocalidade
    .Parameters("@coluna") = sColuna
    .Parameters("@ordem") = sOrdem

    end with

    set rsData = cnData.execute
    cnData.ActiveConnection.Close


    I got an Error regarding the @idDistAss is not define in the query

    [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable
    '@idDistAss'

    But, as you can see, I have it defined in the sSQL variable...

    What can I do? I'm really stuck here, please help me

    --

    Bruno Miguel Alexandre
    Dep Informática do Grupo Filtrarte

    Av General Humberto Delgado, 91
    Vila Verde
    2705-887 Terrugem SNT
    Portugal

    T. +351 219 608 130
    F. +351 219 615 369
    w. www.filtrarte.com
    @.
     
    Bruno Alexandre, Mar 3, 2004
    #1
    1. Advertising

  2. AFAIK you cannot pass parameters to the query as you do it.

    Probably you can try

    sSQL = " SELECT * FROM vATSlistaAssistencias " & _
    " WHERE estado = 'ACTIVO' and estadoEsc not in ('FORA SERVICO', 'NAO
    QUER', 'NAO TEM MAQUINA', 'OUTRA 2') and " & _
    " idDistribuidorAssistencia = " & idDistAssistencia&" @and localidade
    like " & sLocalidade
    " & _
    " ORDER BY " & sColuna & " " & sOrdem


    --
    Roji. P. Thomas
    SQL Server Programmer

    "Bruno Alexandre" <> wrote in message
    news:...
    > Hi guys,
    >
    > withou using SP, I want to be able to add a Parameter to the SQL Query and
    > retrive the Recordset so I can use the Paging property under the recorset
    > object.... how can I do this?
    >
    > I'm stuck here.
    >
    >
    >
    > Set cnData = server.createObject("ADODB.Command")
    > Set rsData = server.createObject("ADODB.RecordSet")
    > ' set the page size
    > rsData.PageSize = iPSize
    > rsData.CursorLocation = adUseClient
    >
    > ' open the data
    > sSQL = " SELECT * FROM vATSlistaAssistencias " & _
    > " WHERE estado = 'ACTIVO' and estadoEsc not in ('FORA SERVICO', 'NAO
    > QUER', 'NAO TEM MAQUINA', 'OUTRA 2') and " & _
    > " idDistribuidorAssistencia = @idDistAss and localidade like

    @localidade
    > " & _
    > " ORDER BY @coluna @ordem"
    >
    > with cnData
    > .ActiveConnection = sConnCW
    > .CommandText = sSQL
    > .CommandType = adCmdText
    >
    > .Parameters.Append = .CreateParameter("@idDistAss", adInteger,
    > adParamInput)
    > .Parameters.Append = .CreateParameter("@localidade", adVarChar,
    > adParamInput, 100)
    > .Parameters.Append = .CreateParameter("@coluna", adVarChar,

    adParamInput,
    > 100)
    > .Parameters.Append = .CreateParameter("@ordem", adVarChar, adParamInput,
    > 5)
    >
    > .Parameters("@idDistAss") = idDistAssistencia
    > .Parameters("@localidade") = sLocalidade
    > .Parameters("@coluna") = sColuna
    > .Parameters("@ordem") = sOrdem
    >
    > end with
    >
    > set rsData = cnData.execute
    > cnData.ActiveConnection.Close
    >
    >
    > I got an Error regarding the @idDistAss is not define in the query
    >
    > [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the

    variable
    > '@idDistAss'
    >
    > But, as you can see, I have it defined in the sSQL variable...
    >
    > What can I do? I'm really stuck here, please help me
    >
    > --
    >
    > Bruno Miguel Alexandre
    > Dep Informática do Grupo Filtrarte
    >
    > Av General Humberto Delgado, 91
    > Vila Verde
    > 2705-887 Terrugem SNT
    > Portugal
    >
    > T. +351 219 608 130
    > F. +351 219 615 369
    > w. www.filtrarte.com
    > @.
    >
    >
    >
    >
     
    Roji. P. Thomas, Mar 3, 2004
    #2
    1. Advertising

  3. Well, maybe not like I do it, but I can...

    I'm trying to avoid in line parameters ..." FROM [table] WHERE [field] = '"
    & request.querystring("field") & "' " ...

    because of the SQL injection


    all I want is to be able to make paging (I can using the recordset) but I
    need to add parameters to the SQL query (without using Store Procedures) the
    same way thet we use the parameters property to add parameters to SP


    --

    Bruno Miguel Alexandre
    Dep Informática do Grupo Filtrarte

    Av General Humberto Delgado, 91
    Vila Verde
    2705-887 Terrugem SNT
    Portugal

    T. +351 219 608 130
    F. +351 219 615 369
    w. www.filtrarte.com
    @.


    "Roji. P. Thomas" <> escreveu na mensagem
    news:...
    > AFAIK you cannot pass parameters to the query as you do it.
    >
    > Probably you can try
    >
    > sSQL = " SELECT * FROM vATSlistaAssistencias " & _
    > " WHERE estado = 'ACTIVO' and estadoEsc not in ('FORA SERVICO', 'NAO
    > QUER', 'NAO TEM MAQUINA', 'OUTRA 2') and " & _
    > " idDistribuidorAssistencia = " & idDistAssistencia&" @and localidade
    > like " & sLocalidade
    > " & _
    > " ORDER BY " & sColuna & " " & sOrdem
    >
    >
    > --
    > Roji. P. Thomas
    > SQL Server Programmer
    >
    > "Bruno Alexandre" <> wrote in message
    > news:...
    > > Hi guys,
    > >
    > > withou using SP, I want to be able to add a Parameter to the SQL Query

    and
    > > retrive the Recordset so I can use the Paging property under the

    recorset
    > > object.... how can I do this?
    > >
    > > I'm stuck here.
    > >
    > >
    > >
    > > Set cnData = server.createObject("ADODB.Command")
    > > Set rsData = server.createObject("ADODB.RecordSet")
    > > ' set the page size
    > > rsData.PageSize = iPSize
    > > rsData.CursorLocation = adUseClient
    > >
    > > ' open the data
    > > sSQL = " SELECT * FROM vATSlistaAssistencias " & _
    > > " WHERE estado = 'ACTIVO' and estadoEsc not in ('FORA SERVICO', 'NAO
    > > QUER', 'NAO TEM MAQUINA', 'OUTRA 2') and " & _
    > > " idDistribuidorAssistencia = @idDistAss and localidade like

    > @localidade
    > > " & _
    > > " ORDER BY @coluna @ordem"
    > >
    > > with cnData
    > > .ActiveConnection = sConnCW
    > > .CommandText = sSQL
    > > .CommandType = adCmdText
    > >
    > > .Parameters.Append = .CreateParameter("@idDistAss", adInteger,
    > > adParamInput)
    > > .Parameters.Append = .CreateParameter("@localidade", adVarChar,
    > > adParamInput, 100)
    > > .Parameters.Append = .CreateParameter("@coluna", adVarChar,

    > adParamInput,
    > > 100)
    > > .Parameters.Append = .CreateParameter("@ordem", adVarChar,

    adParamInput,
    > > 5)
    > >
    > > .Parameters("@idDistAss") = idDistAssistencia
    > > .Parameters("@localidade") = sLocalidade
    > > .Parameters("@coluna") = sColuna
    > > .Parameters("@ordem") = sOrdem
    > >
    > > end with
    > >
    > > set rsData = cnData.execute
    > > cnData.ActiveConnection.Close
    > >
    > >
    > > I got an Error regarding the @idDistAss is not define in the query
    > >
    > > [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the

    > variable
    > > '@idDistAss'
    > >
    > > But, as you can see, I have it defined in the sSQL variable...
    > >
    > > What can I do? I'm really stuck here, please help me
    > >
    > > --
    > >
    > > Bruno Miguel Alexandre
    > > Dep Informática do Grupo Filtrarte
    > >
    > > Av General Humberto Delgado, 91
    > > Vila Verde
    > > 2705-887 Terrugem SNT
    > > Portugal
    > >
    > > T. +351 219 608 130
    > > F. +351 219 615 369
    > > w. www.filtrarte.com
    > > @.
    > >
    > >
    > >
    > >

    >
    >
     
    Bruno Alexandre, Mar 3, 2004
    #3
  4. Bruno Alexandre

    Bob Barrows Guest

    Bruno Alexandre wrote:
    > Hi guys,
    >
    > withou using SP, I want to be able to add a Parameter to the SQL
    > Query and retrive the Recordset so I can use the Paging property
    > under the recorset object.... how can I do this?
    >
    > I'm stuck here.
    >
    >
    >
    > Set cnData = server.createObject("ADODB.Command")
    > Set rsData = server.createObject("ADODB.RecordSet")
    > ' set the page size
    > rsData.PageSize = iPSize
    > rsData.CursorLocation = adUseClient
    >
    > ' open the data
    > sSQL = " SELECT * FROM vATSlistaAssistencias " & _
    > " WHERE estado = 'ACTIVO' and estadoEsc not in ('FORA SERVICO',
    > 'NAO QUER', 'NAO TEM MAQUINA', 'OUTRA 2') and " & _
    > " idDistribuidorAssistencia = @idDistAss and localidade like
    > @localidade " & _
    > " ORDER BY @coluna @ordem"


    This will not work. The @variables are only usable in a stored procedure
    (see below). I strongly suggest using the solution I show below, but if for
    some reason you can't, you need to use the ODBC parameter placeholder (?)
    instead of the @variable names. Like this:

    sSQL = " SELECT <list of columns - don't use * in production code>" & _
    " FROM vATSlistaAssistencias " & _
    " WHERE estado = 'ACTIVO' and estadoEsc not in " & _
    " ('FORA SERVICO', 'NAO QUER', 'NAO TEM MAQUINA', 'OUTRA 2')" & _
    " and idDistribuidorAssistencia = ? and localidade like ? " & _

    'I have never tried this in the ORDER BY clause, so I am not sure it
    'will work. If you try it and it works, please let us know.

    " ORDER BY ? ?"


    Even if this technique of using the parameters in the ORDER BY does work for
    you, I suspect that this will defeat your objective of preventing sql
    injection. You need to try putting some sql in the sOrdem variable to see if
    it will execute. Something harmless, like this:

    sOrdem = "ASC; Select 'sql injected'"

    Run the code and see if you have a second recordset (use the NextRecordset
    method to check for this).


    Now, since you have the parameters marked with the ODBC polaceholders, the
    following Command object code should work (assuming it is possible to use
    parameters in the ORDER BY clause, that is). However, I want to reiterate
    that you should not do it this way. See below for a more efficient solution
    using a stored procedure.

    <Command code snipped>
    >
    > I got an Error regarding the @idDistAss is not define in the query
    >
    > [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the
    > variable '@idDistAss'


    You should use the SQLOLEDB provider, not ODBC. Here is an example:
    For Standard Security

    oConn.Open "Provider=sqloledb;" & _
    "Data Source=myServerName;" & _
    "Initial Catalog=myDatabaseName;" & _
    "User Id=myUsername;" & _
    "Password=myPassword"
    For other examples, see:
    http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForSQLServer

    You should create a stored procedure on your sql server, like this:

    CREATE PROCEDURE GetData (
    @idDistAss int,
    @localidade varchar(100)
    )
    AS
    SELECT <list of columns - don't use * in production code>
    FROM vATSlistaAssistencias
    WHERE estado = 'ACTIVO' and estadoEsc not in ('FORA SERVICO', 'NAO
    QUER', 'NAO TEM MAQUINA', 'OUTRA 2') and
    idDistribuidorAssistencia = @idDistAss and localidade like @localidade


    You cannot use this syntax:
    ORDER BY @coluna @ordem
    The items in an ORDER BY list cannot be variables. Here are some options for
    you to consider:
    http://www.winnetmag.com/SQLServer/Article/ArticleID/16495/16495.html

    I will leave this part out of the example. You can put it in later after
    reading the article.

    To execute this in ASP, just do this:

    Set rsData = server.createObject("ADODB.RecordSet")
    ' set the page size
    rsData.PageSize = iPSize
    rsData.CursorLocation = adUseClient
    oConn.Open
    oConn.GetData idDistAssistencia,sLocalidade,rsData
    if rsData.eof then
    'no records
    else
    'do your stuff
    end if

    Once you figure out how to deal with the order by parameters, just do this:
    oConn.GetData idDistAssistencia,sLocalidade, _
    sColuna,sOrdem,rsData

    HTH,
    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    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, Mar 3, 2004
    #4
  5. Hi Bob,

    in your example, "oConn.GetData idDistAssistencia,sLocalidade,rsData"
    doesn't the rsData will be interpretate as an input parameter in the SP?


    I know how to use the parameters in a Store Procedure... but I was kind
    of avoiding to create a SP for each select with parameters,
    I didn't know that parameters are only used for SP :(

    But thank you anyway...


    To all the other who read this post, you can do this:

    Set cnPrds = server.createObject("ADODB.Command")
    Set rsPrds = server.createObject("ADODB.RecordSet")

    with cnPrds
    .ActiveConnection = sConnCW
    .CommandText = "spProductsFromClient"
    .CommandType = adCmdStoredProc
    .Parameters.Append = .CreateParameter("@idClient", adVarChar,
    adParamInput, 10)
    .Parameters("@idCliente") = strClientID
    end with
    set rsPrds = cnPrds.execute

    and with "set rsPrds = cnPrds.execute" you will be able to use ADO Paging
    using a RecordSet
    if you guys want, I can make an example to post here on how you can build a
    Next, Previous, Start and End Paging links



    --

    Bruno Miguel Alexandre
    Dep Informática do Grupo Filtrarte

    Av General Humberto Delgado, 91
    Vila Verde
    2705-887 Terrugem SNT
    Portugal

    T. +351 219 608 130
    F. +351 219 615 369
    w. www.filtrarte.com
    @.


    "Bob Barrows" <> escreveu na mensagem
    news:...
    > Bruno Alexandre wrote:
    > > Hi guys,
    > >
    > > withou using SP, I want to be able to add a Parameter to the SQL
    > > Query and retrive the Recordset so I can use the Paging property
    > > under the recorset object.... how can I do this?
    > >
    > > I'm stuck here.
    > >
    > >
    > >
    > > Set cnData = server.createObject("ADODB.Command")
    > > Set rsData = server.createObject("ADODB.RecordSet")
    > > ' set the page size
    > > rsData.PageSize = iPSize
    > > rsData.CursorLocation = adUseClient
    > >
    > > ' open the data
    > > sSQL = " SELECT * FROM vATSlistaAssistencias " & _
    > > " WHERE estado = 'ACTIVO' and estadoEsc not in ('FORA SERVICO',
    > > 'NAO QUER', 'NAO TEM MAQUINA', 'OUTRA 2') and " & _
    > > " idDistribuidorAssistencia = @idDistAss and localidade like
    > > @localidade " & _
    > > " ORDER BY @coluna @ordem"

    >
    > This will not work. The @variables are only usable in a stored procedure
    > (see below). I strongly suggest using the solution I show below, but if

    for
    > some reason you can't, you need to use the ODBC parameter placeholder (?)
    > instead of the @variable names. Like this:
    >
    > sSQL = " SELECT <list of columns - don't use * in production code>" & _
    > " FROM vATSlistaAssistencias " & _
    > " WHERE estado = 'ACTIVO' and estadoEsc not in " & _
    > " ('FORA SERVICO', 'NAO QUER', 'NAO TEM MAQUINA', 'OUTRA 2')" & _
    > " and idDistribuidorAssistencia = ? and localidade like ? " & _
    >
    > 'I have never tried this in the ORDER BY clause, so I am not sure it
    > 'will work. If you try it and it works, please let us know.
    >
    > " ORDER BY ? ?"
    >
    >
    > Even if this technique of using the parameters in the ORDER BY does work

    for
    > you, I suspect that this will defeat your objective of preventing sql
    > injection. You need to try putting some sql in the sOrdem variable to see

    if
    > it will execute. Something harmless, like this:
    >
    > sOrdem = "ASC; Select 'sql injected'"
    >
    > Run the code and see if you have a second recordset (use the NextRecordset
    > method to check for this).
    >
    >
    > Now, since you have the parameters marked with the ODBC polaceholders,

    the
    > following Command object code should work (assuming it is possible to use
    > parameters in the ORDER BY clause, that is). However, I want to reiterate
    > that you should not do it this way. See below for a more efficient

    solution
    > using a stored procedure.
    >
    > <Command code snipped>
    > >
    > > I got an Error regarding the @idDistAss is not define in the query
    > >
    > > [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the
    > > variable '@idDistAss'

    >
    > You should use the SQLOLEDB provider, not ODBC. Here is an example:
    > For Standard Security
    >
    > oConn.Open "Provider=sqloledb;" & _
    > "Data Source=myServerName;" & _
    > "Initial Catalog=myDatabaseName;" & _
    > "User Id=myUsername;" & _
    > "Password=myPassword"
    > For other examples, see:
    >

    http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForSQLServer
    >
    > You should create a stored procedure on your sql server, like this:
    >
    > CREATE PROCEDURE GetData (
    > @idDistAss int,
    > @localidade varchar(100)
    > )
    > AS
    > SELECT <list of columns - don't use * in production code>
    > FROM vATSlistaAssistencias
    > WHERE estado = 'ACTIVO' and estadoEsc not in ('FORA SERVICO', 'NAO
    > QUER', 'NAO TEM MAQUINA', 'OUTRA 2') and
    > idDistribuidorAssistencia = @idDistAss and localidade like @localidade
    >
    >
    > You cannot use this syntax:
    > ORDER BY @coluna @ordem
    > The items in an ORDER BY list cannot be variables. Here are some options

    for
    > you to consider:
    > http://www.winnetmag.com/SQLServer/Article/ArticleID/16495/16495.html
    >
    > I will leave this part out of the example. You can put it in later after
    > reading the article.
    >
    > To execute this in ASP, just do this:
    >
    > Set rsData = server.createObject("ADODB.RecordSet")
    > ' set the page size
    > rsData.PageSize = iPSize
    > rsData.CursorLocation = adUseClient
    > oConn.Open
    > oConn.GetData idDistAssistencia,sLocalidade,rsData
    > if rsData.eof then
    > 'no records
    > else
    > 'do your stuff
    > end if
    >
    > Once you figure out how to deal with the order by parameters, just do

    this:
    > oConn.GetData idDistAssistencia,sLocalidade, _
    > sColuna,sOrdem,rsData
    >
    > HTH,
    > Bob Barrows
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > 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"
    >
    >
     
    Bruno Alexandre, Mar 3, 2004
    #5
  6. Bruno Alexandre

    Bob Barrows Guest

    Bruno Alexandre wrote:
    > Hi Bob,
    >
    > in your example, "oConn.GetData
    > idDistAssistencia,sLocalidade,rsData" doesn't the rsData will be
    > interpretate as an input parameter in the SP?
    >


    No. I use this technique all the time. It works. An instantiated recordset
    object added after the list of parameter values will be used to receive the
    resultset from the procedure. If your procedure does not return a resultset,
    simply omit the recordset variable from the procedure call.

    I only use an explicit Command object if my stored procedure uses output
    parameters or I need to retrieve the value returned by a RETURN statement in
    the SP.


    >
    > I know how to use the parameters in a Store Procedure... but I
    > was kind
    > of avoiding to create a SP for each select with parameters,
    > I didn't know that parameters are only used for SP :(
    >


    It's the @variables that are only used for stored procedures. Dynamic SQL
    can be parameterized using the ? placeholders.

    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    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, Mar 3, 2004
    #6
    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. Jonck van der Kogel
    Replies:
    2
    Views:
    1,008
    Jonck van der Kogel
    May 27, 2004
  2. Suresh
    Replies:
    0
    Views:
    556
    Suresh
    Jun 2, 2006
  3. vyshu
    Replies:
    0
    Views:
    393
    vyshu
    Apr 11, 2007
  4. Hung Huynh
    Replies:
    8
    Views:
    347
    Bob Barrows
    Sep 24, 2003
  5. Replies:
    1
    Views:
    133
    Austin Ziegler
    Mar 6, 2007
Loading...

Share This Page