Results 1 to 2 of 2
  1. #1
    Join Date
    May 2002

    Angry Unanswered: Re: Trouble in getting a value from bit data type in stored procedure

    Hi eveyone,

    I'm trying to get the stored procedure to return a value from a field in a table. The value in the field stores a bit value and default value is set to 0. So there should always a value in that field but it is giving me a null value. Can anyone see why.
    I'm calling sp thru vb. Thanks much!

    This is VB:
    Set cancel_inspection_query = Nothing
    With cancel_inspection_query
    .ActiveConnection = CurrentProject.Connection
    .CommandText = "spInspec_cancel_initial_scan1"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
    .Parameters.Append .CreateParameter("@inspec_id", adInteger, adParamInput, 4, Me!inspecid.Caption)
    .Parameters.Append .CreateParameter("@bag_num", adInteger, adParamInput, 4, Me!bag_num.Caption)
    .Parameters.Append .CreateParameter("@sampling_id", adInteger, adParamInput, 4, Me!rmr.Caption)
    .Execute , , adExecuteNoRecords

    End With
    Debug.Print cancel_inspection_query("ret_val").Value
    end sub

    This is sp:

    CREATE PROCEDURE dbo.spInspec_cancel_initial_scan1
    @inspec_id int,
    @sampling_id int,
    @bag_num int
    declare @inspection_complete bit

    SELECT @inspection_complete = inspection_complete
    FROM dbo.tblBag_results
    WHERE bag_num = @bag_num;
    if @inspection_complete= 1
    if @inspection_complete = 0

  2. #2
    Join Date
    Nov 2002
    The Table DDL would help.

    Is Bag_Num a PK or unique index?

    If not, that's a problem...

    Also is the column defined as NOT NULL?

    If not, that's a problem...

    And why not use an OUTPUT variable instead?

    You should let SQL Server manage the return value. I've seen times when it overrides your value....which could be a problem if you code for a particular value....

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts