ASP.NET sends a single character instead of multiple characters to SQL

Hello there,

I’ve been working on this very simple problem for days. I really don’t know what’s wrong. I have an insert statement that saves the Category Code, Call Number, and other details of a book to an SQL Server Database. The code is found below. The bold font is the one having the problem.

neither the code, nor the stored procedure has an error. The only problem I have is that the Category Code of “CHM” for instance, is reduced to a single character “C”. The same thing happens with all other category codes that were inserted. Only the first character is saved. This is odd, because the Call Number, which has the same data type and string length, does not suffer the problem. It is saved with an intact 3 number of characters in the database. I left a breakpoint on the part that captures the content of the string variable CategoryCode, and it clearly contains a total of 3 characters. I also executed the stored procedure directly in the database, and it’s also saving 3 characters. However, when ASP.NET connects to the database using that stored procedure, it always save a single character for category code.

I really don’t know why this is happening. I am really hoping that you could help me. ūüôĀ

Thank you so much in advance.

 public static string Insert(string Title, string Author, string CategoryCode, string CallNumber, int NoOfCopies, string Details, string UserID, string InsertType, DateTime DateUpdated)
    {
¬†¬† ¬† ¬† ¬†string returnMsg = “”;
¬†¬† ¬† ¬† ¬†String myDbCmdStr = “Book_Insert”;
        using (DbConnection myDbConn = _DbFactory.CreateConnection())
        {
            myDbConn.ConnectionString = _ConnectionString;
            try
            {
                using (DbCommand myDbCmd = _DbFactory.CreateCommand())
                {
                    myDbCmd.CommandType = CommandType.StoredProcedure;
                    myDbCmd.CommandText = myDbCmdStr;
                    DbParameter myDbParam;
                    myDbParam = _DbFactory.CreateParameter();
                    myDbParam.DbType = DbType.String;
                    myDbParam.Direction = ParameterDirection.Input;
¬†¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬†myDbParam.ParameterName = _ParamPrefix + “Title”;
                    myDbParam.Size = 70;
                    myDbParam.Value = Title;
                    myDbCmd.Parameters.Add(myDbParam);
                    DbParameter myDbParam1;
                    myDbParam1 = _DbFactory.CreateParameter();
                    myDbParam1.DbType = DbType.String;
                    myDbParam1.Direction = ParameterDirection.Input;
¬†¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬†myDbParam1.ParameterName = _ParamPrefix + “Author”;
                    myDbParam1.Size = 70;
                    myDbParam1.Value = Author;
                    myDbCmd.Parameters.Add(myDbParam1);
                    DbParameter myDbParamT;
                    myDbParamT = _DbFactory.CreateParameter();
                    myDbParamT.DbType = DbType.String;
                    myDbParamT.Direction = ParameterDirection.Input;
¬†¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬†myDbParamT.ParameterName = _ParamPrefix + “CatCode”;
                    //myDbParam2.Size = 9;
                    myDbParamT.Value = CategoryCode;
                    myDbCmd.Parameters.Add(myDbParamT);
                    DbParameter myDbParam3;
                    myDbParam3 = _DbFactory.CreateParameter();
                    myDbParam3.DbType = DbType.String;
                    myDbParam3.Direction = ParameterDirection.Input;
¬†¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬†myDbParam3.ParameterName = _ParamPrefix + “CallNumber”;
                    myDbParam3.Size = 9;
                    myDbParam3.Value = CallNumber;
                    myDbCmd.Parameters.Add(myDbParam3);
                    DbParameter myDbParam4;
                    myDbParam4 = _DbFactory.CreateParameter();
                    myDbParam4.DbType = DbType.Int32;
                    myDbParam4.Direction = ParameterDirection.Input;
¬†¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬†myDbParam4.ParameterName = _ParamPrefix + “NoOfCopies”;
                    myDbParam4.Value = NoOfCopies;
                    myDbCmd.Parameters.Add(myDbParam4);
                    DbParameter myDbParam5;
                    myDbParam5 = _DbFactory.CreateParameter();
                    myDbParam5.DbType = DbType.String;
                    myDbParam5.Direction = ParameterDirection.Input;
¬†¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬†myDbParam5.ParameterName = _ParamPrefix + “Details”;
                    myDbParam5.Size = 70;
                    myDbParam5.Value = Details;
                    myDbCmd.Parameters.Add(myDbParam5);
                    DbParameter myDbParam6;
                    myDbParam6 = _DbFactory.CreateParameter();
                    myDbParam6.DbType = DbType.String;
                    myDbParam6.Direction = ParameterDirection.Input;
¬†¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬†myDbParam6.ParameterName = _ParamPrefix + “UserID”;
                    myDbParam6.Size = 20;
                    myDbParam6.Value = UserID;
                    myDbCmd.Parameters.Add(myDbParam6);
                    DbParameter myDbParam7;
                    myDbParam7 = _DbFactory.CreateParameter();
                    myDbParam7.DbType = DbType.String;
                    myDbParam7.Direction = ParameterDirection.Input;
¬†¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬†myDbParam7.ParameterName = _ParamPrefix + “InsertType”;
                    myDbParam7.Size = 1;
                    myDbParam7.Value = InsertType;
                    myDbCmd.Parameters.Add(myDbParam7);
                    DbParameter myDbParam8;
                    myDbParam8 = _DbFactory.CreateParameter();
                    myDbParam8.DbType = DbType.DateTime;
                    myDbParam8.Direction = ParameterDirection.Input;
¬†¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬†myDbParam8.ParameterName = _ParamPrefix + “DateUpdated”;
                    myDbParam8.Value = DateUpdated;
                    myDbCmd.Parameters.Add(myDbParam8);
                    myDbCmd.Connection = myDbConn;
                    myDbConn.Open();
                    int inserted = (int)myDbCmd.ExecuteNonQuery();
                    if (inserted >= 1)
¬†¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬†returnMsg = “New book added to the database.”;
                }
            }
            catch (Exception error)
            {
¬†¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬†returnMsg = “New book was not added to the database.\nReason: ” + error.Message;
            }
            finally
            {
                if (myDbConn.State == ConnectionState.Open) myDbConn.Close();
            }
        } // using db connection
        return returnMsg;
    }

 public static string Insert(string Title, string Author, string CategoryCode, string CallNumber, int NoOfCopies, string Details, string UserID, string InsertType, DateTime DateUpdated)

    {

¬†¬† ¬† ¬† ¬†string returnMsg = “”;

¬†¬† ¬† ¬† ¬†String myDbCmdStr = “Book_Insert”;

        using (DbConnection myDbConn = _DbFactory.CreateConnection())

        {

            myDbConn.ConnectionString = _ConnectionString;

            try

            {

                using (DbCommand myDbCmd = _DbFactory.CreateCommand())

                {

                    myDbCmd.CommandType = CommandType.StoredProcedure;

                    myDbCmd.CommandText = myDbCmdStr;

                    DbParameter myDbParam;

                    myDbParam = _DbFactory.CreateParameter();

                    myDbParam.DbType = DbType.String;

                    myDbParam.Direction = ParameterDirection.Input;

¬†¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬†myDbParam.ParameterName = _ParamPrefix + “Title”;

                    myDbParam.Size = 70;

                    myDbParam.Value = Title;

                    myDbCmd.Parameters.Add(myDbParam);

                    DbParameter myDbParam1;

                    myDbParam1 = _DbFactory.CreateParameter();

                    myDbParam1.DbType = DbType.String;

                    myDbParam1.Direction = ParameterDirection.Input;

¬†¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬†myDbParam1.ParameterName = _ParamPrefix + “Author”;

                    myDbParam1.Size = 70;

                    myDbParam1.Value = Author;

                    myDbCmd.Parameters.Add(myDbParam1);

                    DbParameter myDbParam2;

                    myDbParam2 = _DbFactory.CreateParameter();

                    myDbParam2.DbType = DbType.String;

                    myDbParam2.Direction = ParameterDirection.Input;

¬†¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬†myDbParam2.ParameterName = _ParamPrefix + “CatCode”;

                    myDbParam2.Size = 3;

                    myDbParamT.Value = CategoryCode;

                    myDbCmd.Parameters.Add(myDbParam2);

                    DbParameter myDbParam3;

                    myDbParam3 = _DbFactory.CreateParameter();

                    myDbParam3.DbType = DbType.String;

                    myDbParam3.Direction = ParameterDirection.Input;

¬†¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬†myDbParam3.ParameterName = _ParamPrefix + “CallNumber”;

                    myDbParam3.Size = 3;

                    myDbParam3.Value = CallNumber;

                    myDbCmd.Parameters.Add(myDbParam3);

                    DbParameter myDbParam4;

                    myDbParam4 = _DbFactory.CreateParameter();

                    myDbParam4.DbType = DbType.Int32;

                    myDbParam4.Direction = ParameterDirection.Input;

¬†¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬†myDbParam4.ParameterName = _ParamPrefix + “NoOfCopies”;

                    myDbParam4.Value = NoOfCopies;

                    myDbCmd.Parameters.Add(myDbParam4);

                    DbParameter myDbParam5;

                    myDbParam5 = _DbFactory.CreateParameter();

                    myDbParam5.DbType = DbType.String;

                    myDbParam5.Direction = ParameterDirection.Input;

¬†¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬†myDbParam5.ParameterName = _ParamPrefix + “Details”;

                    myDbParam5.Size = 70;

                    myDbParam5.Value = Details;

                    myDbCmd.Parameters.Add(myDbParam5);

                    DbParameter myDbParam6;

                    myDbParam6 = _DbFactory.CreateParameter();

                    myDbParam6.DbType = DbType.String;

                    myDbParam6.Direction = ParameterDirection.Input;

¬†¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬†myDbParam6.ParameterName = _ParamPrefix + “UserID”;

                    myDbParam6.Size = 20;

                    myDbParam6.Value = UserID;

                    myDbCmd.Parameters.Add(myDbParam6);

                    DbParameter myDbParam7;

                    myDbParam7 = _DbFactory.CreateParameter();

                    myDbParam7.DbType = DbType.String;

                    myDbParam7.Direction = ParameterDirection.Input;

¬†¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬†myDbParam7.ParameterName = _ParamPrefix + “InsertType”;

                    myDbParam7.Size = 1;

                    myDbParam7.Value = InsertType;

                    myDbCmd.Parameters.Add(myDbParam7);

                    DbParameter myDbParam8;

                    myDbParam8 = _DbFactory.CreateParameter();

                    myDbParam8.DbType = DbType.DateTime;

                    myDbParam8.Direction = ParameterDirection.Input;

¬†¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬†myDbParam8.ParameterName = _ParamPrefix + “DateUpdated”;

                    myDbParam8.Value = DateUpdated;

                    myDbCmd.Parameters.Add(myDbParam8);

                    myDbCmd.Connection = myDbConn;

                    myDbConn.Open();

                    int inserted = (int)myDbCmd.ExecuteNonQuery();

                    if (inserted >= 1)

¬†¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬†returnMsg = “New book added to the database.”;

                }

            }

            catch (Exception error)

            {

¬†¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬†returnMsg = “New book was not added to the database.\nReason: ” + error.Message;

            }

            finally

            {

                if (myDbConn.State == ConnectionState.Open) myDbConn.Close();

            }

        } // using db connection

        return returnMsg;

    }

Here is the stored procedure that was called by that ASP.NET C# code.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[Book_Insert]

@Title NVARCHAR(70) = null,

@Author NVARCHAR(70) = null,

@CatCode NVARCHAR(3) = null,

@CallNumber NVARCHAR(3) = null,

@NoOfCopies INT = 0,

@Details NVARCHAR(70) = null,

@UserID NVARCHAR(20) = null,

@InsertType CHAR(1) = null,

@DateUpdated datetime = null

AS

DECLARE @counter int

DECLARE @inserted int

DECLARE @noOfBooks int

DECLARE @noOfBookCopies int

SET @noOfBooks = 0

IF @InsertType = ‘B’ –for inserting books and the number of book copies

  

BEGIN

¬†¬† –BEGIN TRANSACTION

   SET @noOfBooks = 

   ( 

   SELECT COUNT(*) FROM Book 

   WHERE Category = @CatCode AND

   CallNumber = @CallNumber

   )

   IF @noOfBooks > 0 

        BEGIN

             ROLLBACK TRANSACTION 

             RETURN -1

        END

   ELSE

   BEGIN

   INSERT INTO Book (Category,CallNumber,Title,Author,Details,LastUpdateBy,LastUpdateDate)

    VALUES (@CatCode,@CallNumber,@Title,@Author,@Details,@UserID,@DateUpdated)

   SET @noOfBookCopies = 0

   SET @counter = 1

   SET @inserted = 0

   WHILE @inserted < @NoOfCopies

         BEGIN

              SET @noOfBookCopies = 

              (

              SELECT COUNT(*) FROM BookCopy

              WHERE CategoryCode = @CatCode AND

              CallNumber = @CallNumber AND

¬†¬† ¬† ¬† ¬† ¬† ¬† ¬†CONVERT(int, CopyNumber) = @counter –this checks if a certain bookcopy already exists in the database

              )

              IF @noOfBookCopies = 0

                 BEGIN

                    BEGIN TRANSACTION 

                    INSERT INTO BookCopy (CategoryCode, CallNumber, CopyNumber,OnLoan,TimesBorrowed,LastUpdateBy,LastUpdateDate)

¬†¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬†VALUES(@CatCode,@CallNumber,Right(‘000’+RTrim(CONVERT(varchar(3),@counter)),3),0,0,@UserID,@DateUpdated)

                    SET @inserted = @inserted + 1                    

                    IF @@ERROR <> 0

                      BEGIN

                         ROLLBACK TRANSACTION

                         RETURN -1

                      END

                    ELSE

                      BEGIN

                         COMMIT TRANSACTION

                      END

                END

          SET @counter = @counter + 1

    END

¬†¬† –COMMIT TRANSACTION

   RETURN 1

   END

END

IF @InsertType = ‘C’ –for inserting book copies only

BEGIN

¬†¬† ¬†–BEGIN TRANSACTION

      SET @counter = 1

      SET @inserted = 0

      SET @noOfBookCopies = 0

      WHILE @inserted < @NoOfCopies

          BEGIN 

              SET @noOfBookCopies = 

              (

              SELECT COUNT(*) FROM BookCopy

              WHERE CategoryCode = @CatCode AND

              CallNumber = @CallNumber AND

              CONVERT(int, CopyNumber) = @counter

              )

              IF @noOfBookCopies = 0

                 BEGIN

                    BEGIN TRANSACTION

                    INSERT INTO BookCopy (CategoryCode, CallNumber, CopyNumber,OnLoan,TimesBorrowed,LastUpdateBy,LastUpdateDate)

¬†¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬†VALUES(@CatCode,@CallNumber,Right(‘000’+RTrim(CONVERT(varchar(3),@counter)),3),0,0,@UserID,@DateUpdated)

                    SET @inserted = @inserted + 1            

                    IF @@ERROR <> 0

                      BEGIN

                        ROLLBACK TRANSACTION

                        RETURN -1

                      END

                    

                    ELSE 

                      BEGIN

                        COMMIT TRANSACTION

                      END

                 END

          SET @counter = @counter + 1

          END

¬†¬† ¬† ¬†–COMMIT TRANSACTION

    RETURN 1

END

Nothing comes to mind by looking at your code, so what I would do is use SQL Profiler to see exactly the SQL command that .NET is passing to SQL in the call.