Hi, I'm trying to make an update to an existing application so following the same rules for querying, updating the DB. I'm getting this error when trying to execute an update transaction:
"{"String or binary data would be truncated.\r\nThe statement has been terminated."}"
this is my method:
try
{
connectionString = ConfigurationManager.AppSettings["ConnString"];
connectionString = MBRSEncrypt.Encryption.Decrypt(connectionString, "ABC123");
commandSQL = ConfigurationManager.AppSettings["PTMIGReleaseStepUpdateSQL"];
commandSQL = commandSQL.Replace("agenttask_Id", record.agenttaskId);
commandSQL = commandSQL.Replace("record_Id", record.recordId);
commandSQL = commandSQL.Replace("release_Id", record.releaseId);
commandSQL = commandSQL.Replace("status_code", status);
sqlConn = new SqlConnection(connectionString);
SqlCommand sqlComm = sqlConn.CreateCommand();
sqlComm.CommandText = commandSQL;
sqlConn.Open();
rowCount = sqlComm.ExecuteNonQuery();
if (sqlConn.State == System.Data.ConnectionState.Open)
sqlConn.Close();
if (rowCount == 0)
{
ret.success = false;
ret.status = "Record Id " + record.releaseId + " no longer in a PNDG/INPRG status";
}
else
ret.success = true;
}
Update transaction in app.config:
<add key="PTMIGReleaseStepUpdateSQL" value="
BEGIN TRANSACTION
UPDATE ERPIT_AGENT_Q_TASKS
SET AQT_STATUS_CD = 'status_code',
AQT_LAST_UPDATE_DT = GETUTCDATE(),
AQT_MODIFY_USER_ID = 'FORCE_COMPLETE_APP'
WHERE AQT_AGENT_Q_TASK_ID = 'agenttask_Id'
AND AQT_STATUS_CD IN ('INPRG', 'PNDG')
UPDATE ERPIT_REL_STEP_AGT_APP_STATUS
SET ERPIT_REL_STEP_AGT_APP_STATUS.AQTAS_STATUS_CD = 'status_code',
ERPIT_REL_STEP_AGT_APP_STATUS.AQTAS_LAST_UPDATE_DT = GETUTCDATE()
WHERE (ERPIT_REL_STEP_AGT_APP_STATUS.AQTAS_REL_STEP_AGT_APP_STATUS_ID = 'record_Id')
AND (ERPIT_REL_STEP_AGT_APP_STATUS.AQTAS_STATUS_CD IN ('INPRG', 'PNDG'))
UPDATE ERPIT_RELEASE_STEPS
SET ERPIT_RELEASE_STEPS.RELSP_STATUS_CD = 'status_code',
ERPIT_RELEASE_STEPS.RELSP_LAST_UPDATE_DT = GETUTCDATE(),
ERPIT_RELEASE_STEPS.RELSP_MODIFY_USER_ID = 'FORCE_COMPLETE_APP'
WHERE ERPIT_RELEASE_STEPS.RELSP_RELEASE_STEP_ID = 'release_Id'
COMMIT TRANSACTION
" />
Query at Runtime:
"
\r\n BEGIN TRANSACTION
\r\n UPDATE ERPIT_AGENT_Q_TASKS
\r\n SET AQT_STATUS_CD = 'SKPD',
\r\n AQT_LAST_UPDATE_DT = GETUTCDATE(),
\r\n AQT_MODIFY_USER_ID = 'FORCE_COMPLETE_APP'
\r\n WHERE AQT_AGENT_Q_TASK_ID = 'd2ce707b-362c-e411-9a87-002481f91a59'
\r\n AND AQT_STATUS_CD IN ('INPRG', 'PNDG')
\r\n
\r\n UPDATE ERPIT_REL_STEP_AGT_APP_STATUS
\r\n SET\tERPIT_REL_STEP_AGT_APP_STATUS.AQTAS_STATUS_CD = 'SKPD',
\r\n ERPIT_REL_STEP_AGT_APP_STATUS.AQTAS_LAST_UPDATE_DT = GETUTCDATE()
\r\n WHERE (ERPIT_REL_STEP_AGT_APP_STATUS.AQTAS_REL_STEP_AGT_APP_STATUS_ID = 'd8967935-342c-e411-9a87-002481f91a59')
\r\n AND\t(ERPIT_REL_STEP_AGT_APP_STATUS.AQTAS_STATUS_CD IN ('INPRG', 'PNDG'))
\r\n
\r\n UPDATE ERPIT_RELEASE_STEPS
\r\n SET ERPIT_RELEASE_STEPS.RELSP_STATUS_CD = 'SKPD',
\r\n\t ERPIT_RELEASE_STEPS.RELSP_LAST_UPDATE_DT = GETUTCDATE(),
\r\n\t ERPIT_RELEASE_STEPS.RELSP_MODIFY_USER_ID = 'FORCE_COMPLETE_APP'
\r\n WHERE ERPIT_RELEASE_STEPS.RELSP_RELEASE_STEP_ID = 'd4967935-342c-e411-9a87-002481f91a59'
\r\n COMMIT TRANSACTION
\r\n\t\t
"
Is there anything obvious causing the problem?
- Missing code block (3rd) added
- Divided the snippet into lines and...
- Quotes out of code block to better readability