Solved

using ExecuteScalar() to retrieve a single value not functioning

Posted on 2006-06-15
19
344 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ConsoleSql 1 34
C# replace string in void with dR["myData"].ToString() 3 39
In WPF / C# binding a local database in code behind 1 24
ASP.NET 5 Templates 2 65
Article by: Ivo
C# And Nullable Types Since 2.0 C# has Nullable(T) Generic Structure. The idea behind is to allow value type objects to have null values just like reference types have. This concerns scenarios where not all data sources have values (like a databa…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

911 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

22 Experts available now in Live!

Get 1:1 Help Now