CODEWITHSUNDEEP
c#sql
user3731074
user3731074

Reputation: 93

SqlParameter and c#

Hi all please can someone help me out with this. I have spend nearly 2 days trying to figure this one out.

I have a this parameter query in 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

I have this assign the value '' to the @Telephone parameter but it fails so I opened up SQL Server Profiler and I saw it gave me this

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

No looking at this query I see that it has placed the word DEFAULT in there even thought I had specifically assigned '' so it comes back with a warning.

The parameterized query '(@UserID int,@LoginName nvarchar(6), @Name nvarchar(9),@Surname n' expects the parameter '@Telphone', which was not supplied.

But I did assign a '' to it new SqlParameter("@Telphone", "''");

What am I missing? I need another set of eyes please help.

table 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]

Thanks for all the eyes. I manage to trace the issue

the culprit.. I had to modify the reflection part to check if it is a string and not use the default value that would be 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()();
    }

Upvotes: 1

Views: 205

Answers (3)

DWright
DWright

Reputation: 9500

Try this:

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

Upvotes: 0

Guffa
Guffa

Reputation: 700182

You shouldn't have apostrophes around parameters, that makes them literal string values instead of parameters. It would for example save '@LoginName' in the table instead of 'daniel'.The query should be:

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

Regarding the @Telphone parameter, it seems that you actually didn't add it to the command before executing it. Check the spelling, and check where and how you add it to the command.

Upvotes: 0

Soner G&#246;n&#252;l
Soner G&#246;n&#252;l

Reputation: 98740

You don't need to use single quotes with parameters. Just use them like;

Telphone = @Telphone

With single quote, sql sees it as a string literal, not a parameter.

Upvotes: 3

Related Questions