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

How to determinate if ExecuteNonQuery sucessful?

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" wrote:

> I use the codes below;
> SqlCommand myCommand = new SqlCommand(sSql, cnn);
> cnn.Open();
> myCommand.ExecuteNonQuery();
>
> How to determinate if ExecuteNonQuery sucessful?
>
>
>

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

"Kerry Moorman" <(E-Mail Removed)> ¼¶¼g©ó¶l¥ó·s»D:9529E6B3-8628-45E9-99A9-(E-Mail Removed)...
> 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" wrote:
>
>> I use the codes below;
>> SqlCommand myCommand = new SqlCommand(sSql, cnn);
>> cnn.Open();
>> myCommand.ExecuteNonQuery();
>>
>> How to determinate if ExecuteNonQuery sucessful?
>>
>>
>>

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


"ad" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> But my command is Alter statement, it always return -1
>
> "Kerry Moorman" <(E-Mail Removed)>
> ¼¶¼g©ó¶l¥ó·s»D:9529E6B3-8628-45E9-99A9-(E-Mail Removed)...
>> 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" wrote:
>>
>>> I use the codes below;
>>> SqlCommand myCommand = new SqlCommand(sSql, cnn);
>>> cnn.Open();
>>> myCommand.ExecuteNonQuery();
>>>
>>> How to determinate if ExecuteNonQuery sucessful?
>>>
>>>
>>>
>
>

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.
__________________________________

"ad" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I use the codes below;
> SqlCommand myCommand = new SqlCommand(sSql, cnn);
> cnn.Open();
> myCommand.ExecuteNonQuery();
>
> How to determinate if ExecuteNonQuery sucessful?
>
>

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" <(E-Mail Removed)> wrote in message
news:e5Y$(E-Mail Removed)...
> 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.
> __________________________________
>
> "ad" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>I use the codes below;
>> SqlCommand myCommand = new SqlCommand(sSql, cnn);
>> cnn.Open();
>> myCommand.ExecuteNonQuery();
>>
>> How to determinate if ExecuteNonQuery sucessful?
>>
>>
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:e5Y$(E-Mail Removed)...
>> 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.
>> __________________________________
>>
>> "ad" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>>I use the codes below;
>>> SqlCommand myCommand = new SqlCommand(sSql, cnn);
>>> cnn.Open();
>>> myCommand.ExecuteNonQuery();
>>>
>>> How to determinate if ExecuteNonQuery sucessful?
>>>
>>>
>>
>>
>
>

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.


"William (Bill) Vaughn" <(E-Mail Removed)> wrote in message
news:uHqi%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:e5Y$(E-Mail Removed)...
>>> 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.
>>> __________________________________
>>>
>>> "ad" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>>I use the codes below;
>>>> SqlCommand myCommand = new SqlCommand(sSql, cnn);
>>>> cnn.Open();
>>>> myCommand.ExecuteNonQuery();
>>>>
>>>> How to determinate if ExecuteNonQuery sucessful?
>>>>
>>>>
>>>
>>>
>>
>>
>
>