vb.net 从没有输出参数的存储过程中获取返回值

get return value from stored procedure without output parameter

提问by vbNewbie

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.

我有一个 vb.net 应用程序，它使用应该返回一个值的存储过程将记录插入到 db 表中。这个存储过程是由其他人设置的，最初链接到一个 web 服务，我的应用程序通过该服务进行插入并在返回的 xml 中获取返回值。我现在可以访问 db 表，但不确定如何在我的 vb.net 方法中接收返回值。

SQl stored procedure snippet;

SQL存储过程片段；

     @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

和我的 vb.net 代码来做插入

        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

回答by amit_g

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

您可以使用ExecuteScalar来获取该值。ExecuteNonQuery返回受影响的行数，而您想要获取上次选择生成的值。您也可以使用ExecuteReader，但这在您的 SP 可能返回更多列和/或更多行时很有用。

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