Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2002
    Posts
    5

    Unanswered: SQL 7 -> SQL 2000 Upgrade

    Hi

    When I set a SmallDateTime field in VB to a value that includes seconds (e.g. = Now) my update fails in 2000 (was ok in 7) with the message

    Row cannot be located for updating. Some values may have been changed since it was last read.

    The problem is that the SQL generated is as follows (taken from Profiler)

    exec sp_executesql N'UPDATE "db".."tbl" SET "ModUser"=@P1,"ModDate"=@P2 WHERE "ModUser"=@P3 AND "ModDate"=@P4 AND "keyfld"=@P5', N'@P1 int,@P2 datetime,@P3 int,@P4 datetime,@P5 int', 2, 'Feb 6 2002 8:02:11:000PM', 2, 'Feb 6 2002 8:02:06:000PM', 40

    The generated SQL looks for the date with seconds in the DB but there are none because it's a SmallDateTime field so the seconds are stripped - therefore "Row cannot be located for updating"

    I can work around this by changing stripping the seconds off or by telling 2000 to behave as 7. I don't fancy either solution. Any better ideas? Are there any other undocumented funnies out there?

    Thanks
    Niall

    I'm using SQL 2000, SP1 (and 2), VB6 using ADO 2.6

  2. #2
    Join Date
    Jan 2002
    Location
    Sweden
    Posts
    11

    Arrow

    Data type smalldatetime cannot store seconds, I'm afraid.

  3. #3
    Join Date
    Jan 2002
    Location
    Sweden
    Posts
    11
    Sorry, I think I misinterpreted your question... I replied too quickly...

Posting Permissions

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