Solved

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

Posted on 2008-06-10
18
994 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]
Comment Utility
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]
Comment Utility
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
Comment Utility
angelIII....do you use QuickPost, or am I just on a serious email notification delay?
0
 
LVL 19

Expert Comment

by:Melih SARICA
Comment Utility
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]
Comment Utility
@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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>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
Comment Utility
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]
Comment Utility
I don't know how this could be done using the Dataadapter ...
0
 
LVL 18

Assisted Solution

by:jcoehoorn
jcoehoorn earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Use my code.
0
 

Author Comment

by:DanGettel
Comment Utility
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
Comment Utility
Looks fine.
0
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
Then you should close the question and assign jcoehoorn the points....
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now