Microsoft.ApplicationBlocks question

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
WalterSqlCommand 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
"Walter Levine" <wlevine@.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
>
>
>|||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" <wlevine@.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
>
>
>|||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" <jbellnewsposts@.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" <wlevine@.sigsys.net> wrote in message
> news:%23BjIhmhtFHA.2592@.TK2MSFTNGP09.phx.gbl...
>|||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
"Walter Levine" <wlevine@.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" <jbellnewsposts@.hotmail.com> wrote in message
> news:O7mkEFjtFHA.3932@.TK2MSFTNGP15.phx.gbl...
>|||Walter you should get in the habit of owner qualifying the sp calls.

> retVal = (Int32)SqlHelper.ExecuteScalar(cn, CommandType.StoredProcedure,
> "dbo.getEmployeeFromSSNO", new SqlParameter("@.EmpSSNO", emp_ssno));
Andrew J. Kelly SQL MVP
"Walter Levine" <wlevine@.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
>
>
>