http://qs1969.pair.com?node_id=76558

fpw138 has asked for the wisdom of the Perl Monks concerning the following question:

I'm new in programming. I'm looking for the steps or ways to put visitors' name, company and email address information into MS Access database once they hit the form submit button . Do I have to write CGI program and set up DBI/DBD module? Can anyone give me some hints where I can find some example or coding?
  • Comment on What are the steps to put the form data into MS Access using Perl CGI?

Replies are listed 'Best First'.
Re: What are the steps to put the form data into MS Access using Perl CGI?
by olly (Scribe) on Apr 30, 2001 at 14:20 UTC
    Hi,

    If you are new to coding then you are really diving in head forward, but hey if you get it done you will feel even better. You will have to make the form ofcourse and could then make a CGI script in perl in which you parse the form using CGI.pm for example (for more information about it type perldoc cgi) Then you have to contact the the database to store the information there is another node about it here. If you need more help I suggest you search around a litle because there is alot of information in already given answers.

    Imagination is more important than knowledge -Einstein-

[reply]
Re: What are the steps to put the form data into MS Access using Perl CGI?
by InfiniteSilence (Curate) on Apr 30, 2001 at 18:58 UTC
    Here's a working example using Win32::ODBC using a generic SystemDSN called LIMS2. You can read the perldoc (type perldoc Win32::ODBC on your command line) for a further explanation of the functions. 
    #!/usr/local/bin/perl -w
use strict;
use Win32::ODBC;
use CGI qw/:standard -debug/;

my $data = new Win32::ODBC("LIMS2") or die ODBC::Error();
if (!defined($data))
{
        print "Unable to connect to LIMS DSN!";
        exit;
}
my $IP = "unknown";
if (defined($ENV{'REMOTE_ADDR'}))
{
        $IP = $ENV{'REMOTE_ADDR'};
        
}
my $sug = param('suggestion');
my $sqlstatement = qq(Insert into tblSuggestionBox (suggestion, IPAddr
+ess,TimeOfSuggestion) VALUES (\'$sug\',\'$IP\',Now()););

print "$sqlstatement\n";

$data->Sql($sqlstatement);


if(defined($data->Error()))
{
        print $data->Error();
}
$data->Close();

my $data2 = new Win32::ODBC('LIMS2')||die ODBC::Error();
$data2->Sql("Select * from tblSuggestionBox");
print header, start_html, h1('Suggestions');
print "<table border\=\"1\">";
        print Tr(td('Suggestion'), td('IPAddress'), td('Timestamp'));
while ($data2->FetchRow())
{
        my %Ihash = $data2->DataHash();
        print "<TR><TD width\=\"33%\">$Ihash{'suggestion'}</TD><TD wid
+th\=\"33%\">$Ihash{'IPAddress'}</TD><TD width\=\"33%\">$Ihash{'TimeOf
+Suggestion'}</TD></TR>\n";

}
print "</table>";
print end_html;
1;

    [download]
    For those of you who notice that I used CGI and then manually added table tags, I seemed to have a problem with the way CGI handles manually defined tags on different systems. This method is thus more portable for me.

    Celebrate Intellectual Diversity

[reply]
[d/l]
Re: What are the steps to put the form data into MS Access using Perl CGI?
by SageMusings (Beadle) on May 01, 2001 at 00:20 UTC
    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]
[reply]
[d/l]
Re: What are the steps to put the form data into MS Access using Perl CGI?
by olly (Scribe) on Apr 30, 2001 at 15:04 UTC
    Hi,

    If you are new to coding then you are really diving in head forward, but hey if you get it done you will feel even better. You will have to make the form ofcourse and could then make a CGI script in perl in which you parse the form using CGI.pm for example (for more information about it type perldoc cgi) Then you have to contact the the database to store the information there is another node about it here. If you need more help I suggest you search around a litle because there is alot of information in already given awnsors.

    Imagination is more important then knowledge -Einstein-

    Originally posted as a Categorized Answer.

[reply]

Back to Seekers of Perl Wisdom