CODEWITHSUNDEEP
vb.netsql-server-2008stored-procedures
vbNewbie
vbNewbie

Reputation: 3345

get return value from stored procedure without output parameter

I have a vb.net application that inserts records into a db table using a stored procedure that is supposed to return a value. This stored procedure was setup by someone else and initially was linked to a webservice through which my application made the insert and got the return value in the returned xml. I now have access to the db table and not sure how to receive the return value in my vb.net method.

SQl stored procedure snippet;

     @urlname varchar(500),
@siteid varchar(16),
@origin varchar(50),
@queryid varchar(25)
  AS
SET NOCOUNT ON;
declare @cnt int
declare @serverip varchar(16)
declare @mincnt int
declare @siteservercnt int
select @cnt=COUNT(*) from sites
      where urlname=@urlname
if @cnt = 0
    begin
    insert into sites (urlname,siteid,exported,origin,queryid)
    values(@urlname,@siteid,1,@origin,@queryid)
    select @siteservercnt = COUNT(*) from siteserverip where siteid=@siteid
    if @siteservercnt=0
       begin
            select top 1 @mincnt=COUNT(*),@serverip=serverip from siteserverip
        group by serverip
        order by COUNT(*)
        select top 1 @mincnt=sitecount,
                         @serverip=serverip from serveripcounts
        order by sitecount
        insert into siteserverip values(@siteid,@serverip)
        update serveripcounts set sitecount=sitecount+1
        where serverip=@serverip
        end
    end
SELECT siteid from sites
where urlname=@urlname
return

and my vb.net code to do the insert

        CommandObj.CommandText = "Getsite"
        CommandObj.CommandTimeout = 90

        Dim newUrl As String = String.Empty

        CommandObj.Parameters.Clear()

        Dim m_param As SqlParameter

        m_param = CommandObj.Parameters.Add("@urlname", SqlDbType.VarChar, 500)
        m_param.Direction = ParameterDirection.Input
        m_param.Value = name


        m_param = CommandObj.Parameters.Add("@siteid", SqlDbType.VarChar, 16)
        m_param.Direction = ParameterDirection.Input
        m_param.Value = siteid

        m_param = CommandObj.Parameters.Add("@origin", SqlDbType.VarChar, 50)
        m_param.Direction = ParameterDirection.Input
        m_param.Value = method

        m_param = CommandObj.Parameters.Add("@queryId", SqlDbType.VarChar, 25)
        m_param.Direction = ParameterDirection.Input
        m_param.Value = forumID


        Dim recordsAffected As Integer = CommandObj.ExecuteNonQuery

Upvotes: 0

Views: 8251

Answers (1)

amit_g
amit_g

Reputation: 31270

You can use ExecuteScalar to get that value. ExecuteNonQuery returns number of rows affected while you want to get the value generated by last select. You could use ExecuteReader as well but that is useful when your SP might be returning more columns and/or more rows.

'Populate first column and first row value in siteID
Dim siteID As Integer = CommandObj.ExecuteScalar

Upvotes: 2

Related Questions