Topic awaiting preservation: ASP + Access DB + SQL Problem $Pages that link to <a href="https://ozoneasylum.com/backlink?for=13046" title="Pages that link to Topic awaiting preservation: ASP + Access DB + SQL Problem" rel="nofollow" >Topic awaiting preservation: ASP + Access DB + SQL Problem\$

Author	Thread
genkidave Nervous Wreck (II) Inmate From: Japan Insane since: Nov 2003	posted 02-03-2004 10:21 OK, it's me again. I finally have my ASP pages up and running, BUT for 2 things (Bare with me here please). 1) I have 3 tables in my Acccess Database: a) tblMovies (fldMovieID (Primary Key), fldMovieName) b) tblActors (fldActorID (Primary Key), fldFName, fldLName) c) tblMovieActorJoin (fldActorID, fldMovieID (Both Foreign Keys)) OK, When I add info to my database from my form on an ASP page, the Movie info and the Actor info are inserted A.O.K, but for some reason (even though there are realationships for the 3 tables) the tblMovieActorJoin isn't updated with the fldActorID or the fldMovieID. In other words: I can't see which actor was in which movies, or vice-a-versa, which movies have which actors UNLESS I manually do it within the databse itself, but I need the database to be updated when I add the info from the form and that would be it. Any helpfull thoughts? Genki Dave
DmS Paranoid (IV) Inmate From: Sthlm, Sweden Insane since: Oct 2000	posted 02-03-2004 10:38 In order to help we reall need to see what code (both .asp and SQL) that is executed as you do an update and the possible error messages you might get. Normally if something isn't inserted in the database when you expect it either the values to be inserted doesn't reach the SQL from the code, or some part of the SQL don't get executed. Just because you have relationships between the tables doesn't mean that updates between the tables is automatically created. For that you need triggers or stored procedures which you need to do yourself. Relationships is more of an integrity check for the database. Now for something like this you describe stored procedures or triggers shouldn't be nessecarry, just make sure that your SQL performs all inserts in all the correct tables, then make sure that the actual values from the web-page reaches the SQL so there is something to insert. /Dan {cell 260} -{ a vibration is a movement that doesn't know which way to go }-
genkidave Nervous Wreck (II) Inmate From: Japan Insane since: Nov 2003	posted 02-04-2004 08:05 Thanks for replying DmS and for the offer to help out. I think it's a NULL problem I'm dealing with, but not sure. Here is the code for BOTH the movieform.asp and the add.asp ///////////////////////// movieform.asp ///////////////////////// <%@ LANGUAGE="VBSCRIPT" %> <% Option Explicit %> <!- - #include file="adovbs.inc" - -> <% 'declare our variables Dim adoConn,sql,sql2,sql3,rs,rs2,rs3 %> <% 'Create an ADO connection object Set adoConn = Server.CreateObject("ADODB.Connection") 'Set an active connection to the Connection object using a DSN-less connection adoConn.Open "DSN=movies" %> <% sql="SELECT * FROM tblGenres ORDER BY fldGenreID" %> <% sql2="SELECT * FROM tblRatings ORDER BY fldRatingID" %> <% sql3="SELECT * FROM tblCodecs ORDER BY fldCodecID" %> <% Set rs=adoConn.execute(sql) %> <% Set rs2=adoConn.execute(sql2) %> <% Set rs3=adoConn.execute(sql3) %> <HTML> <HEAD><TITLE>Genki Dave's Movie Collection - Add Movie</TITLE></HEAD> <BODY text="#FFFF00" bgcolor="#800000"> <BR> <CENTER> <H1><font face="Japan"><U>Please Fill In ALL Data Fields</U></font></H1> <BR> <FORM NAME="frmAddMovie" ACTION="add.asp" METHOD="POST"> <TABLE border="0" style="border-collapse: collapse" cellpadding="0" cellspacing="0"> <TR> <TH ALIGN="RIGHT">Movie Name:</TH> <TD><INPUT TYPE="TEXT" maxLength=20 NAME="txtMovieName" size="20"></TD> </TR> </TR> <TR> <TH ALIGN="RIGHT">Genre:</TH> <TD><select name="optGenre"> <% Do While Not rs.eof Response.Write("<option value=""" & rs("fldGenreID") & """>" & _ rs("fldGenre") & "</option>") rs.movenext Loop %> </select> </TD> </TR> <TR> <TH ALIGN="RIGHT">Director's First Name:</TH> <TD><INPUT TYPE="TEXT" maxLength=30 NAME="txtDFname" size="20"></TD> </TR> <TR> <TH ALIGN="RIGHT">Director's Last Name:</TH> <TD><INPUT TYPE="TEXT" maxLength=30 NAME="txtDLname" size="20"></TD> </TR> <TR> <TH ALIGN="RIGHT">Year Released:</TH> <TD><INPUT TYPE="TEXT" maxLength=20 NAME="txtYearReleased" size="20"></TD> </TR> <TR> <TH ALIGN="RIGHT">Length:</TH> <TD><INPUT TYPE="TEXT" maxLength=20 NAME="txtLength" size="20"></TD> </TR> <TR> <TH ALIGN="RIGHT">Actor's First Name:</TH> <TD><INPUT TYPE="TEXT" maxLength=20 NAME="txtAFname" size="20"></TD> </TR> <TR> <TH ALIGN="RIGHT">Actor's Last Name:</TH> <TD><INPUT TYPE="TEXT" maxLength=20 NAME="txtALname" size="20"></TD> </TR> <TR> <TH ALIGN="RIGHT">Rating:</TH> <TD><select name="optRating"> <% Do While Not rs2.eof Response.Write("<option value=""" & rs2("fldRatingID") & """>" & _ rs2("fldRating") & "</option>") rs2.movenext Loop %> </select> </TD> </TR> <TR> <TH ALIGN="RIGHT">Synopsis:</TH> <TD><TEXTAREA NAME="txtSynop" COLS="40" ROWS="6"></TEXTAREA></TD> </TR> <TR> <TH ALIGN="RIGHT">Codec:</TH> <TD><select name="optCodec"> <% Do While Not rs3.eof Response.Write("<option value=""" & rs3("fldCodecID") & """>" & _ rs3("fldCodec") & "</option>") rs3.movenext Loop %> <% adoConn.close Set adoConn=Nothing Set rs=Nothing Set rs2=Nothing Set rs3=Nothing %> </select> </TD> </TR> <TR> <TH ALIGN="RIGHT">How Many CDs:</TH> <TD><INPUT TYPE="TEXT" maxLength=20 NAME="txtCDs" size="20"></TD> </TR> <TR> <TH ALIGN="RIGHT">Notes:</TH> <TD><INPUT TYPE="TEXT" maxLength=255 NAME="txtNotes" size="20"></TD> </TR> </TABLE> </center> </div> <BR><BR> <TABLE ALIGN="CENTER"> <TR> <TD align=middle COLSPAN="2"><INPUT TYPE="submit" VALUE="Add Movie" NAME="btnSubmit"><INPUT TYPE="reset" VALUE="Reset Form" NAME="btnReset"></TD> </TR> </TABLE> </FORM> </BODY> </HTML> ///////////////////////// add.asp ///////////////////////// <%@ LANGUAGE="VBSCRIPT" %> <% Option Explicit %> <!- - #include file="adovbs.inc" - -> <% 'Create an ADO connection object Set adoConn = Server.CreateObject("ADODB.Connection") 'Set an active connection to the Connection object using a DSN-less connection adoConn.Open "DSN=movies" %> <% 'Declare all relevant variables Dim moviename, genre, year, length, rating, review, codec, cds, notes, synop Dim afname, alname Dim dfname, dlname %> <% moviename = Request.form("txtMovieName") genre = Request.form("optGenre") year = Request.form("txtYearReleased") length = Request.form("txtLength") rating = Request.form("optRating") synop = Request.form("txtSynop") codec = Request.form("optCodec") cds = Request.form("txtCds") notes = Request.form("txtNotes") afname = Request.form("txtAFname") alname = Request.form("txtALname") dfname = Request.form("txtDFname") dlname = Request.form("txtDLname") %> <% Dim adoConn, rs, rs2, rs3 Const adOpenDynamic = 2 Const adLockOptimistic = 3 Const adCmdTableDirect = 512 %> <% Set rs = Server.CreateObject("ADODB.Recordset") rs.Open "tblMovies", adoConn, adOpenDynamic, adLockOptimistic, adCmdTableDirect rs.AddNew rs("fldMovieName") = moviename rs("fldGenreID") = genre rs("fldYear") = year rs("fldLength") = length rs("fldRatingID") = rating rs("fldSynopsis") = synop rs("fldCodecID") = codec rs("fldCDs") = cds rs("fldNotes") = notes rs.Update %> <% Set rs2 = Server.CreateObject("ADODB.Recordset") rs2.Open "tblActors", adoConn, adOpenDynamic, adLockOptimistic, adCmdTableDirect rs2.AddNew rs2("fldAFname") = afname rs2("fldALname") = alname rs2.Update %> <% Set rs3 = Server.CreateObject("ADODB.Recordset") rs3.Open "tblDirectors", adoConn, adOpenDynamic, adLockOptimistic, adCmdTableDirect rs3.AddNew rs3("fldDFname") = dfname rs3("fldDLname") = dlname rs3.Update %> <% rs.Close Set rs = nothing rs2.Close Set rs2 = nothing rs3.Close Set rs3 = nothing adoConn.Close Set adoConn = nothing 'Redirect to the movie.asp page Response.Redirect "redirect.asp" %> Genki Dave
DmS Paranoid (IV) Inmate From: Sthlm, Sweden Insane since: Oct 2000	posted 02-04-2004 21:19 Well, as I'm not an ASP user the syntax is rusty to say the least... But as far as I can read this you insert data in the tables: tblMovies tblActors tblDirectors But I can't see where you get the id's of the inserted data back from the inserts, if you don't know what id's the new rows in tblActors and tblMovies got you have nothing to insert in tblMovieActorJoin (which you btw never update in the code). What I believe you need to do here is to perform a "SELECT fldMovieID FROM tblMovies WHERE fldMovieID = LAST_INSERT_ID()" and "SELECT fldActorID FROM tblActors WHERE fldActorID = LAST_INSERT_ID()" directly after you performed the inserts, this to collect the id's from the last insert, then you need to insert those id's in the tblMovieActorJoin in order to establish the relationship. Quite simply, whaat you now have to do manually in the db, you need to do in your code. Now granted, I don't use ASP or Access if I can avoid it so I might be missing something here, however this is how I'd do it in PHP/MySQL and the principle should be the same. /Dan {cell 260} -{ a vibration is a movement that doesn't know which way to go }-

Topic awaiting preservation: ASP + Access DB + SQL Problem\$

Author

Thread

genkidave
Nervous Wreck (II) Inmate

From: Japan
Insane since: Nov 2003

posted 02-03-2004 10:21

OK, it's me again. I finally have my ASP pages up and running, BUT for 2 things (Bare with me here please).

1) I have 3 tables in my Acccess Database:

a) tblMovies (fldMovieID (Primary Key), fldMovieName)
b) tblActors (fldActorID (Primary Key), fldFName, fldLName)
c) tblMovieActorJoin (fldActorID, fldMovieID (Both Foreign Keys))

OK, When I add info to my database from my form on an ASP page, the Movie info
and the Actor info are inserted A.O.K, but for some reason
(even though there are realationships for the 3 tables) the tblMovieActorJoin isn't
updated with the fldActorID or the fldMovieID. In other words:

I can't see which actor was in which movies, or vice-a-versa, which movies have which actors
UNLESS I manually do it within the databse itself, but I need the database to be updated when I
add the info from the form and that would be it.

Any helpfull thoughts?

Genki Dave

DmS
Paranoid (IV) Inmate

From: Sthlm, Sweden
Insane since: Oct 2000

posted 02-03-2004 10:38

In order to help we reall need to see what code (both .asp and SQL) that is executed as you do an update and the possible error messages you might get.

Normally if something isn't inserted in the database when you expect it either the values to be inserted doesn't reach the SQL from the code, or some part of the SQL don't get executed.

Just because you have relationships between the tables doesn't mean that updates between the tables is automatically created. For that you need triggers or stored procedures which you need to do yourself. Relationships is more of an integrity check for the database.

Now for something like this you describe stored procedures or triggers shouldn't be nessecarry, just make sure that your SQL performs all inserts in all the correct tables, then make sure that the actual values from the web-page reaches the SQL so there is something to insert.
/Dan

{cell 260}
-{ a vibration is a movement that doesn't know which way to go }-

genkidave
Nervous Wreck (II) Inmate

From: Japan
Insane since: Nov 2003

posted 02-04-2004 08:05

Thanks for replying DmS and for the offer to help out. I think it's a NULL problem I'm dealing with, but not sure.
Here is the code for BOTH the movieform.asp and the add.asp

///////////////////////// movieform.asp /////////////////////////

<%@ LANGUAGE="VBSCRIPT" %>

<% Option Explicit %>

<!- - #include file="adovbs.inc" - ->

<%
'declare our variables
Dim adoConn,sql,sql2,sql3,rs,rs2,rs3 %>

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

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

<% sql="SELECT * FROM tblGenres ORDER BY fldGenreID" %>
<% sql2="SELECT * FROM tblRatings ORDER BY fldRatingID" %>
<% sql3="SELECT * FROM tblCodecs ORDER BY fldCodecID" %>

<% Set rs=adoConn.execute(sql) %>
<% Set rs2=adoConn.execute(sql2) %>
<% Set rs3=adoConn.execute(sql3) %>

<HTML>
<HEAD><TITLE>Genki Dave's Movie Collection - Add Movie</TITLE></HEAD>
<BODY text="#FFFF00" bgcolor="#800000">
<BR>
<CENTER>
<H1><font face="Japan"><U>Please Fill In ALL Data Fields</U></font></H1>
<BR>
<FORM NAME="frmAddMovie" ACTION="add.asp" METHOD="POST">

<TABLE border="0" style="border-collapse: collapse" cellpadding="0" cellspacing="0">
<TR>
<TH ALIGN="RIGHT">Movie Name:</TH>
<TD><INPUT TYPE="TEXT" maxLength=20 NAME="txtMovieName" size="20"></TD>
</TR>
</TR>
<TR>
<TH ALIGN="RIGHT">Genre:</TH>
<TD><select name="optGenre">
<%
Do While Not rs.eof
Response.Write("<option value=""" & rs("fldGenreID") & """>" & _
rs("fldGenre") & "</option>")
rs.movenext
Loop
%>
</select>
</TD>
</TR>
<TR>
<TH ALIGN="RIGHT">Director's First Name:</TH>
<TD><INPUT TYPE="TEXT" maxLength=30 NAME="txtDFname" size="20"></TD>
</TR>
<TR>
<TH ALIGN="RIGHT">Director's Last Name:</TH>
<TD><INPUT TYPE="TEXT" maxLength=30 NAME="txtDLname" size="20"></TD>
</TR>
<TR>
<TH ALIGN="RIGHT">Year Released:</TH>
<TD><INPUT TYPE="TEXT" maxLength=20 NAME="txtYearReleased" size="20"></TD>
</TR>
<TR>
<TH ALIGN="RIGHT">Length:</TH>
<TD><INPUT TYPE="TEXT" maxLength=20 NAME="txtLength" size="20"></TD>
</TR>
<TR>
<TH ALIGN="RIGHT">Actor's First Name:</TH>
<TD><INPUT TYPE="TEXT" maxLength=20 NAME="txtAFname" size="20"></TD>
</TR>
<TR>
<TH ALIGN="RIGHT">Actor's Last Name:</TH>
<TD><INPUT TYPE="TEXT" maxLength=20 NAME="txtALname" size="20"></TD>
</TR>
<TR>
<TH ALIGN="RIGHT">Rating:</TH>
<TD><select name="optRating">
<%
Do While Not rs2.eof
Response.Write("<option value=""" & rs2("fldRatingID") & """>" & _
rs2("fldRating") & "</option>")
rs2.movenext
Loop
%>
</select>
</TD>
</TR>
<TR>
<TH ALIGN="RIGHT">Synopsis:</TH>
<TD><TEXTAREA NAME="txtSynop" COLS="40" ROWS="6"></TEXTAREA></TD>
</TR>
<TR>
<TH ALIGN="RIGHT">Codec:</TH>
<TD><select name="optCodec">
<%
Do While Not rs3.eof
Response.Write("<option value=""" & rs3("fldCodecID") & """>" & _
rs3("fldCodec") & "</option>")
rs3.movenext
Loop
%>

<%
adoConn.close
Set adoConn=Nothing

Set rs=Nothing
Set rs2=Nothing
Set rs3=Nothing
%>
</select>
</TD>
</TR>
<TR>
<TH ALIGN="RIGHT">How Many CDs:</TH>
<TD><INPUT TYPE="TEXT" maxLength=20 NAME="txtCDs" size="20"></TD>
</TR>
<TR>
<TH ALIGN="RIGHT">Notes:</TH>
<TD><INPUT TYPE="TEXT" maxLength=255 NAME="txtNotes" size="20"></TD>
</TR>
</TABLE>
</center>
</div>
<BR><BR>
<TABLE ALIGN="CENTER">
<TR>
<TD align=middle COLSPAN="2"><INPUT TYPE="submit" VALUE="Add Movie" NAME="btnSubmit"><INPUT TYPE="reset" VALUE="Reset Form" NAME="btnReset"></TD>
</TR>
</TABLE>
</FORM>
</BODY>
</HTML>

///////////////////////// add.asp /////////////////////////

<%@ LANGUAGE="VBSCRIPT" %>

<% Option Explicit %>

<!- - #include file="adovbs.inc" - ->

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

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

<%
'Declare all relevant variables
Dim moviename, genre, year, length, rating, review, codec, cds, notes, synop
Dim afname, alname
Dim dfname, dlname
%>

<%
moviename = Request.form("txtMovieName")
genre = Request.form("optGenre")
year = Request.form("txtYearReleased")
length = Request.form("txtLength")
rating = Request.form("optRating")
synop = Request.form("txtSynop")
codec = Request.form("optCodec")
cds = Request.form("txtCds")
notes = Request.form("txtNotes")
afname = Request.form("txtAFname")
alname = Request.form("txtALname")
dfname = Request.form("txtDFname")
dlname = Request.form("txtDLname")
%>

<%
Dim adoConn, rs, rs2, rs3
Const adOpenDynamic = 2
Const adLockOptimistic = 3
Const adCmdTableDirect = 512
%>

<%
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "tblMovies", adoConn, adOpenDynamic, adLockOptimistic, adCmdTableDirect
rs.AddNew
rs("fldMovieName") = moviename
rs("fldGenreID") = genre
rs("fldYear") = year
rs("fldLength") = length
rs("fldRatingID") = rating
rs("fldSynopsis") = synop
rs("fldCodecID") = codec
rs("fldCDs") = cds
rs("fldNotes") = notes
rs.Update
%>

<%
Set rs2 = Server.CreateObject("ADODB.Recordset")
rs2.Open "tblActors", adoConn, adOpenDynamic, adLockOptimistic, adCmdTableDirect
rs2.AddNew
rs2("fldAFname") = afname
rs2("fldALname") = alname
rs2.Update
%>

<%
Set rs3 = Server.CreateObject("ADODB.Recordset")
rs3.Open "tblDirectors", adoConn, adOpenDynamic, adLockOptimistic, adCmdTableDirect
rs3.AddNew
rs3("fldDFname") = dfname
rs3("fldDLname") = dlname
rs3.Update
%>

<%
rs.Close
Set rs = nothing
rs2.Close
Set rs2 = nothing
rs3.Close
Set rs3 = nothing

adoConn.Close
Set adoConn = nothing

'Redirect to the movie.asp page
Response.Redirect "redirect.asp"
%>

Genki Dave

DmS
Paranoid (IV) Inmate

From: Sthlm, Sweden
Insane since: Oct 2000

posted 02-04-2004 21:19

Well, as I'm not an ASP user the syntax is rusty to say the least...
But as far as I can read this you insert data in the tables:

tblMovies
tblActors
tblDirectors

But I can't see where you get the id's of the inserted data back from the inserts,
if you don't know what id's the new rows in tblActors and tblMovies got you have nothing to insert in tblMovieActorJoin (which you btw never update in the code).

What I believe you need to do here is to perform a "SELECT fldMovieID FROM tblMovies WHERE fldMovieID = LAST_INSERT_ID()" and "SELECT fldActorID FROM tblActors WHERE fldActorID = LAST_INSERT_ID()" directly after you performed the inserts, this to collect the id's from the last insert, then you need to insert those id's in the tblMovieActorJoin in order to establish the relationship.

Quite simply, whaat you now have to do manually in the db, you need to do in your code.

Now granted, I don't use ASP or Access if I can avoid it so I might be missing something here, however this is how I'd do it in PHP/MySQL and the principle should be the same.
/Dan

{cell 260}
-{ a vibration is a movement that doesn't know which way to go }-