在vb.net中使用存储过程 [英] Working with stored procedure in vb.net

问题描述

能否给我一个具体的示例代码来访问存储过程.
我有点困惑如何从存储过程中获取输出变量..

这是存储过程:

Can you give me a concrete sample code to access stored procedures.
I''m kinda confused how to fetch out output variables from the stored procedure..

Here is the stored procedure:

ALTER Procedure sp_Login
(
@o_loginid AS CHAR(10) = '',
@o_password AS CHAR(10) = '',
@o_daysexpire AS NUMERIC = 0 OUTPUT,
@c_firstnme as char(10) = '' OUTPUT,
@c_lastnme as char(10) = '' OUTPUT,
@c_userac as char(1) = '' OUTPUT
)

AS

DECLARE @diffdate SMALLINT
DECLARE @date2 DATETIME

IF NOT exists(SELECT c_userid FROM tbl_Users WHERE c_userid = @o_loginid)
              RETURN 1 -- username doesnt exists
ELSE
   BEGIN
      IF Not exists(SELECT c_password FROM tbl_Users WHERE c_password = @o_password AND c_userid = @o_loginid) 
                   RETURN 2  -- incorrect password
       ELSE
         BEGIN

         IF (SELECT b_logintoggle FROM tbl_Users WHERE c_userid = @o_loginid) = 1
                 RETURN 5 -- already login
                 
            ELSE
                 BEGIN
                 IF NOT (SELECT b_withexpiration FROM tbl_Users WHERE c_userid = @o_loginid) = 1 

                     BEGIN

                          SELECT @c_firstnme = c_firstname,
                          @c_lastnme = c_lastname,
                        @c_userac = c_usergroup
                   FROM tbl_Users
                   WHERE c_userid = @o_loginid

                   UPDATE tbl_Users
                         SET b_logintoggle = 1
                   WHERE c_userid = @o_loginid
                    
                      RETURN 0 -- success login

                   END
                 ELSE
                 BEGIN
                                   
                  SELECT   @date2 = d_expirationdate FROM tbl_Users  WHERE c_userid = @o_loginid                       
                  SELECT @diffdate = DATEDIFF(dd,GETDATE(),@date2)
               --   print @diffdate
                  IF (@diffdate > 0 AND @diffdate < 7)
                           BEGIN
         
                        SET @o_daysexpire = @diffdate 
                        RETURN 3 -- notice of expiration
                 
                     END
                          
                  ELSE
                       BEGIN
                        IF (@diffdate <= 0)   
                          RETURN 4 -- expired account
                        ELSE
                          SELECT @c_firstnme = c_firstname,
                                   @c_lastnme = c_lastname,
                                 @c_userac = c_usergroup
                          FROM tbl_Users
                            WHERE c_userid = @o_loginid

                              UPDATE tbl_Users
                            SET b_logintoggle = 1
                            WHERE c_userid = @o_loginid

                          RETURN 0 -- success login
                     END 
                END 
            END  
           END         
    END
   RETURN (0)

THX FOR HELP

THX FOR THE HELP

推荐答案

Sub Submit(Source As Object, E As EventArgs)
  Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")
  Dim objConnection As New SqlConnection(strConnection)
  Dim objCommand As New SqlCommand("sp_CustomersByStateWithCount",objConnection)
  objCommand.CommandType = CommandType.StoredProcedure
  Dim objParameter As New SqlParameter("@region", SqlDbType.NVarChar, 15)
  objCommand.Parameters.Add(objParameter)
  objParameter.Direction = ParameterDirection.Input
  objParameter.Value = txtRegion.text
  Dim objOutputParameter As New SqlParameter("@matches", SqlDbType.Int)
  objCommand.Parameters.Add(objOutputParameter)
  objOutputParameter.Direction = ParameterDirection.Output
  objConnection.Open()
  Dim objDataReader As SqlDataReader
  objDataReader = objCommand.ExecuteReader()
  dgOutput.DataSource = objDataReader
  dgOutput.DataBind()
  objCommand.Connection.Close()
  objCommand.Connection.Open()
  objCommand.ExecuteNonQuery()
  lblRecords.Text = "Matches: " & CInt(objCommand.Parameters(1).Value)
  objConnection.close()

这里是您的链接...

http://www.vbdotnetheaven.com/UploadFile/dclark/StoredProceduresinVBdotNET04082005072716AM ^ ]

Here is the Link for you...

http://www.vbdotnetheaven.com/UploadFile/dclark/StoredProceduresinVBdotNET04082005072716AM/StoredProceduresinVBdotNET.aspx[^]

这篇关于在vb.net中使用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！