Combine Rows into a Column

Happened to find a way to combine rows into a column. I find it quite useful, and it comes in handy.

For illustration, suppose we have UserGroup & Users. We want to list the users belonging to each group.

UserGroup:

Users:

If we do normal joining of the 2 tables, it will look like:

If we combine the values on the rows into a column, it will look like:

Here is the query to do so:

-- Create temporary table to store UserGroup
SELECT GroupID = 1, UserGroup = 'Group 1' INTO #UserGroup
INSERT INTO #UserGroup VALUES ( 2, 'Group 2' )
INSERT INTO #UserGroup VALUES ( 3, 'Group 3' )

-- Create temporary table to store Users
SELECT UserID = 'Superman', GroupID = 1 INTO #Users
INSERT INTO #Users VALUES ( 'Luck', 2 )
INSERT INTO #Users VALUES ( 'Himura', 1 )
INSERT INTO #Users VALUES ( 'Selvi', 1 )
INSERT INTO #Users VALUES ( 'Smarty', 2 )
INSERT INTO #Users VALUES ( 'Lucky', 3 )

-- Normal Join
SELECT a.UserGroup, b.UserID
FROM #UserGroup a
LEFT JOIN #Users b
 ON b.GroupID = a.GroupID
ORDER BY a.UserGroup

-- Combine Rows into Column
SELECT UserGroup, Users = STUFF(( SELECT ', ' + UserID FROM #Users a
    WHERE a.GroupID = b.GroupID
    ORDER BY a.UserID
    FOR XML PATH('') )
   , 1, 2, '')
FROM #UserGroup b
ORDER BY UserGroup

-- This is the key query to combine rows into column
SELECT UserID + ', ' FROM #Users
WHERE GroupID = 1
FOR XML PATH('')

If you are concatenating a field which contains some HTML tag, it would be automatically encoded. To avoid that, consider using this query:

-- Combine Rows into Column
SELECT UserGroup, Users = STUFF(( SELECT ', ' + UserID FROM #Users a
    WHERE a.GroupID = b.GroupID
    ORDER BY a.UserID
    FOR XML PATH,TYPE).value('.[1]','nvarchar(max)')
   , 1, 2, '')
FROM #UserGroup b
ORDER BY UserGroup

Update: Looks like I posted something similar quite some time ago, but using different method: Selecting several rows into one row in SQL

Read More

Mapping User Login and Database Roles to Existing User

Error:
User, group, or role 'xxx' already exists in the current database. (.Net SqlClient Data Provider)

Explanation:
This error happens when you try to add user mapping to the server logins using username which already exists in the database to be assigned.

In my case, the user exists in both server logins and database logins, but the user mapping and database roles on the server logins for this user cannot be modified although the user mapping is incorrect, because it already exists on the database logins.

Solution:
Run this script to update the user mapping accordingly.

USE <database_name>
EXEC sp_change_Users_login 'update_one', '<login_username>', '<login_username>'

This will update the user mapping and database roles on server logins to follow the ones on the database logins.

Read More

Current Transaction Cannot be Committed

Error:
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

This error occurs when you try to perform other operations when any transaction fails in try catch section, soon after it goes to catch section.

Solution:
You have to ROLLBACK first on the catch section before doing any other operations.

Read More

Directory Lookup Failed on SQL Server

When executing script on SQL server, this error occurs:
Directory lookup for the file <filepath> failed with the operating system error 2(The system cannot find the file specified.)

Directory lookup for the file <filepath> failed with the operating system error 3(The system cannot find the path specified.)

Solution:
Check the directory used in the SQL script. Make sure that the directory used in the SQL script point to the server location, if the script is not directly executed from the server.

Read More

Parameter Sniffing

Parameter sniffing has a weird symptom which can take your hours to find out what actually is happening. The stored procedure you created is running on one side, but when you deploy it on another machine or server, it never ends executing despite the fact that everything is the same.

This problem usually occurs when the query contains LIKE condition. However, it is not limited to this case. Query without LIKE condition can also face this problem.

For example, here is the normal stored procedure:

CREATE PROCEDURE dbo.spr_GetData
@Parameter1 INT,
@Parameter2 VARCHAR(200)
AS
SELECT *
FROM dbo.Table1
WHERE Column1 = @Parameter1
AND Column2 LIKE '%' + @Parameter2 + '%'

To avoid parameter sniffing, there is a need to create new variables to store the values fetched from the parameters.

CREATE PROCEDURE dbo.spr_GetData
@Parameter1 INT,
@Parameter2 VARCHAR(200)
AS

DECLARE
@DummyParameter1 INT = @Parameter1,
@DummyParameter2 VARCHAR(200) = @Parameter2

SELECT *
FROM dbo.Table1
WHERE Column1 = @DummyParameter1
AND Column2 LIKE '%' + @DummyParameter2 + '%'

Read More

Error Converting Data Type Int to Nvarchar

Error: Error converting data type int to nvarchar

This error occurs when a stored procedure which returns a value is used.

e.g

CREATE PROCEDURE [dbo].[spr_SP1]
@ColA INT,
@AutoID INT OUTPUT
AS
BEGIN
 INSERT INTO TableA ( ColA )
 VALUES ( @ColA )

 SELECT @AutoID = SCOPE_IDENTITY();
END

When adding the parameter to the command on the code, usually the code used is:

command.Parameters.AddWithValue("@AutoID", System.DBNull.Value)
command.Parameters("@AutoID").Size = 4
command.Parameters("@AutoID").Direction = ParameterDirection.Output

However, this portion of code would possibly cause error during deployment. To avoid the error, use the code below instead:

Dim param = New System.Data.SqlClient.SqlParameter()
param.ParameterName = "@AutoID"
param.Direction = ParameterDirection.Output
param.Size = 8
command.Parameters.Add(param)

Read More

An Error Occurred While Parsing EntityName

Error: An Error occurred while parsing EntityName. Line 17, position 211.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Xml.XmlException: An error occurred while parsing EntityName. Line 17, position 211.

Source error: responseXml.LoadXml(response)

This error occurs because there is an ampersand (&) in the returned XML command.

Solution: Use “and” or other conjunctions instead of ampersand (&). Another option is to replace the string containing & with "%26".

Read More

Could not Load Type from Assembly

Error:
Could not load type 'xxxxxx' from assembly 'AssemblyName, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null'.

This error occurs when trying to open a webpage. The project is built successfully. However, when accessing particular page, the error pops out. The error message is actually quite long. This is only part of the error message.

Solution:
On Visual Studio, go to menu Tools -> Options -> Debugging -> General -> Disable "Enable Just My Code" options.

PS: I am not sure if there is another more feasible solution. This might only be a workaround. There could be side effects. As of now, it is still working fine after this option is disabled.

Update:
Looks like this does not really solve the issue. I find out that if the project or solution is rebuilt, it will go to normal when the web is run. If it doesn't, rebuild, then try it again.

However, when the web is run on debug mode, some pages will get this error, thus not viewable. Another workaround is to view the web in browser (not in debug mode), then attach the process to the web. On Visual Studio, after the web is viewed in browser, go to menu Tools -> Attach to Process -> look for the web server process under which the web is run, e.g. WebDev.WebServer20.EXE

Read More

DatePicker Calendar Appears on Top of Page

Problem:
On page load, datepicker calendar appear on top of a web page which contains datepicker textbox (either visible or hidden).
This issue appears to be happening on Chrome browser. Some may experience it in Internet Explorer.

Source of the problem:
1. There could be more than 1 element on the page which use the same ID.
2. Another possibility is that this is a bug in some of the versions of jQuery style or script.

Solution:
Either change the ID of the elements with the same IDs if this is the issue.
If the issue is with the jQuery style, add this code to hide the DatePicker calendar when the page is first loaded:

<style type="text/css">
    #ui-datepicker-div { display: none; }
</style>

Read More

Skip RedirectFromLoginPage in ASP.Net

Problem:
You have a web application that has a specific redirect page after user logs into the application. But, in certain circumstances, you want to skip it and redirect the user to another page instead of the default page specified after user usually logs into the application.

Solution:
Thanks to a thread from VelocityReviews, I find the solution.
Instead of using

FormsAuthentication.RedirectFromLoginPage(userName, createPersistantCookie) 

use

FormsAuthentication.SetAuthCookie(userName, createPersistantCookie) 

and

Repsonse.Redirect(url)

methods to redirect the user to the page you want.

Read More

Conflict in Namespace

Error:

Compilation Error

Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30175: module 'ContextMenuHelpers' and module 'ContextMenuHelpers', declared in 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\metroweb\87c204db\55321c07\App_Code.rcey62l6.13.vb', conflict in namespace ''.

Source Error:

Line 3: Line 4: Public Module ContextMenuHelpers  Line 5: Line 6: 'Public SysMsg As GlobalResources.SystemMessages = New GlobalResources.SystemMessages()

Solution:
This problem occurs because more than one file of the same class with the same namespace are found in the folder, usually App_Code folder. If you back-up the source files, make sure it is renamed as another extension, so that it does not conflict with the original file. Otherwise, either put your back-up files outside of the application, or archive it in a compressed file.

In the case I encountered, someone puts a back-up file in a new sub folder inside the App_Code folder. After renaming the extension of the back-up file, the problem is solved.

Read More