stored procedure Optional Feature Not Implemented

[prettitoni]

Can someone take a looka t this and tell me what I'm doing wrong? THis is the first stored procedure I've ever written :-( If I'm supposed to post this in the VB forum, plz let me know.

This is the procedure:
CREATE PROCEDURE CUSTOM_UPDATE_REQSTION
@p_REQUISITIONNUM_in VARCHAR(20),
@p_SiteID_in VARCHAR(15),
@p_REQUESTOR_in VARCHAR(15),
@p_REQUESTDATE_in DATETIME,
@p_NEEDBYDATE_in DATETIME,
@p_DELIVERY_in VARCHAR(50),
@p_DESCRIPTION_in TEXT(16)
@p_ACCTNUM_in VARCHAR(20)
@p_REMARKS_in TEXT(16)
@p_VENDORNAME_in VARCHAR(80),
@p_REQUESTSTATUS_in VARCHAR(20),
@p_PRINTSTATUS_in VARCHAR(65)
AS
BEGIN
BEGIN TRANSACTION
UPDATE REQSTION SET REQUESTORNAME = @p_REQUESTOR_in,
REQUISITIONDATE = @p_REQUESTDATE_in, NEEDBYDATE = @p_NEEDBYDATE_in,
DELIVERY = @p_DELIVERY_in,
IR_DESCRIPTION = @p_DESCRIPTION_in,
ACCTNUM = @p_ACCTNUM_in, NOTES = @p_REMARKS_in,
VENDORNAME = @p_VENDORNAME_in, REQUESTSTATUS = @p_REQUESTSTATUS_in,
PRINT_STATUS = @p_PRINTSTATUS_in
WHERE
REQUISITIONNUM = @p_REQUISITIONNUM_in AND
SITEID = @p_SiteID_in
COMMIT TRANSACTION
END
GO


This is the code that I'm using in VB to call it...

        .CommandText = "CUSTOM_UPDATE_REQSTION"
        .CommandType = adCmdStoredProc
        .CommandTimeout = 15
    
        cmd.Parameters.Append cmd.CreateParameter("RETURN_VALUE", 3, 4)
        cmd.Parameters.Append cmd.CreateParameter("p_SiteID_in", adVarChar, adParamInput, 15, Site)
        cmd.Parameters.Append cmd.CreateParameter("p_REQUESTOR_in", adVarChar, adParamInput, 15, Requestor)
        cmd.Parameters.Append cmd.CreateParameter("p_REQUESTDATE_in", adDate, adParamInput, Len(RequestDate), RequestDate)
        cmd.Parameters.Append cmd.CreateParameter("p_NEEDBYDATE_in", adDate, adParamInput, Len(NeedByDate), NeedByDate)
        cmd.Parameters.Append cmd.CreateParameter("p_DELIVERY_in", adVarChar, adParamInput, 50, Delivery)
        cmd.Parameters.Append cmd.CreateParameter("p_DESCRIPTION_in", adVarChar, adParamInput, 255, IR_Description)
        
        cmd.Parameters.Append cmd.CreateParameter("p_ACCTNUM_in", adVarChar, adParamInput, 20, AcctNum)
        cmd.Parameters.Append cmd.CreateParameter("p_REMARKS_in", adVarChar, adParamInput, 255, Remarks)
        cmd.Parameters.Append cmd.CreateParameter("p_VENDORNAME_in", adVarChar, adParamInput, 80, Vendor)
        cmd.Parameters.Append cmd.CreateParameter("p_REQUESTSTATUS_in", adVarChar, adParamInput, 20, Status)
        cmd.Parameters.Append cmd.CreateParameter("p_PRINTSTATUS_in", adVarChar, adParamInput, 65, Print_Status)

        cmd.Parameters("p_REQUESTDATE_in").Value = CDate(RequestDate)
        cmd.Parameters("p_NEEDBYDATE_in").Value = CDate(NeedByDate)
        
        cmd.Execute

 

[ESquared]

Before I even look at it all to try to find a problem, would you tell us what kind of errors you are getting or whatever?

 

[prettitoni]

It tells me "Optional Feature Not Implemented"....then I ran it again with different values and it told me there was a problem with the DESCRIPTION parameter....that one is defined as TEXT in the stored procedure and I'm calling it as varChar in VB. That might be a problem too.

 

[nigelrivett]

The
BEGIN TRANSACTION
COMMIT TRANSACTION

Doesn't do anything as every sql statement is atomic. You should take them out.

You seem to have missed
@p_REQUISITIONNUM_in
from the parameters.

It's worth using the profiler to trace what is sent to the server then you can run that code in query analyser (inside a transaction with a rollback if it does updates) to see if there is a problem wit hthe call or the SP.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

@p_DESCRIPTION_in TEXT(16)
@p_REMARKS_in TEXT(16)

What are they meant to be? text is a blob and doesn't have a size. If want it to be 16 chars then make it a varchar.
In fact make these varchars to test anyway.
Looks like you are passing 255 chars from the app so make them varchar(255).

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[prettitoni]

You know what.....I sent the wrong SP...I had changed those to just TEXT...so it looks like:

@p_DESCRIPTION_in TEXT,
@p_REMARKS_in TEXT

How do I pass this "blob" in thru VB....am I appending it properly using varChar 255? I'm guess not.