DB Connection Not Working

[mpriest88]

I'm trying to connect to a MS Access database to save a users info (emp_id, emp_name & mail_id) entered on a form. The fields in the table in the db are named the same as they are in the form. I can get the script to print the data entered, but it doesn't save it to the db. I hope this isn't too long ,but here is the entire script:

#!/perl/bin/perl

use CGI qw(:standard);
use DBI;

#Print html to make sure data is being read correctly
print header;
print start_html("Thank You");
print h2("Thank You");
my %form;
foreach my $p (param()) {$form{$p} = param($p); print "$p = $form{$p}<br>\n";}
#Works right

#Validate emp_id field
sub validEmpId
{print "Validating Employee id"; if($emp_id !~ m/\d*/) {return false;} else{return true;}}
#No errors

#Validate emp_name field
sub validEmpName
{print "Validating Employee Name"; if($emp_name !~ m/\d*/) {return false;} else{return true;}}
#No errors

#Validate mail_id field
sub validMailId
{print "validating mail id"; if($mail_id =~ m/\w+\@\w+\.com/) {return true;;} else{return false;}}
#No errors

#Set values to data entered
$emp_id = param("emp_id");
$emp_name = param("emp_name");
$mail_id = param("mail_id");
#No errors

#Check if emp_id contains letters
if($emp_id =~ m/[a-z A-Z]/) {exit;}
#No errors

#I'm not sure what the line below is supposed to do
elsif($mail_id =~ m/\w+\@\w+\.com/)

#This is where I get errors
#store it in the database
{$dbh = DBI->connect("DBI:ODBC:emp");
$sth = $dbh->prepare("Insert into emp_data(Id,Name,Mail_ID) values($emp_id,'$emp_name','$mail_id')");
$sth->execute();
$sth->finish();
$dbh->disconnect();}
else{exit;}

#I put this at the end because I didn't want to delete it in case I needed
#This is what StartLogic said to use to connect to MS Access Database on their server
#'Database connection info and provider
#strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &_
#Server.MapPath("database\emp.mdb")

print end_html;

Thanks in advance for any suggestions or help and your time

Mike

[mpriest88]

I forgot to post the results of the Apache 2.2 Error Log. Here it is:

[Tue Sep 09 05:44:10 2008] [error] [client 127.0.0.1] DBI connect('emp','',...) failed: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (SQL-IM002) at C:/Documents and Settings/Mike/My Documents/My Website/update.cgi line 32
[Tue Sep 09 05:44:10 2008] [error] [client 127.0.0.1] Can't call method "prepare" on an undefined value at C:/Documents and Settings/Mike/My Documents/My Website/update.cgi line 39.

[jthayne]

Have you set up the ODBC connection or is emp the name of your database?

I can help you figure it out if you are not sure how to do it. You are running the site locally on Windows XP, correct?

[mpriest88]

I tried setting up the ODBC connection, but I think I got something wrong. The database is named emp.mdb. I am running the site locally for testing. Any help you can provide will be greatly appreciated. Thank you jthayne

Mike

[jthayne]

Here is something that might help you.

[mpriest88]

I think I got it to connect to the db, but now I'm getting other errors. I uploaded my test web page, database file and cgi file to my server so you could see exactly what errors I'm getting. Go to www.ccrpage.com to see the page. It only has 3 fields with a Submit button. Please try it and see if you can figure out where I'm going wrong. Here the new cgi file:

#!/perl/bin/perl
use POSIX;
use strict;
use CGI qw(:standard);
use CGI::Carp qw(fatalsToBrowser);
use DBI;
use Time::Local
print header;
print start_html("Thank You");
print h2("Thank You");
my %form;
foreach my $p (param()) {$form{$p} = param($p); print "$p = $form{$p}<br>\n";}
$emp_id = param("emp_id");
$emp_name = param("emp_name");
$mail_id = param("mail_id");
my $DSN = 'driver=Microsoft Access Driver (*.mdb);dbq=C:\Documents and Settings\Mike\My Documents\My Website\emp.mdb';
my $dbh = DBI->connect("DBI:ADO:$DSN", '','')
or die "$DBI::errstr\n";
$::db_host = 'localhost';
$::db_name = 'emp.mdb';
$::db_user = 'root';
my $sql = <<"EndOfSQL";
Insert into emp_data(emp_id,emp_name,mail_id) values($emp_id,'$emp_name','$mail_id')
EndOfSQL
$dbh->do($sql)
or die "Execution problem: $DBI::errstr";
print "Table was Created";
$dbh->disconnect();
print end_html;


Thanks again for your help
Mike

[mpriest88]

I finally got it to work! It was the ADO. I forgot to change it ODBC. Thanks for all your help and providing me with the link. It was a huge help. Thanks again

Mike

[jthayne]

Glad to be of assistance.