Update Query Error and Recordsets

[GrantReid]

I need to update records in a table when clicking on a button.
I have a onclick event which declares my variables and passes these to a Recordset. This function produces the following error; "ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed."

I have included my script below. Hope someone can assist.

Sub btnUpdate_onclick()
Dim sSQL, sCriteria, sName, sCell, sSpeed
Criteria=lbxUpdate.getValue(lbxUpdate.selectedIndex)
sName=txtUpName.value
sCell=txtUpCell.value
sSpeed=txtUpSpeed.value
sSQL="UPDATE Cell SET NAME = '" & sName & "', CELLNO = '" & sCell & "', SPEEDDIAL = '" & sSpeed & "' WHERE (NAME = '" & sCriteria & "')"
rsUpdate.setSQLText sSQL
rsUpdate.open()
End Sub

 

[foxbox]

The trouble has to do with:
rsUpdate.setSQLText sSQL
rsUpdate.open()


Normally you create your page with a database connection, recordset etc, and when you are finished you close connection and recordset (it's good practise, better for performance, etc). So when you press your button, there is no open connection/rs..... You need to do that -again- in your SUB. (and close it!)
br
Gerard

 

[MerlinB]

Why are you doing the update via a recordset?

Set up a data-environment command to contain the desired SQL, with the usual question marks as place holders for the desired data...

UPDATE Cell SET NAME = ?
, CELLNO = ?
, SPEEDDIAL = ?
WHERE (NAME = ?)

Call this updCell_CellNo

In your page you can then have:

Sub btnUpdate_onclick()
Dim sSQL, sCriteria, sName, sCell, sSpeed
sCriteria=lbxUpdate.getValue(lbxUpdate.selectedIndex)
sName=txtUpName.value
sCell=txtUpCell.value
sSpeed=txtUpSpeed.value

thisPage.createDE
DE.updCell_CellNo sName, sCell, sSpeed, sCriteria
End Sub

If you wanted to inspect a return value - say from a stored procedure - then after the
DE.<commandName> [parameters,...]
you could add
intRetCode = DE.Commands(&quot;updCell_CellNo&quot.Parameters(&quot;RETURN_VALUE&quot

a stored procedure could have a number of 'OUTPUT' parameters - use the above technique to get the result from each parameter.

The main benefit of this technique is that you do not have to care about quotes and other dodgy characters in the SQL. So if the cell NAME was &quot;FISH'S CELL&quot; then you would need to 'escape' the quote marks - or use the method shown above.
The second benefit is that the SQL is taken out of the web page - which for long SQL can be a real bonus.
And thirdly, you can now use this SQL in other web pages, if you wish.

Oh dear, this has turned into a bit of a lecture. Hope you don't mind - it is meant to be useful for you.

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)