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);
Once in the dataset, the data is loaded into a listView on a form using this code:
private void getUsers()
//Clear the ListView
//Select all rows from the Users table
//Add each row of the DataTable to the ListView
foreach (DataRow row in UsersSQL.userTable.Rows)
ListViewItem lvi = new ListViewItem(row.ToString());
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.
//listViewUsers.Items.Selected = true;
toolStripButtonDelete.Enabled = true;
toolStripButtonEdit.Enabled = true;
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)
//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
//Try to commit changes to the database
//If there is an error, roll back the transaction
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.