Link to home
Create AccountLog in
Avatar of ashwin197816
ashwin197816

asked on

SQL Procedure and VB.NET problem

I've the following procedure which validate user name / password in database and return result bellow.
PROCEDURE [dbo].[UserLogin]
 (
    @username nvarchar(20) = NULL,
    @pwd nvarchar(10) = NULL
)
AS
     select Users.UserID, UserName, FirstName, LastName,
                dbo.GetUserPermissions(Users.UserID) as PermissionList  from Users
           where (ltrim(rtrim(username)) = @username)       and (ltrim(rtrim(pwd)) = @pwd)

RESULT :
UserID, UserName, FirstName, LastName,PermissionList
1            xyz               AAA         BBB          1,2,3,4,5

Now i dont understand how can i code in VB. for exmple: i want perticular user to access perticulr modules....i'm pulling my hair.

permission1 = admin
permission2 = Order Entry
permission3= AR
permission4= AP
permission5= Master

SOLUTION
Avatar of llman
llman

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of ashwin197816
ashwin197816

ASKER


Yes, here is my code.

1. I've MAIN form with 4 TABS (Administrator, AR,AP,GL).
2. Database table (1=administrator, 2= AR...so on)

2. The following code calls a procedure and return result permissionlist depends on user permission(1,2...or so on)
Now what i'm trying to do is, lets say user AAA have only AR permission (means permission = 2) then other TABS has to be disable for him.

 Else
            If connection.State = 1 Then connection.Close()
            connection.Open()
            command = New SqlCommand("UserLogin", connection)
            command.CommandType = CommandType.StoredProcedure
            command.Parameters.AddWithValue("@UserName", UsernameTextBox.Text)
            command.Parameters.AddWithValue("@Pwd", PasswordTextBox.Text)
            Dim result As Int32
            result = CType(command.ExecuteScalar(), Int32)
            If result = 0 Then
                'failure
                MsgBox(" Password is not correct, check again .....?", vbCritical + vbOKOnly, "")
                PasswordTextBox.Focus()
                i = i + 1
                If i = 3 Then
                    MsgBox("Sorry, Try again.....", MsgBoxStyle.Exclamation)
                    Application.Exit()
                End If
            ElseIf result = 1 Then
I NEED SOMTHING HERE.
               frmMain.Show()
                frmMain.tbAdmin.Visible = True
            Else
OR HERE
                'success
                frmMain.Show()
                frmMain.tbAdmin.Hide()
            End If
        End If
SOLUTION
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
This is confusing ...
1. If i move PermissionList to the first item then how am i going to verify USERNAME and PASSWORD?
2. The following code givevalidate username and password in Database.
If connection.State = 1 Then connection.Close()
           connection.Open()
           command = New SqlCommand("UserLogin", connection)
           command.CommandType = CommandType.StoredProcedure
           command.Parameters.AddWithValue("@UserName", UsernameTextBox.Text)
           command.Parameters.AddWithValue("@Pwd", PasswordTextBox.Text)
           Dim result As Int32
           result = CType(command.ExecuteScalar(), Int32)
           If result = 0 Then
               'failure
               MsgBox(" Password is not correct, check again .....?", vbCritical + vbOKOnly, "")
               PasswordTextBox.Focus()
               i = i + 1
               If i = 3 Then
                   MsgBox("Sorry, Try again.....", MsgBoxStyle.Exclamation)
                   Application.Exit()
               End If
3. Once it validate it should give me PERMISSIONLIST (1,2,3)
4. Now according to PERMISSIONLIST, i want to visible or invisible tabs.
Please see the following code its written in C#, i found it from one article but dont understand how to convert in VB.net.
public enum PermissionTypes
  {
    Permission1 = 1,
    Permission2 = 2,
    Permission3 = 3,
    SubPermission1 = 4,
    SubPermission2 = 5,
    SubPermission3 = 6
  }

public bool ValidatePermission(string PermissionList,MyNameSpace.PermissionTypes DesiredPermission)
   {

      bool Ret = false;
      string delimStr = ",";
      char [] delimiter = delimStr.ToCharArray();

         try
         {

              if (PermissionList.Length < 1) { return Ret; }

              string[] Permissions = PermissionList.Split(delimiter,100);

              int Permission = (int)DesiredPermission;

              for(int i=0;i <Permissions.Length;i++)
              {
                if (Permission.ToString()  == Permissions[i].ToString())
                {
                   return true;
                }
              }
           
         }
         catch  { }
         return Ret;
   }

// Sample Method Call - 1,3 is hard coded to display visually a delimited list of permissions.
  if (!ValidatePermission("1,3",MyNameSpace.PermissionTypes.Permission1))
{
     Response.Redirect("default.aspx");
}
 
ASKER CERTIFIED SOLUTION
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Great wotk