How to determinate if ExecuteNonQuery sucessful

[ad]

I use the codes below;
SqlCommand myCommand = new SqlCommand(sSql, cnn);
cnn.Open();
myCommand.ExecuteNonQuery();

How to determinate if ExecuteNonQuery sucessful?

 

[Guest]

ad,

ExecuteNonQuery returns the number of rows affected. You can test that value
to determine if the command was successful. Something like:

Dim rowsAffected As Integer
rowsAffected = myCommand.ExecuteNonQuery()

If rowsAffected = 0 Then
MsgBox "Unsuccessful"
Else
MsgBox "Successful"
End If

Kerry Moorman

 

[ad]

But my command is Alter statement, it always return -1

 

[Jim Hughes]

Failed DDL commands will throw an exception, so wrap it in a try catch

 

[William \(Bill\) Vaughn]

You can also pass back information about the success or failure of any
server-side executable via the InfoMessage event. This returns low-sev
RAISERROR and PRINT messages. You can also set OUTPUT parameters in stored
procedures to return anything you choose. These can be inspected post
execution.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Jim Hughes]

Bill, while I agree that InfoMessage could be used to pass info back,
wouldn't try catch still be needed?

Running the code below generates this output when "alter table customerss
add column gender (c)" (note the extra S).

InfoMessage and State don't provide any info when the error occurs, only the
Exception is generated.

System.Data.StateChangeEventArgs Closed Open
A first chance exception of type 'System.Data.SqlClient.SqlException'
occurred in System.Data.dll
ERROR: Incorrect syntax near the keyword 'column'.
System.Data.StateChangeEventArgs Open Closed

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Using conn As New
SqlClient.SqlConnection("server=(local);database=northwind;Integrated
Security=SSPI")
AddHandler conn.InfoMessage, AddressOf conn_InfoMessage
AddHandler conn.StateChange, AddressOf conn_StateChange
Try
conn.Open()
Dim cmd As New SqlClient.SqlCommand(TextBox1.Text, conn)
cmd.ExecuteNonQuery()
Catch ex As Exception
Trace.WriteLine("ERROR: " & ex.Message)
Finally
conn.Close()
RemoveHandler conn.InfoMessage, AddressOf conn_InfoMessage
RemoveHandler conn.StateChange, AddressOf conn_StateChange
End Try
End Using
End Sub

Private Sub conn_InfoMessage(ByVal sender As Object, ByVal e As
System.Data.SqlClient.SqlInfoMessageEventArgs)
Trace.WriteLine(e.Message)
End Sub

Private Sub conn_StateChange(ByVal sender As Object, ByVal e As
System.Data.StateChangeEventArgs)
Trace.WriteLine(String.Format("{0} {1} {2} ", e.ToString,
e.OriginalState.ToString, e.CurrentState.ToString))
End Sub
End Class

 

[William \(Bill\) Vaughn]

Yes, and no. One should always have a Try Catch to trap the unexpected
issues, but the Infomessage event can be a good feedback channel. ADO.NET
2.0 supports the ability to change the level at which an infomessage is
considered an exception.
You must have been a C# developer in a prior life. VB.NET can generate the
event prototypes for you... you don't need the add handlers unless you get
paid by the line.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Jim Hughes]

I had not noticed the FireInfoMessageEventOnUserErrors property on the 2.0
Connection object before.

"When you set FireInfoMessageEventOnUserErrors to true, errors that were
previously treated as exceptions are now handled as InfoMessage events. All
events fire immediately and are handled by the event handler. If is
FireInfoMessageEventOnUserErrors is set to false, then InfoMessage events
are handled at the end of the procedure."

Adding the line
conn.FireInfoMessageEventOnUserErrors = True
right after the Using statement produced the following output.

And modifying

Private Sub conn_InfoMessage(ByVal sender As Object, ByVal e As
System.Data.SqlClient.SqlInfoMessageEventArgs)
Dim msg As New System.Text.StringBuilder
For Each sqlerr As SqlClient.SqlError In e.Errors
msg.AppendLine(sqlerr.LineNumber)
msg.AppendLine(sqlerr.Procedure)
msg.AppendLine(sqlerr.Message)
msg.AppendLine(sqlerr.Number)
msg.AppendLine(sqlerr.State)
Next
Trace.WriteLine(msg.ToString)
End Sub

does produce some valuable output. Thanks for the tip!

as far as C# goes, yes I do go both ways, the boss says that scope of
variables MUST be limited, so having module level conn objects would not be
a good idea I also should have checked that conn is not nothing.