Inserting into multiple tables 1

[wilcoHead]

Hello;

What I am trying to do is:

1. Insert and create the "TeeTimeChartID"
2. Then Query the newly created TeeTimeChartID
3. Insert the rest of the data into the "TeeTimeChartDetail" Table using the
unique ID of the TeeTimeChartID.

I am using an Access DB
---------------------------------------------
<CFQUERY NAME="AddDay" DATASOURCE="RochesterCCDB" DBTYPE="ODBC">
INSERT INTO TeeTimeChart
(TeeTimeDaysID)

VALUES (#FORM.TeeTimeDaysID#)
</CFQUERY>

<CFQUERY NAME="GetTeeTimeChart" DBTYPE="ODBC" DATASOURCE="RochesterCCDB">
SELECT TeeTimeDaysID,
TeeTimeChartID

FROM TeeTimeChart

WHERE TeeTimeChartID = #FORM.TeeTimeChartID#
</CFQUERY>

<CFQUERY NAME="AddTimeDetail" DATASOURCE="RochesterCCDB" DBTYPE="ODBC">
INSERT INTO TeeTimeChartDetail
(TeeTimeChartID,
MemberID,
TeeTimeDropID)

VALUES (#FORM.TeeTimeChartID#,
#FORM.MemberID#,
#FORM.TeeTimeDropID#)

WHERE TeeTimeChartID = #FORM.TeeTimeChartID#
</CFQUERY>
</CFIF>
------------------------------------------------------------



Does this make sense?

Thanks for your input;
WilcoHEAD

 

[wilcoHead]

Does anyone have any suggestions?

Thanks for your input;
WilcoHEAD

 

[r937]

well, i have a question

first, you insert a row into TeeTimeChart, withoutr specifying a value for TeeTimeChartID

so presumably, this is an Access autonumber

then in the next query, you retrieve a row from TeeTimeChart using a value of TeeTimeChartID in the FORM scope

why?

how do you know what value to give FORM.TeeTimeChartID?


r937.com | rudy.ca

 

[wilcoHead]

Hello;

Yes it is an auto number.

I was hoping to query the correct record using the same auto-number.

"how do you know what value to give FORM.TeeTimeChartID?"

I guess that is the question? How do I do a query to the record I just inserted to complete the form submission in the next insert.

-E

Thanks for your input;
WilcoHEAD

 

[r937]

are the values of TeeTimeDaysID unique?



r937.com | rudy.ca

 

[wilcoHead]

Yes:

TeeTimeDays (Table)
----------------------------------
ID Name
----------------------------------
1 Saturday
2 Sunday
3 Holiday



Thanks for your input;
WilcoHEAD

 

[r937]

okay, then, just query it back based on the value of TeeTimeDaysID that you just inserted

<CFQUERY NAME="GetTeeTimeChart" DBTYPE="ODBC" DATASOURCE="RochesterCCDB">
SELECT TeeTimeDaysID
, TeeTimeChartID
FROM TeeTimeChart
WHERE TeeTimeDaysID = '#FORM.TeeTimeDaysID#'
</CFQUERY>

r937.com | rudy.ca

 

[wilcoHead]

TeeTimeChart (Table)

-------------------------------------
TeeTimeChartID TeeTimeDaysID
-------------------------------------



TeeTimeChartDetail (Table)
---------------------------------------------------------
TeeTimeChartDetailID
TeeTimeChartID
MemberID
TeeTimeDropID
---------------------------------------------------------

So, first I establish what Day it is. One (Day) to many (members, times)
Then I add the Times in which also have a unique number as well.

Thanks for your input;
WilcoHEAD

 

[r937]

this may be a silly question, but why do you need the TeeTimeChart table?

why not just use a DATE field in the TeeTimeChartDetail table?



r937.com | rudy.ca

 

[wilcoHead]

So that I could do one to many.

The thing is, I really do not have to archive the TeeTimes. They are going to be posted every wednesday. So I am not sure if I have set up the database properly.

-E

Thanks for your input;
WilcoHEAD

 

[wilcoHead]

I think it works although I need to loop the query until all of the records have been inserted.

How do I loop it until all the records have been inserted?

-------------------------------------------
<CFQUERY NAME="AddTimeDetail" DATASOURCE="RochesterCCDB" DBTYPE="ODBC">
INSERT INTO TeeTimeChartDetail
(TeeTimeChartID,
MemberID,
TeeTimeDropID)

VALUES (#GetTeeTimeChart.TeeTimeChartID#,
#FORM.MemberID#,
#FORM.TeeTimeDropID#)

</CFQUERY>
----------------------------------------------

Thanks for your input;
WilcoHEAD

 

[r937]

once

if more than once, then how many form fields are there? how do you want to loop over them? how are they named?

r937.com | rudy.ca

 

[wilcoHead]

It will need to add a maximum of 9 TeeTimeDropID's.

Below is an example of the one_pm.cfm form.

--------------------------------------------------
<FORM ACTION="pickdays.cfm" METHOD="post" NAME="add">
<TABLE BORDER="0" CELLSPACING="3" CELLPADDING="3" ALIGN="center" STYLE="border-bottom: 1px solid Gray;">
<TR>
<TD>
<CFOUTPUT><INPUT TYPE="hidden" NAME="TeeTimeDaysID" VALUE="#GetDay.TeeTimeDaysID#"></CFOUTPUT>

<INPUT TYPE="hidden" NAME="TeeTimeDropID" VALUE="51">
1:06
</TD>
<TD>
<SELECT NAME="MemberID" STYLE="border: 1px solid Gray;">
<Option VALUE="" SELECTED="selected"></Option>
<Option VALUE="X">X</Option>
<CFOUTPUT QUERY="GetMember">
<Option VALUE="#MemberID#">#MemberLastName#</Option>
</CFOUTPUT>
</SELECT>
</TD>

<TD>
<SELECT NAME="MemberID" STYLE="border: 1px solid Gray;">
<Option VALUE="" SELECTED="selected"></Option>
<Option VALUE="X">X</Option>
<CFOUTPUT QUERY="GetMember">
<Option VALUE="#MemberID#">#MemberLastName#</Option>
</CFOUTPUT>
</SELECT>
</TD>

<TD>
<SELECT NAME="MemberID" STYLE="border: 1px solid Gray;">
<Option VALUE="" SELECTED="selected"></Option>
<Option VALUE="X">X</Option>
<CFOUTPUT QUERY="GetMember">
<Option VALUE="#MemberID#">#MemberLastName#</Option>
</CFOUTPUT>
</SELECT>
</TD>

<TD>
<SELECT NAME="MemberID" STYLE="border: 1px solid Gray;">
<Option VALUE="" SELECTED="selected"></Option>
<Option VALUE="X">X</Option>
<CFOUTPUT QUERY="GetMember">
<Option VALUE="#MemberID#">#MemberLastName#</Option>
</CFOUTPUT>
</SELECT>
</TD>
</TR>
</TABLE>

<TABLE BORDER="0" CELLSPACING="3" CELLPADDING="3" ALIGN="center" STYLE="border-bottom: 1px solid Gray;">
<TR>
<TD>
<INPUT TYPE="hidden" NAME="TeeTimeDropID" VALUE="52">
1:14
</TD>
<TD>
<SELECT NAME="MemberID" STYLE="border: 1px solid Gray;">
<Option VALUE="" SELECTED="selected"></Option>
<Option VALUE="X">X</Option>
<CFOUTPUT QUERY="GetMember">
<Option VALUE="#MemberID#">#MemberLastName#</Option>
</CFOUTPUT>
</SELECT>
</TD>

<TD>
<SELECT NAME="MemberID" STYLE="border: 1px solid Gray;">
<Option VALUE="" SELECTED="selected"></Option>
<Option VALUE="X">X</Option>
<CFOUTPUT QUERY="GetMember">
<Option VALUE="#MemberID#">#MemberLastName#</Option>
</CFOUTPUT>
</SELECT>
</TD>

<TD>
<SELECT NAME="MemberID" STYLE="border: 1px solid Gray;">
<Option VALUE="" SELECTED="selected"></Option>
<Option VALUE="X">X</Option>
<CFOUTPUT QUERY="GetMember">
<Option VALUE="#MemberID#">#MemberLastName#</Option>
</CFOUTPUT>
</SELECT>
</TD>

<TD>
<SELECT NAME="MemberID" STYLE="border: 1px solid Gray;">
<Option VALUE="" SELECTED="selected"></Option>
<Option VALUE="X">X</Option>
<CFOUTPUT QUERY="GetMember">
<Option VALUE="#MemberID#">#MemberLastName#</Option>
</CFOUTPUT>
</SELECT>
</TD>
</TR>
</TABLE>

<TABLE BORDER="0" CELLSPACING="3" CELLPADDING="3" ALIGN="center" STYLE="border-bottom: 1px solid Gray;">
<TR>
<TD>
<INPUT TYPE="hidden" NAME="TeeTimeDropID" VALUE="53">
1:22
</TD>
<TD>
<SELECT NAME="MemberID" STYLE="border: 1px solid Gray;">
<Option VALUE="" SELECTED="selected"></Option>
<Option VALUE="X">X</Option>
<CFOUTPUT QUERY="GetMember">
<Option VALUE="#MemberID#">#MemberLastName#</Option>
</CFOUTPUT>
</SELECT>
</TD>

<TD>
<SELECT NAME="MemberID" STYLE="border: 1px solid Gray;">
<Option VALUE="" SELECTED="selected"></Option>
<Option VALUE="X">X</Option>
<CFOUTPUT QUERY="GetMember">
<Option VALUE="#MemberID#">#MemberLastName#</Option>
</CFOUTPUT>
</SELECT>
</TD>

<TD>
<SELECT NAME="MemberID" STYLE="border: 1px solid Gray;">
<Option VALUE="" SELECTED="selected"></Option>
<Option VALUE="X">X</Option>
<CFOUTPUT QUERY="GetMember">
<Option VALUE="#MemberID#">#MemberLastName#</Option>
</CFOUTPUT>
</SELECT>
</TD>

<TD>
<SELECT NAME="MemberID" STYLE="border: 1px solid Gray;">
<Option VALUE="" SELECTED="selected"></Option>
<Option VALUE="X">X</Option>
<CFOUTPUT QUERY="GetMember">
<Option VALUE="#MemberID#">#MemberLastName#</Option>
</CFOUTPUT>
</SELECT>
</TD>
</TR>
</TABLE>

<TABLE BORDER="0" CELLSPACING="3" CELLPADDING="3" ALIGN="center" STYLE="border-bottom: 1px solid Gray;">
<TR>
<TD>
<INPUT TYPE="hidden" NAME="TeeTimeDropID" VALUE="54">
1:30
</TD>
<TD>
<SELECT NAME="MemberID" STYLE="border: 1px solid Gray;">
<Option VALUE="" SELECTED="selected"></Option>
<Option VALUE="X">X</Option>
<CFOUTPUT QUERY="GetMember">
<Option VALUE="#MemberID#">#MemberLastName#</Option>
</CFOUTPUT>
</SELECT>
</TD>

<TD>
<SELECT NAME="MemberID" STYLE="border: 1px solid Gray;">
<Option VALUE="" SELECTED="selected"></Option>
<Option VALUE="X">X</Option>
<CFOUTPUT QUERY="GetMember">
<Option VALUE="#MemberID#">#MemberLastName#</Option>
</CFOUTPUT>
</SELECT>
</TD>

<TD>
<SELECT NAME="MemberID" STYLE="border: 1px solid Gray;">
<Option VALUE="" SELECTED="selected"></Option>
<Option VALUE="X">X</Option>
<CFOUTPUT QUERY="GetMember">
<Option VALUE="#MemberID#">#MemberLastName#</Option>
</CFOUTPUT>
</SELECT>
</TD>

<TD>
<SELECT NAME="MemberID" STYLE="border: 1px solid Gray;">
<Option VALUE="" SELECTED="selected"></Option>
<Option VALUE="X">X</Option>
<CFOUTPUT QUERY="GetMember">
<Option VALUE="#MemberID#">#MemberLastName#</Option>
</CFOUTPUT>
</SELECT>
</TD>
</TR>
</TABLE>

<TABLE BORDER="0" CELLSPACING="3" CELLPADDING="3" ALIGN="center" STYLE="border-bottom: 1px solid Gray;">
<TR>
<TD>
<INPUT TYPE="hidden" NAME="TeeTimeDropID" VALUE="55">
1:38
</TD>
<TD>
<SELECT NAME="MemberID" STYLE="border: 1px solid Gray;">
<Option VALUE="" SELECTED="selected"></Option>
<Option VALUE="X">X</Option>
<CFOUTPUT QUERY="GetMember">
<Option VALUE="#MemberID#">#MemberLastName#</Option>
</CFOUTPUT>
</SELECT>
</TD>

<TD>
<SELECT NAME="MemberID" STYLE="border: 1px solid Gray;">
<Option VALUE="" SELECTED="selected"></Option>
<Option VALUE="X">X</Option>
<CFOUTPUT QUERY="GetMember">
<Option VALUE="#MemberID#">#MemberLastName#</Option>
</CFOUTPUT>
</SELECT>
</TD>

<TD>
<SELECT NAME="MemberID" STYLE="border: 1px solid Gray;">
<Option VALUE="" SELECTED="selected"></Option>
<Option VALUE="X">X</Option>
<CFOUTPUT QUERY="GetMember">
<Option VALUE="#MemberID#">#MemberLastName#</Option>
</CFOUTPUT>
</SELECT>
</TD>

<TD>
<SELECT NAME="MemberID" STYLE="border: 1px solid Gray;">
<Option VALUE="" SELECTED="selected"></Option>
<Option VALUE="X">X</Option>
<CFOUTPUT QUERY="GetMember">
<Option VALUE="#MemberID#">#MemberLastName#</Option>
</CFOUTPUT>
</SELECT>
</TD>
</TR>
</TABLE>

<TABLE BORDER="0" CELLSPACING="3" CELLPADDING="3" ALIGN="center" STYLE="border-bottom: 1px solid Gray;">
<TR>
<TD>
<INPUT TYPE="hidden" NAME="TeeTimeDropID" VALUE="56">
1:46
</TD>
<TD>
<SELECT NAME="MemberID" STYLE="border: 1px solid Gray;">
<Option VALUE="" SELECTED="selected"></Option>
<Option VALUE="X">X</Option>
<CFOUTPUT QUERY="GetMember">
<Option VALUE="#MemberID#">#MemberLastName#</Option>
</CFOUTPUT>
</SELECT>
</TD>

<TD>
<SELECT NAME="MemberID" STYLE="border: 1px solid Gray;">
<Option VALUE="" SELECTED="selected"></Option>
<Option VALUE="X">X</Option>
<CFOUTPUT QUERY="GetMember">
<Option VALUE="#MemberID#">#MemberLastName#</Option>
</CFOUTPUT>
</SELECT>
</TD>

<TD>
<SELECT NAME="MemberID" STYLE="border: 1px solid Gray;">
<Option VALUE="" SELECTED="selected"></Option>
<Option VALUE="X">X</Option>
<CFOUTPUT QUERY="GetMember">
<Option VALUE="#MemberID#">#MemberLastName#</Option>
</CFOUTPUT>
</SELECT>
</TD>

<TD>
<SELECT NAME="MemberID" STYLE="border: 1px solid Gray;">
<Option VALUE="" SELECTED="selected"></Option>
<Option VALUE="X">X</Option>
<CFOUTPUT QUERY="GetMember">
<Option VALUE="#MemberID#">#MemberLastName#</Option>
</CFOUTPUT>
</SELECT>
</TD>
</TR>
</TABLE>

<TABLE BORDER="0" CELLSPACING="3" CELLPADDING="3" ALIGN="center" STYLE="border-bottom: 1px solid Gray;">
<TR>
<TD>
<INPUT TYPE="hidden" NAME="TeeTimeDropID" VALUE="57">
1:54
</TD>
<TD>
<SELECT NAME="MemberID" STYLE="border: 1px solid Gray;">
<Option VALUE="" SELECTED="selected"></Option>
<Option VALUE="X">X</Option>
<CFOUTPUT QUERY="GetMember">
<Option VALUE="#MemberID#">#MemberLastName#</Option>
</CFOUTPUT>
</SELECT>
</TD>

<TD>
<SELECT NAME="MemberID" STYLE="border: 1px solid Gray;">
<Option VALUE="" SELECTED="selected"></Option>
<Option VALUE="X">X</Option>
<CFOUTPUT QUERY="GetMember">
<Option VALUE="#MemberID#">#MemberLastName#</Option>
</CFOUTPUT>
</SELECT>
</TD>

<TD>
<SELECT NAME="MemberID" STYLE="border: 1px solid Gray;">
<Option VALUE="" SELECTED="selected"></Option>
<Option VALUE="X">X</Option>
<CFOUTPUT QUERY="GetMember">
<Option VALUE="#MemberID#">#MemberLastName#</Option>
</CFOUTPUT>
</SELECT>
</TD>

<TD>
<SELECT NAME="MemberID" STYLE="border: 1px solid Gray;">
<Option VALUE="" SELECTED="selected"></Option>
<Option VALUE="X">X</Option>
<CFOUTPUT QUERY="GetMember">
<Option VALUE="#MemberID#">#MemberLastName#</Option>
</CFOUTPUT>
</SELECT>

</TD>
</TR>
</TABLE>
<BR>
<DIV ALIGN="center"><INPUT TYPE="submit" NAME="add" VALUE="add" STYLE="border: 1px solid Black;">&nbsp;&nbsp;<INPUT TYPE="reset" NAME="Reset" VALUE="Reset" STYLE="border: 1px solid Black;"></DIV>
</FORM>
------------------------------------------------------


Thanks for your input;
WilcoHEAD

 

[r937]

all your SELECT dropdowns have the same name? and they all list all the members as OPTIONs?

i guess i don't understand what you're doing

r937.com | rudy.ca

 

[wilcoHead]

I am trying to Create Tee-Times for a Golf Course.

Basically there is a Time Grid for each hour, I have it grouped by each hour rather than having a million drop-downs.

The drop-downs will grab the info from the members area and then e-mail them with there tee-time when the form is submitted and there will also be a tee time chart on the site for Saturday, Sunday and Holiday.

This gets changed every wednesday.

go here:

http://www.hifispin.com/rochestercc/pickdays.cfm

then click on | 1PM |

There is only 3 members and the "X" is a holder for a guest that is not in the Database.

Does this help?

Thank you for your patience.

Thanks for your input;
WilcoHEAD

 

[r937]

for the 1 pm page, i see 7 tee-off times, i.e. 7 foursomes, and yet there are only 4 members?

in any case, you are going to ahve to come up with a schem for naming all those different dropdowns in a manner that lets you loop over them

do a search for "loop over form fields" -- i'm pretty sure there are other threads on this technique





r937.com | rudy.ca