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

From: Bob Barrows (reb01501_at_NOyahoo.SPAMcom)
Date: 03/03/04 

Date: Wed, 3 Mar 2004 09:12:02 -0500

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: Stored Procedures v Views
    ... Build a stored procedure, ... The goal here will be to not use any dynamic sql like you used in Access. ... If the query is very complex, but frankly, if you have to do this, you are ... > they make are then used to create the SQL statement which is then used to ...
    (microsoft.public.sqlserver.programming)
  • RE: Class Module Choices
    ... SQL Server has Stored Procedures. ... Perhaps you meant Stored Query with is different. ... When I said stored procedure, ... I don't know how to assign a value to Param1 within the execute command. ...
    (microsoft.public.access.formscoding)
  • RE: How to simple retrieve autonumber valuees or identity from data so
    ... you can write the equivalent of a stored procedure in Access. ... Simply write your SQL in a query and save it. ... > I use the access database in my simple application. ...
    (microsoft.public.dotnet.framework.adonet)
  • Help Please interfacing to SQL
    ... I'm still a novice when it comes to getting MS Access talking to SQL ... I have a pass through query in my access front end called ... Below is the stored procedure in the SQL database that it talks to(written ... NULL, @QueueItemDesc) ...
    (microsoft.public.access.queries)
  • Re: Call SQL Stored procedure in Access 2002
    ... compile it stops on Dim db as DAO.Database and says dao.database ... > Create a new, empty query, and then change it to a SQL pass-through query ... >> move the processing to a stored procedure because I am getting ODBC ...
    (microsoft.public.access.formscoding)