Why wont my recordset pass the value to my query. 3

[bryant89]

I have an sql query that will execute fine if tested in sql server manager.

Once I add a recordset to my asp page and set the appropriate paramaters only one of the parameters gets passed to the query. here is my buttonclick method code

Sub btnSearch_onclick()
'purpose: set the parameters for the recordset and cause the
' recordset to requery to get the desired results
dim key
dim disc

key=txtKey.value
disc=cmbDiscipline.getValue()
rcsLeadSearch.setParameter 1,key
rcsLeadSearch.setParameter 2,disc

rcsLeadSearch.requery()

if rcsLeadSearch.getCount()<1 then
searchMessage &quot;Current search found no matches. Please adjust your search criteria and try again.&quot;
else
searchMessage &quot;&quot;
end if
End Sub

the parameters in the recordset are set as follows

In @vkeyword varchar 70 Request(&quot;txtKey&quot
In @nDisc int 0 Request(&quot;cmbDiscipline&quot

I can enter a keyword and that word gets passed to the query through the asp page. and the appropriate results are returned, but the discipline value is not being passed.
but the query works fine if tested on its own with out using the asp page.
If I reload the page I can even retrieve the number that was set in the recordset like this
txtProv2.value=rcsLeadSearch.getParameter(2)
and it shows the appropriate number that I selected in the cmbDiscipline text box. so the paramater is being set in the recordset but it isnt sending it to the query. The data types are the same in the recordset and the query. I have lost all hope with this. It should be working

Someone please help me

 

[palbano]

bryant,

I remember that using parameterized stored procedures have many catches. There is a great article on MSDN by Mike Pope from a couple of years ago that covers the topic completely. You should read it carefully. I can't be certain but my memory tells me you need to do the following:

*Have the Recordset.auto-open property off
*Set the parameters
*Open the Recordset
*Display the results

In that order, but I would read the article if I were you.

Hope this helps
-pete

 

[bryant89]

Thanks very much Pete, I will check into it and let you know how it goes.
Now to find this article

 

[bryant89]

well I cant find the article. I dont understand why this will not work anyway I copied the exact same page and it works fine but when I use different recordset querys which are essentially the same as the ones that are already working except for a change in names and tables it will not work

hope this makes sense
Bryant

 

[palbano]

http://msdn.microsoft.com/library/techart/msdn_parameters1.htm

-pete

 

[bryant89]

thanks Pete.

I will check the article out. I hope it helps

 

[tmishue]

Hey,

Wondering how your problem worked out. I've got the same one and the article doesn't help me much. I am having the toughest time getting details on how to pass a parameter to the DTC Recordset and based on that parameter requery my SQL database. I have got to be completely missing something. Maybe I am not declaring my variables correctly or something? I am pretty new to InterDev, and ASP. I am learning it totally on my own and I am getting in a crunch need to get my Recordset to take in a variable. Can someone please help???????

Thanks A Bunch!

A voice crying in the wilderness, TMishue

 

[tmishue]

Hey,

Wondering how your problem worked out. I've got the same one and the article doesn't help me much. I am having the toughest time getting details on how to pass a parameter to the DTC Recordset and based on that parameter requery my SQL database. I have got to be completely missing something. Maybe I am not declaring my variables correctly or something? I am pretty new to InterDev, and ASP. I am learning it totally on my own and I am getting in a crunch need to get my Recordset to take in a variable. Can someone please help???????

Thanks A Bunch!

A voice crying in the wilderness, TMishue

 

[Guest_imported]

you defined your second parameter size as zero.
may be its real size should be defined ?

 

[bryant89]

thanks for the help guys I got it working. Thanks alot.
Bryant

 

[computergeek]

Hello,

There is a Microsoft bug documented within Microsoft's Knowledge base. See Q191149. The problem is when you use the DTC scripting platform and try use Setparameter or Getparameter. Such as:

recordset1.setParameter(1, 50) - THIS FAILS?!#

Within the article it suggests that you use setSQLText instead. Note: Use the onbeforeopen event for the recordset to set the the SQLTEXT for your recordset.


Sub rsCompanies_onbeforeopen()
rsCompanies.setSQLText &quot;Select * from company ...
End Sub


I hope this help... it drove me crazy for a few hours!

Computergeek

 

[MerlinB]

The original code at the top of this thread simply needed to remove the requery, and put an (if open) close before the parameter setting, and an open after it. New parameters are only used on a closed recordset.

I have not had any problems with the set/getParameter methods. I would refrain from the setSQLText because then you have to check the quotes within strings - and string handling in VB is fairly cumbersome.

I do sometimes include a dummy WHERE (1=0) clause in the SQL, then use the 'replace' vb command to replace the (1=0) with the actual where clause:

strSQL = rsRS.getSQLText()
strSQL = replace(strSQL, &quot;1=0&quot;, blaa...)
rsRS.setSQLText strSQL

well - at least you can confirm the columns at design-time

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)