Insert a record into MS Access

aiikahn · #1 (**permalink**) 10-07-07, 14:56

I created a webform in which will take textfield inputs and insert the record to my MS Access Database in the server but it keeps giving me a mismatch error and when it works it only places the first 2 field values. I was wondering could help me. I'm still new with perl. Below is the code:

#!/usr/bin/perl -w
#
#

use strict;
#use DBI;
use CGI qw(:standard);
use CGI::Carp qw(fatalsToBrowser); # provide descriptive error messages
use Win32::ODBC;

print header(); # print out "Content-Type: text/html\n\n"

# Get user's desired action from form
my $action = param('form_action');

# Connect to database
my $db = new Win32::ODBC("DSN=rreDB")
or die Win32::ODBC::Error();

# Go to appropriate subroutine based on users's desired action
Add_Record() if($action eq 'addrecord');

################################################## ####

sub Add_Record {
my $lname = param('last');
my $fname = param('first');
my $mi = param('mi');
my $street = param('street');
my $city = param('city');
my $state = param('state');
my $zip = param('zip');
my $prof = param('profession');
my $empl = param('employer');
my $int = param('interest');
my $ref = param('referrer');
my $sql;

$sql = qq{INSERT INTO Customer (last, first, mi, street, city, state, };
$sql .= qq{zip, profession, employer, interest, referrer)};
$sql .= qq{ VALUES ('$lname', '$fname', '$mi', '$street','$city', '$state',};
$sql .= qq{'$zip', '$prof', '$empl', '$int','$ref')};

#print '$sql=' . $sql . "\n";
if ($db->Sql($sql)) {
print "SQL Error: " . $db->Error() . "\n";
Handle_DB_Error();
Display_Page();
exit;
}

Display_Page();
}

################################################## ############

sub Display_Page {
print qq(<html><head><title>Record Added!</title></head>
<body>
<center>
Record Added!
<hr />

<a href="AddRecord.html">Back to Main Page</a>
 
</center>
</body>
</html>);
}

################################################## ##############

sub Handle_DB_Error {
print qq(<html><head><title>Database Error</title></head>
<body>
<center>
Error with database call.
<hr />
ODBC DB Error 
Please hit your Back button to
re-enter the data and try again.
</center>
</body>
</html>);
exit;
}

I would really appreciate any help. Thank you.

sco08y · #2 (**permalink**) 10-09-07, 14:37

Hmm... didn't notice you're using Win32::ODBC when I wrote this. Personally, I think you'd be better off using the DBD::ADO driver for DBI. I never use Win32::ODBC directly, so I can't help you much, except to say that you should probably double check that all your columns are, in fact, strings.

Anyhow, the DBI way of executing a data manipulation statement is prepare and execute.

Code:

my $sth = $dbh->prepare('INSERT INTO Foo (col1, col2) VALUES (?, ?)');

You notice how you've got the '$foo' stuff going on? That's a surefire way to have a SQL injection attack performed on your code. The question marks are placeholders.

The code above creates a statement handle. You can use it to execute multiple insert statements, or just one. To execute a statement, you pass the paramters:

Code:

$sth->execute($col1, $col2)

.. or ...

$sth->execute(param(qw/col1 col2/))

Note the qw// operator, it's the same as saying param('col1', 'col2'). It's a quick way of specifying a list of strings. (And if you check CGI.pm's documentation you'll see that param will return a list of params if you pass multiple names. Handy...)