Microsoft.ApplicationBlocks question

10 Sep 2005 3:11 PM

Walter Levine

I am using this syntax :
retVal = (Int32)SqlHelper.ExecuteScalar(cn, CommandType.StoredProcedure,
"getEmployeeFromSSNO", new SqlParameter("@EmpSSNO", emp_ssno));

to call this stored procedure

CREATE PROCEDURE dbo.getEmployeeFromSSNO(@EmpSSNO varchar(12))
AS SELECT        id
FROM           .tblEmployee
WHERE        (EmployeeSSNO = @EmpSSNO)
GO

it works fine when the input parm exists, it returns the correct result
However, if the parm does not exist, the Catch executes with the message:
"Object reference not set to an instance of an object."

Can anybody tell me what I am doint wrong??

thanks inadvance

Walter

10 Sep 2005 5:53 PM

Dan Guzman

SqlCommand ExecuteScalar returns a null object reference when no data are
found. You can handle this situation more elegantly with something like:

object retValObject = SqlHelper.ExecuteScalar(cn,
CommandType.StoredProcedure,
    "getEmployeeFromSSNO", new SqlParameter("@EmpSSNO", emp_ssno));

if(retValObject == null)
{
    //your appropriate not-found handling
    retVal = 0;
}
else
{
    retVal = (Int32) retValObject;
}

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quote

"Walter Levine" <wlev***@sigsys.net> wrote in message
news:%23BjIhmhtFHA.2592@TK2MSFTNGP09.phx.gbl...
>I am using this syntax :
> retVal = (Int32)SqlHelper.ExecuteScalar(cn, CommandType.StoredProcedure,
> "getEmployeeFromSSNO", new SqlParameter("@EmpSSNO", emp_ssno));
>
> to call this stored procedure
>
>
> CREATE PROCEDURE dbo.getEmployeeFromSSNO(@EmpSSNO varchar(12))
> AS SELECT        id
> FROM           .tblEmployee
> WHERE        (EmployeeSSNO = @EmpSSNO)
> GO
>
> it works fine when the input parm exists, it returns the correct result
> However, if the parm does not exist, the Catch executes with the message:
> "Object reference not set to an instance of an object."
>
>
>
> Can anybody tell me what I am doint wrong??
>
> thanks inadvance
>
> Walter
>
>
>
>
>
>

10 Sep 2005 5:59 PM

John Bell

Hi

I am not an expert on this but you may want to see what gets sent to SQL
Server using SQL profiler.

It is not clear if you are saying that the problem is because no records are
returned or the parameter is not given a value. if the former then try
something like:

CREATE PROCEDURE dbo.getEmployeeFromSSNO(@EmpSSNO varchar(12))
AS
DECLARE @id int

SELECT      @id = id
FROM          dbo.tblEmployee
WHERE        EmployeeSSNO = @EmpSSNO

SELECT @id AS id
GO

It would probably be better to return the id as an output parameter instead.

John

Show quote

"Walter Levine" <wlev***@sigsys.net> wrote in message
news:%23BjIhmhtFHA.2592@TK2MSFTNGP09.phx.gbl...
>I am using this syntax :
> retVal = (Int32)SqlHelper.ExecuteScalar(cn, CommandType.StoredProcedure,
> "getEmployeeFromSSNO", new SqlParameter("@EmpSSNO", emp_ssno));
>
> to call this stored procedure
>
>
> CREATE PROCEDURE dbo.getEmployeeFromSSNO(@EmpSSNO varchar(12))
> AS SELECT        id
> FROM           .tblEmployee
> WHERE        (EmployeeSSNO = @EmpSSNO)
> GO
>
> it works fine when the input parm exists, it returns the correct result
> However, if the parm does not exist, the Catch executes with the message:
> "Object reference not set to an instance of an object."
>
>
>
> Can anybody tell me what I am doint wrong??
>
> thanks inadvance
>
> Walter
>
>
>
>
>
>

10 Sep 2005 8:59 PM

Walter Levine

Thanks to all for the replys.
Either one solves my problem.
Something is still very curious
I was following the code in the MS dataAccess QuickStart examples

this is the code:
    string productName;
    productName = (string)SqlHelper.ExecuteScalar(connection,
CommandType.StoredProcedure, "getProductName", new
SqlParameter("@ProductID", 1));
    txtResults.Text = productName;

"getProductName" looks like this:

-- LookupSingleItem() sample
CREATE PROCEDURE getProductName @ProductID int
AS
SELECT ProductName
FROM Products
WHERE ProductID = @ProductID

ProductId value of 1 exists and returns a value.
However, if I change the 1, to a non-existent value, say 33434, it returns
null, but does not fire the catch.
I modeled my code after this, it seems to me that my code is virtually
identical, except for names and datatypes, but mine fires the catch when the
key is not found.
Thanks again for the help
Walter

Show quote

"John Bell" <jbellnewspo***@hotmail.com> wrote in message
news:O7mkEFjtFHA.3932@TK2MSFTNGP15.phx.gbl...
> Hi
>
> I am not an expert on this but you may want to see what gets sent to SQL
> Server using SQL profiler.
>
> It is not clear if you are saying that the problem is because no records
> are returned or the parameter is not given a value. if the former then try
> something like:
>
> CREATE PROCEDURE dbo.getEmployeeFromSSNO(@EmpSSNO varchar(12))
> AS
> DECLARE @id int
>
> SELECT      @id = id
> FROM          dbo.tblEmployee
> WHERE        EmployeeSSNO = @EmpSSNO
>
> SELECT @id AS id
> GO
>
> It would probably be better to return the id as an output parameter
> instead.
>
> John
>
> "Walter Levine" <wlev***@sigsys.net> wrote in message
> news:%23BjIhmhtFHA.2592@TK2MSFTNGP09.phx.gbl...
>>I am using this syntax :
>> retVal = (Int32)SqlHelper.ExecuteScalar(cn, CommandType.StoredProcedure,
>> "getEmployeeFromSSNO", new SqlParameter("@EmpSSNO", emp_ssno));
>>
>> to call this stored procedure
>>
>>
>> CREATE PROCEDURE dbo.getEmployeeFromSSNO(@EmpSSNO varchar(12))
>> AS SELECT        id
>> FROM           .tblEmployee
>> WHERE        (EmployeeSSNO = @EmpSSNO)
>> GO
>>
>> it works fine when the input parm exists, it returns the correct result
>> However, if the parm does not exist, the Catch executes with the message:
>> "Object reference not set to an instance of an object."
>>
>>
>>
>> Can anybody tell me what I am doint wrong??
>>
>> thanks inadvance
>>
>> Walter
>>
>>
>>
>>
>>
>>
>
>

10 Sep 2005 10:10 PM

Dan Guzman

This is more of a .Net question than a SQL one but I'll try to answer your
question.

Int32 is a value type and value types cannot be null in .Net. The compiler
will identify the following as an error:

    int myInt = (int) null;

String is a reference type so a null reference is allowed. The following is
allowed:

    string myString = (string) null;

In your case, you're converting a reference type (object) to a value type
(Int32). The source object is unboxed before the conversion is attempted.
You get a NullReferenceException during the runtime unboxing when the source
is null because the object is dereferenced during the unboxing:

    //this will throw a NullReferenceException
    object myObject = null;
    int myInt = (int) myObject;

String is a reference type so unboxing is needed. When the source is null,
the target will also be null.

    //this is ok
    object myObject = null;
    string myString = (string) myObject;

> I modeled my code after this, it seems to me that my code is virtually
> identical, except for names and datatypes, but mine fires the catch when
> the key is not found.

Datatype differences are an important consideration. Note that the
QuickStart examples don't always cover all the bases or illustrate Best
Practices. They are a great way to get started quickly but you'll often
need to beef up the code for production use.

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quote

"Walter Levine" <wlev***@sigsys.net> wrote in message
news:OyNNWpktFHA.3316@TK2MSFTNGP12.phx.gbl...
> Thanks to all for the replys.
> Either one solves my problem.
> Something is still very curious
> I was following the code in the MS dataAccess QuickStart examples
>
> this is the code:
>    string productName;
>    productName = (string)SqlHelper.ExecuteScalar(connection,
> CommandType.StoredProcedure, "getProductName", new
> SqlParameter("@ProductID", 1));
>    txtResults.Text = productName;
>
>
> "getProductName" looks like this:
>
> -- LookupSingleItem() sample
> CREATE PROCEDURE getProductName @ProductID int
> AS
> SELECT ProductName
> FROM Products
> WHERE ProductID = @ProductID
>
> ProductId value of 1 exists and returns a value.
> However, if I change the 1, to a non-existent value, say 33434, it
> returns null, but does not fire the catch.
> I modeled my code after this, it seems to me that my code is virtually
> identical, except for names and datatypes, but mine fires the catch when
> the key is not found.
> Thanks again for the help
> Walter
>
> "John Bell" <jbellnewspo***@hotmail.com> wrote in message
> news:O7mkEFjtFHA.3932@TK2MSFTNGP15.phx.gbl...
>> Hi
>>
>> I am not an expert on this but you may want to see what gets sent to SQL
>> Server using SQL profiler.
>>
>> It is not clear if you are saying that the problem is because no records
>> are returned or the parameter is not given a value. if the former then
>> try something like:
>>
>> CREATE PROCEDURE dbo.getEmployeeFromSSNO(@EmpSSNO varchar(12))
>> AS
>> DECLARE @id int
>>
>> SELECT      @id = id
>> FROM          dbo.tblEmployee
>> WHERE        EmployeeSSNO = @EmpSSNO
>>
>> SELECT @id AS id
>> GO
>>
>> It would probably be better to return the id as an output parameter
>> instead.
>>
>> John
>>
>> "Walter Levine" <wlev***@sigsys.net> wrote in message
>> news:%23BjIhmhtFHA.2592@TK2MSFTNGP09.phx.gbl...
>>>I am using this syntax :
>>> retVal = (Int32)SqlHelper.ExecuteScalar(cn, CommandType.StoredProcedure,
>>> "getEmployeeFromSSNO", new SqlParameter("@EmpSSNO", emp_ssno));
>>>
>>> to call this stored procedure
>>>
>>>
>>> CREATE PROCEDURE dbo.getEmployeeFromSSNO(@EmpSSNO varchar(12))
>>> AS SELECT        id
>>> FROM           .tblEmployee
>>> WHERE        (EmployeeSSNO = @EmpSSNO)
>>> GO
>>>
>>> it works fine when the input parm exists, it returns the correct result
>>> However, if the parm does not exist, the Catch executes with the
>>> message:
>>> "Object reference not set to an instance of an object."
>>>
>>>
>>>
>>> Can anybody tell me what I am doint wrong??
>>>
>>> thanks inadvance
>>>
>>> Walter
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>
>

10 Sep 2005 11:10 PM

Andrew J. Kelly

Walter you should get in the habit of owner qualifying the sp calls.

Show quote

> retVal = (Int32)SqlHelper.ExecuteScalar(cn, CommandType.StoredProcedure,
> "dbo.getEmployeeFromSSNO", new SqlParameter("@EmpSSNO", emp_ssno));

--
Andrew J. Kelly SQL MVP

Show quote

"Walter Levine" <wlev***@sigsys.net> wrote in message
news:%23BjIhmhtFHA.2592@TK2MSFTNGP09.phx.gbl...
>I am using this syntax :
> retVal = (Int32)SqlHelper.ExecuteScalar(cn, CommandType.StoredProcedure,
> "getEmployeeFromSSNO", new SqlParameter("@EmpSSNO", emp_ssno));
>
> to call this stored procedure
>
>
> CREATE PROCEDURE dbo.getEmployeeFromSSNO(@EmpSSNO varchar(12))
> AS SELECT        id
> FROM           .tblEmployee
> WHERE        (EmployeeSSNO = @EmpSSNO)
> GO
>
> it works fine when the input parm exists, it returns the correct result
> However, if the parm does not exist, the Catch executes with the message:
> "Object reference not set to an instance of an object."
>
>
>
> Can anybody tell me what I am doint wrong??
>
> thanks inadvance
>
> Walter
>
>
>
>
>
>