Requery from new setSQLText does not update dropdown

[azurel]

I have this problem that I can't seem to find a solution to. I have a RecordsetDTC that is bound to a table that I am querying. The information for the query is pulled from a dropdown, call it Make, on the page via a request.form call. Another dropdown, Provider, is then populated when the first dropdown changes value. This is done in an onchange sub routine in VBScript. It works the first time the Drop1 is changed. The second dropdown is populated with the correct data and things are fine but when you change Drop1 a second time the data in the second dropdown stays the same.

Here is the code I am using, hopefully I am just missing something because I am new to all this.

Sub Provider_onchange()
SQL = Request.Form ("Provider&quot
Recordset2.setSQLText ("SELECT DISTINCT `Model Number`, `Servicer Provider` FROM `Tower Data` WHERE `Servicer Provider` = '" + SQL + "'" )
Recordset2.requery
make.setRowSource
make.setRowSource Recordset2 , "Model Number"
End Sub

Obviously that setSQLText statment is all on one line in my code but I can't fit it on one line in this form. Any ideas on what I am missing?

Thanks,
Steve

 

[GrantReid]

Hi

I've also been battling with this problem for a coupla days and seem to have found a resolution.

lbxAssignee.clear() 'clears content of listbox
rsAssignee.close() ' closes recordset
rsAssignee.setSQLText sSQL ' sets SQL text for recordset
rsAssignee.open() ' opens recordset

This should work

Grant

 

[shepherd]

I'm not really familiar with the design time controls in Interdev, but I do know that at least in VB, the Change event for a dropdown is not fired when you select from the dropdown list - it is fired when you type text into that control, or select one of the values by typing the first few characters on the keyboard. Instead you would link code to the click event for the dropdown. This may apply in your situation as well...?

 

[MerlinB]

Data-Aware drop-downs (combo boxes) and lists are automatically populated as soon as its recordset is opened BUT only if the list is EMPTY - which is why you need the Clear() method.

You rarely need the lists' recordset open after it is populated, so this can be slightly more efficient:

lbxAssignee.clear() 'clears content of listbox
rsAssignee.setSQLText sSQL ' sets SQL text for recordset
rsAssignee.open() ' opens recordset & populates list
rsAssignee.close() ' closes recordset (not needed now)
lbxAssignee.addItem "Select an assignee", -1, 0 'Add prompt to top of list...

Make sure the rsAssignee is not Auto-Opened

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)

 

[Guest_imported]

Hello. I've been unsuccessful at getting the above code to make my combo box work. Below is the code I have been using:

<SCRIPT ID=serverEventHandlersVBS LANGUAGE=vbscript RUNAT=Server>

Sub cboControl_onchange()

If rs.isOpen() then rs.close
sql = &quot;select * from rs Where TestID= &quot; + cboControl.getValue()
rs.setSQLText(sql)
rs.open
End Sub

</SCRIPT>

After making a selection, I receive the following error msg:
ODBC DRIVER does not support the requested properties.
/WeeklyApps/_ScriptLibrary/Recordset.ASP, line 466.

Any help is appreciated....thanks.

 

[MerlinB]

Rene,

Is the cboControl.getValue() returning a number? If not then you need to embed it in single quotes.
sql = &quot;select * from rs Where TestID= '&quot; & cboControl.getValue() & &quot;'&quot;

- Note, it is usual to use & (not +) to join VBscript strings together.

You may find that setting the recordset DTC object with the required SQL as:
select * from rs Where TestID = ?
The DTC object will convert the ? to a parameter.

Your code then becomes:

If rs.isOpen() then rs.close
rs.setParameter 0, cboControl.getValue()
rs.open


If the SQL is stored as a command in the DataEnvironment (set up via the GLOBAL.ASA in visual interdev), then it becomes very easy to test the SQL before trying to use in in one or more web pages.

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)

 

[Guest_imported]

MerlinB, thank you for your response. Yes, my value is not a number - thanks. I went ahead and changed it as suggested. I changed the sql- first I tried the new sql string and then I tried setting the recordset object with the parameter.

Unfortunately,I keep getting the same error:
Miecrosoft OLE DB Provider for ODBC Drivers (0x80040E21)
ODBC driver does not support the requested properties.
_ScriptLibrary/Recordset.ASP

I wanted to tell you that my page properties for DTC are set for a Server platform.
On the recordset control object properties, (on the implementation tab) I also set the scripting platform to server.

The reason I chose those settings is because our app is going to run on both IE and Netscape.

Do you have any other suggestions for me?

Thank you again for helping me.

Rene

 

[MerlinB]

I always use Server Side controls. They are more robust/reliable. [Except in this case, of course].

Firstly, make sure the SQL works by using it outside of the Combo onChange event - with some fixed value for the TestID.

Secondly, turn on the Error and Trace setting at the top of the asp page
@set @debug=true
@set @trace=true
The error may occur at a place that you did not expect. The @debug setting rarely helps, but @trace can be very useful.

Finally, check out msdn (

http://www.msdn.microsoft.com).

(I have looked, but there is not anything that seems to correspond to the issue.}

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)