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(Use rs.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
PROCEDURE [dbo].[UserLogin]
(
@username nvarchar(20) = NULL,
@pwd nvarchar(10) = NULL
)
AS
select Users.UserID, UserName, FirstName, LastName,
dbo.GetUserPermissions(Use
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
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
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.StoredProcedur e
command.Parameters.AddWith Value("@Us erName", UsernameTextBox.Text)
command.Parameters.AddWith Value("@Pw d", PasswordTextBox.Text)
Dim result As Int32
result = CType(command.ExecuteScala r(), 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.
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.StoredProcedur
command.Parameters.AddWith
command.Parameters.AddWith
Dim result As Int32
result = CType(command.ExecuteScala
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.
ASKER
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 .Permissio nTypes DesiredPermission)
{
bool Ret = false;
string delimStr = ",";
char [] delimiter = delimStr.ToCharArray();
try
{
if (PermissionList.Length < 1) { return Ret; }
string[] Permissions = PermissionList.Split(delim iter,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" ,MyNameSpa ce.Permiss ionTypes.P ermission1 ))
{
Response.Redirect("default .aspx");
}
public enum PermissionTypes
{
Permission1 = 1,
Permission2 = 2,
Permission3 = 3,
SubPermission1 = 4,
SubPermission2 = 5,
SubPermission3 = 6
}
public bool ValidatePermission(string PermissionList,MyNameSpace
{
bool Ret = false;
string delimStr = ",";
char [] delimiter = delimStr.ToCharArray();
try
{
if (PermissionList.Length < 1) { return Ret; }
string[] Permissions = PermissionList.Split(delim
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"
{
Response.Redirect("default
}
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Great wotk
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.StoredProcedur
command.Parameters.AddWith
command.Parameters.AddWith
Dim result As Int32
result = CType(command.ExecuteScala
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