Hi,

I hope someone can help me with this. I am new to Perl and I am trying to use form values "checkboxes", to query an access database.

I am having trouble getting the sql statement to use the form values in the query. It passes the values from the form to the perl script but then throws up an error.

Bad name after on' at.........drive2.pl line 34.

This is the script I am "trying" to use to parse the form values and query the database (without much success I might add!)

#!c:/perl/bin/perl.exe -w

use strict;
use DBI;
use CGI;

#open connection to Access database
my $dbh = DBI->connect("dbi:ODBC:directory",
{ 'AutoCommit' =>1, 'RaiseError' =>1}) ||

die "Error connecting: '$DBI::errstr'";

# setup CGI handle
my $cgi = new CGI;

# start HTML

print $cgi->header . $cgi->start_html('Drive');

my $hash ={ Initiative=>'DriveInitiative',
ResOrientation => 'DriveResOrient',
Creativity => 'DriveCreativity',
ChangeOrientation => 'DriveChangeOrient',
DecisionMaking => 'DriveDecisionMake',
SelectLevel => 'ResLevel',
ManagesPeople => 'ResManage'};

my @clauses = ();
foreach my $checkbox (keys %$hash) {
push @clauses, $hash->{$checkbox} if validate($cgi->param($checkbox));
}


my $where_clause = join(' and ',map($_.'= 'on', @clauses));

my $sql = "select a.ResType, a.ResLevel, a.ResManage
a.Details, a.Length, a.Source, a.Cost, a.FurtherDetails
from Resources a
INNER JOIN ResourceSettings b ON a.ResID = b.ResID";
$sql .= " where $where_clause " if ($where_clause);
$sql .= " order by a.ResType, a.ResLevel, a.ResManage,
a.Details, a.Length, a.Source, a.Cost, a.FurtherDetails";


$sth->execute ||
die "Could not execute SQL statement ... " . $dbh->errstr;


my $rows = $dbh->selectall_arrayref($sql) || die $dbh->errstr;
if (@$rows) {
print "<table border=1 cellspacing=0 cellpadding=3><tr>" .
"<th>Type</th><th>Level</th><th>Manage</th><th>Details</th><th>Length</th><th>Source</th><th>Cost</th><th>Further Details</th></tr>";
foreach my $row (@$rows) {
print "<tr><td>" . join ("</td><td>", @$row) . "</td></tr>\n";
}
print "</table>\n";
}
else {
print "<p><i>No matches found</i></p>\n";
}
}


# disconnect from database
$dbh->disconnect();
exit(0);

# validate user input
sub validate {
my $string = shift;
# get rid of all non-letter, non-numerical characters and percents
$string =~ s/[^A-Za-z0-9%]//g;
return $string;
}

Look at that line, you have an extra '

Ok, thanks, that got rid of that error but now I get a blank screen with no errors at all. Any ideas?

try creating the statement handle before executing.
if not .. print out the sql statement and check it. if that looks ok, sub in a simple statement like Select ResType from Resources; (if it's not too large of course) and try to get output from that.
----------------------
my $sql = "select a.ResType, a.ResLevel, a.ResManage
a.Details, a.Length, a.Source, a.Cost, a.FurtherDetails
from Resources a
INNER JOIN ResourceSettings b ON a.ResID = b.ResID";
$sql .= " where $where_clause " if ($where_clause);
$sql .= " order by a.ResType, a.ResLevel, a.ResManage,
a.Details, a.Length, a.Source, a.Cost, a.FurtherDetails";


$sth->execute ||
die "Could not execute SQL statement ... " . $dbh->errstr;


my $rows = $dbh->selectall_arrayref($sql) || die $dbh->errstr;
if (@$rows) {
print "<table border=1 cellspacing=0 cellpadding=3><tr>" .
"<th>Type</th><th>Level</th><th>Manage</th><th>Details</th><th>Length</th><th>Source</th><th>Cost</th><th>Further Details</th></tr>";
foreach my $row (@$rows) {
print "<tr><td>" . join ("</td><td>", @$row) . "</td></tr>\n";
}
print "</table>\n";
}
else {
print "<p><i>No matches found</i></p>\n";
}
}


# disconnect from database
$dbh->disconnect();
exit(0);

# validate user input
sub validate {
my $string = shift;
# get rid of all non-letter, non-numerical characters and percents
$string =~ s/[^A-Za-z0-9%]//g;
return $string;
}[/reply]

 

The html output is fine. I tried this in conjunction with a more simple query, without the form values and that works fine. I also printed out the select which seem to be accepting the form values (I had already tried these). I think it is a problem with the way access interprets my query. I have some revamped code.

HTML form

--------------------------------------------------------------------


<HTML><HEAD>
<BODY>
<FORM action=http://intranet.mycompany.co.uk/cgi-bin/personnel/drive2.pl
method=get>
<H3>Select Drive Elements</H3>
<P><INPUT type=checkbox value=Initiative name=drive>Initiative</P>
<P><INPUT type=checkbox value=ResOrientation name=drive>Results Orientation</P>
<P><INPUT type=checkbox value=Creativity name=drive>Creativity</P>
<P><INPUT type=checkbox value=ChangeOrientation name=drive>Change Orientation
</P>
<P><INPUT type=checkbox value=DecisionMaking name=drive>Decision
Making</P>Select Level: <SELECT name=SelectLevel> <OPTION value=Experienced
selected>Experienced<OPTION value=Introduction>Introduction</OPTION></SELECT>
Manages People: <SELECT name=ManagesPeople> <OPTION value=Yes
selected>Yes<OPTION value=No>No</OPTION></SELECT>
<P><INPUT type=submit value=View name=Query></P></FORM></BODY></HTML>

Perl script to process it

--------------------------------------------------------------------

#!c:/perl/bin/perl.exe -w

use strict;
use DBI;
use CGI;
#use CGI::Carp qw( fatalsToBrowser warningsToBrowser );


#open connection to Access database
my $dbh = DBI->connect("dbi:ODBC:directory",
{ 'AutoCommit' =>1, 'RaiseError' =>1}) ||

die "Error connecting: '$DBI::errstr'";

# setup CGI handle
my $cgi = new CGI;

# start HTML

print $cgi->header . $cgi->start_html('Drive');

my $hash ={ Initiative=>'DriveInitiative',
ResOrientation => 'DriveResOrient',
Creativity => 'DriveCreativity',
ChangeOrientation => 'DriveChangeOrient',
DecisionMaking => 'DriveDecisionMake',
SelectLevel => 'ResLevel',
ManagesPeople => 'ResManage'};

my @clauses = ();
foreach my $checkbox (keys %$hash) {
push @clauses, $hash->{$checkbox} if validate($cgi->param($checkbox));
}


my $where_clause = join(' and ',map($_.= ' = "on"', @clauses));

my $sql = "SELECT ResType, ResLevel, ResManage, Details, Length, Source, Cost, FurtherDetails
FROM ResourceSettings, Resources
WHERE ((ResourceSettings.ResID = Resources.ResID) and
($where_clause))
if ($where_clause)
ORDER BY order by ResType, ResLevel, ResManage,
Details, Length, Source, Cost, FurtherDetails";


print $cgi->p($sql);

my $sth = $dbh->prepare($sql);

$sth->execute ||
die "Could not execute SQL statement ... " . $dbh->errstr;


my $rows = $dbh->selectall_arrayref($sql) || die $dbh->errstr;
if (@$rows) {
print "<table border=1 cellspacing=0 cellpadding=3><tr>" .
"<th>Type</th><th>Level</th><th>Manage</th><th>Details</th><th>Length</th><th>Source</th><th>Cost</th><th>Further Details</th></tr>";
foreach my $row (@$rows) {
print "<tr><td>" . join ("</td><td>", @$row) . "</td></tr>\n";
}
print "</table>\n";
}
else {
print "<p><i>No matches found</i></p>\n";
}



# disconnect from database
$dbh->disconnect();
exit(0);

# validate user input
sub validate {
my $string = shift;
# get rid of all non-letter, non-numerical characters and percents
$string =~ s/[^A-Za-z0-9%]//g;
return $string;
}

have you tried the query that is formed in your script directly from a sql editor of some kind? did it return data?

#!perl
print "Content-type: text/plain\n\n";
use strict;
use DBI;
use CGI;
use CGI::Carp qw(fatalsToBrowser warningsToBrowser );

#open connection to Access database
my $dbh = DBI->connect("dbi:ODBC:directory") or die $DBI::errstr;


# setup CGI handle
my $cgi = new CGI;

# start HTML



my $hash ={ Initiative=>'DriveInitiative',
ResOrientation => 'DriveResOrient',
Creativity => 'DriveCreativity',
ChangeOrientation => 'DriveChangeOrient',
DecisionMaking => 'DriveDecisionMake',
SelectLevel => 'ResLevel',
ManagesPeople => 'ResManage'};

my @clauses = ();
foreach my $checkbox (keys %$hash) {
push @clauses, $hash->{$checkbox} if
validate($cgi->param($checkbox));
}


my $where_clause = join(' and ',map($_.= ' = "on"', @clauses));

my $sql = "SELECT ResType, ResLevel, ResManage, Details, Length, Source,
Cost, FurtherDetails
FROM ResourceSettings, Resources
WHERE ResourceSettings.ResID=Resources.ResID ";
$sql.=" and ($where_clause) " if ($where_clause);
$sql.=" ORDER BY ResType, ResLevel, ResManage,
Details, Length, Source, Cost, FurtherDetails";



# print $cgi->p($sql);

my $sth = $dbh->prepare($sql) ||
die "No sth: '$DBI::ErrStr'", $!;

$sth->execute ||
die "Could not execute SQL statement ... " . $dbh->errstr;


my $rows = $dbh->selectall_arrayref($sql) || die $dbh->errstr;
if (@$rows) {
print "<table border=1 cellspacing=0 cellpadding=3><tr>" .

"<th>Type</th><th>Level</th><th>Manage</th><th>Details</th><th>Length</th><th>Source</th><th>Cost</th><th>Further
Details</th></tr>";
foreach my $row (@$rows) {
print "<tr><td>" . join ("</td><td>", @$row) . "</td></tr>\n";
}
print "</table>\n";
}
else {
print "<p><i>No matches found</i></p>\n";
}



# disconnect from database
# $dbh->disconnect();
# exit(0);

# validate user input
sub validate {
my $string = shift;
# get rid of all non-letter, non-numerical characters and percents
$string =~ s/[^A-Za-z0-9%]//g;
return $string;
}

I changed this line

$sql.=" and ($where_clause) " if ($where_clause);

and it now runs fine as long as you don't try to input form values into it. If you do, you get errors. So all I really need now is to figure out how to execute the sql statement based on the form values from the query string. I too tried out the html output with a simple query and that worked so I knew that wasn't the problem. I also ran the query in access and after a few modifications it worked so that is no longer the problem.

If you can help with this I would be very grateful. Thanks.

Thanks James,

I already did that in access and the query worked fine. I know that it is not the query that is the problem The entire script even runs on its own and executes the query without the form values as long as I don't try to use the form in conjunction with it. When I do, I get an error saying "too few parameters", or words to that effect. I think it is an effect of the script not being able to get the form values from the query string in the url, in order to use them in the where clause. What I don't know is how to fix this........ grrrrrr;-) Over to you.

I really appreciate you taking the time to help.......

Hello Rachel, I am not quite sure where you are going but .....

1. There is no $cgi->param($checkbox) that I can find.

2. All the checkboxes on the form are named "drive" , so they act like a radio grouping and you will only see one value.

3. I printed out the $sql and it was the same no matter what I changed on the html form :



I tested the html submit with the below code to just print out the values of each form field to see what the script will have :



Hope this does not seem too negative.

Time to start from scratch with your $sql building code.

I can make up a very basic html / perl example if you wish.

thanks

cornball

Hello Rachel, I have attached a sample html and perl file.

Edit the perl file for perl location

Edit the html for the form action.

One way to put together the form names / values into a sql query.

Hope this helps

cornball