Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Retrieving values from a database, using a web service.

Posted on 2009-02-17
5
Medium Priority
?
208 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

0
Comment
Question by:aneilg
  • 3
  • 2
5 Comments
 
LVL 22

Accepted Solution

by:
pivar earned 2000 total points
ID: 23658647
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

0
 

Author Comment

by:aneilg
ID: 23659771
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
0
 
LVL 22

Expert Comment

by:pivar
ID: 23659915
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

0
 

Author Comment

by:aneilg
ID: 23659962
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
0
 
LVL 22

Assisted Solution

by:pivar
pivar earned 2000 total points
ID: 23660178
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


0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question