从app.config运行更新事务时出错 [英] Error on update transaction running from app.config
问题描述
我正在尝试对现有应用程序进行更新，因此遵循相同的查询规则，更新数据库。我在尝试执行更新事务时遇到此错误：
{字符串或二进制数据将被截断。\\\\ n语句已被终止。}
这是我的方法：
试用
{
connectionString = ConfigurationManager.AppSettings [ ConnString];
// 解密来自config的连接字符串
connectionString = MBRSEncrypt.Encryption。解密（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 = 记录ID + record.releaseId + 不再处于PNDG / INPRG状态;
}
else
// 没有记录更新
ret.success = true ;
}
在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'
和AQT_STATUS_CD IN（ 'INPRG'， 'PNDG'）
UPDATE ERPIT_REL_STEP_AGT_APP_STATUS
设置ERPIT_REL_STEP_AGT_APP_STATUS.AQTAS_STATUS_CD = 'STATUS_CODE'，
ERPIT_REL_STEP_AGT_APP_STATUS.AQTAS_LAST_UPDATE_DT = GETUTCDATE（）
，其中（ERPIT_REL_STEP_AGT_APP_STATUS.AQTAS_REL_STEP_AGT_APP_STATUS_ID = 'RECORD_ID'）
和（ERPIT_REL_STEP_AGT_APP_STATUS.AQTAS_STATUS_CD IN（ 'INPRG'， 'PNDG'））
更新ERPIT_RELEASE_STEPS
设置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'
其中ERPIT_RELEASE_STEPS.RELSP_RELEASE_STEP_ID = 'release_Id'
COMMIT TRANSACTION
/ >
运行时查询：
< pre lang =SQL> \\\\ n BEGIN TRANSACTION
\\ r \ n 更新 ERPIT_AGENT_Q_TASKS
\\ n SET AQT_STATUS_CD = ' SKPD'，
\\\ AQT_LAST_UPDATE_DT = GETUTCDATE（），
\\ \\\\ AQT_MODIFY_USER_ID = ' FORCE_COMPLETE_APP'
\\ n WHERE AQT_AGENT_Q_TASK_ID = ' d2ce707b-362c-e411-9a87-002481f91a59'
\\ n AND AQT_STATUS_CD IN （' INPRG'，' PNDG'）
\ r \ n
\\ n 更新 ERPIT_REL_STEP_AGT_APP_STATUS
\r\\\
SET\tERPIT_REL_STEP_AGT_APP_STATUS.AQTAS_STATUS_CD = <跨度类= 代码串> ' <跨度类= 代码串> SKPD' ，
\r\\\
ERPIT_REL_STEP_AGT_APP_STATUS.AQTAS_LAST_UPDATE_DT = GETUTCDATE（）
\r\\\
<跨度类= 代码关键字> 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
\\\\ n 更新 ERPIT_RELEASE_STEPS
\\ n SET ERPIT_RELEASE_STEPS.RELSP_STATUS_CD = ' SKPD'，
\\ n \t ERPIT_RELEASE_STEPS.RELSP_LAST_UPDATE_DT = GETUTCDATE（），
\r\\\
\t ERPIT_RELEASE_STEPS.RELSP_MODIFY_USER_ID = <跨度类= 代码串>' <跨度类=代码 - 字符串> FORCE_COMPLETE_APP '
\r\\\
<跨度类= 代码关键字> WHERE ERPIT_RELEASE_STEPS.RELSP_RELEASE_STEP_ID = <跨度类= 代码串>' d4967935-342c-e411-9a87-002481f91a59'
\\\\ n COMMIT TRANSACTION
\\\\\\\
是有什么明显的问题导致问题吗？
- 缺少代码块（第3个）添加
- 将片段划分为行和...
- 代码块中的引号可读性
检查列大小。您的一个输入值大于列数据类型定义。
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"];
//decrypt the connection string from config
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
//no record was updated
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
Check the column size. One of your input value is bigger than your column data type definition.
这篇关于从app.config运行更新事务时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
