using ExecuteScalar() to retrieve a single value not functioning

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
LVL 5
CollindsouzaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
RomModConnect With a Mentor Commented:
Question closed - 500 points refunded.

Best regards,
RomMod
Experts Exchange
Community Support Moderator
0
 
pradeepsudharsanCommented:
first open the connection ans pass it to SqlCommand constructor

--pradeep
0
 
pradeepsudharsanCommented:
first open the connection and pass it to SqlCommand constructor
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
pradeepsudharsanCommented:
or
put myCommand.myConnection.Open() instead of  myConnection.Open();

0
 
rraghvendraCommented:
use
  WPID = (Int32) myCommand.ExecuteScalar().ToString();

U can also use Output parameter in store procedure

0
 
CollindsouzaAuthor Commented:
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
 
CollindsouzaAuthor Commented:
Hi raghvendra,

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

Then while compiling it says

Cannot convert type 'string' to 'short'

Yet no Joy :((
0
 
pradeepsudharsanCommented:
just try like this
Int16 count = (int16)myCommand.ExecuteScalar();
-pradeep
0
 
pradeepsudharsanCommented:
WPID =(int16)myCommand.ExecuteScalar();
0
 
pradeepsudharsanCommented:
WPID =(Int16)myCommand.ExecuteScalar();
0
 
CollindsouzaAuthor Commented:
Hi Pradeep,

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

0
 
CollindsouzaAuthor Commented:
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
 
pradeepsudharsanCommented:
sqlConnection1.Open();
      sqlCommand1.CommandText="Select count(*) from Account";
      Int32 count = (Int32)sqlCommand1.ExecuteScalar();
0
 
rraghvendraCommented:
Hi


plz check following by debuging the code
> is WPName have some value
> is ur query return any value or not...
0
 
pradeepsudharsanCommented:
Int32 count = (Int32)myCommand.ExecuteScalar();
0
 
pradeepsudharsanCommented:
Use same data type  and length for WorkProgramID  and your project variable and  do the conversion.
0
 
CollindsouzaAuthor Commented:
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
 
rraghvendraCommented:
ok, there is no objection but it is not goog people get solution then request to delete the quetion.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.