Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135

    Unanswered: is this query correct?

    Hi all, I have a query but im not sure if this is the correct area for the question to be asked, and if its more an ASP issue than an SQL query, but here is the issue.

    I have the following query:

    USE [intranet]
    GO
    /****** Object: StoredProcedure [dbo].[spUpdateDisplay] Script Date: 08/19/2009 17:00:53 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER PROCEDURE [dbo].[spUpdateDisplay]
    @iStoreID DECIMAL
    ,@pcanon DECIMAL
    AS
    BEGIN
    SET NOCOUNT ON
    IF EXISTS (SELECT fk_storeID FROM tblProducts2Displays2Stores WHERE fk_storeID = @iStoreID)
    BEGIN
    UPDATE
    tblProducts2Displays2Stores

    SET
    canon = @pcanon

    WHERE
    fk_StoreID = @iStoreID

    END
    ELSE
    BEGIN
    INSERT INTO
    tblProducts2Displays2Stores(
    canon
    )



    VALUES(
    @pcanon
    )

    END
    END

    But when i press the submit button in my ASP page, nothing happens the screen returned is blank? just wondering if i have a problem is with the above code really, was hoping someone could just have a look and see if its correct?


    Any help would be appreciated

    Regards
    MG

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I would either separate this into 2 procedures, and determine on the ASP level which one to call, or modify this procedure to inform the calling ASP code which action was taken.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    1) Please wrap it up in [ code] tags to make it easy to read.
    2) If this is SQL 2008 you can do "upserts" with Merge
    3) This doesn't pass out a result set, so your ASP page will not display anything
    4) The IF... statement is redundant IMO - just check @@rowcount after the update. If it is zero then insert.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hi thank you for the reply.

    Do you think thats the reason for this problem?

    You mean either an update or an insert - not both? Im essentially updating columns in a table based on user selections from a drop down menu, so id guess all i need is the Update command?

    I'm not entirely sure what you mean by the latter part of the sentence:
    "or modify this procedure to inform the calling ASP code which action was taken."

    I must confess im new to this field and don't know that much, hence the lack of knowledge.



    Regards
    MG

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - what do you think this procedure is doing? What do you hope to see on the ASP form?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hi pootle, thanks for post.

    Sorry about that, will remember for future.

    Its SQL 2005. At the risk of sounding like a dummy, i have to admit, I dont know how I'd do what you suggested, Im not sure how to check @@rowcount, but i shall google it now.

    Still learning / struggling to learn :-)

    Regards
    MG

  7. #7
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hi,

    Ok, well, I think this Procedure is taking a parameter called "pcanon" and checking to see if it exists in tblProductsdisplys2stores. Then set the column canon with the updating value @canon? is that right?

    The ASP form will have a list of all the products that are specific to a store / shop, (i say "will have" because at the moment there is only a test heading of "canon" that displays to the user), and once an admin user changes an option that relates to canon, this display should change to
    reflect these changes.

    Also, I hoped that i would have been taken back to the ASP page.

    Regards
    MG

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Almost.

    If cannon exists, it updates the row. If not, it inserts one row. This is informally known as an UPSERT (UPDATE and INSERT).

    However, it returns no data. As such, your ASP page will not be changed by this procedure. You need to change your ASP code, perhaps to retrieve the new data. Basically, unless you discover that rows are not getting inserted\ updated then this is not a SQL Server problem
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hi,

    oh no, i dont want it to upsert (to insert one row),

    I have amended it as you suggested, it is now like this:


    Code:
    USE [intranet]
    GO
    /****** Object:  StoredProcedure [dbo].[spUpdateDisplay]    Script Date: 08/19/2009 17:00:53 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [dbo].[spUpdateDisplay] 
    	 @iStoreID DECIMAL
    	,@pcanon DECIMAL
    	
    AS
    BEGIN
    	SET NOCOUNT ON
    			BEGIN
    				UPDATE 
    					tblProducts2Displays2Stores 
    
    				SET 
    					canon = @pcanon
    
    				WHERE
    					fk_StoreID = @iStoreID
    				
    				IF @@rowcount = 0
    					print'n'
    				ELSE
    					print 'y'
    			END
    END
    But again the screen is blank, so you're probably right about no data being returned.

    Now i need to figure out what to do? Dont suppose you know ASP do you?

    Also, is this amended query better (the correct one to use) than the upsert one?

    Regards
    MG

  10. #10
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    HI,

    Quick question. I was lead to believe the "If exist" is used to check to see if a table is there? And, if you know it is, would it not make the use of it pointless ?

    Regards
    MG

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That is better. The IF EXISTS is checking if the row (not the table) is there, and updating it if it is. There's no need to do that - just try to update the row. If it is there, @@rowcount will be greater than 0. If it is not there, @@rowcount will be 0. So you have it right.

    Now - once this is done what do you want to show on your page?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hi all,

    I have amended the sub function in the ASP page, as i think this may be where the problem lies, it read:

    Code:
    Sub UpdateDisplays()
        sSQL = "EXEC spUpdateDisplay " & pcanon 
        oDB.execute(sSQL)
    End Sub
    but now reads:

    Code:
    Sub UpdateDisplays()
        sSQL = "EXEC spUpdateDisplay " & iStoreID & "," & pcanon & "''"
        oDB.execute(sSQL)
    End Sub
    however i now get an error and have no idea where the problem lies.

    error reads:

    Error Type:
    Microsoft OLE DB Provider for SQL Server (0x80040E14)
    Unclosed quotation mark after the character string ''.
    /admin/stores/index.asp, line 479 (line in red)

    any help is most welcome ( and needed :-) )

    Regards
    MG

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    check your single quotes
    George
    Home | Blog

  14. #14
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Ok, so there were a few issues. Firstly the Stored procedure didnt have Execute rights on it, but it does now.

    I passed the @istoredID and pcanon with the Stored Procedure, as highlighted above. But, I keep getting the error (as shown above).


    Regards
    MG

  15. #15
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    hi all, just t oupdate,

    if i change the line to this:

    sSQL = "EXEC spUpdateDisplay " & iStoreID & "," & pcanon

    I get the following error:
    Microsoft OLE DB Provider for SQL Server (0x80040E14)
    Incorrect syntax near ','.
    /admin/stores/index.asp, line 479

    Regards
    MG

Posting Permissions

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