INSERT in 2 different tables?

**Centurion** · Centurion, November 23rd, 2009 08:39 AM

Below is an ASP SQL statement:

Code:

sql="INSERT INTO bookings (performanceID, customerID, bookingDate)"
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("performanceID") & "',"
sql=sql & "'" & Request.Form("customerID") & "',"
sql=sql & "'" & Request.Form("bookingDate") & "')"

on error resume next
conn.Execute sql,recaffected

But straight after this i want to insert "Booked" into the performances table, column name - Status.

Do i just concatenate the SQL statements?

Sorry im soooo noobish with my questions!!

Hope you can help!

**richyrich** · richyrich, November 23rd, 2009 08:45 AM

You can execute multiple SQL statements in one string by using a ; to seperate them

So, you could have, for example:-

Code:

sql="INSERT INTO bookings (performanceID, customerID, bookingDate)"
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("performanceID") & "',"
sql=sql & "'" & Request.Form("customerID") & "',"
sql=sql & "'" & Request.Form("bookingDate") & "');"
sql=sql & "INSERT INTO performances(status) VALUES('Booked');"

That will execute both statements.

Alternatively, I'd suggest using Stored Procedures.

You should never place form values directly into an SQL statement. Leaves you open for SQL injection attacks. You should validate all the values from your form. Using Stored Procedures would also help prevent injection attacks.

**Centurion** · Centurion, November 23rd, 2009 08:51 AM

Originally Posted by richyrich

You can execute multiple SQL statements in one string by using a ; to seperate them

So, you could have, for example:-

Code:

sql="INSERT INTO bookings (performanceID, customerID, bookingDate)"
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("performanceID") & "',"
sql=sql & "'" & Request.Form("customerID") & "',"
sql=sql & "'" & Request.Form("bookingDate") & "');"
sql=sql & "INSERT INTO performances(status) VALUES('Booked');"

That will execute both statements.

Alternatively, I'd suggest using Stored Procedures.

You should never place form values directly into an SQL statement. Leaves you open for SQL injection attacks. You should validate all the values from your form. Using Stored Procedures would also help prevent injection attacks.

my page seems to insert the record 5 times when i run that code..

**richyrich** · richyrich, November 23rd, 2009 08:53 AM

Is it inside some sort of loop?

Executing that SQL query itself wouldn't insert 5 records.

**Centurion** · Centurion, November 23rd, 2009 08:57 AM

Originally Posted by richyrich

Is it inside some sort of loop?

Executing that SQL query itself wouldn't insert 5 records.

Sorry Rich, I do have a Loop but its at the end of the page, didnt think it would matter? Would it be possible to have a quick look at my code?

Code:

<html>
<body>


<%
showName=request.form("showName")
performanceID=request.form("performanceID")
performance_time=request.form("performance_time")
email=request.form("email")

DIM conn

set conn=Server.CreateObject("ADODB.Connection")
conn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0"
conn.open server.mappath("../db/database.mdb")

set rs = Server.CreateObject("ADODB.recordset")
'validating the values on the form
sql= "SELECT shows.[showID], shows.[showName], performances.[performance_time] FROM shows INNER JOIN performances ON shows.[showID] = performances.[showID] WHERE shows.[showName]='"& showName & "' AND performances.[performance_time]='"&performance_time & "'"
rs.Open sql, conn

'response.write sql

if rs.EOF Then
Response.Write "<h4 >Oops! No records found!</h4>" 

else 
do while not rs.EOF
%>

<%
sql="INSERT INTO bookings (performanceID, customerID, bookingDate)"
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("performanceID") & "',"
sql=sql & "'" & Request.Form("customerID") & "',"
sql=sql & "'" & Request.Form("bookingDate") & "');"
sql=sql & "INSERT INTO performances(Status) VALUES('Booked');"

on error resume next
conn.Execute sql,recaffected 

'debug the sql statement
response.write sql


'create a new recordset to hold the new data, as we need to run a seperate query to find the bookingID

set rs2 = Server.CreateObject("ADODB.recordset")
sql2 = "SELECT @@Identity"
set rs2 = conn.execute(sql2)
bookingID = "Your Booking Number is: " & rs2(0)
response.write rs2(0)

if err<>0 then
  Response.Write("No update permissions!")
else
  Response.Write("<h3>" & recaffected & " record added and email sent</h3>")
end if
%>

<%
rs.movenext
											
loop

rs.close
rs2.close
conn.close
end if
%>

</body>
</html>

**richyrich** · richyrich, November 23rd, 2009 09:03 AM

Hmmm...I can only presume this query

Code:

sql= "SELECT shows.[showID], shows.[showName], performances.[performance_time] FROM shows INNER JOIN performances ON shows.[showID] = performances.[showID] WHERE shows.[showName]='"& showName & "' AND performances.[performance_time]='"&performance_time & "'"

is returning 5 records. I also presume, it should only produce 1 based on whatever performance they're booking for? I'm not entirely sure what this process should be doing. Perhaps you could elaborate? Could it return 5 records and why would it?

**Centurion** · Centurion, November 23rd, 2009 09:14 AM

Originally Posted by richyrich

Hmmm...I can only presume this query

Code:

sql= "SELECT shows.[showID], shows.[showName], performances.[performance_time] FROM shows INNER JOIN performances ON shows.[showID] = performances.[showID] WHERE shows.[showName]='"& showName & "' AND performances.[performance_time]='"&performance_time & "'"

is returning 5 records. I also presume, it should only produce 1 based on whatever performance they're booking for? I'm not entirely sure what this process should be doing. Perhaps you could elaborate? Could it return 5 records and why would it?

the idea is check if the data is valid on the form.. if it is, then proceed to inserting the data.

**richyrich** · richyrich, November 23rd, 2009 09:19 AM

Yep, I get that bit.

So, in what circumstance would it return more than one record. Surely it should be checking for a specific performance at a specific time, of which they'd only be one, wouldn't there?

I'm not sure I understand the reason for it being in a loop.

**Centurion** · Centurion, November 23rd, 2009 09:27 AM

Originally Posted by richyrich

Yep, I get that bit.

So, in what circumstance would it return more than one record. Surely it should be checking for a specific performance at a specific time, of which they'd only be one, wouldn't there?

I'm not sure I understand the reason for it being in a loop.

nor do i...

so the best thing to do is, remove the loop and run the query on its own.
then my inserts should work fine rite?

my dev server has gone down... fast2host say all windows servers are down!

**richyrich** · richyrich, November 23rd, 2009 09:31 AM

I think the best thing to do is work out why the query is returning more than 1 record as that's the only reason I can see why you'd get 5 inserts.

Even if you kept the loop, it would only loop through 1 record then.

Are my presumptions on the performance correct? Should it only be 1 record?

Thread: INSERT in 2 different tables?

LinkBack

Thread Tools

Display

INSERT in 2 different tables?

Similar Threads

Simple Insert Page Help?

Bulk Insert with other requirements..

How to insert variable html into php

Help with handling sql insert error

can not insert duplicate key

Bookmarks

Bookmarks

Posting Permissions