Solved

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

Posted on 2008-06-10
18
1,015 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 4
  • +2
18 Comments
 
LVL 143

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 143

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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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 143

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 143

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 143

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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

615 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