Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1030
  • Last Modified:

How to access @@Rowcount value from stored procedure using ADO.NET

Here is a snipit of a stored procedure that I have created:

@FirstName varchar (50),
@LastName varchar (50),
@Email varchar (50)

DECLARE @RowCount int

UPDATE TOP (1) myTable
SET
FirstName = @FirstName,
LastName = @LastName,
Email = @Email,
WHERE FirstName IS NULL AND  Type = 33

SET @RowCount = @@ROWCOUNT
RETURN @RowCount

It is my understanding (and I could be entirely wrong here) that I cannot access @@ROWCOUNT directly in my VB code and that I needed to create another variable, which is why I created @RowCount.

Now I am wondering what I need to do to access this value using VB.NET.

Thanks.
0
DanGettel
Asked:
DanGettel
  • 5
  • 5
  • 4
  • +2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
add a OUTPUT parameter to the procedure:
CREATE PROCEDURE update_user (
@FirstName varchar (50),
@LastName varchar (50),
@Email varchar (50),
@rowcount int OUTPUT
)
AS
BEGIN
UPDATE TOP (1) myTable 
SET 
FirstName = @FirstName, 
LastName = @LastName, 
Email = @Email, 
WHERE FirstName IS NULL AND  Type = 33 
SET @RowCount = @@ROWCOUNT 
END

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
now, in your .net code, all you have to do is to add that additional parameter to the sqlcommands' parameter definition (you might want to show the code you have right now), and after the SqlCommand.ExecuteNonQuery(), that parameter has the value of 0 or 1 ...
0
 
chapmandewCommented:
angelIII....do you use QuickPost, or am I just on a serious email notification delay?
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
Melih SARICACommented:
When u use executenonquery function of sqlcommand class return value is the affactedrow  count..

Melih SARICA

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
@chapmandew:
I do use QuickEE, yes... but doing 2 other things in parallel :)

@non_zero:
that's a good remark!
however, I personally don't recommend using it for stored procedures, as it will only return the last @@rowcount, and that might not be the real stuff... (for example if you have triggers or other statement after the one you are interested in the procedure)
0
 
chapmandewCommented:
OK, good deal.  I was wondering....I am sure there is a delay in the email.  Also am sure that you're really fast.  :)
0
 
Joel CoehoornDirector of Information TechnologyCommented:
I'd use .ExecuteScalar() and build the procedure this way:
REATE PROCEDURE update_user (
@FirstName varchar (50),
@LastName varchar (50),
@Email varchar (50),
@rowcount int OUTPUT
)
AS
BEGIN
UPDATE TOP (1) myTable 
SET 
FirstName = @FirstName, 
LastName = @LastName, 
Email = @Email, 
WHERE FirstName IS NULL AND  Type = 33 
 
SELECT @@ROWCOUNT 
END

Open in new window

0
 
Joel CoehoornDirector of Information TechnologyCommented:
Oops: missed the first character and forgot to remove the extra line:
CREATE PROCEDURE update_user (
@FirstName varchar (50),
@LastName varchar (50),
@Email varchar (50)
)
AS
BEGIN
UPDATE TOP (1) myTable 
SET 
FirstName = @FirstName, 
LastName = @LastName, 
Email = @Email, 
WHERE FirstName IS NULL AND  Type = 33 
 
SELECT @@ROWCOUNT 
END

Open in new window

0
 
DanGettelAuthor Commented:
Here is some of the code behind that I previously created.  You will see that I used rowsAffected, but I heard that this was not the best way to go.  Any help to access @@ROWCOUNT would be great.

    Protected Sub UpdateButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles UpdateButton.Click

        Dim GettelDataSource As New SqlDataSource()
        GettelDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("MyConnectionString").ToString()

        GettelDataSource.UpdateCommandType = SqlDataSourceCommandType.StoredProcedure
        GettelDataSource.UpdateCommand = "update_user"

        GettelDataSource.UpdateParameters.Add("FirstName", FirstNameTextBox.Text)
        GettelDataSource.UpdateParameters.Add("LastName", LastNameTextBox.Text)
        GettelDataSource.UpdateParameters.Add("Email", EmailAddressTextBox.Text)

        Dim rowsAffected As Integer = 0

        Try
            rowsAffected = GettelDataSource.Update()

        Catch ex As Exception

           
            Server.Transfer("update_problems.aspx")

        Finally
            GettelDataSource = Nothing

        End Try

        If rowsAffected <> 1 Then
            Server.Transfer("update_problems.aspx")
        Else

'If it gets to this point (which it should since I used TOP (1) ) it sends a notification message.

            Server.Transfer("update_confirm.aspx")
        End If

    End Sub
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>rowsAffected = GettelDataSource.Update()
will tell you how many rows the datasource has submitted a UpdateCommand request for...
it won't be able to tell how many rows have really been updated.

0
 
DanGettelAuthor Commented:
I have heard that was the casewith rowsAffected, but I struggled to come up with what I already have.  What is the properway to change things (so that I can access the @@ROWCOUNT value)?

Any suggestions?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I don't know how this could be done using the Dataadapter ...
0
 
Joel CoehoornDirector of Information TechnologyCommented:
Using the procedure I posted:
Protected Sub UpdateButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles UpdateButton.Click
 
 
    Dim rowsAffected As Integer
    Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ToString(), _
          cmd As New SqlCommand("update_user", conn)
 
        'These should match the names used in the stored procedure'
        cmd.Parameters.AddWithValue("@FirstName", FirstNameTextBox.Text)
        cmd.Parameters.AddWithValue("@LastName", LastNameTextBox.Text)
        cmd.Parameters.AddWithValue("@Email", EmailAddressTextBox.Text)
 
        Try
            conn.Open()
            rowsAffected = cmd.ExecuteScalar()
        Catch
            rowsAffected = 0
        End Try        
    End Using
 
    If rowsAffected <> 1 Then
        Server.Transfer("update_problems.aspx")
    End If
 
'If it gets to this point (which it should since I used TOP (1) ) it sends a notification message.'
 
    Server.Transfer("update_confirm.aspx")
End Sub

Open in new window

0
 
DanGettelAuthor Commented:
Having never used ExecuteScalar() before am I supposed to be using the most recent code provided by jcoehoorn or I am supposed to be combining it with what I already had?
0
 
Joel CoehoornDirector of Information TechnologyCommented:
Use my code.
0
 
DanGettelAuthor Commented:
For those who might look at this later on I ended up using the stored procedure that jcoehoorn suggested  with the SELECT @@ROWCOUNT.  Here it is:

CREATE PROCEDURE update_user
@FirstName varchar (50),
@LastName varchar (50),
@Email varchar (50)

AS
BEGIN
UPDATE TOP (1) myTable
SET
FirstName = @FirstName,
LastName = @LastName,
Email = @Email
WHERE FirstName IS NULL AND  Type = 33
 
SELECT @@ROWCOUNT
END

As far as the code behind goes I ended up using what jcoehoorn suggested with a few modifications.  First I needed to be sure that I imported System.Data.SqlClient.  I then needed to tweak some of the SqlConnection and SqlCommand stuff to get things to work.  I dont know if I did anything that was inappropriate or incorrect (please let me know if you see something), but here it is:

    Protected Sub UpdateButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles UpdateButton.Click
 
        Dim rowsAffected As Integer
        rowsAffected = 0
 
        Dim conn As SqlConnection
        Dim comm As SqlCommand
        Dim connectionString As String = _
            ConfigurationManager.ConnectionStrings( _
            "MyConnectionString").ConnectionString
        conn = New SqlConnection(connectionString)
        comm = New SqlCommand("update_user", conn)
        comm.CommandType = System.Data.CommandType.StoredProcedure
 
        comm.Parameters.AddWithValue("@FirstName", FirstNameTextBox.Text)
        comm.Parameters.AddWithValue("@LastName", LastNameTextBox.Text)
        comm.Parameters.AddWithValue("@Email", EmailAddressTextBox.Text)
 
        Try
            conn.Open()
            rowsAffected = comm.ExecuteScalar()
        Catch
            Server.Transfer("update_problems.aspx")
        Finally
            conn.Close()
        End Try
 
        If rowsAffected <> 1 Then
            Server.Transfer("update_problems.aspx")
        Else
             'If it gets to this point (which it should since I used TOP (1) ) it sends a notification message.'
            Server.Transfer("update_confirm.aspx")
        End If
 
    End Sub
0
 
Joel CoehoornDirector of Information TechnologyCommented:
Looks fine.
0
 
chapmandewCommented:
Then you should close the question and assign jcoehoorn the points....
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 5
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now