Object reference not set to an instance of an object.

Question

0.00/5 (No votes)

See more:

i have the following stored procedure that tests the type of column if its unique or not

SQL

USE [Filled]
GO
/****** Object:  StoredProcedure [dbo].[CheckType]    Script Date: 09/12/2014 12:58:06 م ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER  Procedure [dbo].[CheckType]
@TableName nvarchar (255),
@ColumnName nvarchar(255)
as
Declare @CheckType int
Select @CheckType= case when DATA_TYPE= 'uniqueidentifier' then 1 else 0 end   
from INFORMATION_SCHEMA.COLUMNS     
where COLUMN_NAME = @ColumnName
and TABLE_NAME = @TableName
Return @CheckType

in my application i call this procedure through command.executeScalar

C#

int result;
SqlConnection connection = new SqlConnection(connection_string);
SqlCommand command = new SqlCommand("CheckType", connection);
command.CommandType = CommandType.StoredProcedure;
connection.Open();
command.Parameters.Add("@TableName", SqlDbType.NVarChar).Value = table_name;
command.Parameters.Add("@ColumnName", SqlDbType.NVarChar).Value = primarykey;
result =(int)command.ExecuteScalar();
connection.Close();

but when i run the code it gives me the exception Object reference not set to an instance of an object in the line result=(int)command.ExecuteScalar()
i tried to define result as object and then cast it to int but it never works
what should i do

Posted 9-Dec-14 0:04am

oula alsheikh

Updated 9-Dec-14 1:08am

/\jmot

v2

Add a Solution

Comments

Thanks7872 9-Dec-14 6:33am

Look at the variables involved in the line you got the exception. You will find one of them as NULL. It should not be NULL.

2 solutions

Add a Solution

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)

Kornfeld Eliyahu Peter · Accepted Answer · 2014-12-09T01:12:00

Solution 1

ExecuteScalar:

MSDN:
Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

You have no SELECT inside your SP so not rows and columns, so ExecuteScalar returns null - and that is a null reference error...
You probably have to replace this line:

SQL

Return @CheckType

To this:

SQL

SELECT @CheckType

Posted 9-Dec-14 1:12am

Kornfeld Eliyahu Peter

Comments

Ankur^\m/ 9-Dec-14 7:31am

Correct except that he is already selecting @CheckType in the query and doesn't need to do that again. Up-voted!

Kornfeld Eliyahu Peter 9-Dec-14 7:35am

You right of course, but for me the main part of the answer is explain why it happened.
To fix the RETURN/SELECT part can be different from what I suggested and it depends on the actual design OP has (that may change with each bit of knowledge)...

Ankur^\m/ 9-Dec-14 7:41am

I understand and that is why I up-voted the answer. But for some reason I don't see the vote. Voting again!

Kornfeld Eliyahu Peter 9-Dec-14 7:42am

Cache! The vote is there - Thank you...

Ankur^\m/ 9-Dec-14 7:46am

Yeah, Server Cache it is because I did force refresh on my browser before re-voting.

oula alsheikh 9-Dec-14 8:16am

it works well thanks for your efforts

Kornfeld Eliyahu Peter 9-Dec-14 8:20am

You are welcome!

/\jmot · Accepted Answer · 2014-12-09T01:18:00

change your sp ..

SQL

USE [Filled]
GO
/****** Object:  StoredProcedure [dbo].[CheckType]    Script Date: 09/12/2014 12:58:06 م ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER  Procedure [dbo].[CheckType]
@TableName nvarchar (255),
@ColumnName nvarchar(255)
as
Select case when DATA_TYPE= 'uniqueidentifier' then 1 else 0 end   
from INFORMATION_SCHEMA.COLUMNS     
where COLUMN_NAME = @ColumnName
and TABLE_NAME = @TableName

or use output parameter..
See here..
http://www.aspsnippets.com/Articles/How-to-return-Output-parameter-from-Stored-Procedure-in-ASPNet-in-C-and-VBNet.aspx[^]

and check..

C#

if(!String.IsNullOrEmpty(command.ExecuteScalar()))
{
result =(int)command.ExecuteScalar();
}