Re: What are the steps to put the form data into MS Access using Perl CGI?

First, I assume you know how to build a form in a static or dynamic HTML'd page. The only tools you need are the CGI and the Win32::ODBC module. The first will parse GET and POST strings with comic ease. The ODBC module will link to a ODBC data source name you specifiy on your system. For help with that look in the Control Panel under ODBC sources. Now take the fields passed from the form and mix them into SQL statements. The code below will provide you with an example. This code is part of a much larger set of scripts and HTML pages, but it can guide you. I wrote it in just over an hour. Do not beat me up over style and error checking. It serves its purpose in a controlled environment.

#!/usr/bin/perl

# ****S4.pl****
# 4/26/01 1:31PM
# This program is called from (RRS_validate.htm).  It first checks the
# the validity of the password passed by the source HTML document.  Ne
+xt,
# a table is constructed, showing current service submissions.  Each t
+able 
# has a reference key assigned from the database which is used as part
+ of a link
# and data parameter.  This information is used by the ( ) program to 
+change the 
# status of a requisition

use CGI qw/:standard/;
use Win32::ODBC;

print header, start_html('S-4 RRS Management'),
h1('<BODY BACKGROUND="/ricebk.jpg"></BODY>');

if (param())
{
    my $response = param('ACTION');
    my $record = param('REQ');
    my $valid = 0;
    if ($response eq "NONE")
    {
        $valid = validate(param('PASSWORD'));
        if ($valid == 0)
        {
            print h1('<center>Invalid Password!</center>');
            die ("Aborted");
        }
    }
    display_intro();
    handle_response($response, $record);
    generate_table();
}

# validate the password passed to the program
sub validate
{
    my $compare = shift;
    if ($compare eq "s4admin")
    {
        return(1);
    }
    else
    {
        return(0);
    }
}
    
# display information table to user
sub generate_table    
{
    print "<CENTER><TABLE BORDER>";
    print "<TD><B>UNIT<TD><B>ID#<TD><B>TYPE<TD><B>QTY<TD><B>UNITS<TD><
+B>PURPOSE<TD><B>REPORT DATE<TD><B>REPORT TIME<TD><B>PENDING<TD><B>APP
+ROVED<TD><B>CANCEL<TD>";
    my ($database) = new Win32::ODBC('RRS');
    $database->Sql("SELECT * FROM REQUESTS");
    while ($database->FetchRow())
    {
        my (%data) = $database->DataHash();
        my $id = $data{'ID'};
        print "<TR><TD>$data{'UNIT'}";
        # Here docs (EOT) must be terminated without tabs, hence the i
+dentention error!
        print <<"EOT";
        <TD><A HREF="/cgi-bin/detail.pl/?RECORD=$id">$id</A>
EOT
        print "<TD>$data{'TYPE'}";
        print "<TD>$data{'QTY'}";
        print "<TD>$data{'UNITS'}";
        print "<TD>$data{'PURPOSE'}";
        print "<TD>$data{'REPORTDATE'}";
        print "<TD>$data{'REPORTTIME'}";
        print "<TD>$data{'PENDING'}";
        print "<TD>$data{'APPROVED'}";
        print "<TD>$data{'CANCEL'}";
    }
    print "</TABLE></CENTER>";
    $database->Close();
}

sub display_intro
{
    print h1('<center>S-4 Management Screen for the RRS</center>'),
    hr, h3('<center>To manipulate a request, click on the link provide
+d in the table below.');
}

sub handle_response
{
    my $response = shift;
    my $record = shift;
    if ($response eq "APPROVE")
    {
        approve($record);
        return;
    }
    elsif ($response eq "PEND")
    {
        pend($record);
        return;
    }
    elsif ($response eq "REMOVE")
    {
        remove($record);
        return;
    }
    else
    {
        cancel($record);
        return;
    }
}

sub approve
{
    my $record = shift;
    my ($database) = new Win32::ODBC('RRS');
    if ($database->Sql("UPDATE REQUESTS SET APPROVED='TRUE', CANCEL='F
+ALSE', PENDING='FALSE' WHERE ID=$record"))
    {
        print "something went wrong " . $database->Error();
    }
    $database->Close();
    return;
}

sub pend
{
    my $record = shift;
    my ($database) = new Win32::ODBC('RRS');
    $database->Sql("UPDATE REQUESTS SET CANCEL = 'FALSE', PENDING = 'T
+RUE', APPROVED = 'FALSE' WHERE ID = $record");
    $database->Close();
    return;
}

sub cancel
{
    my $record = shift;
    my ($database) = new Win32::ODBC('RRS');
    $database->Sql("UPDATE REQUESTS SET CANCEL = 'TRUE', PENDING = 'FA
+LSE', APPROVED = 'FALSE' WHERE ID = $record");
    $database->Close();
    return;
}

sub remove
{
    my $record = shift;
    my ($database) = new Win32::ODBC('RRS');
    $database->Sql("DELETE FROM REQUESTS WHERE ID = $record");
    $database->Close();
    return;
}
[download]

Comment on Re: What are the steps to put the form data into MS Access using Perl CGI? Download Code