Thread: For Next Loop Within another For Next Loop

Originally Posted by Wolffy

That error message does not indicate a data type problem. Post your query.

Code:

sql="INSERT INTO [shows] (showName)"
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("showName") & "')"

'on error resume next
conn.Execute sql,recaffected 

response.write "the show inserted"


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

set rs2 = Server.CreateObject("ADODB.recordset")
sql2 = "SELECT @@Identity"
set rs2 = conn.execute(sql2)
bookingID = "Your Show Number is: " & rs2(0)
showID = rs2(0)
'response.write "<br>"
'response.write "And so did the show: "
'response.write rs2(0)


For x = 1 To 10
	SQL = "INSERT INTO [performances] (showID, SeatNumber, SeatPrice, performance_time, ShowDate, ShowStatus) " & _
		"VALUES ( " & _
		showID & ", " & _
		x & ", " & _
		Request.Form("SeatPrice") & ", " & _
		Request.Form("performance_time") & ", " & _
		Request.Form("ShowDate") & ", " & _
		"'" & Request.Form("ShowStatus") & "')"
	conn.Execute SQL
	'response.write SQL
Next

idea is, insert the show, then query the showID and then run the loop

Originally Posted by Wolffy

I meant the output from the response.write -- the code is useless in debugging this.

wooofy, gonna have to reply back after ive done some further testing of my own.. will post up my findings later on or tommorow.

Originally Posted by Wolffy

There is also a serious flaw in your logic, if this is a multi-user application. @@identity will return the last identity value inserted, regardless of session. If there is more than one user accesses the application, it is possible that an INSERT statement will be executed (and it doesn't matter what table it's on) between the time of your first INSERT and the query to return the @@identity -- the result being that you will get the WRONG VALUE for @@identity.

If you must, do it this way (pseudo code):

Code:

Set timestamp = currentTime()
Insert Into Shows
  (ShowName, DateCreated)
Values
 ('ShowName', timestamp);

Select ShowID
  from Show
 Where ShowName = 'ShowName'
  and DateAdded = timestamp;

IMHO a dateAdded (as well as a dateUpdated) field should be included in every table.

for this particular sub-system its just one person that will create a show.. however i have used @@IDENTITY to get the bookingID for customers and as you suggested it might be a problem.

ill take into account what wolffy said.. i can see that becoming a problem!

however the reason why my date field wont insert is because i think its calculating the date literally. 12/12/2009 = 4.97760079641613E-04

I assume id pass the date into a validator? to format it correctly?

Originally Posted by Wolffy

I believe in Access a date literal is #12/12/2009#

Code:

Request.Form("#""ShowDate""#") & ", " & _

Originally Posted by Centurion

Code:

Request.Form("#""ShowDate""#") & ", " & _

just to show you that i am trying stuff out:

Code:

For x = 1 To 10
	SQL = "INSERT INTO [performances] (showID, SeatNumber, SeatPrice, performance_time, ShowDate, Status) " & _
		"VALUES ( " & _
		showID & ", " & _
		x & ", " & _
		Request.Form("SeatPrice") & ", " & _
		Request.Form("performance_time") & ", " & "#" & _
		Request.Form("ShowDate") & ", " & "#" & _
		"'" & Request.Form("Status") & "')"
	conn.Execute SQL

it shows:

Code:

'#22/12/2009'.

just need to work out how to do the other side?