Ado command object returns method 'Execute' of object '_Connection' failed

Error: method 'Execute' of object '_Connection' failed

Solution: Check the query used while executing the recordset, it may contain keyword used in the database. In my case, my query is:

select languageid, language from masterlanguage order by languageid

The error occurs because I use a keyword in the query, i.d. language.
To fix the error, either change the column name on the database, or use []:

select languageid, [language] from masterlanguage order by languageid

Read More

Operation is not allowed when object is closed

Error: Operation is not allowed when object is closed.

Solution:
1. Check if a recordset or connection is opened before recordset operation is performed.

2. If stored procedure is used while retrieving the data to recordset, REMEMBER to use "SET NOCOUNT ON" at the beginning of the stored procedure. This is a problem which usually occurs when multiple actions are executed in a stored procedure.

3. Again, if stored procedure is used, make sure the stored procedure does return a record. Otherwise, make sure there is a routine in the code checking if the record doesn't return nothing.

e.g.

create procedure dbo.spr_getData
as
set nocount on

select ......

select ......

set nocount off

Read More

ADO: Recordcount return -1

Error : Recordcount May Return -1 while using ADO connection

The problem is ADO defaults to a Server side cursor. You can only use the RecordCount property with a Client side Cursor.

Do something like...
rsMyRecordset.CursorLocation = adUseClient

If I may guess, the reason the RecordCount doesn't work in your code is that the default cursortype, adOpenForwardOnly, of the recordset doesn't support it. ForwardOnly recordsets ("firehose cursors") are very fast when you just want to run through them from top to bottom. So, if you are using adOpenForwardOnly for the cursorType, you may have to change it.

Here's the code:

rs.CursorLocation = adUseClient
rs.Open sql, con, adOpenKeyset, adLockPessimistic

Read More