CODEWITHSUNDEEP
c#mysqlasp.net-mvc-4
Stainn
Stainn

Reputation: 119

Retrieve all data from the database got the second row same with the first row

This problem is not appear when I had combined both the models and the codes in the same class, but right now I already separate the models and the codes into different class. I made 2 column and each one have different value (data).

When I ran the program, it will retrieve all data from the database, and this is the result that I got: (Totally wrong)

Database table (User) :

enter image description here

However, when I check the database from the IDE (Visual Studio 2013), it appears correctly:

enter image description here

Here is the code that I am using:

Model (UserContext) :

public class UserContext
    {
        [Display(Name = "Username:")]
        public string Username
        {
            get;
            set;
        }
    }

Controller :

UserManager manager = new UserManager();

public ActionResult List()
        {
            List<UserContext> user = manager.Fetch();

            return View(user);
        }

Code Behind (UserManager) :

public List<UserContext> Fetch()
        {
            List<UserContext> users = new List<UserContext>();

            UserContext context = new UserContext();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                string query = "SELECT [Username] FROM [User] ORDER BY [Username] ASC";

                conn.Open();

                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            context.Username = reader["Username"].ToString();

                            users.Add(context);
                        }
                    }
                }
            }

            return users;
        }

View (List View) :

@model  List<Project_Name.Models.UserContext>

@{
    ViewBag.Title = "User List";
}

<h2>User List</h2>
<br />

<table id="example" class="display" cellspacing="0" width="100%">
    <thead>
        <tr>
            <th>
                Username
            </th>
        </tr>
    </thead>

    <tbody>
        @foreach (var item in Model)
        {
            <tr>
                <td>
                    @Html.Label(item.Username)
                </td>
            </tr>
        }
    </tbody>
</table>

Is a problem was because I put the UserManager class outside the Models folder, and the UserContext inside the Models folder?

Thank you very much.

Your answer much appreciated.

Upvotes: 1

Views: 109

Answers (4)

Vivek Sadh
Vivek Sadh

Reputation: 4268

You need to create new instance in "each" while loop iteration like this:

while (reader.Read())
{
  UserContext context = new UserContext();
  .....
  .........;
}

Upvotes: 1

user3559349
user3559349

Reputation:

Your error occurs because you declare just one instance of context, and the keep updating its UserName in the while loop (inside the while loop you just add another reference of it to the collection)

You need to declare a new instance inside the loop

while (reader.Read())
{
  UserContext context = new UserContext();
  context.Username = reader["Username"].ToString();
  users.Add(context);
}

Upvotes: 1

Robert Moskal
Robert Moskal

Reputation: 22553

Your first problem is in your UserManager. You need to create a new UserContext for each row you read from the database.

Move 

UserContext context = new UserContext();

From where it is to inside the

while (reader.Read()) {

Upvotes: 1

Moo-Juice
Moo-Juice

Reputation: 38825

You have no WHERE clause. You're always going to end up with the last person selected.

Upvotes: 0

Related Questions