0

When I run my code locally everything is fine, but the same code running externally I am getting a strange issue, instead of displaying a string ID on screen I am getting a message String or binary data would be truncated.

Below is a sample of the error:

http://www.mydomain.com/String or binary data would be truncated.~38249/

Functions.cs

public string EmailBodyPaymentLink(string sysId, bool forDisplay)
{
    var sb = new StringBuilder();
    sb.Append("<p><a href='" + "http://www.mydomain.com/pID~aID/'>PAY NOW</a></p>");
    sb.Append("<p>If the link above is not clickable, please copy the following into the Address Bar of your chosen Internet Browser:</p>");
    sb.Append("<p>http://www.mydomain.com/pID~aID/</p>");
}

HomeController.cs

var sbSql = new StringBuilder();
sbSql.Append("INSERT INTO [dbo].[Transactions] ");
sbSql.Append("([aID], [emailDate], [Amount], [dDate], [status], [systemID], [userID], [emailFrom]) ");
sbSql.Append("OUTPUT Inserted.[pID] ");
sbSql.Append("VALUES (");
sbSql.Append("'" + aId + "', '" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "', " + amount + ", '" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "', 'Email Sent', '" + sysId + "', '" + userDetails[1] + "', '" + emailFrom + "' ");
sbSql.Append(")");

string pId = _cf.RunSql(sbSql.ToString());

string strEmailBody = TempData["EmailHeader"].ToString().Replace("<span id='lblValue'></span>", amount) + "<p>" + emailBody + "</p>" + TempData["EmailFooter"].ToString().Replace("pID", pId).Replace("aID", aId);

This is bizarre, works locally with no issues whatsoever and I've published to our internal server via Visual Studio 2010 using FileSystem.

Any help would be much appreciated :-)

Rune
  • 8,340
  • 3
  • 34
  • 47
iggyweb
  • 2,373
  • 12
  • 47
  • 77
  • 1
    Are you saving data to Data-Base ? May be length of your column is not enough to store. – Saranga Jul 07 '14 at 09:31
  • This error occurs when inserting/updating data in the db where the data is larger than the specified column length. – Ric Jul 07 '14 at 09:31
  • split out the concatenation and figure out which variable is causing the error. – user1666620 Jul 07 '14 at 09:32
  • 1
    a bit more about `sbSql`,maybe ? – tschmit007 Jul 07 '14 at 09:32
  • What is the value/contents of `sbSql` here? (note: the fact that it is a single string rather than string+parameters already makes me think that I don't want to know the answer to that question...) – Marc Gravell Jul 07 '14 at 09:34
  • I've added the sbSql to help. – iggyweb Jul 07 '14 at 09:41
  • One of your values is longer than the database is allowing. For example `emailFrom` may be a `VARCHAR(50)` but you are passing in a 55 character long string. Without the definition of the tables and the values passed in, it's impossible to know which one. – DavidG Jul 07 '14 at 09:49
  • possible duplicate of [SQLException : String or binary data would be truncated](http://stackoverflow.com/questions/779082/sqlexception-string-or-binary-data-would-be-truncated) – DavidG Jul 07 '14 at 09:53
  • The returned pID should have been `[pID] [int] IDENTITY(1,1) NOT NULL` I've output the sbSql to screen and viewed, locally and remotely and the test user I am using has not been setup with one of our systems so is not returning a varchar(3) userID, it was returning an error `end of function login user *` instead, hence the issue. Thank you for everyone's input this is how resolved. – iggyweb Jul 07 '14 at 09:58

2 Answers2

0

I think the bottom line of the problem is the actual data which is passed in. My guess is one of your fields is bigger than your destination columns.

Check using SQL Profiler

RaM
  • 1,126
  • 10
  • 25
0

Check your columns in [dbo].[Transactions] Table and they may have a length which is less than the length of the data you are sending.

Thanks !

Saranga
  • 3,178
  • 1
  • 18
  • 26