Stored Procedure not updating table from ASP.net page.

Could "top 8" have something to do with it?

dgmg,

Thanks for the reply.  I don't think so because the id that in am working with has 16 images and when I do this from the query analyzer.  I get 2 completely different data sets every other time.  It is only when I call it from the ASP.net page that I keep getting the same data.

GabicusC

I'm still suspicous about top 8.  Because, as coded without an order by clause it returns unpredictable results.  What is the purpose?

Also, you may be having some issues with Getdate(), which is referenced multiple times as if it should return the same value every time. It does not!  You should declare a DATETIME variable and assign getdate() to it at the top of your procedure, then reference the variable in your SQL. That way your date range criteria will be constant for the duration of the procedure.  Also, be advised that getdate() returns a time component which you may want to strip off.

dgmg,

I am going to make the changes you suggested.  The reason for the top 8 is I have a page that will have 8 images on it.  But I want all the image to change each time someone new comes to the site.  I will let you know how it goes.

Thanks,

GabicusC

dgmg,

I made all the changes.  I kept in the top 8 but I now have an Order by idImage.  I am still getting the same results.  This is what I have both on the SQL side and ASP.net Side

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

ALTER Procedure [dbo].[selGenreImage](
                                                @idGenre as int,
                                                @Date as datetime
)
as
      SET NOCOUNT ON

set @Date = convert(smalldatetime, @Date, 110)

-- 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 < @Date
                        and            tpi.fldClosingdate > @Date
                        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 < @Date
                              and            tpi.fldClosingdate > @Date
                              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 < @Date
                        and            tpi.fldClosingdate > @Date
                        and            tpi.idImage <> 1
                        and            i.isUsed = 0
                        and            i.fldWidth <= 125
                        and            i.fldHeight <= 125
                        and            tpi.idGenre = @idGenre
                        order by i.idImage


                        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

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

------------------------------  ASP.net  -----------------------------------

    Sub Get_Checked_Button(ByVal src As Object, ByVal arg As EventArgs)

        Dim dtGenreImage As New Data.DataTable
        sp.cmdDAGenreImage(Convert.ToInt32(src.text), Now).Fill(dtGenreImage)
        lblGenre.Text = "<img src='images/production/" + dtGenreImage.Rows(0).Item("fldImageName").ToString + "'"

    End Sub


    Function cmdDAGenreImage(ByVal idGenre As Integer, ByVal dtDate As Date) As SqlDataAdapter
        Dim cmdGenreImage As New SqlCommand
        cmdGenreImage.CommandText = "selGenreImage"
        cmdGenreImage.CommandType = Data.CommandType.StoredProcedure
        cmdGenreImage.Connection = objConn
        cmdGenreImage.Parameters.Add(New SqlParameter("@idGenre", Data.SqlDbType.Int)).Value = idGenre
        cmdGenreImage.Parameters.Add(New SqlParameter("@Date", Data.SqlDbType.DateTime)).Value = dtDate
        objConn.Open()
        cmdGenreImage.ExecuteNonQuery()
        objConn.Close()
        cmdDAGenreImage = New SqlDataAdapter(cmdGenreImage)
    End Function
-----------------------------------------------------------------------------

GabicusC

dgmg,

This is very strange.  I made the change you suggested, and still the same issue.  I went a head and put it up on my webserver to see if maybe it was an issue with my envierment.  If you want you can go to http://web2.lastagealliance.com/ and click between radio button 1 and 4.  4 is the idGenre of the data set with 16 images.  I have only one image coming up at the moment.

The only other thing I can think of and do not want to do because I do not want to keep hitting the DB is to take out the update from this sp and make a new SP with the update and just keep calling the new update for each image.

 What do you think?

Thanks again for all your help.

GabicusC

dgmg,

Well I tried to add a sp that did the update and I left the update in the orginal SP.  When I did this the same stuff happened.  I then took out the update from the orginal SP and kept the new SP in the code and all worked.  I just can not figure out why the update would not work when it was part of the same SP?  Any suggetions?

GabicusC

Sorry, I'm fresh out of ideas