SqlParameter和c#

Question

大家好，有人能帮我解决这个问题吗？我花了将近两天的时间来想办法解决这个问题。

我在c#中有一个参数查询

UPDATE [User] 
SET LoginName = '@LoginName', Name = '@Name',
    Surname = '@Surname', Telphone = '@Telphone', 
    Cellphone = '@Cellphone', Fax = '@Fax', Email = '@Email', 
    Password = '@Password', PasswordCreationDate = @PasswordCreationDate, 
    IsActive = @IsActive, ManagerID = @ManagerID, 
    SuperVisorID = @SuperVisorID, TeamLeaderID = @TeamLeaderID, 
    PermissionID = @PermissionID 
WHERE UserID = @UserID

我已经将值'‘赋值给@Telephone参数，但是它失败了，所以我打开了Server，我看到它给了我以下内容

USE Collector
exec sp_executesql N'UPDATE [User] SET LoginName=''@LoginName'', Name=''@Name'', Surname=''@Surname'', 
Telphone=''@Telphone'',
 Cellphone=''@Cellphone'', Fax=''@Fax'', 
Email=''@Email'', Password=''@Password'', PasswordCreationDate = @PasswordCreationDate, 
IsActive = @IsActive, ManagerID = @ManagerID, SuperVisorID = @SuperVisorID, 
TeamLeaderID = @TeamLeaderID, PermissionID = @PermissionID WHERE UserID = @UserID',
N'@UserID int,@LoginName nvarchar(6),@Name nvarchar(9),@Surname nvarchar(8),
@Telphone nvarchar(4000)
,@Cellphone nvarchar(4),@Fax nvarchar(3),@Email nvarchar(3),
@Password nvarchar(32),@PasswordCreationDate datetime,@IsActive bit,@ManagerID int,
@SuperVisorID int,@TeamLeaderID int,@PermissionID int',@UserID=29,@LoginName=N'daniel',
@Name=N'Daniel123',@Surname=N'asdfasdf',
@Telphone=DEFAULT,
@Cellphone=N'3453',@Fax=N'345',
@Email=N'adf',@Password=N'5F4DCC3B5AA765D61D8327DEB882CF99',
@PasswordCreationDate='2015-04-03 21:55:57',@IsActive=1,@ManagerID=7,@SuperVisorID=7,
@TeamLeaderID=7,@PermissionID=1

不查看这个查询，我发现它已经在其中放置了默认这个词，即使我认为我指定了'‘，所以它带着一个警告回来了。

参数化查询'(@UserID int，@LoginName nvarchar(6)，@Name nvarchar(9)，@Surname n‘)希望参数'@Telphone’没有提供。

但我确实给它分配了一个'‘new SqlParameter("@Telphone", "''");

我遗漏了什么？我需要另一双眼睛，请帮帮忙。

表def：

USE [Collector]
GO

/****** Object:  Table [dbo].[User]    Script Date: 2015/04/11 08:19:09 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[User](
    [UserID] [int] IDENTITY(1,1) NOT NULL,
    [LoginName] [nvarchar](255) NOT NULL,
    [Name] [nvarchar](255) NOT NULL,
    [Surname] [nvarchar](255) NOT NULL,
    [Telphone] [nvarchar](50) NULL,
    [Cellphone] [nvarchar](50) NULL,
    [Fax] [nvarchar](50) NULL,
    [Email] [nvarchar](50) NULL,
    [Password] [nvarchar](255) NOT NULL,
    [PasswordCreationDate] [datetime] NOT NULL,
    [IsActive] [bit] NOT NULL CONSTRAINT [DF_User_IsActive]  DEFAULT ((1)),
    [ManagerID] [int] NULL,
    [SuperVisorID] [int] NULL,
    [TeamLeaderID] [int] NULL,
    [PermissionID] [int] NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
    [UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

谢谢你的眼睛。我设法追踪这个问题

罪魁祸首。我不得不修改反射部分，以检查它是否是字符串，而不是使用默认值为null。

public static object GetDefaultValue(this Type type)
    {
        if (type == null) throw new ArgumentNullException("type");

        Expression<Func<object>> e = Expression.Lambda<Func<object>>(
            Expression.Convert(
            Expression.Default(type), typeof(object)
            )
        );

        // Compile and return the value.
        return e.Compile()();
    }

Answer 1

您不需要使用带有参数的单引号。只要像这样使用它们；

Telphone = @Telphone

使用单引号，sql将其视为字符串文字，而不是参数。

Answer 2

您不应该在参数周围有撇号，这使它们成为文字字符串值而不是参数。例如，它将把'@LoginName'保存在表中而不是'daniel'.中，查询应该是：

UPDATE [User] SET LoginName=@LoginName, Name=@Name,
Surname=@Surname, Telphone=@Telphone, 
Cellphone=@Cellphone, Fax=@Fax, Email=@Email, 
Password=@Password, PasswordCreationDate = @PasswordCreationDate, 
IsActive = @IsActive, ManagerID = @ManagerID, 
SuperVisorID = @SuperVisorID, TeamLeaderID = @TeamLeaderID, 
PermissionID = @PermissionID WHERE UserID = @UserID

关于@Telphone参数，在执行它之前，似乎实际上没有将它添加到命令中。检查拼写，并检查将其添加到命令的位置和方式。

Answer 3

试试这个：

new SqlParameter("@Telphone", DBNull.Value);