Re: using Command to set Parameters and Recordset to retrive the Query

From: Bruno Alexandre (bruno_at_filtrarte.com)
Date: 03/03/04 

Date: Wed, 3 Mar 2004 14:57:17 -0000

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
@. bruno@filtrarte.com
"Bob Barrows" <reb01501@NOyahoo.SPAMcom> escreveu na mensagem
news:evvY5nSAEHA.3184@TK2MSFTNGP09.phx.gbl...
> 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"
>
>

Relevant Pages

  • Re: Setting control value based on a SQL Select statement
    ... SQL Server doesn't make any distinction between INPUT/OUTPUT or OUTPUT ... @CRRNumber int OUTPUT, ... The actual code I use to call my stored procedure is as follows: ... using the OPEN statement of a recordset to capture the result in the ...
    (microsoft.public.access.adp.sqlserver)
  • Re: How does ADO 2 update a SQL stored procedure recordset?
    ... > Generally when I want to use ADO 2 to update a recordset based on a MSSQL ... > call a second stored procedure to perform the update. ... Well if you have SELECTed the columns in the recordset that form the Primary ... You are running SQL Server. ...
    (microsoft.public.data.ado)
  • Re: Getting SQL recordset into Access table
    ... What you can do is create a pass-through query that will return the ... Microsoft Access Developer's Guide to SQL Server ... >Currently I have access to a stored procedure on the SQL ... >system which returns a recordset ...
    (microsoft.public.access.externaldata)
  • Re: HELP! Error on Stored Procedure - AbsolutePage Property
    ... > "Current Recordset does not support bookmarks. ... > 'Connect command object to database (as I understand how you said to do ... SQL Server is a very capable provider, ... Of course all of this assumes the underlying SQL (in the stored procedure) ...
    (microsoft.public.vb.database.ado)
  • Re: Simple Insert Into...
    ... The form is based on an SQL in the form's Record Source property, ... holds the current text in the control, ... Sometimes the unbound controls do have a value (such as when they are set ... I need this value to assign a unique ID to the entire recordset being ...
    (microsoft.public.access.modulesdaovba)