Solved

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

Posted on 2008-06-10
18
1,007 Views
Last Modified: 2012-08-13
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
Comment
Question by:DanGettel
  • 5
  • 5
  • 4
  • +2
18 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21754905
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21754916
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 21754982
angelIII....do you use QuickPost, or am I just on a serious email notification delay?
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 19

Expert Comment

by:Melih SARICA
ID: 21755086
When u use executenonquery function of sqlcommand class return value is the affactedrow  count..

Melih SARICA

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21755112
@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
 
LVL 60

Expert Comment

by:chapmandew
ID: 21755129
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
 
LVL 18

Expert Comment

by:jcoehoorn
ID: 21755145
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
 
LVL 18

Accepted Solution

by:
jcoehoorn earned 500 total points
ID: 21755153
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
 

Author Comment

by:DanGettel
ID: 21756750
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21756758
>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
 

Author Comment

by:DanGettel
ID: 21756806
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21757030
I don't know how this could be done using the Dataadapter ...
0
 
LVL 18

Assisted Solution

by:jcoehoorn
jcoehoorn earned 500 total points
ID: 21759489
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
 

Author Comment

by:DanGettel
ID: 21763142
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
 
LVL 18

Expert Comment

by:jcoehoorn
ID: 21763150
Use my code.
0
 

Author Comment

by:DanGettel
ID: 21770799
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
 
LVL 18

Expert Comment

by:jcoehoorn
ID: 21771429
Looks fine.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21771468
Then you should close the question and assign jcoehoorn the points....
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question