尝试更新表时从数据库中获取错误消息

本文关键字：获取 取错误 消息 数据库 更新 | 更新日期: 2023-09-27 17:57:52

我创建了一个如下表：

string fullTableName = string.Format("[dbo].[{0}]", tableName);
string fullKeyName = string.Format("[PK_{0}]", tableName);
string query = string.Format("{0} {1} {2} {3} {4} {5} {6} {7} {8} {9} {10} {11} {12} {13} {14} {15} {16} {17} {18} {19}"
     , "USE [DB_15202_2614d162]"
     , "SET ANSI_NULLS ON"
     , "SET QUOTED_IDENTIFIER ON"
     , "CREATE TABLE ", fullTableName, " ([ClientCode] [nchar](5) NOT NULL, [CompanyName] [nvarchar](40) NOT NULL,"
     , "[Address1] [nvarchar](60) NOT NULL, [Address2] [nvarchar](60) NULL, [City] [nvarchar](20) NULL,"
     , "[Province] [nvarchar](10) NOT NULL, [PostalCode] [nvarchar](10) NULL, [YTDSales] [decimal](18, 2) NOT NULL,"
     , "[CreditHold] [tinyint] NOT NULL, [Notes] [nvarchar](max) NULL,"
     , "CONSTRAINT ", fullKeyName, " PRIMARY KEY CLUSTERED"
     , "([ClientCode] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])"
     , "ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]"
     , "INSERT INTO ", fullTableName, " ([ClientCode], [CompanyName], [Address1], 
        [Address2], [City], [Province], [PostalCode], [YTDSales], [CreditHold], [Notes])
        VALUES (N'AROUT', N'Around the Horn', N'120 Hanover Sq.', NULL, N'London', N'ON', 
                N'L4N 7G5', CAST(1500.00 AS Decimal(18, 2)), 1, null)"
     , "INSERT INTO ", fullTableName, " ([ClientCode], [CompanyName], [Address1], 
        [Address2], [City], [Province], [PostalCode], [YTDSales], [CreditHold], [Notes])
        VALUES (N'BOTTM', N'Bottom-Dollar Markets', N'23 Tsawassen Blvd.', NULL, 
                N'Tsawassen', N'BC', N'V2R 7A6', CAST(4689.24 AS Decimal(18, 2)), 0, N'Longest standing customer')"
                                   );
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = query;
                cmd.Connection = conn;
                conn.Open();
                cmd.ExecuteNonQuery();
            }
        }

当我尝试从这里更新表格时：

public static int UpdateClient(Client client)
    {
        using (SqlConnection conn = new SqlConnection(connString))
        {
            string query = string.Format("{0} {1} {2} {3} {4} {5}{6} {7} {8} {9}"
                          , "UPDATE Client906697"
                          , "SET ClientCode = @clientCode"
                          , "CompanyName = @companyName"
                          , ",Address1 = @address1"
                          , ",Address2 = @address2"
                          , ",City = @city"
                          , ",Province = @province"
                          , ",PostalCode = @postalCode"
                          , ",YTDSales =@ytdSales"
                          , ",CreditHold = @creditHold"
                          , ",Notes = @notes");
            byte creditHold = client.HoldsCredit ? (byte)1 : (byte)0;
            using (SqlCommand cmd= new SqlCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = query;
                cmd.Connection = conn;
                cmd.Parameters.AddWithValue("@clientCode", client.ClientCode);
                //cmd.Parameters.AddWithValue("@companyName", client.CompanyName);
                cmd.Parameters.AddWithValue("@address1", client.Address1);
                if (client.Address2 != null)
                {
                    cmd.Parameters.AddWithValue("@address2", client.Address2);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@address2", DBNull.Value);
                }
                if (client.City != null)
                {
                    cmd.Parameters.AddWithValue("@city", client.City);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@city", DBNull.Value);
                }
                cmd.Parameters.AddWithValue("@province", client.Province);
                cmd.Parameters.AddWithValue("@postalCode", client.PostalCode);
                cmd.Parameters.AddWithValue("@ytdSales", client.YTDSales);
                cmd.Parameters.AddWithValue("@creditHold", creditHold);
                if (client.Notes != null)
                {
                    cmd.Parameters.AddWithValue("@notes", client.Notes);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@notes", DBNull.Value);
                }
                conn.Open();
                int rowsAffected = cmd.ExecuteNonQuery();
                return rowsAffected;

            }
        }
    }

我得到重复的钥匙约束错误，我找不到原因。有人能帮我吗？

尝试更新表时从数据库中获取错误消息

ClientCode是主键，这意味着任何给定的值只能在该列中出现一次。

当你更新时

"UPDATE Client906697"
                      , "SET ClientCode = @clientCode"
                      , "CompanyName = @companyName"
                      , ",Address1 = @address1"
                      , ",Address2 = @address2"
                      , ",City = @city"
                      , ",Province = @province"
                      , ",PostalCode = @postalCode"
                      , ",YTDSales =@ytdSales"
                      , ",CreditHold = @creditHold"
                      , ",Notes = @notes");

没有WHERE子句。WHERE子句确定更新哪些行。如果没有，它会尝试更新表中的每一行。因此，无论@clientCode的值是多少，都要尝试更新表中的每一行，使其具有ClientCode的值。如果表中有多条记录，则保证会引发重复键异常，因为多行不能具有相同的ClientCode。（我相信这不是你想要做的。ClientCode是你的主要密钥，所以你可能无论如何都不想改变它。）

我想你的意思是这样的（对不起，我不会试图把这个放在你的string.Format中，但你明白了。）

UPDATE Client906697"
    SET CompanyName = @companyName,
    Address1 = @address1,
    -- ETC
 WHERE ClientCode = @ClientCode

现在您没有更改ClientCode的值，只更新了一行，即具有匹配ClientCode的行。

[ClientCode]是表上的主键。无法更新主键。您必须删除约束，更新所需的行，然后重新创建键。

相关文章：