Solved

using ExecuteScalar() to retrieve a single value not functioning

Posted on 2006-06-15
19
363 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Technology Partners: 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!

Question has a verified solution.

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

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

617 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