SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
 
User Name:
Password:
Remember me
Go Back   ASP Free Forums > Database > SQL Development
Reload this Page

Multiple Tables

Discuss Multiple Tables in the SQL Development forum on ASP Free.
Multiple Tables SQL forums discussing structured query language related questions that are platform independent. Discussions include advanced topics such as joins, stored procedures, and triggers.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread ASP Free Forums Sponsor:
  #1  
Old January 24th, 2004, 10:38 AM
GenkiDave's Avatar
GenkiDave GenkiDave is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Jan 2004
Location: Canada
Posts: 60 GenkiDave User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 5
Angry Multiple Tables
My god, there are a lot of trouble ppl out here. I'm glad I'm not alone!

Anyway, as the Thread indicates, I seem to be haing truble with my SQL statements when it comes to INSERTING or ADDing information to a database that have a Parent table and a few child tables.

Getting the Info and displaying it is OK, but I can't figure out why I can't add, update, or delete. If anyone could take a look at my code here and correnct me on my SQL, I'd be greatly appreciative.


----HTML Form that sends the inifo to the ASP page to process----

<HTML>

<BODY text="#FFFF00" bgcolor="#800000">

<FORM NAME="frmAddMovie" ACTION="add.asp" METHOD="post">

<TABLE BORDER=0>

<TR>
<TH ALIGN="LEFT">Movie Name:</TH>
<TD><INPUT TYPE="textbox" maxLength="20" NAME="txtMovieName" SIZE="25" VALUE=""></TD>
</TR>
<TR>
<TH ALIGN="LEFT">Genre:</TH>
<TD><SELECT NAME="optGenre">
<OPTION VALUE="0"></OPTION>
<OPTION VALUE="1">Action</OPTION>
<OPTION VALUE="2">Action/Adventure</OPTION>
<OPTION VALUE="3">Adult (XXX)</OPTION>
<OPTION VALUE="4">Animation (Classic Style)</OPTION>
<OPTION VALUE="5">Animation (Modern Style)</OPTION>
<OPTION VALUE="6">Comedy</OPTION>
<OPTION VALUE="7">Drama</OPTION>
<OPTION VALUE="8">Fantasy</OPTION>
<OPTION VALUE="9">Horror</OPTION>
<OPTION VALUE="10">JapanAmation</OPTION>
<OPTION VALUE="11">Martial Arts</OPTION>
<OPTION VALUE="12">Mystery</OPTION>
<OPTION VALUE="13">Romance</OPTION>
<OPTION VALUE="14">Romantic Comedy</OPTION>
<OPTION VALUE="15">Science Fiction</OPTION>
<OPTION VALUE="16">Spy Film</OPTION>
<OPTION VALUE="17">Thriller/Suspense</OPTION>
<OPTION VALUE="18">War</OPTION>
<OPTION VALUE="19">Western</OPTION>
</SELECT>
</TD>
</TR>
<TR>
<TH ALIGN="LEFT">Director:</TH>
<TD>
<INPUT TYPE="textbox" maxLength=30 NAME="txTDirector" SIZE="25" VALUE=""></TD>
</TR>
<TR>
<TH ALIGN="LEFT">Year Released:</TH>
<TD>
<INPUT TYPE="textbox" maxLength=20 NAME="txtYearReleased" SIZE="25" VALUE=""></TD>
</TR>
<TR>
<TH ALIGN="LEFT">Length:</TH>
<TD>
<INPUT TYPE="textbox" maxLength=20 NAME="txtLength" SIZE="25" VALUE=""></TD>
</TR>
<TR>
<TH ALIGN="LEFT">Actor First Name:</TH>
<TD>
<INPUT TYPE="textbox" maxLength=20 NAME="txtFname" SIZE="25" VALUE=""></TD>
</TR>
<TR>
<TH ALIGN="LEFT">Actor Last Name:</TH>
<TD>
<INPUT TYPE="textbox" maxLength=20 NAME="txtLname" SIZE="25" VALUE=""></TD>
</TR>
<TR>
<TH ALIGN="LEFT">Rating:</TH>
<TD><SELECT NAME="optRating">
<OPTION VALUE="0"></OPTION>
<OPTION VALUE="1">G (General)</OPTION>
<OPTION VALUE="2">PG (Parental Guidance)</OPTION>
<OPTION VALUE="3">PG-13</OPTION>
<OPTION VALUE="4">14A</OPTION>
<OPTION VALUE="5">18A</OPTION>
<OPTION VALUE="6">R (Restricted)</OPTION>
<OPTION VALUE="7">XXX (Adult)</OPTION>
</SELECT>
</TD>
</TR>
<TR>
<TH ALIGN="LEFT">Review:</TH>
</TR>
<TR>
<TD COLSPAN="2"><TEXTAREA NAME="txtReview" COLS="35" ROWS="10"></TEXTAREA></TD>
</TR>
<TR>
<TH ALIGN="LEFT">Codec:</TH>
<TD><SELECT NAME="optCodec">
<OPTION VALUE="0"></OPTION>
<OPTION VALUE="1">DivX</OPTION>
<OPTION VALUE="2">XviD</OPTION>
</SELECT>
</TD>
</TR>
<TR>
<TH ALIGN="LEFT">CDs:</TH>
<TD>
<INPUT TYPE="textbox" maxLength=20 NAME="txtCDs" SIZE="25" VALUE=""></TD>
</TR>
<TR>
<TH ALIGN="LEFT">Notes:</TH>
<TD>
<INPUT TYPE="textbox" maxLength=20 NAME="txtNotes" SIZE="25" VALUE=""></TD>
</TR>
<TR>
</TR>
<TR>
</TR>
<TR>
</TR>
<TR>
<TD ALIGN="middle" COLSPAN="2"><INPUT TYPE="submit" VALUE="Add Record" NAME="btnSubmit"><INPUT TYPE="reset" VALUE="Reset Form" NAME="btnReset"></TD>
</TR>
</TABLE>
</FORM>
</BODY>
</HTML>


----ASP page to process the info recieved from HTML page----


<%
'Dimension variables
Dim conn 'Holds the Database Connection Object
Dim rsAddMovie 'Holds the recordset for the new record to be added
Dim strSQL 'Holds the SQL query to query the database
%>

<%
'Create an ADO connection object
Set conn = Server.CreateObject("ADODB.Connection")
%>

<%
'Set an active connection to the Connection object using a DSN-less connection
conn.Open "DSN=movie"
%>

<%
'Create an ADO recordset object
Set rsAddMovie = Server.CreateObject("ADODB.Recordset")
%>

<%
'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "INSERT INTO tblMovies, tblActors (MovieName, GenreID, Direcector, YearReleased, Length, Fname, Lname, RatingID, Review, CodecID, CDs, Notes)"
strSQL = strSQL & " VALUES ('" & request.form("txtMovieName") & "'," & request.form("optGenre") & ",'" & request.form("txtDirector") & "','" & request.form("txtYearReleased") & "','" & request.form("txtLength") & "','" & request.form("txtFname") & "','" & request.form("txtLname") & "'," & request.form("optRating") & ",'" & request.form("txtReview") & "'," & request.form("optCodec") & ",'" & request.form("txtCds") & "','" & request.form("txtNotes") & "')"
conn.Execute (strSQL)
%>

<%
'Set the cursor type we are using so we can navigate through the recordset
rsAddMovie.CursorType = 2
%>

<%
'Set the lock type so that the record is locked by ADO when it is updated
rsAddMovie.LockType = 3
%>

<%
'Open the recordset with the SQL query
rsAddMovie.Open strSQL, conn
%>

<%
'Tell the recordset we are adding a new record to it
rsAddMovie.AddNew
%>

<%
'Add a new record to the recordset
rsAddMovie.Fields("MovieName") = Request.Form("txtMovieName")
rsAddMovie.Fields("GenreID") = Request.Form("optGenre")
rsAddMovie.Fields("Director") = Request.Form("txtDirector")
rsAddMovie.Fields("YearReleased") = Request.Form("txtYearReleased")
rsAddMovie.Fields("Length") = Request.Form("txtLength")
rsAddMovie.Fields("Fname") = Request.Form("txtFname")
rsAddMovie.Fields("Lname") = Request.Form("txtLname")
rsAddMovie.Fields("RatingID") = Request.Form("optRating")
rsAddMovie.Fields("Review") = Request.Form("txtReview")
rsAddMovie.Fields("CodecID") = Request.Form("optCodec")
rsAddMovie.Fields("CDs") = Request.Form("txtCds")
rsAddMovie.Fields("Notes") = Request.Form("txtNotes")
%>

<%
'Write the updated recordset to the database
rsAddMovie.Update
%>

<%
'Reset server objects
rsAddMovie.Close
Set rsAddMovie = Nothing
Set conn = Nothing
%>

<%
'Redirect to the movie.asp page
Response.Redirect "movie.asp"
%>
Reply With Quote
  #2  
Old January 24th, 2004, 11:24 AM
Memnoch's Avatar
Memnoch Memnoch is offline
Unholy Moderator
Click here for more information.
  
Join Date: Oct 2003
Location: In hell, where did you think?
Posts: 11,781 Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level) 
Time spent in forums: 3 Weeks 5 Days 8 h 45 m 55 sec
Reputation Power: 470
In a parent/child relationship you must always enter data into the parent table first, then the child table.
Reply With Quote
  #3  
Old January 24th, 2004, 06:48 PM
GenkiDave's Avatar
GenkiDave GenkiDave is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Jan 2004
Location: Canada
Posts: 60 GenkiDave User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 5
Thanks Memnoch. I rearranged my Field Names in the SQL so the Fname and the Lname from the Actors table would come last both in the INSERT INTO and the VALUES. I even rearranged it on the HTML form. Still no go. I keep getting the:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
/add.asp, line 27


Here's a copy of the ASP page. again, but changed SQL if anyone won't kind helpping out.


---------------------------------ASP Page--------------------------------------

<%
'Dimension variables
Dim conn 'Holds the Database Connection Object
Dim rsAddMovie 'Holds the recordset for the new record to be added
Dim strSQL 'Holds the SQL query to query the database
%>

<%
'Create an ADO connection object
Set conn = Server.CreateObject("ADODB.Connection")
%>

<%
'Set an active connection to the Connection object using a DSN-less connection
conn.Open "DSN=movie"
%>

<%
'Create an ADO recordset object
Set rsAddMovie = Server.CreateObject("ADODB.Recordset")
%>

<%
'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "INSERT INTO tblMovies, tblActors (MovieName, GenreID, Director, YearReleased, Length, RatingID, Review, CodecID, CDs, Notes, Fname, Lname)"
strSQL = strSQL & " VALUES ('" & request.form("txtMovieName") & "'," & request.form("optGenre") & ",'" & request.form("txtDirector") & "','" & request.form("txtYearReleased") & "','" & request.form("txtLength") & "'," & request.form("optRating") & ",'" & request.form("txtReview") & "'," & request.form("optCodec") & ",'" & request.form("txtCds") & "','" & request.form("txtNotes") & "','" & request.form("txtFname") & "','" & request.form("txtLname") & "')"
conn.Execute (strSQL)
%>

<%
'Set the cursor type we are using so we can navigate through the recordset
rsAddMovie.CursorType = 2
%>

<%
'Set the lock type so that the record is locked by ADO when it is updated
rsAddMovie.LockType = 3
%>

<%
'Open the recordset with the SQL query
rsAddMovie.Open strSQL, conn
%>

<%
'Tell the recordset we are adding a new record to it
rsAddMovie.AddNew
%>

<%
'Add a new record to the recordset
rsAddMovie.Fields("MovieName") = Request.Form("txtMovieName")
rsAddMovie.Fields("GenreID") = Request.Form("optGenre")
rsAddMovie.Fields("Director") = Request.Form("txtDirector")
rsAddMovie.Fields("YearReleased") = Request.Form("txtYearReleased")
rsAddMovie.Fields("Length") = Request.Form("txtLength")
rsAddMovie.Fields("RatingID") = Request.Form("optRating")
rsAddMovie.Fields("Review") = Request.Form("txtReview")
rsAddMovie.Fields("CodecID") = Request.Form("optCodec")
rsAddMovie.Fields("CDs") = Request.Form("txtCds")
rsAddMovie.Fields("Notes") = Request.Form("txtNotes")
rsAddMovie.Fields("Fname") = Request.Form("txtFname")
rsAddMovie.Fields("Lname") = Request.Form("txtLname")
%>

<%
'Write the updated recordset to the database
rsAddMovie.Update
%>

<%
'Reset server objects
rsAddMovie.Close
Set rsAddMovie = Nothing
Set conn = Nothing
%>

<%
'Redirect to the movie.asp page
Response.Redirect "movie.asp"
%>
Reply With Quote
  #4  
Old January 24th, 2004, 11:27 PM
Memnoch's Avatar
Memnoch Memnoch is offline
Unholy Moderator
Click here for more information.
  
Join Date: Oct 2003
Location: In hell, where did you think?
Posts: 11,781 Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level) 
Time spent in forums: 3 Weeks 5 Days 8 h 45 m 55 sec
Reputation Power: 470
don't insert into both tables at the same time...insert into the first table, then insert into the second table, with 2 different insert sql statements.
Reply With Quote
Reply

Viewing: ASP Free Forums > Database > SQL Development > Multiple Tables

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 3 hosted by Hostway. 
Stay green...Green IT