How to Use the infomessage event of the sqlconnection class to display the Print message of stored procedure.

(Domestic version)



I think a lotProgramMembers will face a problem... This is how to debug stored procedure. The following are my experiences in studying ADO. net.

Only in VB. NET or C #CodeAdd the sqlconnect_infomessage event, and then read allPrint/ErrorThe message is enough.

Types of returned messages:

Source= The class that return the information.

Error = T-SQL/database error number.

Server = SQL error serverity (please refer to msdn for detail information ).

Line = The line number of the T-SQL that cause the output.

Message= Output/error message.

Private   Sub cn_infomessage () Sub Cn_infomessage ( Byval Sender As   Object , Byval E As System. Data. sqlclient. sqlinfomessageeventargs) Handles CN. infomessage


' Declare Variables

Dim Se As Sqlerror


With Listbox1

' Display message

. Items. Add ( " + Message: "   & E. Message)

. Items. Add ( " + Source: "   & E. Source)


' Check whether sqlerrorcollection is empty

If E. errors. Count >   0   Then

' Read each sqlerror object message

For   Each Se In E. Errors

. Items. Add ( " ? Error: "   & Se. Number)

. Items. Add ( " ? Level: "   & Se. Server)

. Items. Add ( " ? Line: "   & Se. linenumber)

. Items. Add ( " ? Message: "   & Se. Message)

. Items. Add ( " ************************** " )

Next

End   If


' Select the last record

. Selectedindex = . Items. Count -   1

End   With


End sub

The following is the stored procedure code,

Create   Procedure Custordersordersex

(

@ Customerid Nchar ( 5 ),

@ Count Int Output

)

As


-- Debug message

Print   ' Debug start '

Print   ' @ Customerid = '   + @ Customerid


Select Orderid,

Orderdate,

Requireddate,

Shippeddate

From Orders

Where Customerid = @ Customerid

Order   By Orderid


-- Debug message

Print @ Rowcount

Print   ' Debug end '


Set @ Count = @ Rowcount

Set Quoted_identifier Off  


Go

Click "Next" to "Next Generation.