#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2008
    Posts
    5
    Rep Power
    0

    Stored proceedure only partially working


    Hi,

    I am the intake coordinator for a dog rescue. I am trying to create a web based database to keep track of our dogs, volunteers, adopters and surrendering owners. About 4-5 years ago I almost completed the .ASP web Programmer program at the local technical college. One of the classes I didn't take has come back to haunt me. That would be the advanced database class. I am having trouble with a stored procedure with three operations in it. 2 of the three are working just fine and populating their respective tables with no issues. I hope people don't mind helping someone who sort of stumbling in the semi-dark...........

    It is the DOG_DOG table that isn't populating. The function it calls follows the procedure.

    Code:
    ALTER PROCEDURE dbo.spInsertDog
    
        (
        @name varchar(50),
    	@color varchar(50),
    	@age varchar(50),
    	@sex varchar(50),
    	@cats varchar(50),
    	@fluffy varchar(50),
    	@dogs varchar(MAX),
    	@children varchar(MAX),
    	@entrydate date,
    	@exitdate date,
    	@disposition varchar(50),
    	@notes varchar(MAX),
    	@housetrained varchar(50),
    	@heartwormdue date,
    	@distemperparvo date,
    	@rabies date,
    	@heartwormstatus varchar(50),
    	@status varchar(50),
    	@surrenderingownerid varchar(50),
    	@fosterid varchar(50),
    	@reason varchar(MAX),
    	@DogID int OUTPUT
        )
    
    AS
    
    
    BEGIN
    INSERT INTO DOGS (DogName, EntryDate, ExitDate, Color, Age,
    Sex, FinalDisposition, Notes, Fluffy, Cats, HouseTrained, HeartwormDue, DistemperParvoDue, RabiesDue, Children, HeartwormStatus, Status, SurrenderReason) VALUES (@name, @entrydate, @exitdate, @color, @age, @sex, @disposition, @notes, @fluffy, @cats, @housetrained, @heartwormdue, @distemperparvo, @rabies, @children, @heartwormstatus, @status, @reason)
    SET @DogID = SCOPE_IDENTITY()
    END
    
    BEGIN
            INSERT INTO DOG_DOG (DogID, DogType)
            (SELECT @DogID, r.DogType FROM dbo.parseCSVstring(@dogs) pr JOIN
    DOGSOCIAL r ON pr.stringValue = r.DogType)
    END
    
    
    
    BEGIN
    	INSERT INTO DOG_FOSTER(DogID, PersonID) VALUES (@DogID, @fosterid)
    END
    Code:
    ALTER function [dbo].[parseCSVString](@rawcsv varchar(7998))
    returns @stringlist table (
            rank int identity(1,1),
            stringValue varchar(255)
    )
    as
    begin
            declare @pcsv varchar(8000), @plen int
             select @pcsv = ',' + @rawcsv + ',', @plen = len(@rawcsv)+2
    
            insert into @stringlist (stringValue)
            select ltrim(rtrim(substring(@pcsv,startIndex,endIndex-startIndex+1)))
            from
            (
            select intvalue +1 'startIndex', charindex(',',@pcsv,intValue+1)-1 'endIndex'
            from dbo.Integers(LEN(@rawcsv))
            where intValue = charindex(',',@pcsv,intValue)
              and intValue between 1 and @plen
            ) a
            where startIndex<=@plen
    
            return
    end
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2008
    Posts
    5
    Rep Power
    0
    116 people have looked at this thread.... Has this problem baffled you all or is that lack of response due to another reason? Did I not give you enough information? Am I so over my head that it isn't worth trying to help me?
  4. #3
  5. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,444
    Rep Power
    4539
    Actually, you posted in the wrong forum, and Google Chrome doesn't seem to let me move your thread where it belongs, to a database forum. Your question has nothing to do with asp. You might re-post in the appropriate database forum, perhaps you'll find an answer there.

    When you repost you might include information about the debug steps you've already done and the results you got.
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester
  6. #4
  7. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,444
    Rep Power
    4539
    Aha, there is another dropdown that let me move a thread with Chrome! Thanks, Nilpo.

    Moved from the asp forum
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2008
    Posts
    5
    Rep Power
    0
    Actually, I figured it out by myself - and it is embarrassing. The values I was putting into the CVS String didn't match the values in the joined table. For instance, I put "Large" in the joined table and "Large Dog" in the checkboxlist (whose selected values created the CVS String). "A" did not equal "B" so nothing happened.

IMN logo majestic logo threadwatch logo seochat tools logo