none
SqlDbType.Text ==> Max length 4000 chars? RRS feed

  • Question

  • Hi,

    We've a serious problem.


    We've a .net CLR Stored Procedure for SQL Server 2005. Here's a part of it:

     

    [SqlProcedure]

    public static void ChangeProduct(int productId, string name, int groupid, bool visible, double PurchasePrice, double SellPrice,

    bool RepeatingBool, int repeatmonths, int PriceRepeatMonths, string facdescription, string picture, string shortdescription,

    string longdescription, int sortID, bool priceVisible)

    {

    SqlPipe sp = SqlContext.Pipe;

    using (SqlConnection conn = new SqlConnection("context connection=true"))

    {

    conn.Open();

    SqlCommand cmd = new SqlCommand();

    cmd.CommandType = CommandType.Text;

    cmd.Connection = conn;

    cmd.CommandText = @"UPDATE Products SET name = @name, groupId = @groupid, visible = @visible,

    PurchasePrice = @PurchasePrice, SellPrice = @SellPrice, RepeatingBool = @RepeatingBool,

    repeatmonths = @repeatmonths, PriceRepeatMonths = @PriceRepeatMonths, facdescription = @facdescription,

    picture = @picture, shortdescription = @shortdescription, longdescription = @longdescription, sortID = @sortID,

    priceVisible = @priceVisible WHERE productId = @productId";

    //productID

    SqlParameter paramProductID = new SqlParameter("@productID", SqlDbType.Int);

    paramProductID.Value = productId;

    cmd.Parameters.Add(paramProductID);

    //name

    SqlParameter paramName = new SqlParameter("@name", SqlDbType.VarChar, 50);

    paramName.Value = name;

    cmd.Parameters.Add(paramName);

    //groupId

    SqlParameter paramGroupId = new SqlParameter("@groupId", SqlDbType.Int);

    paramGroupId.Value = groupid;

    cmd.Parameters.Add(paramGroupId);

    //visible

    SqlParameter paramVisible = new SqlParameter("@visible", SqlDbType.Bit);

    paramVisible.Value = visible;

    cmd.Parameters.Add(paramVisible);

    //purchasePrice

    SqlParameter paramPurchasePrice = new SqlParameter("@purchasePrice", SqlDbType.Decimal);

    paramPurchasePrice.Value = PurchasePrice;

    cmd.Parameters.Add(paramPurchasePrice);

    //sellPrice

    SqlParameter paramSellPrice = new SqlParameter("@sellPrice", SqlDbType.Decimal);

    paramSellPrice.Value = SellPrice;

    cmd.Parameters.Add(paramSellPrice);

    //repeatingBool

    SqlParameter paramRepeatingBool = new SqlParameter("@repeatingBool", SqlDbType.Bit);

    paramRepeatingBool.Value = RepeatingBool;

    cmd.Parameters.Add(paramRepeatingBool);

    //repeatmonths

    SqlParameter paramRepeatmonths = new SqlParameter("@repeatMonths", SqlDbType.Int);

    paramRepeatmonths.Value = repeatmonths;

    cmd.Parameters.Add(paramRepeatmonths);

    //priceRepeatMonths

    SqlParameter paramPriceRepeatMonths = new SqlParameter("@priceRepeatMonths", SqlDbType.Int);

    paramPriceRepeatMonths.Value = PriceRepeatMonths;

    cmd.Parameters.Add(paramPriceRepeatMonths);

    //facdescription

    SqlParameter paramFacdescription = new SqlParameter("@facdescription", SqlDbType.Text);

    paramFacdescription.Value = facdescription;

    cmd.Parameters.Add(paramFacdescription);

    //picture

    SqlParameter paramPicture = new SqlParameter("@picture", SqlDbType.VarChar, 255);

    paramPicture.Value = picture;

    cmd.Parameters.Add(paramPicture);

    //shortdescription

    SqlParameter paramShortdescription = new SqlParameter("@shortdescription", SqlDbType.Text);

    paramShortdescription.Value = shortdescription;

    cmd.Parameters.Add(paramShortdescription);

    //longdescription

    SqlParameter paramLongdescription = new SqlParameter("@longdescription", SqlDbType.Text);

    paramLongdescription.Value = longdescription;

    cmd.Parameters.Add(paramLongdescription);

    //sortID

    SqlParameter paramSortID = new SqlParameter("@sortID", SqlDbType.Int);

    paramSortID.Value = sortID;

    cmd.Parameters.Add(paramSortID);

    //priceVisible

    SqlParameter paramPriceVisible = new SqlParameter("@priceVisible", SqlDbType.Bit);

    paramPriceVisible.Value = priceVisible;

    cmd.Parameters.Add(paramPriceVisible);

    cmd.ExecuteNonQuery();

    }

    }


     

    Our problem: we can't insert more than 4000 characters in the "longdescription" field. How can we fix this? The database field is "text".

    Thursday, September 27, 2007 12:50 PM

All replies