Newbie: SQL error handling in VB 
Author Message
 Newbie: SQL error handling in VB

Hi everyone,

I'm executing several INSERT statements and I wanted to handle any
error that might come from them (i.e Duplicate values, null values,
etc...)

Here's an example of what I have done:

- Set db = OpenDatabase("",rdDriverNoPrompt,0,
"ODBC;DSN=test;DATABASE=xpto")
- BeginTrans
- s1 = "insert into table1 select * from table2"
- Set InsValTable1 = db.CreateQueryDef("", s1)
- InsValTable1.Execute
- If InsValTable1.RecordsAffected = 0 Then
-    resp = MsgBox("Error message", vbCritical, "Error box")
-    Rollback
- Else
-    CommitTrans
- End If

This works ok but I need to know what happened so I can send a message
to the user.
Err doesn't work... Its value remains 0 whatever I do.

I would really appreciate if someone could help me with this.

Thanks in advance,
Rui G.



Mon, 01 Aug 2005 02:06:16 GMT  
 Newbie: SQL error handling in VB
Is this DAO? If you're expecting an exception from that code then you need
to set up some error handling. Also, an INSERT from one table to another
where the source table has no rows and therefore nothing will be done -
that's not an exception and RecordsAffected should be zero. You only get
errors from Jet when you violate a table constraint or when it can't find an
object, for example.

--
____________________
Klaus H. Probst, MVP
   http://www.vbbox.com/

Please post/reply to the newsgroup(s)


Quote:
> Hi everyone,

> I'm executing several INSERT statements and I wanted to handle any
> error that might come from them (i.e Duplicate values, null values,
> etc...)

> Here's an example of what I have done:

> - Set db = OpenDatabase("",rdDriverNoPrompt,0,
> "ODBC;DSN=test;DATABASE=xpto")
> - BeginTrans
> - s1 = "insert into table1 select * from table2"
> - Set InsValTable1 = db.CreateQueryDef("", s1)
> - InsValTable1.Execute
> - If InsValTable1.RecordsAffected = 0 Then
> -    resp = MsgBox("Error message", vbCritical, "Error box")
> -    Rollback
> - Else
> -    CommitTrans
> - End If

> This works ok but I need to know what happened so I can send a message
> to the user.
> Err doesn't work... Its value remains 0 whatever I do.

> I would really appreciate if someone could help me with this.

> Thanks in advance,
> Rui G.



Mon, 01 Aug 2005 12:09:40 GMT  
 Newbie: SQL error handling in VB
Hi,

Thanks for the help.

I'll just use ADO instead of DAO, that way I can get the error codes and
messages.
I should've thought of that before...Duh!!!

Thanks again.
Rui G.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Mon, 01 Aug 2005 23:26:43 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. HOW TO HANDLE THE MS.SQL ERRORS BY VB

2. Error handling between SQL Server and VB

3. Error Handling Between SQL Server and VB

4. newbie: Access error handling

5. Newbie Question: VBScript Error handling and Session Timeout

6. Error handling in component - Newbie

7. ADO Error Handling....Newbie Question...

8. newbie error handling problem

9. Error handling newbie, help please

10. Handling Errors in SQL queries

11. Seeking advice on VB6->ADO->SQL Stored Procedure Error Handling

12. Error Handling: SQL Server Transaction Locks

 

 
Powered by phpBB® Forum Software