Solved

using ExecuteScalar() to retrieve a single value not functioning

Posted on 2006-06-15
19
353 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
  • 3
  • +1
19 Comments
 
LVL 7

Expert Comment

by:pradeepsudharsan
ID: 16910525
first open the connection ans pass it to SqlCommand constructor

--pradeep
0
 
LVL 7

Expert Comment

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

Expert Comment

by:pradeepsudharsan
ID: 16910540
or
put myCommand.myConnection.Open() instead of  myConnection.Open();

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Expert Comment

by:rraghvendra
ID: 16910544
use
  WPID = (Int32) myCommand.ExecuteScalar().ToString();

U can also use Output parameter in store procedure

0
 
LVL 5

Author Comment

by:Collindsouza
ID: 16910551
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
ID: 16910574
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
ID: 16910593
just try like this
Int16 count = (int16)myCommand.ExecuteScalar();
-pradeep
0
 
LVL 7

Expert Comment

by:pradeepsudharsan
ID: 16910596
WPID =(int16)myCommand.ExecuteScalar();
0
 
LVL 7

Expert Comment

by:pradeepsudharsan
ID: 16910626
WPID =(Int16)myCommand.ExecuteScalar();
0
 
LVL 5

Author Comment

by:Collindsouza
ID: 16910628
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
ID: 16910717
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
ID: 16910719
sqlConnection1.Open();
      sqlCommand1.CommandText="Select count(*) from Account";
      Int32 count = (Int32)sqlCommand1.ExecuteScalar();
0
 
LVL 2

Expert Comment

by:rraghvendra
ID: 16910737
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
ID: 16910748
Int32 count = (Int32)myCommand.ExecuteScalar();
0
 
LVL 7

Expert Comment

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

Author Comment

by:Collindsouza
ID: 16911077
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
ID: 16931731
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
ID: 16970655
Question closed - 500 points refunded.

Best regards,
RomMod
Experts Exchange
Community Support Moderator
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…

756 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