Error Object reference not set to an instance of an object

Question

1.00/5 (2 votes)

See more:

Please help.

I am trying to create a procedure similar to below code: (SSMS)

SQL

DECLARE @OldPK AS VARCHAR(50)
DECLARE @PK AS VARCHAR(50)
DECLARE @S AS NVARCHAR(MAX)
DECLARE @P AS NVARCHAR(MAX)

SET @PK = 'ModeName'
SET @S = N'SELECT @pOldPK = @pPK FROM Modes '
SET @P = N'@pPK VARCHAR(50),  @pOldPK VARCHAR(50) OUTPUT'
EXECUTE dbo.sp_executesql @S, @P,  @pPK = @PK, @pOldPK = @OldPK OUTPUT
SELECT @OldPK

When I try to debug and after the line

EXECUTE dbo.sp_executesql @S, @P,  @pPK = @PK, @pOldPK = @OldPK OUTPUT

it stops and generated an error

SQL

"Object reerence not set to an instance of an object"

Your valuable input is very much appreciated.

Thanks.

Posted 8-Apr-15 8:07am

Putchicks

Updated 10-Apr-15 9:21am

John C Rayan

v3

Add a Solution

Comments

Sergey Alexandrovich Kryukov 8-Apr-15 14:32pm

This is not in SQL. It looks like a .NET exception message. Are you using ADO.NET? You can find out in what line the exception is thrown, that would be enough to solve the problem.
—SA

Putchicks 8-Apr-15 14:46pm

I tried to run the in SSMS - Thanks Sergey

ZurdoDev 8-Apr-15 15:19pm

I'm with SA on this one. I've never seen Sql give that error message. In fact if I change "Modes" to a table in my db this code executes without error.

Your error must be coming from C#.

Putchicks 8-Apr-15 15:52pm

Thank RyanDev. I rectified the error in codes but it doesn't give me what I want to have a dynamic column (injected by user). please see below changes:

DECLARE @OldPK AS VARCHAR(50)
DECLARE @S AS NVARCHAR(MAX)
DECLARE @P AS NVARCHAR(MAX)
DECLARE @pOldPK AS VARCHAR(50)

SET @S = N'SELECT @pOldPK = m.ModeName FROM Modes m'
SET @P = N'@pOldPK VARCHAR(50) OUTPUT'
EXECUTE dbo.sp_executesql @S, @P, @pOldPK OUTPUT
SELECT @pOldPK

and it gives me the value sample "XXXX" when executed...

ZurdoDev 8-Apr-15 15:54pm

If you want to let the user pick which column then you just need to use a variable in place of m.ModeName. Right now, you're always selecting ModeName from Modes.

Putchicks 8-Apr-15 16:27pm

Thanks RyanDev. Please see below code:
DECLARE @OldPK AS VARCHAR(50)
DECLARE @PK AS VARCHAR(50)
DECLARE @S AS NVARCHAR(MAX)
DECLARE @P AS NVARCHAR(MAX)
DECLARE @pPK AS VARCHAR(50)

SET @PK = 'ModeName'
SET @S = N'SELECT TOP 1 @pOldPK = ' + QUOTENAME(@pPK) + ' FROM Modes '
SET @P = N'@pOldPK VARCHAR(50) OUTPUT'
EXECUTE dbo.sp_executesql @S
, @P
, @PK
, @pOldPK = @OldPK OUTPUT
SELECT @OldPK

this generates a NULL value it supposed to give a 'xxx'

ZurdoDev 8-Apr-15 16:34pm

That's because @pPK is null so adding other strings to it will still be NULL.

Putchicks 8-Apr-15 16:48pm

Sorry RyanDev I somehow deleted the @PK in below line, appreciate your help on this

EXECUTE dbo.sp_executesql @S, @P, @PK, @pOldPK OUTPUT

ZurdoDev 10-Apr-15 15:35pm

I don't understand where you are stuck now.

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)