Solved

Dataset loads incorrect data from SQL server

Posted on 2006-10-21
3
244 Views
Last Modified: 2010-04-16
Hi all. I have an application that connects to a SQL server and loads data from it. Sometimes however it loads the columns in the wrong order. The "Users" table that I query has the columns in this order: Username, Policy, Tickets, OpenTickets, Notes, Image. Sometimes they load in this order: Policy, Username, Tickets, OpenTickets, Notes, Image.

The code that I use to load the data into a datatable is this, where userTable is my datatable.:

        public static void selectAllUsers()
        {
            SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Users", Connections.SQLconnection);

            try
            {
                userTable.Clear();
                da.Fill(userTable);
            }
            catch (SqlException)
            {

            }
        }

Once in the dataset, the data is loaded into a listView on a form using this code:

        private void getUsers()
        {
            //Clear the ListView
            listViewUsers.Items.Clear();

            //Select all rows from the Users table
            UsersSQL.selectAllUsers();

            //Add each row of the DataTable to the ListView
            foreach (DataRow row in UsersSQL.userTable.Rows)
            {
                ListViewItem lvi = new ListViewItem(row[0].ToString());
                lvi.SubItems.Add(row[1].ToString());
                lvi.SubItems.Add(row[2].ToString());
                lvi.SubItems.Add(row[3].ToString());
                lvi.SubItems.Add(row[4].ToString());
                listViewUsers.Items.Add(lvi);
            }

            //Update controls
            if (listViewUsers.Items.Count > 0)
            {
                // Set the column number that is to be sorted; default to ascending.
                lvwColumnSorter.SortColumn = 0;
                lvwColumnSorter.Order = SortOrder.Ascending;

                // Perform the sort with these new sort options.
                this.listViewUsers.Sort();

                //listViewUsers.Items[0].Selected = true;
                toolStripButtonDelete.Enabled = true;
                toolStripButtonEdit.Enabled = true;
            }
            else
            {
                toolStripButtonDelete.Enabled = false;
                toolStripButtonEdit.Enabled = false;
            }

            toolStripProgressBar.Value = 100;
            toolStripStatusLabelStatus.Text = listViewUsers.Items.Count.ToString() + " Users Found";
        }

I think that the problem comes when loading data into the dataTable, rather than when populating the form, but I'm not sure. It's also worth noting that the problem only seems to occur after I change the user's policy and update the Policy column. For that I use this code:

        public static void updateUserPolicy(string oldPolicy, string newPolicy)
        {
            try
            {
                //Begin a new transaction on our main connection
                sqlTrans = Connections.SQLconnection.BeginTransaction();
                sqlCommand = new SqlCommand(@"UPDATE Users SET Policy='" + Gatekeeper.SQLShieldRemoveApostrophes(newPolicy) + "' WHERE Policy='" + Gatekeeper.SQLShieldRemoveApostrophes(oldPolicy) + "'", Connections.SQLconnection, sqlTrans);
                //Execute the transaction
                sqlCommand.ExecuteNonQuery();
                //Try to commit changes to the database
                sqlTrans.Commit();
            }
            catch (SqlException)
            {
                //If there is an error, roll back the transaction
                sqlTrans.Rollback();
            }
        }

Gatekeeper.SQLShieldRemoveApostrophes(); protects against some SQL injection attacks, but is not the cause of the problem. The transaction executes fine, but afterwards I find the two columns reversed. I hope someone can figure this out. Thanks.
0
Comment
Question by:pab89
  • 2
3 Comments
 
LVL 29

Expert Comment

by:Nightman
ID: 17781343
Try changing your select statement to explicitly select the columns in the order that you want, instead of 'select *'. Just a suggestion, may not work.

An alternative would be when adding the ListView SubItems, reference the row by name instead of the ordinal position.
0
 
LVL 1

Accepted Solution

by:
pascalprob earned 325 total points
ID: 17791506
To guarantee the order in which your select statement retrieves columns change it to:
"SELECT Username, Policy, Tickets, OpenTickets, Notes, Image FROM Users"
If this does not solve the problem, you will at least know that the select statement is not at fault.
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17817153
Hmm, that looks suspiciously like what I said - "Try changing your select statement to explicitly select the columns in the order that you want, instead of 'select *'".
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Moving code from one TFS branch to another 3 33
Problem!!! 8 37
abstract class C# 1 30
JQuery Detecting page post back 6 4
In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question