Convention for writing parameters in CommandText

[Serge Myrand]

Hi,

This function always return and empty RecordSet (R.eof=True) the problem
is probably the way I pass the parameter in the CommandText. To avoid
looking at all code first look at this I think my mistake is
'[@prmClientNo]' but it does not give any error message. Any help will
be appreciate!

// code from the function below
Call R.Open("SELECT c.COMP_NAME, c.CLIENT_NO, cr.NAME FROM CLIENT AS c
" & _
"LEFT JOIN CLIENT_REF AS cr ON c.CLIENT_NO = cr.CLIENT_NO "
& _
"WHERE c.CLIENT_NO = '[@prmClientNo]' " & _
"ORDER BY NAME ASC", conn, adOpenForwardOnly,
adLockReadOnly, _
adCmdText)


//************************************************** ********************************

function ClientNoIsValid(sClientNo)
dim conn
dim R
dim F
dim prm
dim cmd
set conn = Server.CreateObject("ADODB.Connection")
set R = server.CreateObject("ADODB.RecordSet")
set cmd = Server.CreateObject("ADODB.Command")

conn.ConnectionString="Provider=Microsoft.Jet.OLED B.4.0;Data Source=" &
_
Server.MapPath("Data\WEB_PROD.mdb") & ";Persist
Security Info=True"
conn.CursorLocation=adUseClient
conn.Mode= adModeRead
conn.Open
Set cmd.ActiveConnection = Conn
set prm = cmd.CreateParameter("@prmClientNo", adChar, adParamInput, 13)

cmd.Parameters.Append prm
cmd.Parameters("@prmClientNo").Value = sClientNo
cmd.Parameters.Refresh

R.CursorLocation=adUseClient
Call R.Open("SELECT c.COMP_NAME, c.CLIENT_NO, cr.NAME FROM CLIENT AS c
" & _
"LEFT JOIN CLIENT_REF AS cr ON c.CLIENT_NO = cr.CLIENT_NO "
& _
"WHERE c.CLIENT_NO = '[@prmClientNo]' " & _
"ORDER BY NAME ASC", conn, adOpenForwardOnly,
adLockReadOnly, _
adCmdText)
ClientNoIsValid = not R.eof
Response.Write R.eof & "<br>"
'Response.Write R.Fields("COMP_NAME").Value & " " &
R.Fields("NAME").Value & "<BR>"
Set conn = Nothing
Set R = Nothing

end function


//*******************************

Thank's in advance
serge

[Serge Myrand]

Thank you very much,

I will use 'saved parameter query' when I will be able to do everything by hand.





"Bob Barrows [MVP]" wrote:

> Serge Myrand wrote:

> > Hi,
> >
> > This function always return and empty RecordSet (R.eof=True) the
> > problem
> > is probably the way I pass the parameter in the CommandText. To avoid
> > looking at all code first look at this I think my mistake is
> > '[@prmClientNo]'

>
> True.
> When using a text sql statement, you have to use the ODBC ? parameter
> placeholder, like this:.
>
> sSQL = "SELECT ... WHERE c.CLIENT_NO = ? ..."
>

> > but it does not give any error message. Any help will
> > be appreciate!
> >
> > // code from the function below
> > Call R.Open("SELECT c.COMP_NAME, c.CLIENT_NO, cr.NAME FROM CLIENT AS
> > c " & _
> > "LEFT JOIN CLIENT_REF AS cr ON c.CLIENT_NO =
> > cr.CLIENT_NO " & _
> > "WHERE c.CLIENT_NO = '[@prmClientNo]' " & _
> > "ORDER BY NAME ASC", conn, adOpenForwardOnly,
> > adLockReadOnly, _
> > adCmdText)
> >

> You just undid all of your hard work in building the Command object. See
> below
>

> >
> >

> //************************************************** ************************
> ********

> >
> > function ClientNoIsValid(sClientNo)
> > dim conn
> > dim R
> > dim F
> > dim prm
> > dim cmd
> > set conn = Server.CreateObject("ADODB.Connection")
> > set R = server.CreateObject("ADODB.RecordSet")
> > set cmd = Server.CreateObject("ADODB.Command")
> >
> > conn.ConnectionString="Provider=Microsoft.Jet.OLED B.4.0;Data
> > Source=" & _
> > Server.MapPath("Data\WEB_PROD.mdb") & ";Persist
> > Security Info=True"
> > conn.CursorLocation=adUseClient
> > conn.Mode= adModeRead
> > conn.Open
> > Set cmd.ActiveConnection = Conn
> > set prm = cmd.CreateParameter("@prmClientNo", adChar, adParamInput,
> > 13)
> >
> > cmd.Parameters.Append prm
> > cmd.Parameters("@prmClientNo").Value = sClientNo

>
> NO! Get rid of the following line:
> ************************************

> > cmd.Parameters.Refresh

> **************************************

> >
> > R.CursorLocation=adUseClient

>
> I look at the following and all I can say is: "Incredible!" Why did you
> build the Command object if you weren't going to use it??
>

> > Call R.Open("SELECT c.COMP_NAME, c.CLIENT_NO, cr.NAME FROM CLIENT AS
> > c " & _
> > "LEFT JOIN CLIENT_REF AS cr ON c.CLIENT_NO =
> > cr.CLIENT_NO " & _
> > "WHERE c.CLIENT_NO = '[@prmClientNo]' " & _
> > "ORDER BY NAME ASC", conn, adOpenForwardOnly,
> > adLockReadOnly, _
> > adCmdText)

>
> 1) Since you are using a client-side cursor, the only cursor-type available
> is a static cursor. If you really want a forward-only cursor (and it appears
> that you do), you need to get rid of the lines where you set the
> cursorlocation to adUseClient.
>
> Let's modify your code as follows:
>
> dim conn
> dim R
> dim F 'What is this?
> dim sSQL
> dim prm
> dim cmd
> sSQL = "SELECT ... WHERE c.CLIENT_NO = ? ..."
> set conn = Server.CreateObject("ADODB.Connection")
> conn.ConnectionString="Provider=Microsoft.Jet.OLED B.4.0;" & _
> "Data Source=" & Server.MapPath("Data\WEB_PROD.mdb")
> conn.Open
> set cmd = Server.CreateObject("ADODB.Command")
> Set cmd.ActiveConnection = Conn
> cmd.CommandText = sSQL
> cmd.CommandType=adCmdText
> set prm = cmd.CreateParameter("@prmClientNo", adVarChar, _
> adParamInput, 13)
> cmd.Parameters.Append prm
> cmd.Parameters("@prmClientNo").Value = sClientNo
>
> Set R = cmd.Execute
>

> > ClientNoIsValid = not R.eof
> > Response.Write R.eof & "<br>"
> > 'Response.Write R.Fields("COMP_NAME").Value & " " &
> > R.Fields("NAME").Value & "<BR>"
> > Set conn = Nothing
> > Set R = Nothing
> >
> > end function
> >

>
> By not using a saved parameter query, you are making life difficult for
> youself. Do yourself a favor and Google for posts by me containing the words
> "saved parameter query".
>
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.

[Serge Myrand]

Everythings works find now.

If I had many parameters would I "Append" them in the order the query will use
them and still use the '?' as placeholder in the CommandText?

thank's
serge

"Bob Barrows [MVP]" wrote:

> Serge Myrand wrote:

> > Hi,
> >
> > This function always return and empty RecordSet (R.eof=True) the
> > problem
> > is probably the way I pass the parameter in the CommandText. To avoid
> > looking at all code first look at this I think my mistake is
> > '[@prmClientNo]'

>
> True.
> When using a text sql statement, you have to use the ODBC ? parameter
> placeholder, like this:.
>
> sSQL = "SELECT ... WHERE c.CLIENT_NO = ? ..."
>

> > but it does not give any error message. Any help will
> > be appreciate!
> >
> > // code from the function below
> > Call R.Open("SELECT c.COMP_NAME, c.CLIENT_NO, cr.NAME FROM CLIENT AS
> > c " & _
> > "LEFT JOIN CLIENT_REF AS cr ON c.CLIENT_NO =
> > cr.CLIENT_NO " & _
> > "WHERE c.CLIENT_NO = '[@prmClientNo]' " & _
> > "ORDER BY NAME ASC", conn, adOpenForwardOnly,
> > adLockReadOnly, _
> > adCmdText)
> >

> You just undid all of your hard work in building the Command object. See
> below
>

> >
> >

> //************************************************** ************************
> ********

> >
> > function ClientNoIsValid(sClientNo)
> > dim conn
> > dim R
> > dim F
> > dim prm
> > dim cmd
> > set conn = Server.CreateObject("ADODB.Connection")
> > set R = server.CreateObject("ADODB.RecordSet")
> > set cmd = Server.CreateObject("ADODB.Command")
> >
> > conn.ConnectionString="Provider=Microsoft.Jet.OLED B.4.0;Data
> > Source=" & _
> > Server.MapPath("Data\WEB_PROD.mdb") & ";Persist
> > Security Info=True"
> > conn.CursorLocation=adUseClient
> > conn.Mode= adModeRead
> > conn.Open
> > Set cmd.ActiveConnection = Conn
> > set prm = cmd.CreateParameter("@prmClientNo", adChar, adParamInput,
> > 13)
> >
> > cmd.Parameters.Append prm
> > cmd.Parameters("@prmClientNo").Value = sClientNo

>
> NO! Get rid of the following line:
> ************************************

> > cmd.Parameters.Refresh

> **************************************

> >
> > R.CursorLocation=adUseClient

>
> I look at the following and all I can say is: "Incredible!" Why did you
> build the Command object if you weren't going to use it??
>

> > Call R.Open("SELECT c.COMP_NAME, c.CLIENT_NO, cr.NAME FROM CLIENT AS
> > c " & _
> > "LEFT JOIN CLIENT_REF AS cr ON c.CLIENT_NO =
> > cr.CLIENT_NO " & _
> > "WHERE c.CLIENT_NO = '[@prmClientNo]' " & _
> > "ORDER BY NAME ASC", conn, adOpenForwardOnly,
> > adLockReadOnly, _
> > adCmdText)

>
> 1) Since you are using a client-side cursor, the only cursor-type available
> is a static cursor. If you really want a forward-only cursor (and it appears
> that you do), you need to get rid of the lines where you set the
> cursorlocation to adUseClient.
>
> Let's modify your code as follows:
>
> dim conn
> dim R
> dim F 'What is this?
> dim sSQL
> dim prm
> dim cmd
> sSQL = "SELECT ... WHERE c.CLIENT_NO = ? ..."
> set conn = Server.CreateObject("ADODB.Connection")
> conn.ConnectionString="Provider=Microsoft.Jet.OLED B.4.0;" & _
> "Data Source=" & Server.MapPath("Data\WEB_PROD.mdb")
> conn.Open
> set cmd = Server.CreateObject("ADODB.Command")
> Set cmd.ActiveConnection = Conn
> cmd.CommandText = sSQL
> cmd.CommandType=adCmdText
> set prm = cmd.CreateParameter("@prmClientNo", adVarChar, _
> adParamInput, 13)
> cmd.Parameters.Append prm
> cmd.Parameters("@prmClientNo").Value = sClientNo
>
> Set R = cmd.Execute
>

> > ClientNoIsValid = not R.eof
> > Response.Write R.eof & "<br>"
> > 'Response.Write R.Fields("COMP_NAME").Value & " " &
> > R.Fields("NAME").Value & "<BR>"
> > Set conn = Nothing
> > Set R = Nothing
> >
> > end function
> >

>
> By not using a saved parameter query, you are making life difficult for
> youself. Do yourself a favor and Google for posts by me containing the words
> "saved parameter query".
>
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.

[Bob Barrows [MVP]]

Serge Myrand wrote:

> Hi,
>
> This function always return and empty RecordSet (R.eof=True) the
> problem
> is probably the way I pass the parameter in the CommandText. To avoid
> looking at all code first look at this I think my mistake is
> '[@prmClientNo]'

True.
When using a text sql statement, you have to use the ODBC ? parameter
placeholder, like this:.

sSQL = "SELECT ... WHERE c.CLIENT_NO = ? ..."

> but it does not give any error message. Any help will
> be appreciate!
>
> // code from the function below
> Call R.Open("SELECT c.COMP_NAME, c.CLIENT_NO, cr.NAME FROM CLIENT AS
> c " & _
> "LEFT JOIN CLIENT_REF AS cr ON c.CLIENT_NO =
> cr.CLIENT_NO " & _
> "WHERE c.CLIENT_NO = '[@prmClientNo]' " & _
> "ORDER BY NAME ASC", conn, adOpenForwardOnly,
> adLockReadOnly, _
> adCmdText)
>

You just undid all of your hard work in building the Command object. See
below

>
>

//************************************************** ************************
********

>
> function ClientNoIsValid(sClientNo)
> dim conn
> dim R
> dim F
> dim prm
> dim cmd
> set conn = Server.CreateObject("ADODB.Connection")
> set R = server.CreateObject("ADODB.RecordSet")
> set cmd = Server.CreateObject("ADODB.Command")
>
> conn.ConnectionString="Provider=Microsoft.Jet.OLED B.4.0;Data
> Source=" & _
> Server.MapPath("Data\WEB_PROD.mdb") & ";Persist
> Security Info=True"
> conn.CursorLocation=adUseClient
> conn.Mode= adModeRead
> conn.Open
> Set cmd.ActiveConnection = Conn
> set prm = cmd.CreateParameter("@prmClientNo", adChar, adParamInput,
> 13)
>
> cmd.Parameters.Append prm
> cmd.Parameters("@prmClientNo").Value = sClientNo

NO! Get rid of the following line:
************************************

> cmd.Parameters.Refresh

**************************************

>
> R.CursorLocation=adUseClient

I look at the following and all I can say is: "Incredible!" Why did you
build the Command object if you weren't going to use it??

> Call R.Open("SELECT c.COMP_NAME, c.CLIENT_NO, cr.NAME FROM CLIENT AS
> c " & _
> "LEFT JOIN CLIENT_REF AS cr ON c.CLIENT_NO =
> cr.CLIENT_NO " & _
> "WHERE c.CLIENT_NO = '[@prmClientNo]' " & _
> "ORDER BY NAME ASC", conn, adOpenForwardOnly,
> adLockReadOnly, _
> adCmdText)

1) Since you are using a client-side cursor, the only cursor-type available
is a static cursor. If you really want a forward-only cursor (and it appears
that you do), you need to get rid of the lines where you set the
cursorlocation to adUseClient.

Let's modify your code as follows:


dim conn
dim R
dim F 'What is this?
dim sSQL
dim prm
dim cmd
sSQL = "SELECT ... WHERE c.CLIENT_NO = ? ..."
set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionString="Provider=Microsoft.Jet.OLED B.4.0;" & _
"Data Source=" & Server.MapPath("Data\WEB_PROD.mdb")
conn.Open
set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = Conn
cmd.CommandText = sSQL
cmd.CommandType=adCmdText
set prm = cmd.CreateParameter("@prmClientNo", adVarChar, _
adParamInput, 13)
cmd.Parameters.Append prm
cmd.Parameters("@prmClientNo").Value = sClientNo

Set R = cmd.Execute

> ClientNoIsValid = not R.eof
> Response.Write R.eof & "<br>"
> 'Response.Write R.Fields("COMP_NAME").Value & " " &
> R.Fields("NAME").Value & "<BR>"
> Set conn = Nothing
> Set R = Nothing
>
> end function
>

By not using a saved parameter query, you are making life difficult for
youself. Do yourself a favor and Google for posts by me containing the words
"saved parameter query".

Bob Barrows


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Aaron [SQL Server MVP]]

> If I had many parameters would I "Append" them in the order the query
will use

> them and still use the '?' as placeholder in the CommandText?

Wouldn't it be a bit more sane and manageable to use named parameters?
Doesn't Bob have a script that will automate the creation of this?

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)

[Bob Barrows [MVP]]

Serge Myrand wrote:

> Everythings works find now.
>
> If I had many parameters would I "Append" them in the order the
> query will use them and still use the '?' as placeholder in the
> CommandText?
>

Yes. But it would be MUCH simpler to use saved parameter queries.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bob Barrows [MVP]]

Aaron [SQL Server MVP] wrote:

>> If I had many parameters would I "Append" them in the order the
>> query will use them and still use the '?' as placeholder in the
>> CommandText?

>
> Wouldn't it be a bit more sane and manageable to use named parameters?
> Doesn't Bob have a script that will automate the creation of this?

Unfortunately, he's using Access, so my code generator won't work for that.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Serge Myrand]

Ok, Thank you for your time.
serge

"Bob Barrows [MVP]" wrote:

> Serge Myrand wrote:

> > Everythings works find now.
> >
> > If I had many parameters would I "Append" them in the order the
> > query will use them and still use the '?' as placeholder in the
> > CommandText?
> >

>
> Yes. But it would be MUCH simpler to use saved parameter queries.
>
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.