Member Avatar for palavi

hi ,
can i update multiple tables on submitting one form, i ve a form which includes vehicle details and driver details like driver name vehicle no,vehile type booked for the date DD/MM/YYYY from some place to another place,these details shud be updated in the driver table , vehicle table and generate bill table. wer to code ,I will use MS acess as database. is it good ??or shall i use MYSQL, please tel me ,

  • 2 Contributors
  • 1 Reply
  • 73 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by peter_budo

Dani AI

Generated

As described, you can update multiple tables from a single form. As noted, the key is doing it correctly: perform all related INSERT/UPDATE statements inside one server-side transaction so the set of changes is atomic (all succeed or all roll back). In a JSP/JDBC setup use a single DB connection, setAutoCommit(false), prepared statements, then commit() on success or rollback() on error. Avoid issuing independent updates from separate connections or from client-side multiple requests — that risks partial updates.

Simple JDBC flow (JSP/Servlet): validate inputs, get a pooled connection, disable autocommit, execute prepared statements for booking/driver/vehicle/bill (use generated keys if you need booking_id to create the bill), then commit or rollback in a catch block.

try (Connection conn = dataSource.getConnection()) {
  conn.setAutoCommit(false);

  try (PreparedStatement p1 = conn.prepareStatement("INSERT INTO booking (...) VALUES (...)", Statement.RETURN_GENERATED_KEYS);
       PreparedStatement p2 = conn.prepareStatement("INSERT INTO bill (booking_id, amount) VALUES (?, ?)")) {
    // set params for p1
    p1.executeUpdate();
    try (ResultSet keys = p1.getGeneratedKeys()) {
      keys.next();
      int bookingId = keys.getInt(1);
      p2.setInt(1, bookingId);
      // set other bill params
      p2.executeUpdate();
    }
    conn.commit();
  } catch (SQLException ex) {
    conn.rollback();
    throw ex;
  }
}

Practical tips: normalize into driver, vehicle, booking and bill tables and use foreign keys. Compute bill amounts server-side and store only needed summary. For web apps prefer MySQL with InnoDB (transactions and FK support); MS Access is fine for small desktop or prototype setups but not recommended for production web concurrency. Use connection pooling, parameterized queries, proper exception handling and tests that force rollbacks to verify consistency.

Member Avatar for peter_budo

Yes, you can as long you do it correctly...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.