Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2006
    Posts
    20

    Unanswered: Insert a record into MS Access

    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>
    <font size="6">Record Added!</font>
    <hr />
    <font size="4">
    <a href="AddRecord.html">Back to Main Page</a>
    </font><br />
    </center>
    </body>
    </html>);
    }

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

    sub Handle_DB_Error {
    print qq(<html><head><title>Database Error</title></head>
    <body>
    <center>
    <font size="6">Error with database call.</font>
    <hr />
    <font size="4" color="red">ODBC DB Error</font><br />
    <font size="3">Please hit your <b>Back</b> button to
    re-enter the data and try again.</font>
    </center>
    </body>
    </html>);
    exit;
    }

    I would really appreciate any help. Thank you.

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    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...)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •