Professional Web Applications Themes

Rs updating with mySql - MySQL

I used this code to add a record to table 'adItems' when I used access. once I added the record, I pulled the new id of it (just after it was created - bolded). Set rs = Server.CreateObject("ADODB.Recordset") rs.open "select * from adItems",conn,3,3 rs.AddNew rs("catId") = catId rs("expiry") = expiry rs("adTitle") = adTitle rs.Update newid = rs("id") <--- nothing there! rs.close now, that I moved to MySql, newid remains blank. why is that? Any idea? Thanks...

  1. #1

    Default Rs updating with mySql

    I used this code to add a record to table 'adItems' when I used
    access.
    once I added the record, I pulled the new id of it (just after it was
    created - bolded).


    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.open "select * from adItems",conn,3,3
    rs.AddNew
    rs("catId") = catId
    rs("expiry") = expiry
    rs("adTitle") = adTitle
    rs.Update
    newid = rs("id") <--- nothing there!
    rs.close

    now, that I moved to MySql, newid remains blank. why is that? Any
    idea?

    Thanks

    garf85 Guest

  2. #2

    Default Re: Rs updating with mySql

    > rs.open "select * from adItems",conn,3,3 

    How can you add a record to a SELECT query? Either open the recordset
    with the tablename (if ODBC is smart enough to understand that) or build
    an INSERT query.

    Best regards
    --
    Willem Bogaerts

    Application smith
    Kratz B.V.
    http://www.kratz.nl/
    Willem Guest

  3. #3

    Default Re: Rs updating with mySql

    select statement or the table name are the same - and both are not
    working (newid stays empty).
    INSERT statement is ok: conn.execute "INSERT ..."
    the problem is that if you do this - you have no way of knowing the
    new id (which is created automatically).
    so what can I do?
    This worked with Access. what's the problem here?

    thanks


    Willem Bogaerts כתב: 
    >
    > How can you add a record to a SELECT query? Either open the recordset
    > with the tablename (if ODBC is smart enough to understand that) or build
    > an INSERT query.
    >
    > Best regards
    > --
    > Willem Bogaerts
    >
    > Application smith
    > Kratz B.V.
    > http://www.kratz.nl/[/ref]

    garf85 Guest

  4. #4

    Default Re: Rs updating with mySql

    garf85 wrote: 

    Welcome to the world of SQL extensions. You're finding just because
    something works with one RDB doesn't mean it works with another.

    You need to SELECT LAST_INSERT_ID() to get the id.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  5. #5

    Default Re: Rs updating with mySql

    Thanks alot for your help.
    But your solution makes me connect to the DB twice.
    one time for inserting, and second for retrieving the new id.
    isn't there a way to make it more efficient?

    Thanks again!

    Jerry Stuckle כתב: 
    >
    > Welcome to the world of SQL extensions. You're finding just because
    > something works with one RDB doesn't mean it works with another.
    >
    > You need to SELECT LAST_INSERT_ID() to get the id.
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]

    garf85 Guest

  6. #6

    Default Re: Rs updating with mySql

    garf85 wrote: 
    >> Welcome to the world of SQL extensions. You're finding just because
    >> something works with one RDB doesn't mean it works with another.
    >>
    >> You need to SELECT LAST_INSERT_ID() to get the id.
    >>[/ref]
    > Thanks alot for your help.
    > But your solution makes me connect to the DB twice.
    > one time for inserting, and second for retrieving the new id.
    > isn't there a way to make it more efficient?
    >
    > Thanks again!
    >[/ref]

    (top posting fixed)

    You shouldn't have to connect twice (in fact, you'll lose the id when
    the connection closes). Just issue the SELECT statement on the same
    connection.

    P.S. Please don't top post. Thanks.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  7. #7

    Default Re: Rs updating with mySql

    Sorry but i don't know what "top posting" is (?)

    the last_insert_id() is supposed to be like that:
    ================================================== ===========
    sqlt = "INSERT INTO adItems (catID, expiry, adTitle, adBody, name,
    email, prefix1, prefix2, number1, number2, bounce) VALUES "&_
    "("& catID &", "& expiry &", '"& adTitle &"', '"& adBody &"', '"&
    name &"', '"& email &"', '"& prefix1 &"', '"& prefix2 &"', '"& number1
    &"', '"& number2 &"', '"& theNow &"');"

    conn.execute (sqlt)

    set rs = conn.execute("SELECT LAST_INSERT_ID()")
    newid = rs(0)
    ================================================== ===========
    ??

    it's two executions. isn't there any better way to do it?

    Thanks

    Jerry Stuckle כתב: 
    > > Thanks alot for your help.
    > > But your solution makes me connect to the DB twice.
    > > one time for inserting, and second for retrieving the new id.
    > > isn't there a way to make it more efficient?
    > >
    > > Thanks again!
    > >[/ref]
    >
    > (top posting fixed)
    >
    > You shouldn't have to connect twice (in fact, you'll lose the id when
    > the connection closes). Just issue the SELECT statement on the same
    > connection.
    >
    > P.S. Please don't top post. Thanks.
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]

    garf85 Guest

  8. #8

    Default Re: Rs updating with mySql

    garf85 wrote: 
    >>
    >> (top posting fixed)
    >>
    >> You shouldn't have to connect twice (in fact, you'll lose the id when
    >> the connection closes). Just issue the SELECT statement on the same
    >> connection.
    >>
    >> P.S. Please don't top post. Thanks.
    >>
    >> --
    >> ==================
    >> Remove the "x" from my email address
    >> Jerry Stuckle
    >> JDS Computer Training Corp.
    >> net
    >> ==================[/ref]
    >[/ref]
     

    (Top posting fixed)

    Top posting is posting your response above the previous responses. This
    newsgroup uses bottom or interwoven response postings.

    And no, there isn't another way to do it. But it's quite fast - and I
    highly suspect it was doing something very similar under the covers for
    your Access database.


    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

Similar Threads

  1. Updating mysql ODBC drivers in CF
    By ChuckInAtl in forum Coldfusion Database Access
    Replies: 2
    Last Post: June 29th, 10:39 PM
  2. Updating MySQL record
    By Dag Eilertsen in forum PHP Development
    Replies: 2
    Last Post: August 31st, 05:36 PM
  3. updating substrings in mysql
    By kaptain in forum PHP Development
    Replies: 2
    Last Post: December 11th, 04:30 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