SQL Server Stored Procedure /ASP.net

[alewis25]

I have a procedure I need to get the values out of..I am using outputs...I have no idea why it wont work......I need all values listed in the select part of procedure....

 

 

 

CREATE procedure dbo.Appt_Login_NET

(

@LoginName nvarchar(15),

@Password NvarChar(15),

@UserName nvarchar(15)Output,

@UserPassword nvarchar(15)Output,

@UserClinic nvarchar(3)Output,

@UserTester bit Output

)

as

select

UserName,

UserPassword,

UserClinic,

UserTester

from

Clinic_users

where

UserName = @LoginName

and

UserPassword = @Password

 

GO

 

 

 

my vb.net code to retrive this info is

 

Private Sub Button1_ServerClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.ServerClick

Dim con As New SqlConnection("Server=myserver;database=APPOINTMENTS;uid=webtest;pwd=webtest")

Dim cmd As New SqlCommand

Dim parmuser As SqlParameter

Dim parmus As SqlParameter

Dim parmpass As SqlParameter

Dim parmtest As SqlParameter

Dim struser As String

Dim strpass As String

Dim strclinic As String

Dim strnames As String

Dim tester As String

strpass = txtPass.Value

struser = txtUser.Value

cmd = New SqlCommand("Appt_Login_NET", con)

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@LoginName", struser)

cmd.Parameters.Add("@Password", strpass)

parmus = cmd.Parameters.Add("@UserName", SqlDbType.NVarChar)

parmus.Size = 15

parmus.Direction = ParameterDirection.Output

parmuser = cmd.Parameters.Add("@UserClinic", SqlDbType.NVarChar)

parmuser.Size = 3

parmuser.Direction = ParameterDirection.Output

parmpass = cmd.Parameters.Add("@UserPassword", SqlDbType.NVarChar)

parmpass.Size = 15

parmpass.Direction = ParameterDirection.Output

parmtest = cmd.Parameters.Add("@UserTester", SqlDbType.Bit)

parmtest.Size = 1

parmtest.Direction = ParameterDirection.Output

 

con.Open()

cmd.ExecuteNonQuery()

If Not IsDBNull(cmd.Parameters("@UserName").Value) Then

Label1.Text = cmd.Parameters("@UserName").Value()

Else

Label1.Text = "No Results Found"

End If

 

con.Close()

End Sub

 

Why does this always show as "DBNUll" I get nothing when I debug any of my parm variables.I searched the SQl Server and in Query analyzer instead of the output variables in the procedure being just outputs they are input/outputs...................What does it take to get this working??? Do I need a conversion datatype I would prefer I gain the values and store them in variables......

[PlausiblyDamp]

I notice you aren't setting the parameters to a value inside the procedure - that's why they are returning as null. You would need to do something like (may be a much better way to this though)

 

 CREATE procedure dbo.Appt_Login_NET 
 ( 
 @LoginName nvarchar(15), 
 @Password NvarChar(15), 
 @UserName nvarchar(15)Output, 
 @UserPassword nvarchar(15)Output, 
 @UserClinic nvarchar(3)Output, 
 @UserTester bit Output 
 ) 
 as 
 select 
 UserName, 
 UserPassword, 
 UserClinic, 
 UserTester 
 from 
 Clinic_users 
 where 
 UserName = @LoginName 
 and 
 UserPassword = @Password 

set @UserName = (Select USerName from clinic_users where  UserName = @LoginName 
 and 
 UserPassword = @Password 

set @UserName = (Select UserPAssword from clinic_users where  UserName = @LoginName 
  and 
  UserPassword = @Password 

 

but that is pretty inefficient - is there a reason you are using output parameters in this way? Couldn't you just check the values in the results the stored proc returns? Or even check the rowcount to see if any rows where returned? Possibly use a datareader.

i.e. modify the end of your code to look like

 con.Open() 
dim dr as SQlDataReader
dr = cmd.ExecuteReader() 

if dr.read
 Label1.Text = dr(0)
 Else 
 Label1.Text = "No Results Found" 
 End If 
 
 con.Close()