Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

stored procedure Optional Feature Not Implemented

Status
Not open for further replies.

prettitoni

Programmer
Apr 22, 2004
74
US
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...
Code:
        .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
 
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?
 
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.
 
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.
 
@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.
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top