Hey, I'm a three-week newbie myself, and this is the same thing I was
working on!
My example is an insert program, cross-referencing products (games,
actually), with three attribute tables: genre (horror, sci-fi, etc.),
category (board game, card game, etc.), and mechanics (card melding, dice
rolling, etc.).
Part 1 was to collect/select the data (including multiple genres,
categories, and mechanics), and to choose whether to commit or dispose of
the data.
Part 2 was to perform the insert.
Here are the scripts - keep in mind that they were written by an egg...
ProdAdd1: data entry
------------------------------
#!/localhost/perl/bin
# -*- Perl -*-
#
# DiceTribe Games Database
#
# Product Entry - Page 1
#
use strict;
use warnings;
use lib "C:/perl/site/lib/Win32";
use CGI;
use Win32::ODBC;
my $debugflag = 0;
#
# Instantiate CGI and parse form data
#
my $cgi = new CGI;
my $dsn = $cgi->param('DSN');
my $ODBCString = "DSN=".$dsn.";";
print "Content-type: text/html\n\n";
#
# Product-related data
#
my $ProdName;
my $ProdSubtitle;
my $ProdDesc;
my $OurComment;
my $ProdManuID;
my $UnitPrice;
my $NumPlayers;
my $Duration;
my @PassGenre;
my @PassCat;
my @PassMech;
#
# Query strings
#
my $qryGenreStm = "SELECT GenreID, GenreName FROM Genre ORDER BY GenreID";
my $qryCatStm = "SELECT CategoryID, CategoryName FROM Categories ORDER BY
CategoryID";
my $qryMechStm = "SELECT MechanicID, Mechanic FROM Mechanic ORDER BY
MechanicID";
#
# here-doc syntax for upper section of HTML
#
print <<EOF;
<HTML>
<HEAD>
<TITLE>Product Entry Page</TITLE></HEAD>
<BODY BGCOLOR="Black"
BACKGROUND="/bkgrnd/purplemesh.gif"
LINK="#000080" VLINK="#660099" TEXT="#000000">
<H1
STYLE="width:600;color:purple;font-size:32pt;filter:Glow(color=violet,streng
th=8)">
<FONT FACE=Chiller>Product Entry Page</FONT>
</H1>
<HR>
EOF
#
# issue a SELECT statement to pull up all Manufacturers
#
my $db = new Win32::ODBC($ODBCString);
my $qryStatement="SELECT ManufacturerID, ManufacturerName FROM Manufacturer
ORDER BY ManufacturerName";
if ($db->sql($qryStatement)){
my $debacle = "<H3>".$db->error."</H3>";
print $debacle;
print $cgi->end_html;
exit;
}
#
# here-doc syntax for entry form 1: header information.
#
print <<EOF;
<BR>
<FORM ACTION="ProdAdd2.pl" METHOD="POST">
<TABLE CELLPADDING=5>
<TR>
<TD VALIGN=TOP>
<FONT COLOR="violet"><STRONG>Product Name
:</STRONG></FONT><BR>
<INPUT type="text" name="ProdName" maxlength="50"><BR>
<FONT COLOR="violet"><STRONG>Product Subtitle
:</STRONG></FONT><BR>
<INPUT type="text" name="ProdSubtitle"
maxlength="50"><BR>
<FONT COLOR="violet"><STRONG>Select Manufacturer
:</STRONG></FONT><BR>
<SELECT name="ProdManuID"><BR>
EOF
#
# Add Manufacturers to the form select list
#
while ($db->FetchRow) {
my %Data;
undef %Data;
%Data = $db->DataHash();
my $ManufacturerName = $db->data('ManufacturerName');
my $ManuID = $db->data('ManufacturerID');
my $OutputString=" <OPTION
VALUE=".$ManuID.">".$ManufacturerName."<BR>\n";
print $OutputString;
}
print <<EOF;
</SELECT><P>
</TD>
<TD VALIGN=TOP>
<FONT COLOR="violet"><STRONG>Product Description
:</STRONG></FONT><BR>
<textarea name="ProdDesc" rows=9 cols=28></textarea>
</TD>
<TD VALIGN=TOP>
<FONT COLOR="violet"><STRONG>Copyright Year
:</STRONG></FONT><BR>
<INPUT type="text" name="Copyright" maxlength="20"><BR>
<FONT COLOR="violet"><STRONG>List Price
:</STRONG></FONT><BR>
<INPUT type="text" name="UnitPrice" maxlength="10"><BR>
<FONT COLOR="violet"><STRONG># of Players
:</STRONG></FONT><BR>
<INPUT type="text" name="NumPlayers" maxlength="15"><BR>
<FONT COLOR="violet"><STRONG>Duration
:</STRONG></FONT><BR>
<INPUT type="text" name="Duration" maxlength="15">
</TD>
</TR>
</TABLE>
<TABLE CELLPADDING=5>
<TR>
<TD WIDTH="25%"><FONT COLOR="violet"><STRONG>DiceTribe
Comments</STRONG></FONT></TD>
<TD WIDTH="75%"><textarea name="OurComments" rows=2
cols=60></textarea></TD>
</TR>
</TABLE>
<TABLE WIDTH="99%" CELLPADDING=5>
<TR>
<TD WIDTH="33%"><FONT COLOR="violet"><STRONG>Select
Genres</STRONG></FONT></TD>
<TD WIDTH="33%"><FONT COLOR="violet"><STRONG>Select
Categories</STRONG></FONT></TD>
<TD WIDTH="33%"><FONT COLOR="violet"><STRONG>Select
Mechanics</STRONG></FONT></TD>
</TR>
<TR>
<TD VALIGN="TOP"><SELECT NAME="ProdGenre" SIZE=4 MULTIPLE>
EOF
#
# Close database
#
$db->Close();
#
# issue a SELECT statement to populate product genre information
#
my $qryGenre = new Win32::ODBC($ODBCString);
if ($qryGenre->sql($qryGenreStm)) {
print "</TD></TR></TABLE><P>";
my $debacle = "<H3>".$qryGenreStm."</H3>".$qryGenre->error;
print $debacle;
print $cgi->end_html;
exit;
}
#
# Add Genres to the form select list
my $GenreCount=0;
while ($qryGenre->FetchRow) {
my %Data;
undef %Data;
%Data = $qryGenre->DataHash();
my $GenreID = $qryGenre->data('GenreID');
my $GenreName = $qryGenre->data('GenreName');
$PassGenre[$GenreCount]=$GenreID;
$GenreCount++;
my $OutputString =" <OPTION
VALUE=".$GenreID.">".$GenreName."<BR>\n";
print $OutputString;
}
$qryGenre->Close();
#
# Close Genre, open Categories...
print <<EOF;
</SELECT>
</TD>
<TD VALIGN="TOP"><SELECT name="ProdCat" SIZE=4 MULTIPLE><P>
EOF
#
# issue a SELECT statement to populate product category information
# in an HTML select construct.
#
my $qryCat = new Win32::ODBC($ODBCString);
if ($qryCat->sql($qryCatStm)){
my $debacle = "<H3>".$qryCatStm."</H3>".$qryCat->error;
print $debacle;
print $cgi->end_html;
exit;
}
#
# Add Categories to the form select list
while ($qryCat->FetchRow) {
my %Data;
my $CatCount=0;
undef %Data;
%Data = $qryCat->DataHash();
my $CategoryID = $qryCat->data('CategoryID');
my $CategoryName= $qryCat->data('CategoryName');
$PassCat[$CatCount]=$CategoryID;
$CatCount++;
my $OutputString =" <OPTION
VALUE=".$CategoryID.">".$CategoryName."<BR>\n";
print $OutputString;
}
$qryCat->Close();
#
# Close Categories, open Mechanics...
print <<EOF;
</SELECT>
</TD>
<TD VALIGN="TOP"><SELECT NAME="ProdMech" SIZE=4 MULTIPLE><P>
EOF
#
# issue a SELECT statement to populate product mechanics information
#
my $qryMech = new Win32::ODBC($ODBCString);
if ($qryMech->sql($qryMechStm)){
my $debacle = "<H3>".$qryMechStm."</H3>".$qryMech->error;
print $debacle;
print $cgi->end_html;
exit;
}
#
# Add Mechanics to the form select list
while ($qryMech->FetchRow) {
my %Data;
my $MechCount=0;
undef %Data;
%Data = $qryMech->DataHash();
my $MechanicID= $qryMech->data('MechanicID');
my $Mechanic = $qryMech->data('Mechanic');
$PassMech[$MechCount]=$MechanicID;
$MechCount++;
my $OutputString =" <OPTION
VALUE=".$MechanicID.">".$Mechanic."<BR>\n";
print $OutputString;
}
$qryMech->Close();
#
# Close form
#
my $h_dsn = "<INPUT TYPE=\"hidden\" NAME=\"DSN\"
VALUE=\"".$dsn."\">";
print <<EOF;
</SELECT>
</TD>
</TR>
</TABLE>
<P>
$h_dsn
<INPUT TYPE="submit" VALUE="Insert Record">
<INPUT TYPE="reset" VALUE="Reset Form">
</FORM>
</BODY>
</HTML>
EOF
------------------------------
ProdAdd2: insert
------------------------------
#!/localhost/perl/bin
# -*- Perl -*-
#
# DiceTribe Games Database
#
# Product Entry - Database Insert
#
use strict;
use warnings;
use lib "C:/perl/site/lib/Win32";
use CGI;
use Win32::ODBC;
my $debugflag=0;
#
# Instantiate CGI and parse form data
#
my $cgi = new CGI;
my $dsn = $cgi->param('DSN');
my $ODBCString = "DSN=".$dsn.";";
#
# Extract product parameters
#
my $ProdName = $cgi->param('ProdName');
my $ProdSubtitle = $cgi->param('ProdSubtitle');
if (!$ProdSubtitle) {
$ProdSubtitle = "..."
}
my $ProdDesc = $cgi->param('ProdDesc');
if (!$ProdDesc) {
$ProdDesc = "..."
}
my $OurComment = $cgi->param('OurComment');
if (!$OurComment) {
$OurComment = "..."
}
my $ProdManuID = $cgi->param('ProdManuID');
my $Copyright = $cgi->param('Copyright');
my $UnitPrice = $cgi->param('UnitPrice');
my $NumPlayers = $cgi->param('NumPlayers');
my $Duration = $cgi->param('Duration');
my $ReviewID = "no review";
my $ProductID;
my @ProdGenre;
my @ProdCat;
my @ProdMech;
my $tempstr;
my $iter;
#
# here-doc syntax for upper section of HTML
#
print <<EOF;
<HTML>
<HEAD><TITLE>Product Entry - Insert Into Database</TITLE></HEAD>
<BODY BGCOLOR="Black" LINK="#000080" VLINK="#660099" TEXT="red">
<H1>Product Entry - Insert Into Database</H1>
<HR>
EOF
$iter=0;
foreach $tempstr ($cgi->param('ProdGenre')) {
$ProdGenre[$iter]=$tempstr;
$iter++;
}
$iter=0;
foreach $tempstr ($cgi->param('ProdCat')) {
$ProdCat[$iter]=$tempstr;
$iter++;
}
$iter=0;
foreach $tempstr ($cgi->param('ProdMech')) {
$ProdMech[$iter]=$tempstr;
$iter++;
}
my $SelProd = new Win32::ODBC($ODBCString);
my $SelProdStm = "SELECT MAX(ProductID) FROM Products";
if ($debugflag) {
print $SelProdStm."<BR>";
}
else {
if ($SelProd->sql($SelProdStm)){
my $debacle="<H3>".$SelProdStm."</H3>";
print $debacle;
print $SelProd->error;
print $cgi->end_html;
exit;
}
if ($SelProd->FetchRow) {
my %Data;
undef %Data;
%Data = $SelProd->DataHash();
my $item;
my $value;
while (($item,$value) = each(%Data)){
$ProductID = $value;
}
}
$ProductID++;
}
print "Ready to INSERT<BR>";
my $InsProd = new Win32::ODBC($ODBCString);
my $InsProdStm = "INSERT INTO Products VALUES
(".$ProductID.",\'".$ProdName."\',\'".
$ProdSubtitle."\',\'".$ProdDesc."\',\'".$OurComment."\',".
$ProdManuID.",".$Copyright.",".
$UnitPrice.",\'".$ReviewID."\',\'".$NumPlayers."\',\'".$Duration."\')";
if ($debugflag) {
print $InsProdStm."<BR>";
}
else {
if ($InsProd->sql($InsProdStm)){
my $debacle="<H3>".$InsProdStm."</H3>";
print $debacle;
print $InsProd->error;
print $cgi->end_html;
exit;
}
}
print "<H3>Product ".$ProdName." inserted as product number
".$ProductID."</H3><P>";
my $InsGenre = new Win32::ODBC($ODBCString);
my $InsGenreStm = "INSERT INTO ProductGenre VALUES (".$ProductID.",";
my $genre;
foreach $genre (@ProdGenre) {
my $qry = $InsGenreStm.$genre.")";
if ($debugflag) {
print $qry."<BR>";
}
else {
print $qry."<BR>";
if ($InsGenre->sql($qry)) {
my $debacle = "<H3>".$qry."</H3>";
print $debacle;
print $InsGenre->error;
print $cgi->end_html;
last;
}
}
}
my $InsCat = new Win32::ODBC($ODBCString);
my $InsCatStm = "INSERT INTO ProductCategory VALUES
(".$ProductID.",";
my $category;
foreach $category (@ProdCat) {
my $qry = $InsCatStm.$category.")";
if ($debugflag) {
print $qry."<BR>";
}
else {
print $qry."<BR>";
if ($InsCat->sql($qry)) {
my $debacle = "<H3>".$qry."</H3>";
print $debacle;
print $InsCat->error;
print $cgi->end_html;
last;
}
}
}
my $InsMech = new Win32::ODBC($ODBCString);
my $InsMechStm = "INSERT INTO ProductMechanic VALUES
(".$ProductID.",";
my $mechanic;
foreach $mechanic (@ProdMech) {
my $qry = $InsMechStm.$mechanic.")";
if ($debugflag) {
print $qry."<BR>";
}
else {
print $qry."<BR>";
if ($InsMech->sql($qry)) {
my $debacle = "<H3>".$qry."</H3>";
print $debacle;
print $InsMech->error;
print $cgi->end_html;
last;
}
}
}
if (!$debugflag){
close $SelProd;
close $InsProd;
close $InsGenre;
close $InsCat;
close $InsMech;
}
my $h_dsn = "<INPUT TYPE=\"hidden\" NAME=\"DSN\"
VALUE=\"".$dsn."\">";
#
# here-doc syntax for entry form 1: header information.
#
print <<EOF;
<BR>
<FORM ACTION="ProdAdd1.pl" METHOD="POST">
$h_dsn;
<INPUT type="submit" VALUE="Back">
</FORM>
</BODY>
</HTML>
EOF
------------------------------
> -----Original Message-----
> From: SAWMaster [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, June 06, 2001 12:42 PM
> To: [EMAIL PROTECTED]
> Subject: MS Access database manipulation
>
>
> Hello group, I've successfully set up a page on my server
> that allows me to make custom querys to my database, and
> display the results on a html page. Now I would like to make
> another page that would allow me to add new records etc to
> the database directly from the web, instead of only being
> able to locally launch Access, and editing it from there.
>
> I'm a complete newbie, I didn't know what Perl even was 2
> weeks ago, but I'm moving along nicely, and having a blast
> learning this stuff.
>
