Hi friends!
Could anyone tell me how can I receive messages of SQL Server from VB?
Example, error messages or messages in a @string.
Thanks!

Hi
There are some kind of errors that just terminate the batch , for example
Violation of PRIMARY KEY constraint 'PK__tablename__3F98067B'. Cannot insert
duplicate key in object table
name______________________________________________ __________________________
__________________________________00000000BE5D'.
The statement has been terminated.

So ,wait for SQL Server 2005 and you will be able to use try catch operation







"Pedrito Portugal" <PedritoPortugal (AT) discussions (DOT) microsoft.com> wrote in
message news:EB233CBF-87E3-4ED6-A7D7-1B611EFE9C90 (AT) microsoft (DOT) com...

Pedrito,

Which interface are you using to connect to sql server, ADO?

Take a look to the connection object errors collection. Also see "Err
Object" in the vb documentation.



AMB

"Pedrito Portugal" wrote:

is ADO.
But i dont need to write "return @@Error" in the query to see error message
from VB?
And if I have my own errors in the query in string @MyError, how can I see
this value from VB?
THANKS!

"Alejandro Mesa" wrote:

Uri,

A PK violation doesn't terminate the batch (see below script). But you are perfectly correct that
other types of errors does. More info in www.sommarkog.se, the articles on error handling.

CREATE TABLE t(c1 int PRIMARY KEY)
INSERT INTO T VALUES(1)
INSERT INTO T VALUES(1)
SELECT 'Hello'

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/


"Uri Dimant" <urid (AT) iscar (DOT) co.il> wrote

In vb you have to use "err object" or "connection object errors collection"
to get information about the error. From sql server you use RETURN in the
stored procedure to return an integer value to indicate if the sp succeeded
or failed, you can also use output parameters to return a value to the client
app. To return a custom message from sql server you can use RAISERROR, see it
in BOL.

Example:

-- In QA

use northwind
go

create table t (colA int)
go

create trigger dbo.utr_t_ins on t
for insert
as
if exists(select * from inserted where colA < 0)
begin
raiserror ('Insert a number greater than zero.', 16, 1)
rollback transaction
end
go

create procedure dbo.usp_proc1
@i int
as
set nocount on
insert into t values(@i)

return @@error
go

--drop dbo.usp_proc1
--go

--drop table t
--go

In vb:

Private Sub Command1_Click()
On Error GoTo ErrorHandler

Dim oConn As ADODB.Connection
Dim oCmd As ADODB.Command

Set oConn = New ADODB.Connection
Set oCmd = New ADODB.Command

With oConn
.ConnectionString =
"provider=sqloledb;server=pivotalr5;database=north wind;trusted_connection=yes"
.Errors.Clear
.Open
End With

With oCmd
.CommandText = "dbo.usp_proc1"
.CommandType = adCmdStoredProc
.NamedParameters = True

.Parameters.Append .CreateParameter("ReturnValue", adInteger,
adParamReturnValue)
.Parameters.Append .CreateParameter("@i", adInteger, adParamInput)

Set .ActiveConnection = oConn

.Parameters("@i").Value = 1
.Execute

Debug.Print .Parameters("ReturnValue").Value

.Parameters("@i").Value = -1
.Execute
End With

Exit_Sub:
If Not oConn Is Nothing Then
If oConn.State = adStateOpen Then
oConn.Close
End If
End If

Set oCmd = Nothing
Set oConn = Nothing

Exit Sub
ErrorHandler:
Dim lngErrNo As Long
Dim strErrDesc As String

lngErrNo = Err.Number
strErrDesc = Err.Description

Debug.Print
Debug.Print lngErrNo & " - " & strErrDesc
Debug.Print

Dim oError As ADODB.Error

If Not oConn Is Nothing Then
For Each oError In oConn.Errors
Debug.Print oError.Number & " - " & oError.Description
Next
End If

Resume Exit_Sub
End Sub


Remember to drop the table and sp in northwind db.



AMB

"Pedrito Portugal" wrote:

Hi Alejandro!!!
Thanks!!!!!!
VERY GOOD!!!

"Alejandro Mesa" wrote: