Dealing with a null BLOB field

[Matt]

I could use some help dealing with null blobs. I'm
returning a transaction from an Image BLOB field in SQL
Server 2000 using C#. If the transaction exists the value
is returned with out trouble, but because the ID can exist
without having a value in the Image column the returned
value is NULL and the code can't handle it and I receive
this error when the Stored Procedure's value is returned
(the line of code is marked with "**HERE**":

Description: An unhandled exception occurred during the
execution of the current web request. Please review the
stack trace for more information about the error and where
it originated in the code.

Exception Details:
System.Web.Services.Protocols.SoapException:
System.Web.Services.Protocols.SoapException: Server was
unable to process request. --->
System.InvalidCastException: Specified cast is not valid.
at
CareVu.WebServices.TransactionServicesm.TransactionServices
..GetRejectedTransaction(Int32 txnId) in
c:\inetpub\wwwroot\web\transactionservices.asmx.cs:line
475 --- End of inner exception stack trace ---


Here is the code in the transactionservices.asmx.cs file:


/// <summary>
/// Retrieves an XML representation of a
transaction given its ID
/// </summary>
/// <param name="txnId">The transaction
ID</param>
/// <returns>String (xml)</returns>
[WebMethod]
public string GetRejectedTransaction(int
txnId)
{
SqlConnection conn = new
SqlConnection();

try
{
conn.ConnectionString =
System.Configuration.ConfigurationSettings.AppSettings
["ConnectionString"];
SqlParameter[] parms = new
SqlParameter[1];
parms[0] = new SqlParameter
("@txnId", SqlDbType.Int);
parms[0].Value = txnId;


**HERE** byte[] weakBlob = (byte[])
SqlHelper.ExecuteScalar(conn,
CommandType.StoredProcedure, "GetRejectedTransaction",
parms);

if (weakBlob != null)
{

System.Text.StringBuilder contents = new
System.Text.StringBuilder(weakBlob.Length - 1);

for(int i = 0; i <
weakBlob.Length; i++)
{

contents.Append(System.Convert.ToChar(weakBlob
));
}

return
contents.ToString();
}
else
{
return "";
}
}
catch(Exception exc)
{
throw exc;
}
finally
{
if (conn.State !=
ConnectionState.Closed)
{
conn.Close();
}
}
}


And here is the Stored Procedure:

CREATE proc dbo.GetRejectedTransaction
(
@txnId int
)
as
set nocount on
select
WeakBlob
from Transactions
where ID = @txnId
GO

Any help would be appreciated.

Thanks,
Matt

 

[Matt]

I found a way to do it. Since the ID being sent in to the
SP will be in the table whether the BLOB is there or not,
I changed from using a byte[] to an object and then
converting to a byte[] after I knew if was not null:


[WebMethod]
public string GetRejectedTransaction(int
txnId)
{
SqlConnection conn = new
SqlConnection();

try
{
conn.ConnectionString =
System.Configuration.ConfigurationSettings.AppSettings
["ConnectionString"];
SqlParameter[] parms = new
SqlParameter[1];
parms[0] = new SqlParameter
("@txnId", SqlDbType.Int);
parms[0].Value = txnId;


object wBlob = (object)
SqlHelper.ExecuteScalar(conn,
CommandType.StoredProcedure, "GetRejectedTransaction",
parms);

if (wBlob !=
System.DBNull.Value)
{
byte[] weakBlob =
(byte[])wBlob;

System.Text.StringBuilder contents = new
System.Text.StringBuilder(weakBlob.Length - 1);

for(int i = 0; i <
weakBlob.Length; i++)
{

contents.Append(System.Convert.ToChar(weakBlob
));
}

return
contents.ToString();
}
else
{
return "";
}
}
catch(Exception exc)
{
throw exc;
}
finally
{
if (conn.State !=
ConnectionState.Closed)
{
conn.Close();
}
}
}

-----Original Message-----
I could use some help dealing with null blobs. I'm
returning a transaction from an Image BLOB field in SQL
Server 2000 using C#. If the transaction exists the value
is returned with out trouble, but because the ID can exist
without having a value in the Image column the returned
value is NULL and the code can't handle it and I receive
this error when the Stored Procedure's value is returned
(the line of code is marked with "**HERE**":

Description: An unhandled exception occurred during the
execution of the current web request. Please review the
stack trace for more information about the error and where
it originated in the code.

Exception Details:
System.Web.Services.Protocols.SoapException:
System.Web.Services.Protocols.SoapException: Server was
unable to process request. --->
System.InvalidCastException: Specified cast is not valid.
at
CareVu.WebServices.TransactionServicesm.TransactionService s
..GetRejectedTransaction(Int32 txnId) in
c:\inetpub\wwwroot\web\transactionservices.asmx.cs:line
475 --- End of inner exception stack trace ---


Here is the code in the transactionservices.asmx.cs file:


/// <summary>
/// Retrieves an XML representation of a
transaction given its ID
/// </summary>
/// <param name="txnId">The transaction
ID</param>
/// <returns>String (xml)</returns>
[WebMethod]
public string GetRejectedTransaction(int
txnId)
{
SqlConnection conn = new
SqlConnection();

try
{
conn.ConnectionString =
System.Configuration.ConfigurationSettings.AppSettings
["ConnectionString"];
SqlParameter[] parms = new
SqlParameter[1];
parms[0] = new SqlParameter
("@txnId", SqlDbType.Int);
parms[0].Value = txnId;


**HERE** byte[] weakBlob = (byte[])
SqlHelper.ExecuteScalar(conn,
CommandType.StoredProcedure, "GetRejectedTransaction",
parms);

if (weakBlob != null)
{

System.Text.StringBuilder contents = new
System.Text.StringBuilder(weakBlob.Length - 1);

for(int i = 0; i <
weakBlob.Length; i++)
{

contents.Append(System.Convert.ToChar(weakBlob
));
}

return
contents.ToString();
}
else
{
return "";
}
}
catch(Exception exc)
{
throw exc;
}
finally
{
if (conn.State !=
ConnectionState.Closed)
{
conn.Close();
}
}
}


And here is the Stored Procedure:

CREATE proc dbo.GetRejectedTransaction
(
@txnId int
)
as
set nocount on
select
WeakBlob
from Transactions
where ID = @txnId
GO

Any help would be appreciated.

Thanks,
Matt

.