Hi All,

I am facing a unknown problem while using stored procedure with SubSonic. I have written a stored procedure & application code that takes first name & last name as input parameter and return last login id as ouput parameter. It creates login id as first character of first name & complete last name for no-existing login id otherwise it adds 1 in the last login id e.g.

First Name - Mark, Last Name - Waugh, First Login Id - MWaugh, Second Login Id - MWaugh1, Third Login Id - MWaugh2 etc.

Stored Procedure

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Users_FetchLoginId]
(
    @FirstName		nvarchar(64),
    @LastName		nvarchar(64),
    @LoginId		nvarchar(256) OUTPUT
)
AS

DECLARE @UserId nvarchar(256);
SET @UserId = NULL;
SET @LoginId = NULL;

SELECT @UserId = LoweredUserName FROM aspnet_Users WHERE LoweredUserName LIKE (LOWER(SUBSTRING(@FirstName,1,1) + @LastName))
IF @@rowcount = 0 OR @UserId IS NULL
    BEGIN
    	SET @LoginId = (SUBSTRING(@FirstName, 1, 1) + @LastName);
    	print @LoginId
    	RETURN 1;
    END
ELSE
    BEGIN
    	SELECT TOP 1 LoweredUserName FROM aspnet_Users WHERE LoweredUserName LIKE (LOWER(SUBSTRING(@FirstName,1,1) + @LastName + '%')) ORDER BY LoweredUserName DESC
    	RETURN 2;
    END

Application Code

public string FetchLoginId(string firstName, string lastName)
{
    SubSonic.StoredProcedure sp = SPs.UsersFetchLoginId(
    														firstName,
    														lastName,
    														null
    													);

    sp.Command.AddReturnParameter();
    sp.Execute();

    if (sp.Command.Parameters.Find(delegate(QueryParameter queryParameter)
    				  {
    					  return queryParameter.Mode == ParameterDirection.ReturnValue;
    				  }).ParameterValue != System.DBNull.Value)
    {
    	int returnCode = Convert.ToInt32(sp.Command.Parameters.Find(delegate(QueryParameter queryParameter)
    							  {
    								  return queryParameter.Mode == ParameterDirection.ReturnValue;
    							  }).ParameterValue, CultureInfo.InvariantCulture);
    	if (returnCode == 1)
    	{
    		// UserName as First Character of First Name & Full Last Name
    		return sp.Command.Parameters[2].ParameterValue.ToString();
    	}

    	if (returnCode == 2)
    	{
    		DataSet ds = sp.GetDataSet();
    		if (null == ds || null == ds.Tables[0] || 0 == ds.Tables[0].Rows.Count)
    			return "";

    		string maxLoginId = ds.Tables[0].Rows[0]["LoweredUserName"].ToString();
    		string initialLoginId = firstName.Substring(0, 1) + lastName;

    		int maxLoginIdIndex = 0;
    		int initialLoginIdLength = initialLoginId.Length;

    		if (maxLoginId.Substring(initialLoginIdLength).Length == 0)
    		{
    			maxLoginIdIndex++;

    			// UserName as Max Lowered User Name Found & Incrementer as Suffix (Here, First Incrementer i.e. 1)
    			return (initialLoginId + maxLoginIdIndex);
    		}

    		if (int.TryParse(maxLoginId.Substring(initialLoginIdLength), out maxLoginIdIndex))
    		{
    			if (maxLoginIdIndex > 0)
    			{
    				maxLoginIdIndex++;

    				// UserName as Max Lowered User Name Found & Incrementer as Suffix
    				return (initialLoginId + maxLoginIdIndex);
    			}
    		}
    	}
    }

Now the problem is for some input (see test data below), the login id created at sql server end correctly but at application subsonic dal side, it truncates some characters.

First Name - Jenelia and Last Name - Kanupatikenalaalayampentyalavelugoplansubhramanayam

[dbo].[Users_FetchLoginId] - Execute Stored Procedure Separately - Login Id Is Correct JKanupatikenalaalayampentyalavelugoplansubhramanayam

public string FetchLoginId(string firstName, string lastName) - Application Code DAL Side - LginId Is Wrongly Received From Stored Procedure JKanupatikenalaalayampentyalavelugoplansubhramanay

You can easily see that 2 charactes are removed. If the data is correctly generated by stored procedure then why the characters are removed when data is received in output parameter of stored procedure? Is it due to any internal known or unknown bug of SubSonic? Your help is significant. Thanks in advance...