I have two tables in my project - Signup and User wallet tables; the sign up table contains login data and the user wallet contains monetary data of the Admin. There are two users who will be granted access to the site (Admin and Users). The Admin is created
from the start, the “user” is being created by the Admin. After Admin Registers and logs in, the Admin ID and wallet balance is displayed on the web forms of the site. Then Admin will then proceed to the dashboard to create new users the team; after new user
is created it displays in the gridview based on the Admin who created the user(s).
Now I want a situation where a user will log in and since the user is linked to an Admin, the Admin ID and wallet balance who created that user, will be displayed except in the user’s case, the user will not be able to see the link that navigates to the
dashboard and will also not be able to see Deposit button which is on the navbar, only an Admin can see those. However, the user can use paid services or perform activities under the Admin who created the user, thereby effecting changes in the admin’s wallet
balance. Here is the structure of my tables:
SIGN UP TABLE
Key: UserType refers to the type of user on the site (A = Admin and U = User)
USERWALLET TABLE
How should I code this please? That will call Admin “userwallet” when user logs in based on the admin who created the user.
When the user logs in, you could check whether the user is linked to an Admin.
If linked, the page will show the panel including the Admin ID and wallet balance,etc.
If not linked, then the role is an Admin, the page will show the panel including the link that navigates to the dashboard and other content.
You could use the Visible property to control whether to display the panel.
Best regards,
Xudong Peng
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
This is where I have an issue; how do I link the user to the wallet Table where the Admin ID and wallet is?
The data shown above is a simple list of records with Admin and Users types. A one-to-many relationship is required to relate User to Admin. One solution is to add another column, "Admin", to the table. Place the AdminId in each User record to join the
User to the Admin. The admin will have a null value.
One solution is to add another column, "Admin", to the table. Place the AdminId in each User record to join the User to the Admin. The admin will have a null value.
If you checked the first image table there is a column named "CreatedBy" that is where the AdminId is and each user has AdminId related to it. But where the AdminID and wallet balance is displayed from, is from another table named "UserWallet", so is there
any code that will be written to link user and Admin ID and its wallet balance ? such that when a user logs in the wallet account of the Admin (which is in another table) will be linked to the user who logs in, and will be displayed.
I'm confused. Your question amounts to how to get the CreatedBy value from the SignUp table? Get the value when the user logs in. Use the email get the value from the UserWallet table.
SELECT CreatedBy
FROM SignUp
WHERE email = @email and pass = @pass
Another option is getting the amount from the UserWallet table using a join and the user's email address.
SELECT amount
FROM UserWallet AS w
INNER JOIN SignUp AS s ON w.email = s.CreatedBy
WHERE s.email = @email
So what I want achieve is when the user (tyler@yahoo.com) logs in, the ID and wallet balance of Admin (check@gmail.com) will be displayed and the user will not be able to do some of
the things the Admin does, which are see deposit button to make deposit or see dashboard link too. But user can be able to use funds in the Admin's wallet and Admin can know if funds has been used.
Please what does the "w", "s", "w.email" and "s.CreatedBy" mean? do I have to declare them?
So what I want achieve is when the user (tyler@yahoo.com) logs in, the ID and wallet balance of Admin (check@gmail.com) will be displayed
I provided a query that gets the admin wallet amount using a user's email address. Of course this only works for a User account because the CreatedBy is null on admin accounts.
SELECT amount
FROM #UserWallet AS w
INNER JOIN #Signup AS s ON w.email = s.CreatedBy
WHERE s.email = 'Simon@gmail.com'
With your current configuration, if the login is an Admin then you'll get the amount from the WalletTable.
DECLARE @userType VARCHAR(1)
DECLARE @email VARCHAR(64) = 'ukosimons@gmail.com'
SELECT @userType = s.UserType FROM #Signup AS s WHERE s.email = @email
IF(@userType = 'U')
BEGIN
SELECT amount
FROM #UserWallet AS w
INNER JOIN #Signup AS s ON w.email = s.CreatedBy
WHERE s.email = @email
END
ELSE
BEGIN
SELECT amount FROM #UserWallet AS w WHERE w.email = @email
END
Another option is to set the CreatedBy to the admin's email rather than NULL
georgeakpan233
and the user will not be able to do some of the things the Admin does, which are see deposit button to make deposit or see dashboard link too. But user can be able to use funds in the Admin's wallet and Admin can know if funds has been used.
This is code that you have to write. The community cannot comment on a "good" approach because we have no idea how you custom security works. It's confusing why you are unable to identity a user or admin.
IMHO, the design presented is very poor and why you are having trouble. I recommend using the security API, Identity, that comes with ASP.NET rather than building your own if you do not have the experience.
georgeakpan233
Please what does the "w", "s", "w.email" and "s.CreatedBy" mean? do I have to declare them?
They are standard SQL aliases so you are not forced to type the entire table name.
How will you insert userId into another column of the same table. The column is highlighted in yellow area, and also insert it into wallet table (also highlighetd in yellow)?
Honestly, this is the first time I am seeing this. I have not seen a situation where userId is inserted into two separate columns of the same table (UserId, CreatedBy)
Honestly, this is the first time I am seeing this. I have not seen a situation where userId is inserted into two separate columns of the same table (UserId, CreatedBy)
I recommend using ASP.NET Identity to handle user account management. Assign Users and Administrators roles. Use claims to designate the User's Wallet Id. Craft a table or update the user table to store the admin Id.
All this information will follow the user after a successful login which should make your business logic much easier to implement.
Hi wavemaster,
I checked the thread and found nothing on how to insert data into the "CreatedBy" column in the Sign up table. What I found was how query on how get data from wallet table and display. You states that when Admin (user A) signs up, the A user has only UserId
and when user (user U) is created the user has its own UserId... Allnthus is about the wallet table, how about "CreatedBy" column of the Sign up table?
Understood. Thanks.
I still have two related issues;
1. There's a red line error in "db.GetLastInsertId" and "db.Execute".
2. When creating Usertype U, how will the parameter for Usertype A who invited the user be written?
Or will I have to create a textbox to input UserId for user A when creating user U?
The userId is generated when you entered a record in SignUp, provided you set that column to "identity". That way SQL will generate a unique userId.
When user logs in you query the db to see if there is a value in Created.
If there is, you know:
this is a type U user
what the primaryUserId is (i.e. Created)
the walletId
That primaryUserId needs to travel with a type U user throughout your code, and it will drive the logic of what you are going to show where and what you are going to do at that point.
I would set a flag, for instance isAdmin (true is type A user) (false is type U user). This works as long as you have two types of users.
Early on in your threads you mentioned UserType A would invite "U" type users.
Click on "Mark as Answer" if my answers help you along.
The design does not meet the requirements. Also the requirements are not well defined which adds to the confusion.
IMHO, you should separate account management with the Wallet. Use ASP.NET Identity to manage account as recommended above. This will solve a lot of logical design issues you are currently facing.
It is not clear how an Admin is selected but I'll assume that at least one admin account exists in the database. The admin adds user email addresses associated with the admin to a table with AdminId, Email, and UserId columns. The user registration process
checks this table for the email address. If the email address exits then the record is updated with the userId created in the registration process. This joins the User and Admin accounts.
Mgebhard,<br>
In a design, the designer knows what is required to achieve the desired goal; so you cannot possibly know if the does not meet the requirements. there's room to think out of the box in order to achieve the goal; to do or try out what maybe has not been done
before. So I tend to ask these questions in order to use the lines of codes to achieve my goal regarding what I'm building. I guess if you see the diagram in this regard, you will know what I mean.
I'm not trying to state here that you're wrong..on the contrary your contributions are standard practice and very very knowledgeable. But I'm trying to achieve something I drafted down
The connection is initialized through its connection string. Besides, without it being connected, an error would have been shown when trying to insert a record
Member
66 Points
175 Posts
Having Admin wallet display when user logs in
Jun 21, 2020 05:09 PM|georgeakpan233|LINK
I have two tables in my project - Signup and User wallet tables; the sign up table contains login data and the user wallet contains monetary data of the Admin. There are two users who will be granted access to the site (Admin and Users). The Admin is created from the start, the “user” is being created by the Admin. After Admin Registers and logs in, the Admin ID and wallet balance is displayed on the web forms of the site. Then Admin will then proceed to the dashboard to create new users the team; after new user is created it displays in the gridview based on the Admin who created the user(s).
Now I want a situation where a user will log in and since the user is linked to an Admin, the Admin ID and wallet balance who created that user, will be displayed except in the user’s case, the user will not be able to see the link that navigates to the dashboard and will also not be able to see Deposit button which is on the navbar, only an Admin can see those. However, the user can use paid services or perform activities under the Admin who created the user, thereby effecting changes in the admin’s wallet balance. Here is the structure of my tables:
Key: UserType refers to the type of user on the site (A = Admin and U = User)
USERWALLET TABLE
How should I code this please? That will call Admin “userwallet” when user logs in based on the admin who created the user.
Thank you
Contributor
2080 Points
664 Posts
Re: Having Admin wallet display when user logs in
Jun 22, 2020 10:00 AM|XuDong Peng|LINK
Hi georgeakpan233,
When the user logs in, you could check whether the user is linked to an Admin.
If linked, the page will show the panel including the Admin ID and wallet balance,etc.
If not linked, then the role is an Admin, the page will show the panel including the link that navigates to the dashboard and other content.
You could use the Visible property to control whether to display the panel.
Best regards,
Xudong Peng
Member
66 Points
175 Posts
Re: Having Admin wallet display when user logs in
Jun 22, 2020 11:15 AM|georgeakpan233|LINK
This is where I have an issue; how do I link the user to the wallet Table where the Admin ID and wallet is?
All-Star
53021 Points
23604 Posts
Re: Having Admin wallet display when user logs in
Jun 22, 2020 11:34 AM|mgebhard|LINK
The data shown above is a simple list of records with Admin and Users types. A one-to-many relationship is required to relate User to Admin. One solution is to add another column, "Admin", to the table. Place the AdminId in each User record to join the User to the Admin. The admin will have a null value.
Member
66 Points
175 Posts
Re: Having Admin wallet display when user logs in
Jun 22, 2020 12:04 PM|georgeakpan233|LINK
Hi mgebhard,
If you checked the first image table there is a column named "CreatedBy" that is where the AdminId is and each user has AdminId related to it. But where the AdminID and wallet balance is displayed from, is from another table named "UserWallet", so is there any code that will be written to link user and Admin ID and its wallet balance ? such that when a user logs in the wallet account of the Admin (which is in another table) will be linked to the user who logs in, and will be displayed.
All-Star
53021 Points
23604 Posts
Re: Having Admin wallet display when user logs in
Jun 22, 2020 03:47 PM|mgebhard|LINK
I'm confused. Your question amounts to how to get the CreatedBy value from the SignUp table? Get the value when the user logs in. Use the email get the value from the UserWallet table.
Another option is getting the amount from the UserWallet table using a join and the user's email address.
SELECT amount FROM UserWallet AS w INNER JOIN SignUp AS s ON w.email = s.CreatedBy WHERE s.email = @emailIMHO, the design is very confusing.
Member
66 Points
175 Posts
Re: Having Admin wallet display when user logs in
Jun 22, 2020 06:15 PM|georgeakpan233|LINK
Thanks mgebhard,
I dont know, maybe this explanation will be clearer:
For example, if an Admin (check@gmail.com) registers as new Admin and logs in, the ID and wallet balance of Admin (check@gmail.com) will display. Then Admin (check@gmail.com) proceeds to dashboard and creates new user (tyler@yahoo.com). The user (tyler@yahoo.com) will be under the Admin (check@gmail.com).
So what I want achieve is when the user (tyler@yahoo.com) logs in, the ID and wallet balance of Admin (check@gmail.com) will be displayed and the user will not be able to do some of the things the Admin does, which are see deposit button to make deposit or see dashboard link too. But user can be able to use funds in the Admin's wallet and Admin can know if funds has been used.
Please what does the "w", "s", "w.email" and "s.CreatedBy" mean? do I have to declare them?
Contributor
2611 Points
2708 Posts
Re: Having Admin wallet display when user logs in
Jun 22, 2020 06:40 PM|wavemaster|LINK
Abbreviations
FROM UserWallet AS w
or you can type
FROM UserWallet w
Now w = UserWallet
Contributor
2611 Points
2708 Posts
Re: Having Admin wallet display when user logs in
Jun 22, 2020 07:00 PM|wavemaster|LINK
I would advise against using email addresses as primary keys.
your SignUp table should look like this
userId int PK (primary key)
email
pass
con-pass
Name
UserType
CreatedBy int
CreatedDate
then you Wallet table like this:
walletId int PK
userId int FK (foreign key)
Balance
The relationship is:
If the user is of Type A then there is 1 wallet
If the user is of Type U then there is no wallet.
A wallet (Type A user) can be seen by multiple Type U users
The query to get the wallet for a Type U user is then:
SELECT walletId, Balance, email, UserType, CreatedBy
FROM Wallet w
JOIN SignUp s ON w.userId = s.CreatedBy
WHERE email = 'the login email from the Type U user'
All-Star
53021 Points
23604 Posts
Re: Having Admin wallet display when user logs in
Jun 22, 2020 07:36 PM|mgebhard|LINK
I provided a query that gets the admin wallet amount using a user's email address. Of course this only works for a User account because the CreatedBy is null on admin accounts.
SELECT amount FROM #UserWallet AS w INNER JOIN #Signup AS s ON w.email = s.CreatedBy WHERE s.email = 'Simon@gmail.com'With your current configuration, if the login is an Admin then you'll get the amount from the WalletTable.
Another option is to set the CreatedBy to the admin's email rather than NULL
This is code that you have to write. The community cannot comment on a "good" approach because we have no idea how you custom security works. It's confusing why you are unable to identity a user or admin.
IMHO, the design presented is very poor and why you are having trouble. I recommend using the security API, Identity, that comes with ASP.NET rather than building your own if you do not have the experience.
They are standard SQL aliases so you are not forced to type the entire table name.
Member
66 Points
175 Posts
Re: Having Admin wallet display when user logs in
Jun 23, 2020 02:42 AM|georgeakpan233|LINK
What's the best design? How would you design yours, not to make it look poor?
Member
66 Points
175 Posts
Re: Having Admin wallet display when user logs in
Jun 23, 2020 09:28 AM|georgeakpan233|LINK
If I dont have email column in wallet table, how will be able to display email along with wallet balance ?
Contributor
2611 Points
2708 Posts
Re: Having Admin wallet display when user logs in
Jun 23, 2020 10:02 AM|wavemaster|LINK
email comes from SignUp table.
I could have written s.email, but since there is only one email I do not have to use "s."
Member
66 Points
175 Posts
Re: Having Admin wallet display when user logs in
Jun 23, 2020 10:10 AM|georgeakpan233|LINK
How will you insert userId into another column of the same table. The column is highlighted in yellow area, and also insert it into wallet table (also highlighetd in yellow)?
Contributor
2611 Points
2708 Posts
Re: Having Admin wallet display when user logs in
Jun 23, 2020 11:49 AM|wavemaster|LINK
When you create the SignUp record you ask for the userId that was just created
then:
if (usertype == "A") { int newuserid = db.GetLastInsertId();
db.Execute("INSERT INTO Wallet (UserId, Wallet) VALUES (@0, @1)", newuserid, wallet); }
Also, make sure all your tables have a primary key, and set it to identity.
https://www.youtube.com/watch?v=HbjcoC7gLEU
Member
66 Points
175 Posts
Re: Having Admin wallet display when user logs in
Jun 23, 2020 12:23 PM|georgeakpan233|LINK
Besides, those parameters (@0, @1), newuserid, wallet).can you explain better?
Contributor
2611 Points
2708 Posts
Re: Having Admin wallet display when user logs in
Jun 23, 2020 02:21 PM|wavemaster|LINK
I answered the first question in a different thread. My suggestion was that you call it primaryUserId. Look at that again.
Those parameters are necessary to prevent SQL injection attacks. You can look up that term and get it better explained than I can.
Member
66 Points
175 Posts
Re: Having Admin wallet display when user logs in
Jun 23, 2020 06:42 PM|georgeakpan233|LINK
Contributor
2611 Points
2708 Posts
Re: Having Admin wallet display when user logs in
Jun 23, 2020 06:44 PM|wavemaster|LINK
I answered that in one of your threads....
Member
66 Points
175 Posts
Re: Having Admin wallet display when user logs in
Jun 23, 2020 06:59 PM|georgeakpan233|LINK
Contributor
2611 Points
2708 Posts
Re: Having Admin wallet display when user logs in
Jun 23, 2020 07:04 PM|wavemaster|LINK
Look at the last one in this thread.
UserType A:
only has the UserId
UserType U:
has its own UserId
AND
the UserId of the Admin who invited this user
All-Star
53021 Points
23604 Posts
Re: Having Admin wallet display when user logs in
Jun 23, 2020 07:44 PM|mgebhard|LINK
I recommend using ASP.NET Identity to handle user account management. Assign Users and Administrators roles. Use claims to designate the User's Wallet Id. Craft a table or update the user table to store the admin Id.
All this information will follow the user after a successful login which should make your business logic much easier to implement.
https://docs.microsoft.com/en-us/aspnet/identity/overview/getting-started/introduction-to-aspnet-identity
https://docs.microsoft.com/en-us/aspnet/core/security/authentication/identity?view=aspnetcore-3.1&tabs=visual-studio
Member
66 Points
175 Posts
Re: Having Admin wallet display when user logs in
Jun 24, 2020 07:16 AM|georgeakpan233|LINK
I checked the thread and found nothing on how to insert data into the "CreatedBy" column in the Sign up table. What I found was how query on how get data from wallet table and display. You states that when Admin (user A) signs up, the A user has only UserId and when user (user U) is created the user has its own UserId... Allnthus is about the wallet table, how about "CreatedBy" column of the Sign up table?
Contributor
2611 Points
2708 Posts
Re: Having Admin wallet display when user logs in
Jun 24, 2020 07:59 AM|wavemaster|LINK
Let me clarify
UserType A:
has one record in SignUp, Created is NULL
AND
has one record in Wallet, with UserId set to the UserId from SignUp
UserType U:
has one record in SignUp, Created is set to the UserId of the Type A user who invited that user
this UserType has no Wallet record of its own
Member
66 Points
175 Posts
Re: Having Admin wallet display when user logs in
Jun 24, 2020 08:45 AM|georgeakpan233|LINK
I still have two related issues;
1. There's a red line error in "db.GetLastInsertId" and "db.Execute".
2. When creating Usertype U, how will the parameter for Usertype A who invited the user be written?
Or will I have to create a textbox to input UserId for user A when creating user U?
Contributor
2611 Points
2708 Posts
Re: Having Admin wallet display when user logs in
Jun 24, 2020 09:13 AM|wavemaster|LINK
Has "db" been defined in your application?
The userId is generated when you entered a record in SignUp, provided you set that column to "identity". That way SQL will generate a unique userId.
When user logs in you query the db to see if there is a value in Created.
If there is, you know:
That primaryUserId needs to travel with a type U user throughout your code, and it will drive the logic of what you are going to show where and what you are going to do at that point.
I would set a flag, for instance isAdmin (true is type A user) (false is type U user). This works as long as you have two types of users.
Early on in your threads you mentioned UserType A would invite "U" type users.
Member
66 Points
175 Posts
Re: Having Admin wallet display when user logs in
Jun 24, 2020 10:46 AM|georgeakpan233|LINK
Or how is it defined?
All-Star
53021 Points
23604 Posts
Re: Having Admin wallet display when user logs in
Jun 24, 2020 11:01 AM|mgebhard|LINK
The design does not meet the requirements. Also the requirements are not well defined which adds to the confusion.
IMHO, you should separate account management with the Wallet. Use ASP.NET Identity to manage account as recommended above. This will solve a lot of logical design issues you are currently facing.
It is not clear how an Admin is selected but I'll assume that at least one admin account exists in the database. The admin adds user email addresses associated with the admin to a table with AdminId, Email, and UserId columns. The user registration process checks this table for the email address. If the email address exits then the record is updated with the userId created in the registration process. This joins the User and Admin accounts.
Contributor
2611 Points
2708 Posts
Re: Having Admin wallet display when user logs in
Jun 24, 2020 01:08 PM|wavemaster|LINK
Is your application connected to the database?
Something like:
var db = Database.Open("yourdbname")
Member
66 Points
175 Posts
Re: Having Admin wallet display when user logs in
Jun 24, 2020 06:45 PM|georgeakpan233|LINK
Mgebhard,<br>
In a design, the designer knows what is required to achieve the desired goal; so you cannot possibly know if the does not meet the requirements. there's room to think out of the box in order to achieve the goal; to do or try out what maybe has not been done
before. So I tend to ask these questions in order to use the lines of codes to achieve my goal regarding what I'm building. I guess if you see the diagram in this regard, you will know what I mean.
I'm not trying to state here that you're wrong..on the contrary your contributions are standard practice and very very knowledgeable. But I'm trying to achieve something I drafted down
Member
66 Points
175 Posts
Re: Having Admin wallet display when user logs in
Jun 28, 2020 07:28 AM|georgeakpan233|LINK
Contributor
2611 Points
2708 Posts
Re: Having Admin wallet display when user logs in
Jun 28, 2020 03:55 PM|wavemaster|LINK
How do you know it is connected and how did you initialize the connection in your app?
Member
66 Points
175 Posts
Re: Having Admin wallet display when user logs in
Jun 30, 2020 07:54 AM|georgeakpan233|LINK
The connection is initialized through its connection string. Besides, without it being connected, an error would have been shown when trying to insert a record
Contributor
2611 Points
2708 Posts
Re: Having Admin wallet display when user logs in
Jul 02, 2020 05:55 AM|wavemaster|LINK
Please share the code so we do not have to speculate about what you are doing.