January 27, 2003 at 9:52 am
Hi,
I'm a newbe in sqlserver and I don't know much about VB. Here's my very basic issue:
I'd like to find out how to handle in a VB program cases where the Integrity Ref. is violated, such as: inserting a record with a foreign key not present in the parent table (primary key).
Can anyone show me the lines of code necessary to handle this situation (as I suspect the system will throw an error) or put me in the right direction to start coding?
Thanks in advance.
M71
January 30, 2003 at 8:00 am
This was removed by the editor as SPAM
January 30, 2003 at 10:25 am
If you are doing an insert in a VB program, use an ADO Command object, setting up appropriate parameters for the insert stored procedure, then issuing a call to the Command's .Execute method, passing in the adExecuteNoRecords argument. An Error Handling section should encapsulate catching all errors from the Execute method, which will throw an error passed from SQL Server if a Primary Key is violated. For instance...a very simple example:
Sub InsertRecord(ByVal lngRecordID as Long, ByVal sRecordData as String)
On Error GoTo Routine_Err
Dim oConn as ADODB.Connection
Dim oCmd as ADODB.Command
Set oConn = New ADODB.Connection
Set oCmd = New ADODB.Command
' Initialize connection object with
' appropriate connection string, etc...
With oConn
.ConnectionString = "your connection..."
.Open
End With
With oCmd
.ActiveConnection = oConn
.CommandType = ADODB.adCmdStoredProc
.CommandText = "usp_MyProc" 'Name of SP
.Parameters.Append .CreateParameter("@ID", adInteger, adParamInput, , lngRecordID)
.Parameters.Append .CreateParameter("@Data", adChar, adParamInput, LEN_DATA_FLD, sRecordData) ' LEN_DATA_FLD should be length of SQL Server data type.
.Execute , , adExecuteNoRecords
.ActiveConnection = Nothing
End With
Set oCmd = Nothing
Set oConn = Nothing
Exit Function
Routine_Err:
If Instr(Err.Description, "Primary Key") <> 0 Then
' Do Something about PK Violation error
Else
' Do something about everything else...
End If
End Function
I f hope this gets you started...
January 30, 2003 at 10:34 am
One suggestion / comment : When checking the error, you chould inspect the ADODB.Connection.Errors collection (if it is not Nothing) as it can contain multiple errors and SQL will frequently populate this with more than one error when you run mutiple statements. Even if you error trapping in your stored proc is designed to prevent other staements from running when you execute a statement, what if that statement calls several functions? Each one can raise a different individual error. Most likely you want to raise these back to the user.
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food
January 30, 2003 at 11:26 am
agreed. simple example, that's all. Design depends on whether it's a client-server, web app, etc. and a variety of other factors. I just wanted to get the ball rolling here...
February 5, 2003 at 12:48 am
Just a line to say "thank you" to everybody.
M71
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply