Hello, I read that when using stored procedures you can set a default value as shown below, so that if nothing gets passed in the parameter the value will default to the given value. When I try this I get the following error. Did I misread this not understand this correctly? (I have tested these samples one at a time to make sure I was doing this correctly and received the same error results for both.)

@strDM_QuoteDueDate datetime = NULL,
@strDM_BindShippableBoxes bit = 1,

ADODB.Command error '800a0d5d'
Application uses a value of the wrong type for the current operation.


Thanks
Mike

__________________
Peace
Mike
http://www.eclecticpixel.com

Can you post just the CREATE PROCEDURE statement along with the parameter definition and also about how you are trying to execute the procedure?

Cheers!

_________________________
- Vijay G
Strive for Perfection

Thanks for the help! This is the code from my asp page. The reason I am asking if this is possible is that the Shippable Boxes is a checkbox and if it is not checked then a value will not be passed, and the database errors out. So my thinking is that if I can supply the default value as shown below it would prevent this, and that when the user does select the checkbox it will overide the default value at the database. Am I correct on this thought?

Set objCmd = Server.CreateObject("ADODB.Command")
With objCmd
.ActiveConnection = strConnect
.CommandText = "sp_ModifyBid"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@strID", adInteger, adParamInput, , strPieceID)
.Parameters.Append .CreateParameter("@strDM_QuoteDueDate", adDate, adParamInput, , strEstimateDue)
.Parameters.Append .CreateParameter("@strDM_BindShippableBoxes", adInteger, adParamInput, , strBindShippableBoxes)
.Execute
End With


CREATE PROCEDURE sp_ModifyBid
(
@strID int,
@strDM_QuoteDueDate datetime = NULL,
@strDM_BindShippableBoxes bit = 0
)
AS
BEGIN TRAN
If @strID > 0
BEGIN
    UPDATE DirectMail
    SET
    DM_QuoteDueDate = @strDM_QuoteDueDate,
    DM_BindShippableBoxes = @strDM_BindShippableBoxes
    WHERE ID_DM = @strID
END
Else
BEGIN
    INSERT INTO DirectMail
    (DM_QuoteDueDate, DM_BindShippableBoxes)
    VALUES
    (@strDM_QuoteDueDate, @strDM_BindShippableBoxes)
END
IF @@ERROR <> 0
    ROLLBACK TRAN
ELSE
    COMMIT TRAN

Thanks
Mike

You have to add validation in your ASP page based on the selection for @strDM_BindShippableBoxes.

If there is some value then only pass this parameter in the SP. Otherwise you can leave this parameter as it already have the default value of 0.


Om Prakash

Are you aware that you are using @strDM_BindShippableBoxes as BIT type in the SP and passing the parameter as adInteger in your ASP code? Use the correct type in both the places. Then something like this should work for you. Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

Thanks I will fix the bit type.

Just so you know this actually has about 50 fields , I added the validation like you all have told me. What is happening is that the number of parameters being passed does not match the SP parameters. So it is ignoring the @strDM_BindShippableBoxes bit = 0 and moving everything below it up one and erroring out telling me the parameters do not match up.

Passing Par 1int = SP Para 1 int
Passing Par 3 int = SP Para 2 datetime
                                 =SP Para 3 int

Hi Mike,

You should actually append all parameters to the Command object, but assign value only if the variable contains something. That was just the logic of code. You got to alter that accordingly.

Cheers!

_________________________
- Vijay G
Strive for Perfection