0

I have this C# code

try
{
    using (var conn = new SqlConnection(connectionString))
    using (var command = new SqlCommand("deepstoredProc", conn)
               {
                   CommandType = CommandType.StoredProcedure
               })
    {
        command.Parameters.Add("@recid", SqlDbType.Int).Value = Int32.Parse(recid);
        command.Parameters.Add("@jsonstr", SqlDbType.VarChar, -1).Value = jsonstr;
        command.Parameters.Add("@text", SqlDbType.VarChar, -1).Value = txt;

        conn.Open();
        command.ExecuteNonQuery();
    }
}
catch(Exception e)
{
    WriteToSysLog("Exception execSP() " + e.Message);
}
finally
{
}

This is the SQL Server stored procedure. I am having trouble only with the update query

USE [testDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[deepstoredProc] 
    @recid int, 
    @jsonstr nvarchar(max), 
    @text nvarchar(max)
AS
BEGIN
    UPDATE table1 
    SET transcript = @text, 
        field1 = 'Y' 
    WHERE recordingid = @recid

    INSERT INTO othertable  
    VALUES (@recid, '', 'textvalue1', COMPRESS(@jsonstr), 'en')

    INSERT INTO othertable  
    VALUES (@recid, '', 'textvalue2', COMPRESS(@text), 'en')
END

This only updates table1 transcript field with the first character of the @text value

The table column is defined as varchar(max).

Also note that when I run the update script in SSMS, it executes perfectly.

This is a strange problem and appreciate any help.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Genius86
  • 1
  • 1
    `command.Parameters.Add("@text", SqlDbType.VarChar,-1)` Why are you passing `-1` here? – sujith karivelil Sep 06 '22 at 07:07
  • your varchar is too short and in fact the wrong type. – Jodrell Sep 06 '22 at 07:15
  • Also: your parameters in the stored procedure are defined as `NVARCHAR(MAX)` - yet the parameter in your C# code are defined as `SqlDbType.VarChar` - that doesn't match. You should use `SqlDbType.NVarChar` in your C# parameters! – marc_s Sep 06 '22 at 08:16
  • 1
    @sujithkarivelil: specifying `-1` as size for a `varchar` or `nvarchar` parameters defines that it's a `varchar(max)` / `nvarchar(max)` in the T-SQL code. This is ***NOT*** the problem! – marc_s Sep 06 '22 at 08:19
  • Are you sure you declared it as `nvarchar(max)` and not just `nvarchar` which defaults to `nvarchar(1)`? Also do you have any triggers on the table, or any other conversions etc that you are not showing? – Charlieface Sep 06 '22 at 11:03
  • Thanks for all of your responses! I changed the SP to make parameters of the type varchar(max) this fixed the update query. Now the insert gets messed up. The COMPRESS values return Chinese characters when decompressed. I tried to CAST the parameters to NVARCHAR like COMPRESS(CAST(@text AS NVARCHAR(MAX)) then it works but messes up the insert. – Genius86 Sep 06 '22 at 23:54
  • Please show your current procedure and relevant C# client code. Do you have any triggers on the table? – Charlieface Sep 07 '22 at 01:57
  • @Charlieface Yes there was a trigger updating the table again. I commented it out and it works fine. Thanks for the help. – Genius86 Sep 07 '22 at 05:26

0 Answers0