locked
Having Admin wallet display when user logs in RRS feed

  • Question

  • User-1994446809 posted

    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:

    1. 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. 

    Thank you

    Sunday, June 21, 2020 5:09 PM

Answers

  • User475983607 posted

    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

    IMHO, the design is very confusing.  

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 22, 2020 3:47 PM
  • User475983607 posted

    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

    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.

    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.  

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 22, 2020 7:36 PM
  • User379720387 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 23, 2020 11:49 AM

All replies

  • User-939850651 posted

    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

    Monday, June 22, 2020 10:00 AM
  • User-1994446809 posted

    If linked, the page will show the panel including the Admin ID and wallet balance,etc.

    This is where I have an issue; how do I link the user to the wallet Table where the Admin ID and wallet is?

    Monday, June 22, 2020 11:15 AM
  • User475983607 posted

    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.  

    Monday, June 22, 2020 11:34 AM
  • User-1994446809 posted

    Hi mgebhard,

    mgebhard

    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.

    Monday, June 22, 2020 12:04 PM
  • User475983607 posted

    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

    IMHO, the design is very confusing.  

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 22, 2020 3:47 PM
  • User-1994446809 posted

    Thanks mgebhard,

    IMHO, the design is very confusing.  

    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?

    Monday, June 22, 2020 6:15 PM
  • User379720387 posted

    Abbreviations

    FROM UserWallet AS w 

    or you can type

    FROM UserWallet w

    Now w = UserWallet

    Monday, June 22, 2020 6:40 PM
  • User379720387 posted

    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'

    Monday, June 22, 2020 7:00 PM
  • User475983607 posted

    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

    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.

    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.  

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 22, 2020 7:36 PM
  • User-1994446809 posted
    Hi mgebhard,
    What's the best design? How would you design yours, not to make it look poor?
    Tuesday, June 23, 2020 2:42 AM
  • User-1994446809 posted

    then you Wallet table like this:

    walletId int PK

    userId int FK (foreign key)

    Balance

    If I dont have email column in wallet table, how will be able to display email along with wallet balance ? 

    Tuesday, June 23, 2020 9:28 AM
  • User379720387 posted

    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."

    Tuesday, June 23, 2020 10:02 AM
  • User-1994446809 posted

    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

    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)?

    Tuesday, June 23, 2020 10:10 AM
  • User379720387 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 23, 2020 11:49 AM
  • User-1994446809 posted
    This is for wallet; how about the column "CreatedBy" in Sign up Table?
    Besides, those parameters (@0, @1), newuserid, wallet).can you explain better?
    Tuesday, June 23, 2020 12:23 PM
  • User379720387 posted

    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.

    Tuesday, June 23, 2020 2:21 PM
  • User-1994446809 posted
    Which thread please?
    Tuesday, June 23, 2020 6:42 PM
  • User379720387 posted

    I answered that in one of your threads....

    Tuesday, June 23, 2020 6:44 PM
  • User-1994446809 posted
    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)
    Tuesday, June 23, 2020 6:59 PM
  • User379720387 posted

    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

    Tuesday, June 23, 2020 7:04 PM
  • User475983607 posted

    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.

    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

    Tuesday, June 23, 2020 7:44 PM
  • User-1994446809 posted
    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?

    Wednesday, June 24, 2020 7:16 AM
  • User379720387 posted

    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

    SignUp:
    userId usertype created
    1 A null
    2 U 1
    3 U 1
    4 A null
    5 U 4
    Wallet
    walletId userId balance
    1 1 200
    2 4 500

    Wednesday, June 24, 2020 7:59 AM
  • User-1994446809 posted
    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?
    Wednesday, June 24, 2020 8:45 AM
  • User379720387 posted

    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:

    1. this is a type U user
    2. what the primaryUserId is (i.e. Created) 
    3. 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.

    Wednesday, June 24, 2020 9:13 AM
  • User-1994446809 posted
    Defined? As int?
    Or how is it defined?
    Wednesday, June 24, 2020 10:46 AM
  • User475983607 posted

    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.  

    Wednesday, June 24, 2020 11:01 AM
  • User379720387 posted

    Is your application connected to the database?

    Something like:

    var db = Database.Open("yourdbname")

    Wednesday, June 24, 2020 1:08 PM
  • User-1994446809 posted

    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
    Wednesday, June 24, 2020 6:45 PM
  • User-1994446809 posted
    My application is connected to the database but still got red error line under "db"
    Sunday, June 28, 2020 7:28 AM
  • User379720387 posted

    How do you know it is connected and how did you initialize the connection in your app?

    Sunday, June 28, 2020 3:55 PM
  • User-1994446809 posted

    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

    Tuesday, June 30, 2020 7:54 AM
  • User379720387 posted

    Please share the code so we do not have to speculate about what you are doing.

    Thursday, July 2, 2020 5:55 AM