SQL server DB problem with VB app

[Painkiller]

Hi all,

I have the following problem with a database on a SQL server. the database has two tables, Advice and Advicenotice. The two are linked in the sense that the key of table Advice is a foreign key in table Advicenotice. I use a VB app where for each table a recordset is opened. When I try to delete a record in Advice when there is still a record in Advicenotice with the same foreign key, Vb generates an error (as it should). These errors are not shown however, but I write them to an errorlog and let the app continue. However, after this error, I can't seem to do anything with the recordset Advice, I can't close it or open it, or give it other commands. I get the following error in the errorlog whith the deletion:

-2147217873[Microsoft][ODBC SQL Server Driver][SQL Server]DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_TblAdvicenotice_TblAdvice'. The conflict occurred in database '########', table 'TblAdvicenotice', column 'CaseNr'.

After this the app tries to close the connection, but then the following error is generated:
3219-The operation requested by the application is not allowed in this context.

At first I thought that the recordset had been closed by the database itself after the first error, so I tried to open it after the first error, but this generated another error:

3705
The operation requested by the application is not allowed if the object is open.

So this means the recordset is still open. But why can't I issue other commands to it after the first error??

Anybody knows why the other errors occur and how I can deal with them?

Thanx in advance.

Sujesh

 

[Painkiller]

Already figured out how. After giving a Cancelupdate to the recordset after the first error occurs the recordset closes after all. problem solved!

 

[Phathi]

Hi,

This is a shot in the dark. But I think the reason you get error #1 is because you don't handle your errors.

[on error goto ProcErr]

When the rs generates an error, it semi-corrupts (for lack of a better word), so after writing your error to a file, type [Resume Next] or something.


I assume when you open the app, you establish a connection. Then when you try to manipulate the database (in procedures), you open a new recordset each time

dim rs as New Recordset 'in each proc when req.

or

dim rs as Recordset 'in a bas module
set rs=new adodb.recordset 'in each proc when req.

When opening this rs, you should

rs.open yourDB,,adlockoptimistic

This should prevent Error #2

Did it help?