connect to Access database in asp.net and Join statement

Question

Let say there is an Asp.ne5 3.5 web application with an Access database. The database has two tables like this:

enter image description here

In some pages I can get the username with `"page.users.identity.name". In this Application, each user can create so many pages. My question is how to write a statement to get pages created with specific username (the Group_ID in joined).

NOTE: This is what i tried and i got "error reading database".

SELECT Pages.* FROM 
Pages INNER JOIN Users ON Pages.Group_ID = Users.Group_ID
WHERE Users.Username = page.users.identity.name

Darin Dimitrov · Accepted Answer · 2012-10-15 08:32:17Z

1

Have you tried using a parametrized query:

public ActionResult SomeAction()
{
    // get the username of the currently connected user
    string username = User.Identity.Name;

    string cs = WebConfigurationManager.ConnectionStrings["MyConnStr"].ConnectionString;
    using (var conn = new OleDbConnection(cs))
    using (var cmd = conn.CreateCommand())
    {
        conn.Open();
        var sql = 
            @"SELECT Pages.* FROM Pages 
              INNER JOIN Users 
              ON Pages.Group_ID = Users.Group_ID
              WHERE Users.Username = ?";
        cmd.CommandText = sql;
        cmd.Parameters.AddWithValue("Username", username);
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                ... 
            }
        }
    }

    ...

}

answered Oct 15, 2012 at 8:32

Darin Dimitrov

1.0m277 gold badges3.3k silver badges3k bronze badges

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

Comments

sp_m · Accepted Answer · 2012-10-15 08:31:09Z

0

Try with this query,

SELECT Pages.* FROM 
Pages,Users where Pages.Group_ID = Users.Group_ID
and Users.Username = page.users.identity.name

answered Oct 15, 2012 at 8:31

sp_m

2,7159 gold badges40 silver badges66 bronze badges

Comments

David Peterson · Accepted Answer · 2012-10-15 11:05:55Z

0

I Missed the Single quote "' " in the statement for page.users.identity.name. now this should be like this:

dim lasts as string= "'"

"SELECT Pages.* FROM 
Pages INNER JOIN Users ON Pages.Group_ID = Users.Group_ID
WHERE Users.Username ='" &  page.users.identity.name & lasts

answered Oct 15, 2012 at 11:05

David Peterson

55215 silver badges31 bronze badges

Collectives™ on Stack Overflow

connect to Access database in asp.net and Join statement

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related