How to access multiple tables from a SELECT FROM in ASP.NET with SQL Server?

Question

I have this project I am working on for an assignment, and I have a question working with ASP.NET and SQL Server. I have a login page that kinda works, but there are two tables that I need to get data from - users (subscribers) and admin page but am unsure how to access both of them as I can only access one.

public void login(Object src,EventArgs e)
{
    get_connection();

    try
    {
        connection.Open();
        command = new SqlCommand("SELECT * FROM subscribers WHERE Email = @Email AND Password = @Password", connection);
        command.Parameters.AddWithValue("@Email", loginName.Text);
        command.Parameters.AddWithValue("@Password", loginPass.Text);

        //command = new SqlCommand("SELECT * FROM admin WHERE Email =@Email and Password = @Password", connection);
        //command.Parameters.AddWithValue("@Email", loginName.Text);
        //command.Parameters.AddWithValue("@Password", loginPass.Text);

        reader = command.ExecuteReader();

I commented out the admin part because when I include it, only admin username and password is accepted and not subscribers. What would I need to do to fix this?

If schemas are the same and you don't want to make two cmd instance, you can UNION the SELECTs. — Circle Hsiao
– Circle Hsiao, Commented Nov 4, 2017 at 6:12

Pete M · Accepted Answer · 2017-11-03 22:07:24Z

1

The "admin part" causes you to only get records from the admin table because you're destroying and recreating your SqlCommand object. You'll need to create a new command in a different variable and read from it separately. There are ways to get multiple recordsets in the same call but I don't recommend it in this case.

That out of the way... Normally I'd expect to see a single users table with permissions/roles granted elsewhere. Consider something like the out of the box ASP.NET membership provider to take care of these implementation details for you:

https://learn.microsoft.com/en-us/dotnet/framework/wcf/feature-details/how-to-use-the-aspnet-membership-provider

answered Nov 3, 2017 at 22:07

Pete M

2,04811 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to access multiple tables from a SELECT FROM in ASP.NET with SQL Server?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related