We help IT Professionals succeed at work.
Get Started

Stored Procedure not updating table from ASP.net page.

216 Views
Last Modified: 2013-11-07
I am using SQL 2005 and I have this stored procedure that works well when I run it from the Query analyzer.  But when I call it from an ASP.net page, it does not seem to do the updates to the isUsed field every time I call the SP.  Even if I open a different browser I get the same image and no update to the isUsed field in the tblImage table.   Can anyone tell me why this is happening or how I should debug this issue?

------------------------  Stored Procedure  --------------------------------------------------

ALTER Procedure [dbo].[selGenreImage](
                                                @idGenre as int
)
as
      SET NOCOUNT ON
-- Check to see if there are any images ready
if not exists(
                        select      p.idProduction
                        from tblProduction as p inner join
                              tblProductioninfo as tpi
                        on      p.idProduction = tpi.idProduction inner join
                              tblImage as i
                        on      tpi.idImage = i.idImage
                        where      tpi.fldOpeningDate < getdate()
                        and            tpi.fldClosingdate > getdate()
                        and            tpi.idImage <> 1
                        and            i.isUsed = 0
                        and            i.fldWidth <= 125
                        and            i.fldHeight <= 125
                        and            tpi.idGenre = @idGenre
                        )
                        -- If there are not image ready then update all the images back to 0
                        begin
                              update i
                              set i.isUsed = 0
                              from tblProduction as p inner join
                                    tblProductioninfo as tpi
                              on      p.idProduction = tpi.idProduction inner join
                                    tblImage as i
                              on      tpi.idImage = i.idImage
                              where      tpi.fldOpeningDate < getdate()
                              and            tpi.fldClosingdate > getdate()
                              and            tpi.idImage <> 1
                              and            i.isUsed = 1
                              and            i.fldWidth <= 125
                              and            i.fldHeight <= 125
                              and            tpi.idGenre = @idGenre
                        end

                        -- Create a temp table to up date images
                        -- Drop Table if exist
                        if exists (select * from TempDB.dbo.sysobjects where id = object_id(N'tempdb..[#tempImageID]') and type='U')
                              Begin
                                    drop table [#tempImageID]
                              End

            -- Create the new table
            create table #tempImageID(
            idProduction int,
            idImage int,
            fldTitle varchar(300),
            fldImageName varchar(500),
            fldWidth int,
            fldHeight int                              
            )
            -- Insert into the new table
            insert into #tempImageID(
            idProduction,
            idImage,
            fldTitle,
            fldImageName,
            fldWidth,
            fldHeight      
            )
                        select      top 8
                                          p.idProduction,
                                          i.idImage,
                                          p.fldTitle,
                                          i.fldImageName,
                                          i.fldWidth,
                                          i.fldHeight
                        from tblProduction as p inner join
                              tblProductioninfo as tpi
                        on      p.idProduction = tpi.idProduction inner join
                              tblImage as i
                        on      tpi.idImage = i.idImage
                        where      tpi.fldOpeningDate < getdate()
                        and            tpi.fldClosingdate > getdate()
                        and            tpi.idImage <> 1
                        and            i.isUsed = 0
                        and            i.fldWidth <= 125
                        and            i.fldHeight <= 125
                        and            tpi.idGenre = @idGenre


                        update i
                        set i.isUsed = 1
                        from #tempImageID as ti inner join
                              tblImage as i
                        on      ti.idImage = i.idImage

                        select *
                        from #tempImageID

                        -- Drop Table if exist
                        if exists (select * from TempDB.dbo.sysobjects where id = object_id(N'tempdb..[#tempImageID]') and type='U')
                              Begin
                                    drop table [#tempImageID]
                              End


----------------------------------------------------------------------------------------------------------------


Thanks
Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 9 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE