Solved

using ExecuteScalar() to retrieve a single value not functioning

Posted on 2006-06-15
19
347 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
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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

776 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