Working with stored procedure in vb.net

Question

0.00/5 (No votes)

See more:

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:

SQL

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 &gt; 0 AND @diffdate &lt; 7)
                           BEGIN
         
                        SET @o_daysexpire = @diffdate 
                        RETURN 3 -- notice of expiration
                 
                     END
                          
                  ELSE
                       BEGIN
                        IF (@diffdate &lt;= 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 THE HELP

Posted 28-Oct-10 21:08pm

Nerv101

Updated 28-Oct-10 22:42pm

Dalek Dave

v3

Add a Solution

Comments

Dalek Dave 29-Oct-10 4:42am

Edited for Grammar and Readability.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RECSEN · Answer 1 · 2010-10-28T22:05:00

VB

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()

Rajesh Anuhya · Answer 2 · 2010-10-28T22:49:00

Solution 2

Here is the Link for you...

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

Posted 28-Oct-10 22:49pm

Rajesh Anuhya