We help IT Professionals succeed at work.

Retrieving values from a database, using a web service.

Medium Priority
223 Views
Last Modified: 2013-11-25
What I want to do is validate a user that gets passed in from another source.
The user name is taken from below.

strUserID = Request.ServerVariables("HTTP_ ")

I get my strUserID from here eg 'fred'

so basically fred will get passed into the sp, the result retured should indicate if they are a manager, by returning a value of 1 or 0.

I call a sp that needs to pass the strUserID to @ColleagueuserID


ALTER PROCEDURE [dbo].[p_LogInID]

@ColleagueuserID NVARCHAR(50)

AS

SELECT ColleagueUserId, Manager = CAST
((CASE WHEN Manager = 'Y' THEN 1 ELSE 0 END) AS BIT)
FROM t_ColleagueDetails
WHERE ColleagueUserId = @ColleagueuserID

This then should return CollegueUserID & Manager: 'fred' 1.


this is where I am getting confused, because I need to pass in a value to the sp then return a value of 1 = manager or 0 not a manager.


Then I need to take the value of 1 which represents a manager & allow the access to certain controls on my web page.

As follows.

This is what I use to call my webservice.

Dim daWAB As New WAB_DataAccessWS.WAB_DataAccessWS
 
 
daWAB.GenericQuery(Master.mainConnStr, "exec p_LogInID " & strUserID & ", '" & intManager & "'")
 
If intManager = 1 Then
 
            Me.gvwColleagueDetails.Columns(0).Visible = True
            Me.lblAddColleague.Visible = True
            Me.ddlAddColleague.Visible = True
            Me.btnAdd.Visible = True
 
        Else
 
            intManager = 0
 
            Me.gvwColleagueDetails.Columns(0).Visible = False
            Me.lblAddColleague.Visible = False
            Me.ddlAddColleague.Visible = False
            Me.btnAdd.Visible = False
 
        End If
 
 
I have the following web service.
 
 
    'Retrieve UserID from database
    <WebMethod()> _
    Public Function LogInCollUserID(ByVal strConn As String, ByVal ColleagueuserID As String, ByVal Manager As Integer) As Integer
        Return WAB_DataAccess.LogInbCollUserID(strConn, ColleagueuserID, Manager)
    End Function
 
    'Retrieve UserID from database
    Public Shared Function LogInbCollUserID(ByVal strConn As String, ByVal ColleagueuserID As String, ByVal Manager As Integer) As Integer
 
        Dim conn As SqlConnection = New SqlConnection(strConn)
        Dim cmd As SqlCommand = New SqlCommand("exec p_LogInID '" & ColleagueuserID & "', '" & Manager & "'", conn)
 
        Dim result As Integer = 0
 
        conn.Open()
        result = cmd.ExecuteNonQuery()
 
        conn.Close()
 
        Return result
 
    End Function

Open in new window

Comment
Watch Question

Commented:
Hi,

First alter your procedure so it returns the manager value:

declare @manager int
SELECT @manager = CASE WHEN Manager = 'Y' THEN 1 ELSE 0 END
FROM t_ColleagueDetails
WHERE ColleagueUserId = @ColleagueuserID

return @manager

Then alter your .NET code to retrieve that return value:

   'Retrieve UserID from database
    Public Shared Function LogInbCollUserID(ByVal strConn As String, ByVal ColleagueuserID As String, ByVal Manager As Integer) As Integer
 
        Dim conn As SqlConnection = New SqlConnection(strConn)
        Dim cmd As SqlCommand = conn.CreateCommand()

         cmd.CommandType = CommandType.StoredProcedure
         cmd.CommandText = "p_LogInID "

         SqlParameter inparm = cmd.Parameters.Add("@ColleagueuserID", SqlDbType.VarChar)
         inparm.Direction = ParameterDirection.Input
         inparm.Value = ColleagueuserID

         SqlParameter retval = cmd.Parameters.Add("return_value", SqlDbType.Int);
         retval.Direction = ParameterDirection.ReturnValue;

        conn.Open()
        cmd.ExecuteNonQuery()

        Dim result As Integer = cmd.Parameters["return_value"].Value
 
        conn.Close()
 
        Return result
 
    End Function


/peter

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Thanks for that, is exactally, but i get the following error.

Compiler Error Message: BC30108: 'SqlParameter' is a type and cannot be used as an expression.

Source Error:

 

Line 94:         cmd.CommandText = "p_LogInID "
Line 95:
Line 96:         SqlParameter(inparm = cmd.Parameters.Add("@ColleagueuserID", SqlDbType.VarChar))
Line 97:         inparm.Direction = ParameterDirection.Input
Line 98:         inparm.Value = ColleagueuserID
 

Source File: E:\wmapps\webroot\DataAccessLayer_20\App_Code\WAB_DataAccess.vb    Line: 96

Commented:
There seems to be some extra parenthesis.

The line should read
SqlParameter inparm = cmd.Parameters.Add("@ColleagueuserID", SqlDbType.VarChar)

not SqlParameter(inparm = cmd.Parameters.Add("@ColleagueuserID", SqlDbType.VarChar))

and I see I forgot some ; when I converted from c#

         SqlParameter retval = cmd.Parameters.Add("return_value", SqlDbType.Int)
         retval.Direction = ParameterDirection.ReturnValue

Author

Commented:
Thanks for that I am using VB not c#
Really Appreciate your help

i've changed it to the following, but when i remove the parenthesis i get prompted to add it.

        Dim conn As SqlConnection = New SqlConnection(strConn)
        Dim cmd As SqlCommand = conn.CreateCommand()

        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "p_LogInID "

        SqlParameter(inparm = cmd.Parameters.Add("@ColleagueuserID", SqlDbType.VarChar))
        inparm.Direction = ParameterDirection.Input
        inparm.Value = ColleagueuserID

        SqlParameter(retval = cmd.Parameters.Add("Manager", SqlDbType.Int))
        retval.Direction = ParameterDirection.ReturnValue

        conn.Open()
        cmd.ExecuteNonQuery()

        Dim result As Integer = cmd.Parameters("Manager").Value

        conn.Close()

        Return result
Commented:
Prompted in what way?

Or try this

       cmd.Parameters.Add("@ColleagueuserID", SqlDbType. VarChar)
        cmd.Parameters("@ColleagueuserID").Value = ColleagueuserID

       cmd.Parameters.Add("Manager", SqlDbType. Int)
        cmd.Parameters("Manager").Direction = ParameterDirection.ReturnValue

instead of

        SqlParameter(inparm = cmd.Parameters.Add("@ColleagueuserID", SqlDbType.VarChar))
        inparm.Direction = ParameterDirection.Input
        inparm.Value = ColleagueuserID

        SqlParameter(retval = cmd.Parameters.Add("Manager", SqlDbType.Int))
        retval.Direction = ParameterDirection.ReturnValue


Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.