Results 1 to 7 of 7

Thread: Update Issue

  1. #1
    Join Date
    Apr 2003
    Posts
    114

    Unanswered: Update Issue

    I am having trouble updating a sql server 2000 recordset from an asp page. I know the sql is good because I use the same one to retrieve the data that I want to update (using an execute method) but I get "Invalid Column Name" for a clearly valid column name when I try to update it using rs.open. Could it be my locks or cursor type?

    Here is my code in case there is a brain cramp in there that I am missing:


    code:--------------------------------------------------------------------------------
    Set rs=Server.CreateObject("ADODB.Recordset")
    sql = "SELECT rr.RaceLogRsltsID, rr.FinalTime, rg.RaceDist, rg.RaceType, rg.EventName, rg.EventDate, rg.RaceLogID "
    sql = sql & "FROM RaceLog rg INNER JOIN RaceLogRslts rr ON rr.RaceLogID = rg.RaceLogID "
    sql = sql & sWHERE & " ORDER BY " & sFinalSort
    rs.Open sql, conn, adOpenKeyset, adLockOptimistic
    --------------------------------------------------------------------------------


    It tells me that the rg.RaceDist is an invalid column name.
    Thanks in advance!

  2. #2
    Join Date
    Feb 2004
    Posts
    78
    Please replace the open with response.write sql so we can see an actual select and update you are running.

  3. #3
    Join Date
    Apr 2003
    Posts
    114
    Here are the results of the response.write:

    SELECT rr.RaceLogRsltsID, rr.FinalTime, rg.RaceDist, rg.RaceType, rg.EventName, rg.EventDate, rg.RaceLogID FROM RaceLog rg INNER JOIN RaceLogRslts rr ON rr.RaceLogID = rg.RaceLogID WHERE rg.PartID = 782 ORDER BY rg.EventDate DESC

    Is the problem due to the fact that I am trying to update two tables in the same update? Should I split this up and do them seperately?

    Thanks.

  4. #4
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    The short answer, brought to you from the short bus, is don't use dynamic SQL in your code. Also, don't use recordsets to Update your data.

    Switch to using stored procedures - for example, replace your Select with exec RaceResults_Select and do Updates via exec RaceLogRslts_Update and exec RaceLog_Update.

    Of course, you will need to write these stored procedures.

  5. #5
    Join Date
    Apr 2003
    Posts
    114
    I have been delaying writing stored procedures out of fear Can you tell me why they are prefereable to dynamic sql/recordsets and point me to a beginners tutorial?

    Thanks!

  6. #6
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    There is nothing to fear but fear itself. Franklin D. Roosevelt

    Stored Procedures are preferable because they are highly maintainable and because they even offer performance boosts, often.

    Brief Tutorial [I'm winging this, so it is unlikely to be accurate - consider it pseudo-code to help the beginner learn ]:

    Create Proc RaceLogRslts_Update
    @RaceLogId int
    , @RaceLogRsltsId int
    , @SomeColumn varchar(12)
    As
    Declare @rowcount int, @error int

    Begin Tran
    Update RaceLogRslts_Update
    Set SomeColumn = @SomeColumn
    Where RaceLogRsltsId = @RaceLogRsltsId

    Select @rowcount = @@RowCount, @error = @@Error
    If @error = 0
    Begin
    Commit Tran
    Return(@rowcount)
    End
    Else
    Begin
    Rollback Tran
    Return (-1)
    End
    Go
    ...
    Create Proc RaceLog_Update
    @RaceLogId int
    , @SomeColumn varchar(12)
    ...
    Create Proc RaceResults_Select
    @RaceLogId int
    , @RaceLogRsltsId int
    ...
    Declare @RaceLogId int, RaceLogRsltsId int, @errorid int
    Select @RaceLogId = 1, RaceLogRsltsId = 2, @errorid = -1

    exec RaceResults_Select @RaceLogId = @RaceLogId, @RaceLogRsltsId = @RaceLogRsltsId

    exec @errorid = RaceLog_Update @RaceLogId = @RaceLogId, @somevalue = 'That Value'

    If @errorid >= 0
    exec @errorid = RaceLogRslts_Update @RaceLogId = @RaceLogId, @RaceLogRsltsId = @RaceLogRsltsId, @somevalue = 'This Value'

    exec RaceResults_Select @RaceLogId = @RaceLogId, @RaceLogRsltsId = @RaceLogRsltsId
    Last edited by MaxA; 02-10-05 at 01:02.

  7. #7
    Join Date
    Apr 2003
    Posts
    114
    Thanks very much. Where is this code written?

Posting Permissions

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