在 C# 中使用参数命令更新不起作用

本文关键字：命令 更新 不起作用 参数 | 更新日期: 2023-09-27 18:34:21

美好的一天。

这是我的更新参数代码：

===================================================================================================================================================================================================================================================

==

UpdateCmd.CommandTimeout = 200;
UpdateCmd.Parameters.Add("@Status", SqlDbType.NVarChar, 50, "Status");
UpdateCmd.Parameters.Add("@JONumber", SqlDbType.NVarChar, 50, "JONumber");
UpdateCmd.Parameters["@Status"].Value = DateTime.Now.ToString("MMM d yyyy h:mm:ss tt") + " " + "Acknowledged";
UpdateCmd.Parameters["@JONumber"].Value = "7796";
UpdateCmd.ExecuteNonQuery();
=====================================================================================================================================================================================================================================================

===

现在这是完整的代码：

string updateSql = "UPDATE dbo.Table4 SET Status = Status + ', ' @Status + Name" + "WHERE JONumber = @JONumber";            
myConnection = new SqlConnection("server=192.168.249.4, 1433;" + "user id=SQL User;" +
                                "password=8C=S&6dw{2;" +
                                "Trusted_Connection=yes;" +
                                "database=ADMIN; " +
                                "connection timeout=200");
try
{
myConnection.Open();
ConsoleWriteLine("Database Open...");
SqlCommand UpdateCmd = new SqlCommand(updateSql, myConnection);
Update.CommandTimeout = 200;
UpdateCmd.CommandTimeout = 200;
UpdateCmd.Parameters.Add("@Status", SqlDbType.NVarChar, 50, "Status");
UpdateCmd.Parameters.Add("@JONumber", SqlDbType.NVarChar, 50, "JONumber");
UpdateCmd.Parameters["@Status"].Value = DateTime.Now.ToString("MMM d yyyy h:mm:ss tt") + " " + "Acknowledged";
UpdateCmd.Parameters["@JONumber"].Value = "7796";
UpdateCmd.ExecuteNonQuery();
ConsoleWriteLine("Testing Successful!!");
UpdateCmd.Dispose();
myConnection.Close();
}
catch (Exception err)
{
ConsoleWriteLine("Error: " + err.ToString());
}
if (myConnection != null)
{
myConnection.Close();
}
================================================================================================================================================================================================================================================

==注意：1( 如果从中删除以下代码，则建立连接：

    UpdateCmd.CommandTimeout = 200;
UpdateCmd.Parameters.Add("@Status", SqlDbType.NVarChar, 50, "Status");
UpdateCmd.Parameters.Add("@JONumber", SqlDbType.NVarChar, 50, "JONumber");
UpdateCmd.Parameters["@Status"].Value = DateTime.Now.ToString("MMM d yyyy h:mm:ss tt") + " " +   "Acknowledged";
UpdateCmd.Parameters["@JONumber"].Value = "7796";
UpdateCmd.ExecuteNonQuery();

2(如果未删除上述代码，则显示以下错误：

Error: System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '@Status'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()at GSMTechnicalReportWebService.MainWindow.button3_Click(Object sender, EventArgs e)
ClientConnectionId:f291cc22-e926-42ad-9901-9f44d088bd0d

3(我也尝试了这种方法，但同样的错误：

try
{
using (SqlConnection conn = new SqlConnection("user id=SQL User;" + "password=8C=S&6dw{2;server=192.168.249.4, 1433;" + "Trusted_Connection=yes;" + "database=ADMIN; " + "connection timeout=200"))
{
conn.Open();
string updateSql = "UPDATE dbo.Table4 SET Status = Status + ', ' @Status + Name" + "WHERE JONumber = @JONumber";
using (SqlCommand UpdateCmd = new SqlCommand(updateSql, conn))
{
UpdateCmd.CommandTimeout = 200;
UpdateCmd.Parameters.Add("@Status", SqlDbType.NVarChar, 50, "Status");
                    UpdateCmd.Parameters.Add("@JONumber", SqlDbType.NVarChar, 50, "JONumber");
                    UpdateCmd.Parameters["@Status"].Value = DateTime.Now.ToString("MMM d yyyy h:mm:ss tt") + " " + "Acknowledged";
                    UpdateCmd.Parameters["@JONumber"].Value = "7796";
                    UpdateCmd.ExecuteNonQuery();
                    UpdateCmd.Dispose();
                    myConnection.Close();
                }
                conn.Close();
            }
        }
        catch(Exception e){
            ConsoleWriteLine("Error: " + e.ToString());
        }
    }

4( 数据库Microsoft SQL Server

====

=====================================================================================

现在：我的问题是：

1(我的代码是否正确？如果是，我错过了什么吗？如果没有，你能为我验证一下吗？

2(使用C#更新Microsoft SQL Server中的数据库的正确方法是什么？

3( 有什么方法可以更新我的数据库吗？

在 C# 中使用参数命令更新不起作用

看起来这条线是问题所在

string updateSql = "UPDATE dbo.Table4 SET Status = Status + ', ' @Status + Name" + "WHERE JONumber = @JONumber";

上面的代码将生成以下 SQL 语法

UPDATE dbo.Table4 SET Status = Status + ', ' @Status + NameWHERE JONumber = @JONumber

这是无效的，因为@Status之前没有+，并且Name和WHERE之间没有空格，因此您得到了Incorrect syntax near '@Status'错误。

尝试将其更改为以下内容

string updateSql = "UPDATE dbo.Table4 SET Status = Status + ', ' + @Status + Name WHERE JONumber = @JONumber";
相关文章：