-1

All related subjects I saw in this site discuss stored procedures and alike. I have a C# program that receives a string (not important from where, maybe file or uploaded) and tries to insert it into a varchar(MAX) column. My code is like this:

command.Parameters.AddWithValue("@pxslt", mer.XsltTemplate);

It raised an error:

String or binary data would be truncated.

I changed it to:

command.Parameters.Add("@pxslt", SqlDbType.).Value = mer.XsltTemplate;

but still it raises the same error.

What is the solution? The size of my string is about 8100 characters.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Pat928
  • 49
  • 7
  • 1
    Check the size of the column in the DB, likely this question is a duplicate of http://stackoverflow.com/questions/5591473/error-string-or-binary-data-would-be-truncated-when-trying-to-insert – RamblinRose Jan 23 '17 at 16:45
  • Your code is incorrect... `SqlDbType.VarChar` and assign a maximum value parameter? – IronAces Jan 23 '17 at 16:47
  • http://stackoverflow.com/questions/973260/what-size-do-you-use-for-varcharmax-in-your-parameter-declaration – TyCobb Jan 23 '17 at 16:48
  • 1
    Even if the column really is varchar(MAX), the stored procedure parameter might be declared incorrectly so make sure you check that too. – itsme86 Jan 23 '17 at 16:49
  • @itsme86 I dont use stored procedure. – Pat928 Jan 23 '17 at 17:00
  • 1
    Maybe post your SQL command. – itsme86 Jan 23 '17 at 17:02
  • SqlCommand command = new SqlCommand("INSERT INTO TBMERCHANT(MERCHANTID,MERCHANTNAME,MERCHANTURI,DESCRIPTION,USERNAME,PASSWORD,LOGO,XSLTTEMPLATE)" + "values(@pid,@pnom,@puri,@pdes,@puser,@ppass,@plogo,@pxslt)"); command.Parameters.AddWithValue("@pid", mer.merid); command.Parameters.AddWithValue("@pnom", mer.mername); command.Parameters.AddWithValue("@puri", mer.uri); – Pat928 Jan 23 '17 at 17:09
  • command.Parameters.AddWithValue("@pdes", mer.description); command.Parameters.AddWithValue("@puser", mer.username); command.Parameters.AddWithValue("@ppass", mer.password); command.Parameters.AddWithValue("@plogo", mer.logo); command.Parameters.Add("@pxslt", SqlDbType.VarChar,-1).Value = mer.XsltTemplate; command.Connection = connection; connection.Open(); command.ExecuteNonQuery(); connection.Close(); – Pat928 Jan 23 '17 at 17:10
  • How do you know it is the parameter `@pxslt` that is causing the issue? – GarethD Jan 24 '17 at 08:55

2 Answers2

3

You can use this syntax:

command.Parameters.Add("@pxslt", SqlDbType.VarChar, -1).Value = mer.XsltTemplate;

NB: -1 equates to (MAX).

Nick Allan
  • 387
  • 4
  • 10
  • I tested this but still the same problem. I also tested this: command.Parameters.Add("@pxslt", SqlDbType.VarChar,mer.XsltTemplate.Length).Value = mer.XsltTemplate; – Pat928 Jan 23 '17 at 16:58
  • What data type is .XsltTemplate? String I presume? Can you also give us the definition for the stored procedure? At least from the CREATE PROCEDURE down to the AS clause please? – Nick Allan Jan 23 '17 at 17:02
  • I dont use stored procedure. As a hint, I use an mdf file as the database. Is it possible that this causes the problem? – Pat928 Jan 23 '17 at 17:06
  • Ah okay, assumed you were using SQL Server on account of the [sql] [server] tags. If you are using MS Access I guess you will be constructing an INSERT statement rather than an EXECUTE statement, which is what the command.Parameters are normally for. – Nick Allan Jan 23 '17 at 17:36
0

I got it. The error raises because of another field. This was a field varchar(15) and the program tries to insert a string of 20 chars. I also noticed that varchar(max) works even without casting to DbType.

Pat928
  • 49
  • 7