|
 |
asp_databases thread: How do I pass a parameter to a stored procedure with a wild card?
Message #1 by rrrabe@y... on Fri, 24 Nov 2000 15:49:27 -0000
|
|
Hello,
I hope somebody can help me on this.
How do I pass a parameter to a stored procedure with a wild card?
merci beaucoup,
Bay
Message #2 by Robert Chartier <rchartierh@a...> on Fri, 24 Nov 2000 11:18:07 -0500
|
|
your just passing a string in...so you can either add the wild card to the
string your sending in, or in the SP itself
sCrit = sCrit & "*"
At 10:49 AM 11/24/00, you wrote:
>Hello,
>
>I hope somebody can help me on this.
>
>How do I pass a parameter to a stored procedure with a wild card?
>
>merci beaucoup,
>Bay
>
Robert Chartier
Author, AspFree.com
xxx-xxx-xxxx
rchartierh@a...
http://www.aspfree.com/devlinks
http://www.aspfree.com/authors/robert
http://www.aspalliance.com/nothingmn
Message #3 by Stephane_Dattenny@D... on Fri, 24 Nov 2000 10:22:23 -0600
|
|
What do you want to do exactly ? (Which database server ?)
Describe your stored proc and the parameter you want to pass...
Best regards / Cordialement
Stephane Dattenny
Dell Computers - EMEA IT - VB and Web developer
Phone: +33 (0)4 99 75 49 88
-----Original Message-----
From: rrrabe@y... [mailto:rrrabe@y...]
Sent: Friday, November 24, 2000 4:49 PM
To: ASP Databases
Subject: [asp_databases] How do I pass a parameter to a stored procedure
with a wild card?
Hello,
I hope somebody can help me on this.
How do I pass a parameter to a stored procedure with a wild card?
merci beaucoup,
Bay
Message #4 by rrrabe@y... on Tue, 28 Nov 2000 10:29:41 -0000
|
|
thank you for the reply.
However, I already did that both(to the string I'm sending in and on the
SP). No errors were presented but no records were retrieved despite I
entered exactly the same data.
I'm using the Access 2000
What else should I try?
merci,
Bay
Message #5 by rrrabe@y... on Tue, 28 Nov 2000 10:48:46 -0000
|
|
Hello Stephane,
Thank you for your reply. To start with...
I'm using an Access 2000 dB. What I want is to pass the value of the text
box and the wild card to the stored Procedure. Something like this.
search = Request.Form("search")
set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = strConnect
objCommand.CommandText = "qryRec"
objCommand.CommandType = adCmdStoredProc
set objParam = objCommand.CreateParameter("itu-r no", adVariant,
adParamInput, 20)
objCommand.Parameters.Append objParam
objCommand.Parameters("itu-r no") = "%" & search & "%"
set objRs = objCommand.Execute
On the stored procedure side, the criteria is set to "[itu-r no]". I had
also tried setting the criteria to "like '*' & [itu-r no] & '*'". The
field to which this criteria resides is a concatenation of two fields like
this - [rec_num] & "-" & [rec_id_num]as itu-r_no. [rec_num]is text while
[rec_id_num]is an integer. On the parameter side, I had already tried
replacing % with * but to no avail.
In all of these situations (and combinations), no errors were presented.
And no records were either shown despite proper and correct data was
entered on the search text box.
Hoping for your valuable help.
merci,
Bay
Message #6 by Stephane_Dattenny@D... on Tue, 28 Nov 2000 05:23:57 -0600
|
|
Hi
Could you give me the SQL code for your Stored Procedure ?
Did you try this ? :
search = Request.Form("search")
set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = strConnect
objCommand.CommandText = "SELECT <whatever is your sql query> WHERE <your
param> LIKE '%" & search & "%'"
objCommand.CommandType = adCmdText
set objRs = objCommand.Execute
Does this work ?
Best regards / Cordialement
Stephane Dattenny
Dell Computers - EMEA IT - VB and Web developer
Phone: +33 (0)4 99 75 49 88
-----Original Message-----
From: rrrabe@y... [mailto:rrrabe@y...]
Sent: Tuesday, November 28, 2000 11:49 AM
To: ASP Databases
Subject: [asp_databases] RE: How do I pass a parameter to a stored
procedu re with a wild card?
Hello Stephane,
Thank you for your reply. To start with...
I'm using an Access 2000 dB. What I want is to pass the value of the text
box and the wild card to the stored Procedure. Something like this.
search = Request.Form("search")
set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = strConnect
objCommand.CommandText = "qryRec"
objCommand.CommandType = adCmdStoredProc
set objParam = objCommand.CreateParameter("itu-r no", adVariant,
adParamInput, 20)
objCommand.Parameters.Append objParam
objCommand.Parameters("itu-r no") = "%" & search & "%"
set objRs = objCommand.Execute
On the stored procedure side, the criteria is set to "[itu-r no]". I had
also tried setting the criteria to "like '*' & [itu-r no] & '*'". The
field to which this criteria resides is a concatenation of two fields like
this - [rec_num] & "-" & [rec_id_num]as itu-r_no. [rec_num]is text while
[rec_id_num]is an integer. On the parameter side, I had already tried
replacing % with * but to no avail.
In all of these situations (and combinations), no errors were presented.
And no records were either shown despite proper and correct data was
entered on the search text box.
Hoping for your valuable help.
merci,
Bay
|
|
|
View Cart
