Solved

using ExecuteScalar() to retrieve a single value not functioning

Posted on 2006-06-15
19
341 Views
Last Modified: 2011-04-14
I'm trying to use the following function that will retrieve a single value from the database..

i'm trying to retrieve this value by using ExecuteScalar() method of ADO.NET since i only need to retrieve a single value..

However when i invoke this function (Function name GetWPID) i get a error 'Object reference not set to an instance of an object' at the following line
WPID = Convert.ToInt16(myCommand.ExecuteScalar().ToString());


Given below is the Function.. It tries to called a Stored Procedure named "GetWorkProgramID" which is also listed after the function

---> FUNCTION <---
       public int GetWPID(string WPName)
        {
            int WPID;
            SqlConnection myConnection = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand myCommand = new SqlCommand("GetWorkProgramID", myConnection);
            // Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure;

            // Add Parameters to SPROC
            SqlParameter parameterWPID = new SqlParameter("@WPName", SqlDbType.NVarChar, 50);
            parameterWPID.Value = WPName;
            myCommand.Parameters.Add(parameterWPID);

            // Execute the stored procedure and Return the datareader result
            myConnection.Open();
            WPID = Convert.ToInt16(myCommand.ExecuteScalar().ToString());
            myConnection.Close();
            return WPID=0;
        }

---> STORED PROCEDURE <---
ALTER PROCEDURE [dbo].[GetWorkProgramID]
(
      @WPName  nvarchar
)
AS
SET NOCOUNT ON;

SELECT WorkProgramID
FROM tblWorkProgramme
WHERE WorkprogramDesc = @WPName

Also.. is there a simple way to re-write this function.. So that i can retrieve single values from the database without using ExecuteScalar() method
0
Comment
Question by:Collindsouza
  • 9
  • 5
  • 3
  • +1
19 Comments
 
LVL 7

Expert Comment

by:pradeepsudharsan
Comment Utility
first open the connection ans pass it to SqlCommand constructor

--pradeep
0
 
LVL 7

Expert Comment

by:pradeepsudharsan
Comment Utility
first open the connection and pass it to SqlCommand constructor
0
 
LVL 7

Expert Comment

by:pradeepsudharsan
Comment Utility
or
put myCommand.myConnection.Open() instead of  myConnection.Open();

0
 
LVL 2

Expert Comment

by:rraghvendra
Comment Utility
use
  WPID = (Int32) myCommand.ExecuteScalar().ToString();

U can also use Output parameter in store procedure

0
 
LVL 5

Author Comment

by:Collindsouza
Comment Utility
Hi pradeep

i am opening the connection at this line

myConnection.Open();

Also.. I tried moving the myConnection.Open(); line before passing it to the SqlCommand constructor

But no joy :((


0
 
LVL 5

Author Comment

by:Collindsouza
Comment Utility
Hi raghvendra,

If i use (Int32) myCommand.ExecuteScalar().ToString();

Then while compiling it says

Cannot convert type 'string' to 'short'

Yet no Joy :((
0
 
LVL 7

Expert Comment

by:pradeepsudharsan
Comment Utility
just try like this
Int16 count = (int16)myCommand.ExecuteScalar();
-pradeep
0
 
LVL 7

Expert Comment

by:pradeepsudharsan
Comment Utility
WPID =(int16)myCommand.ExecuteScalar();
0
 
LVL 7

Expert Comment

by:pradeepsudharsan
Comment Utility
WPID =(Int16)myCommand.ExecuteScalar();
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 5

Author Comment

by:Collindsouza
Comment Utility
Hi Pradeep,

Using WPID =(int16)myCommand.ExecuteScalar();
gives me the same damn error..'Object reference not set to an instance of an object'

0
 
LVL 5

Author Comment

by:Collindsouza
Comment Utility
Ok.. Got ti.. There was a mistake in the declaration of the variable  @WPName  nvarchar

I forgot to add the length as @WPName  nvarchar(50)

Thanks for the help everyone..
0
 
LVL 7

Expert Comment

by:pradeepsudharsan
Comment Utility
sqlConnection1.Open();
      sqlCommand1.CommandText="Select count(*) from Account";
      Int32 count = (Int32)sqlCommand1.ExecuteScalar();
0
 
LVL 2

Expert Comment

by:rraghvendra
Comment Utility
Hi


plz check following by debuging the code
> is WPName have some value
> is ur query return any value or not...
0
 
LVL 7

Expert Comment

by:pradeepsudharsan
Comment Utility
Int32 count = (Int32)myCommand.ExecuteScalar();
0
 
LVL 7

Expert Comment

by:pradeepsudharsan
Comment Utility
Use same data type  and length for WorkProgramID  and your project variable and  do the conversion.
0
 
LVL 5

Author Comment

by:Collindsouza
Comment Utility
its resolved now.. the error was because there was a mistake in the declaration of the variable  @WPName  nvarchar in the Stored procedure

I forgot to add the length as @WPName  nvarchar(50)

Thanks for the help everyone..
0
 
LVL 2

Expert Comment

by:rraghvendra
Comment Utility
ok, there is no objection but it is not goog people get solution then request to delete the quetion.
0
 

Accepted Solution

by:
RomMod earned 0 total points
Comment Utility
Question closed - 500 points refunded.

Best regards,
RomMod
Experts Exchange
Community Support Moderator
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction                                                 Was the var keyword really only brought out to shorten your syntax? Or have the VB language guys got their way in C#? What type of variable is it? All will be revealed.   Also called…
Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now