Professional Web Applications Themes

INSERT or UPDATE - ASP Database

Hi there. I have this problem here : I want to insert a record if it does not already exist, or to update it if it does (based on index of course). Can I do this with a simple SQL query? Thanks! Bingo...

  1. #1

    Default INSERT or UPDATE

    Hi there.

    I have this problem here :
    I want to insert a record if it does not already exist, or to update it if
    it does (based on index of course).
    Can I do this with a simple SQL query?

    Thanks!
    Bingo


    Bingo Guest

  2. #2

    Default Re: INSERT or UPDATE

    How are you determining if the record exists? By a unique ID? Here's a
    dirty method:

    If objADO.Execute("select id from theTable where id=" & iLookForThis).EOF
    Then
    objADO.Execute "insert into theTable ([column1]) values ('" & sVal &
    "')"
    Else
    objADO.Execute "update theTable set [column1]='" & sVal & "' where id="
    & iLookForThis
    End If


    You may want something a little nicer though like:


    iLookForThis = 34
    sTheValue = "Bingo's value"
    sTheValue = Replace(sTheValue, "'", "''")

    Set rsDoesItExist = objADO.Execute("SELECT [Anything] FROM [YourTable] WHERE
    [ID]=" & iLookForThis)
    If rsDoesItExist.EOF Then '''record with this id does not exist
    sSQL = "INSERT INTO [YourTable] ([SomeColumn]) VALUES ('" & sTheValue &
    "')"
    sResponse = "Your record was added."
    Else '''record exists - udpate it instead
    sSQL = "UPDATE [YourTable] SET [SomeColumn]='" & sTheValue & "' WHERE
    [ID]=" & iLookFor
    sResponse = "Your recoded was updated."
    End If
    rsDoesItExist.Close
    Set rsDoesItExist = Nothing

    objADO.Exeucte sSQL
    objADO.CLose
    Set objADO = Nothing

    Response.Write sResponse


    Ray at work
    unproofread



    "Bingo" <bingobingo.be> wrote in message
    news:3f27d256$0$24565$626a54cenews.free.fr...
    > Hi there.
    >
    > I have this problem here :
    > I want to insert a record if it does not already exist, or to update it if
    > it does (based on index of course).
    > Can I do this with a simple SQL query?
    >
    > Thanks!
    > Bingo
    >
    >

    Ray at Guest

  3. #3

    Default Re: INSERT or UPDATE

    Depends, what database???

    In SQL Server, use a stored procedure that takes all the column values and
    inputs, and uses the following code.


    IF EXISTS (SELECT 1 FROM table WHERE <whatever makes this row "exist">)
    UPDATE
    ELSE
    INSERT


    You could also say:


    UPDATE table ... WHERE <whatever makes this row "exist">
    IF ROWCOUNT = 0
    INSERT





    "Bingo" <bingobingo.be> wrote in message
    news:3f27d256$0$24565$626a54cenews.free.fr...
    > Hi there.
    >
    > I have this problem here :
    > I want to insert a record if it does not already exist, or to update it if
    > it does (based on index of course).
    > Can I do this with a simple SQL query?
    >
    > Thanks!
    > Bingo
    >
    >

    Aaron Bertrand - MVP Guest

  4. #4

    Default Re: INSERT or UPDATE

    Hi Ray!
    > How are you determining if the record exists? By a unique ID? Here's a
    Not unique, but it doesn't change your code.

    I'd like to do it without vb code though... But from your answer I
    understand this is not possible... Right?

    In fact, I'm pretty sure this is not possible, since the function (I just
    found the answer in fact) was newly implemented in oracle9i as MERGE.
    If access can do something to oracle9i couldn't do not so long ago, I would
    be pretty much surprised...

    Thanks for your help though!


    Bingo.


    Bingo Guest

  5. #5

    Default Re: INSERT or UPDATE

    Well, you'll obviously need some vbS code (or js), but if you don't want to
    do it all in vbs, you can use a stored procedure as Aaron suggested.

    Ray at work

    "Bingo" <bingobingo.be> wrote in message
    news:3f27db43$0$24595$626a54cenews.free.fr...
    > Hi Ray!
    >
    > > How are you determining if the record exists? By a unique ID? Here's a
    >
    > Not unique, but it doesn't change your code.
    >
    > I'd like to do it without vb code though... But from your answer I
    > understand this is not possible... Right?
    >
    > In fact, I'm pretty sure this is not possible, since the function (I just
    > found the answer in fact) was newly implemented in oracle9i as MERGE.
    > If access can do something to oracle9i couldn't do not so long ago, I
    would
    > be pretty much surprised...
    >
    > Thanks for your help though!
    >
    >
    > Bingo.
    >
    >

    Ray at Guest

  6. #6

    Default Re: INSERT or UPDATE

    > IF EXISTS (SELECT 1 FROM table WHERE <whatever makes this row "exist">)
    > UPDATE
    > ELSE
    > INSERT
    >
    Wow, I should switch to SQL-Server I guess! This looks like a powerfull
    function!
    I'm only using Access2000 though...
    Can I do something that would look like your stored proc?

    Thanks,
    Bingo.


    Bingo Guest

  7. #7

    Default Re: INSERT or UPDATE

    No, as Ray stated, you'll need to do this in VBS.

    While Access supports stored queries, like SQL Server, I don't believe you
    can have control logic like IF/END IF blocks.

    Very curious that you would switch from Oracle to Access???





    "Bingo" <bingobingo.be> wrote in message
    news:3f27ddf2$0$24556$626a54cenews.free.fr...
    > > IF EXISTS (SELECT 1 FROM table WHERE <whatever makes this row "exist">)
    > > UPDATE
    > > ELSE
    > > INSERT
    > >
    >
    > Wow, I should switch to SQL-Server I guess! This looks like a powerfull
    > function!
    > I'm only using Access2000 though...
    > Can I do something that would look like your stored proc?
    >
    > Thanks,
    > Bingo.
    >
    >

    Aaron Bertrand - MVP Guest

  8. #8

    Default Re: INSERT or UPDATE

    > Very curious that you would switch from Oracle to Access???

    That would have been very curious indeed! :-)
    No I never worked on Oracle in fact, I just found out that this SQL command
    existed in Oracle9i, but I always worked with Access...

    Thanks for your help both of you!

    Bingo


    Bingo Guest

Similar Threads

  1. Insert/Update/Add
    By ysurati01 in forum Coldfusion Database Access
    Replies: 1
    Last Post: December 30th, 06:48 PM
  2. insert,update url_id
    By kabbi~thkek in forum Coldfusion Database Access
    Replies: 4
    Last Post: October 1st, 07:07 PM
  3. Insert and Update using ASP/JS Mx v4
    By messiah944 in forum Dreamweaver AppDev
    Replies: 0
    Last Post: March 15th, 06:25 AM
  4. SQL Update, Insert and Delete
    By Lou in forum ASP Database
    Replies: 1
    Last Post: June 22nd, 07:33 PM
  5. IIS Hangs on Update or Insert from ASP
    By Mark in forum ASP Database
    Replies: 2
    Last Post: November 24th, 05:12 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139