using Command to set Parameters and Recordset to retrive the Query

[Bruno Alexandre]

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. [phone]
F. [phone]
w. www.filtrarte.com
@. br***@filtrarte.com

[Roji. P. Thomas]

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" <br***@filtrarte.com> wrote in message
news:uV**************@TK2MSFTNGP09.phx.gbl...

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. [phone]
F. [phone]
w. www.filtrarte.com
@. br***@filtrarte.com

[Bruno Alexandre]

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. [phone]
F. [phone]
w. www.filtrarte.com
@. br***@filtrarte.com
"Roji. P. Thomas" <la********@nowhere.com> escreveu na mensagem
news:eL*************@TK2MSFTNGP09.phx.gbl...

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" <br***@filtrarte.com> wrote in message
news:uV**************@TK2MSFTNGP09.phx.gbl...

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. [phone]
F. [phone]
w. www.filtrarte.com
@. br***@filtrarte.com

[Bob Barrows]

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/...erForSQLServer

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/A...495/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]

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. [phone]
F. [phone]
w. www.filtrarte.com
@. br***@filtrarte.com
"Bob Barrows" <re******@NOyahoo.SPAMcom> escreveu na mensagem
news:ev**************@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/...erForSQLServer
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/A...495/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]

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"