Remember hidden original values in a gridview

July 4, 2007 9:07

Set the DataKeyNames property of the gridview Most of the time I use optimistic concurrency (with a check on all fields) to update a database row. While using a gridview to update rows in a table, problems arise when you don’t show or use all database fields of that table in your grid.
Running an automatic update command with optimistic concurrency is bound to fail because the grid cannot supply all original values.

To solve this issue you have to set and extra property and you have to supply some code to the Rowupdating event of the gridview. The property you have to set with the field names that you are not using is the DataKeyNames property of the gridview. For example:

DataKeyNames=”UniqueId,VersionId”

Now you can add the missing information to the OldValues and NewValues collection in the RowUpdating event.

Protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
IDictionaryEnumerator restoreOldValues = e.Keys.GetEnumerator();
while (restoreOldValues.MoveNext())
{
e.OldValues.Add(restoreOldValues.Key.ToString(), restoreOldValues.Value.ToString());
e.NewValues.Add(restoreOldValues.Key.ToString(), restoreOldValues.Value.ToString());
}
}

The gridview now has all the required information to run a successful update.

Tags: .NET, database, DataKeyNames, field names, optimistic concurrency, RowUpdating.

Posted in .NET | Permalink | Post first comment

Troubleshoot disconnected SQL Server users

July 3, 2007 12:15

Disconnected users If you move a database export to a new server, you might experience login problems inside your applications.

The exported database will contain your users, but they are no longer connected to valid SQL Server logins. Even if the same users are existing on the new SQL Server, their ID’s might be different. One of the options is to just recreate and configure the users, but sometimes this is not an option; You might have a lot of users or complicated user rights that you don’t want to check, test and document.

SQL Server contains stored procedure in the Master database to connect your database users with the available SQL Server users. This stored procedure is called “sp_change_users_login”. Just take the steps below to attach your users:

Create the (new) required login in the Security database if it doesn’t exist.
Run the following SQL and replace the coloured items with your values:
USE [Your database]
go
EXEC sp_change_users_login ‘Update_One’, ‘Original username‘, ‘SQL Server user name‘

Narayana Vyas Kondreddi has written more detailed information about this subject at his website which contains a handy stored procedure “ShowOrphanUsers” which provides you with a list of all unconnected users.

Documentation about the “sp_change_users_login” stored procedure can be found at the MSDN website.

Tags: database users, disconnected, login problems, logins, security, SQL_Server, stored procedure, users.

Posted in SQL Server | Featured url | Permalink | Post first comment

The content expressed in this blog are those of Edwin Vriethoff and do not represent his employer's view in anyway. The contents of this blog has been carefully put together, but Edwin Vriethoff is not responsible in any way for any direct or indirect harm caused by individuals or organizations using the content of this blog in any way.