Usman ur Rehman Ahmed's blog

The stored procedures below can usually be used to swap two records fulfilling a given criteria.Note that the swapping will work even if some records between these two have been deleted. i.e. the monotonic sequence 0, 1, 2 ... is not strictly followed. (e.g. 0, 1, 3, 6, 7...)

The sample use demonstrated here is to swap the Display Priority of two recrods from the table ProjectsPerTab. Such a situation is often required in sorting item by item or giving precedence to one item over other as shown below,

- pk (Primary key - int)

- TabId (Foreign Key from DNN Tabs table - int)

- ProjectFK (Projects table foreign key - int)

- DisplayPriority (int)

Move Down (Increase Display Priority/Swap with next record)

create procedure {databaseOwner}{objectQualifier}IncreaseProjectDisplayPriority
    @TabId int,
    @ProjectFK int
as
    begin
        -- Step 1: Retrieve display priority of this project and store that since that will be the last one to be updated

            declare @CurrentProjectDisplayPriority int
            select @CurrentProjectDisplayPriority = DisplayPriority from {objectQualifier}ProjectsPerTab where TabId = @TabId and ProjectFK = @ProjectFK

        -- Step 2: Exchange display priority with next one

            declare @pk int
            declare @DisplayPriority int

            -- Select minimum primary key for this tab with display priority greater than that of current project
            select @pk = min(pk) from {objectQualifier}ProjectsPerTab where TabId = @TabId and DisplayPriority > @CurrentProjectDisplayPriority
            select @DisplayPriority = DisplayPriority from {objectQualifier}ProjectsPerTab where pk = @pk

            if @pk is not null
            begin
                -- Exchange display priorities
                update {objectQualifier}ProjectsPerTab set DisplayPriority = @CurrentProjectDisplayPriority where pk = @pk
                update {objectQualifier}ProjectsPerTab set DisplayPriority = @DisplayPriority where TabId = @TabId and ProjectFK = @ProjectFK
            end

    end

GO

Move Up (Decrease Display Priority/Swap with previous record)

create procedure {databaseOwner}{objectQualifier}DecreaseProjectDisplayPriority
    @TabId int,
    @ProjectFK int
as
    begin
        -- Step 1: Retrieve display priority of this project and store that since that will be the last one to be updated

            declare @CurrentProjectDisplayPriority int
            select @CurrentProjectDisplayPriority = DisplayPriority from {objectQualifier}ProjectsPerTab where TabId = @TabId and ProjectFK = @ProjectFK

        -- Step 2: Exchange display priority with previous one

            declare @pk int
            declare @DisplayPriority int

            -- Select maximum primary key for this tab with display priority less than that of current project
            select @pk = max(pk) from {objectQualifier}ProjectsPerTab where TabId = @TabId and DisplayPriority < @CurrentProjectDisplayPriority
            select @DisplayPriority = DisplayPriority from {objectQualifier}ProjectsPerTab where pk = @pk

            if @pk is not null
            begin

                -- Exchange display priorities
                update {objectQualifier}ProjectsPerTab set DisplayPriority = @CurrentProjectDisplayPriority where pk = @pk
                update {objectQualifier}ProjectsPerTab set DisplayPriority = @DisplayPriority where TabId = @TabId and ProjectFK = @ProjectFK
            end

    end

GO

I encountered this exception today while trying to read a data set from MySql database from C# .NET using MySql connector and enterprise library. Complete stack trace of this exception is below,

{MySql.Data.MySqlClient.MySqlException: The user specified as a definer ('root'@'%') does not exist

   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()

   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int32& insertedId)

   at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int32& insertedId)

   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId)

   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()

   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)

   at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)

   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

   at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoLoadDataSet(IDbCommand command, DataSet dataSet, String[] tableNames)

   at Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, String[] tableNames)

   at Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, String tableName)

   at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteDataSet(DbCommand command)

 This happens when you try to make connection with MySql database from within .NET and is a privileges issues. For resolution, please follow these steps,

1- Run > Command Prompt

                Move to C:\Program Files\MySql\bin folder. Path for me appears as following,

       C:\Program Files\MySQL\MySQL Server 5.5\bin> 

2- Log into My Sql by exeucting the following command,

       mysql -u root -p

                You will be prompted for password.

       Enter password:

                On successfull password input you will log into mySql console,

                mysql> _

3- Grant All Privileges

                Execute following command under my sql console,

       grant all privileges on *.* to `root`@`%` identified by 'password';

                Upon successfull completion of command you will recieve confirmation simialr to this,

                Query OK, 0 rows affected (0.05 sec)

Note: Pay special heed to position of apostrophe vs. commas. Usernameis enclosed within apostrophy (`) where as password within commas (').

4- Flush Privileges

                Execute following command to ensure the privileges take affect,

       flush privileges;

                which will result in notification similar to this,

                Query OK, 0 rows affected (0.11 sec)

                You are done. Switch back to Visual Studio to test your settings are properly taking affect. To exit mysql console type exit and press enter. Above steps are summarized in figure below,

Today I was testing MySql membership provider using MySql data connector 6.2.4. While the setup for running form based authentication on top of MySql membership was fairly straight forward, something that surprised me for a bit was to get an unexpected exception when I tried to read the ProviderUserKey,

    MembershipUser currentUser = Membership.GetUser();
    Guid currentUserId = (Guid)currentUser.ProviderUserKey;

at second line I got the exception, "Cannot unbox 'currentUser.ProviderUserKey' as a 'System.Guid' System.Guid". Why would that be I thought when ProviderUserKey is object type?

Looking closely I identified that MySQLMembershipProvider offers Integer type ProviderUserKey as shown below,

http://dev.mysql.com/doc/refman/5.1/en/miscellaneous-functions.html#function_...