Professional Web Applications Themes

How to add record to a table and update record toanother table at the same time? - Dreamweaver AppDev

Dear MM DW pros, I am still trying to figure out how to process two tables at the same time. Using "JOIN", I can update info in two related tables. But what about update one and insert another one? The case is like this: When customer ordered a prototype of our product, I need to add a new record into my tblPrototype. And at the same time, I want to change the PrototypeOrderCheck in tblQuote from false to true. So can I do this in one ASP page? How can I write this SQL? Thanks in advance....

  1. #1

    Default How to add record to a table and update record toanother table at the same time?

    Dear MM DW pros,

    I am still trying to figure out how to process two tables at the same time.

    Using "JOIN", I can update info in two related tables. But what about update
    one and insert another one?

    The case is like this:

    When customer ordered a prototype of our product, I need to add a new record
    into my tblPrototype. And at the same time, I want to change the
    PrototypeOrderCheck in tblQuote from false to true.

    So can I do this in one ASP page? How can I write this SQL?

    Thanks in advance.

    Skinny Guest

  2. #2

    Default Re: How to add record to a table and update record toanother table at the same time?

    ?
    Skinny Guest

  3. #3

    Default Re: How to add record to a table and update record toanother table at the same time?

    You need two actions - one to insert and one to update. SQL is designed to
    execute one action - INSERT, UPDATE, DELETE, SELECT, DROP etc. per statement.
    In some versions, manufacturers added things like triggers which may do what
    you want, but then you lose compatability with different SQL environments.

    jerryartman Guest

  4. #4

    Default Re: How to add record to a table and update record to another table at the same time?

    Hey you Skinny Guy:

    Welcome to advanced web application development. You're getting beyond the
    basics that DWMX provides.

    As you've found, Dreamweaver has lousy support for anything resembling a
    relational data model. You'll have to build it yourself.

    Easiest is to write code to chain together an insert transaction. So in
    pseudocode:

    [insert to table1]
    [grab new ID value]
    [insert ID value and other info to table2]

    To learn how to do this in ASP, hunt around on aspin.com and read up on
    database inserts using ADO.

    If you're using MSSQL Server, the "right way" to do this kind of thing is to
    package everything as a stored procedure and call it from Dreamweaver.

    If your experience is like mine, you'll eventually get very frustrated at
    the limits of DWMX, look at the capabilities of something like VS.NET 2005,
    and dump Dreamweaver entirely. After five years with Dreamweaver, I've found
    a much better tool for this kind of work. The VS.NET 2005 beta is amazing.

    -KF





    "Skinny Guy" <com> wrote in message
    news:dbgblj$pv2$macromedia.com... 


    Ken Guest

  5. #5

    Default Re: How to add record to a table and update record toanother table at the same time?

    Now we have two choices:

    I guess Jerry's answer is really straightforward. Just use two ASP pages - one
    to insert, the other to update.

    Just like what Ken said, that is a little bit "lousy".

    Hey Ken, I am really interested in the way you mentioned "to write code to
    chain together an insert transaction". I believe many people are also
    fascinated about your idea. You gave us this psuedocode:

    [insert to table1]
    [grab new ID value]
    [insert ID value and other info to table2]

    Can you clarify more about this [grab new ID]? Do you write it in one SQL
    sentence or two consecutive ones performed in one page? Hope to see your
    further help in this.


    Skinny Guest

  6. #6

    Default Re: How to add record to a table and update record toanother table at the same time?

    And I also assume this is a common situation programmers face. So everyone who happens to read this post, may I ask what you do with such a problem?
    Skinny Guest

  7. #7

    Default Re: How to add record to a table and update record toanother table at the same time?

    ?
    Skinny Guest

  8. #8

    Default Re: How to add record to a table and update record toanother table at the same time?

    I basically do what others have stated
    Page 1
    [insert or update table1]

    Page 2
    [If I inserted on page1, grab new ID value] I do this with a select statement
    that returns the max RID (which is the most recent) that also matches the
    values in the session variables created when I insert on page1.
    [insert or update table2] depending on your needs you can populate a hidden
    form that automatically submits and inserts into your 2nd table, or populate a
    visible form that the user can edit, or add info and update. Or a combination
    of hidden fields you don?t want the user to edit the values passed into it, and
    other visible form controls for the values you want them to ad or edit.

    This page explains how to turn inserted values into session variable.
    How to pass form data from an insert, update or delete
    pagehttp://www.macromedia.com/cfusion/knowledgebase/index.cfm?id=tn_16516

    Glitch808 Guest

Similar Threads

  1. How to select record on one table but not the other
    By Rustywater in forum Coldfusion Database Access
    Replies: 1
    Last Post: September 11th, 11:15 AM
  2. How can i retrieve record ONLY From 300 - 400 in amillion Record Table?
    By keonglah in forum Coldfusion Database Access
    Replies: 6
    Last Post: June 13th, 02:53 AM
  3. Copy a record to another table?
    By Adam in forum Microsoft Access
    Replies: 11
    Last Post: August 17th, 01:09 PM
  4. move record from 1 table to another table
    By Jason Frazer in forum Microsoft Access
    Replies: 6
    Last Post: August 4th, 02:45 AM
  5. post record to table
    By LGarcia in forum Microsoft Access
    Replies: 3
    Last Post: August 1st, 06:15 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