Method Execute of Object - Command Falied(Runtime error-2147217900)

[Nachi]

I am getting above error,

could pls anyone help me........?

Soruce Code :

Sub updateOfferCategoryAmounts(ByVal Transid As Long)
Dim spComm As Command
Set spComm = New Command
Dim intCatID As Integer
Dim rsBarADO As Recordset
Dim spADO As Command

Set spADO = New Command
spADO.CommandText = "sp_get_TransactionCategories"
spADO.CommandType = adCmdStoredProc
spADO.ActiveConnection = cnADO

Set rsBarADO = New Recordset
rsBarADO.CacheSize = 50

spADO.Parameters.Append spADO.CreateParameter("@TransID",
adBigInt, adParamInput, 20, Transid)
rsBarADO.Open spADO, , adOpenStatic, adLockReadOnly

While Not rsBarADO.EOF
Dim rowsAffected As Long
intCatID = rsBarADO("categoryID")

spComm.CommandText = "sp_insert_CatAmount"
spComm.CommandType = adCmdStoredProc
spComm.ActiveConnection = cnADO

spComm.Parameters.Append spComm.CreateParameter("@TransID",
adBigInt, adParamInput, 20, Transid)
spComm.Parameters.Append spComm.CreateParameter("@CatID",
adInteger, adParamInput, 4, intCatID)
spComm.Execute

rsBarADO.MoveNext

Wend

Set rsBarADO = Nothing
Set spADO = Nothing

End Sub

Stored Procedure :
CREATE proc sp_insert_CatAmount @TransID bigint, @CatID int
as

declare @Amount money
select @Amount = SUM(DiscountValue)
from
dbo.fn_list_PossibleOffersCategory(@TransId, @CatID)
if @Amount = null

set @Amount = 0

insert into
Current_SalesRefund_Discount (
TransactionNo,
CategoryID,
Discount)
values (
@TransID,
@CatID, @Amount )

GO

[Erland Sommarskog]

[posted and mailed, please reply in news]

Nachi (na********@yahoo.com) writes:

I am getting above error,

could pls anyone help me........?
First, before you actually fix the error - keep it! What you need is
to improve your error handling, so that you can get useful diagnosis
from SQL Server. You need to have an On Error Goto, and in that part
you pick what is in the .Errors collection on the connection object,
and then print this out. (One message is also in the plain Err.Description.)

In this case you probaly would have gotten the error "Cannot insert the
value NULL into column 'Discount', table 'Current_SalesRefund_Discount';
column does not allow nulls. INSERT fails."

Why? Hang on. But first another issue:
CREATE proc sp_insert_CatAmount @TransID bigint, @CatID int
Don't use the sp_ prefix for stored procedure. This prefix is reserved
for system procedures, and SQL Server first looks for these in the master
database.
if @Amount = null

set @Amount = 0
But this will not have any effect, because @Amout is never equal to NULL,
because nothing is ever equal to NULL. NULL is an unknown value, so you
can't tell whether two NULLs are the same unknown value or not.

When testing for NULL, you should use IS NULL or IS NOT NULL instead.

In this particular case, it would have been more convenient to write:
insert into
Current_SalesRefund_Discount (
TransactionNo,
CategoryID,
Discount)
values (
@TransID,
@CatID, coalesce(@Amount, 0) )

coalesce is a function that accepts a list of values and returns the
first non-NULL value in the list.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp