Tweet
Help with SQL insert statement
My problem is with a single quotation mark, or apostrophe, and sql to update a database.
These are the results if a single quote (apostrophe) is used in a field, in this case, O'Hare (oh, and by the way double quotes work fine).
SQL failed. Error: [-3100] [2] [0] "[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''O'Hare')'." insert into Tester ( TestID, FirstName, LastName, RentalLocation) values ( 7, 'Amy', 'Gatewood', 'O'Hare')
It is imperative that a single quote in a field work. Please help! This is the Perl code (the entire test code, with only a 4 fields in the database - TestID, FirstName, LastName, RentalLocation). If needed, I can attach the test database, and copy the HTML code for full testing. Although at that time you would have to set up the ODBC driver name to TEST, etc. I have added comments to help guide you through the code. Thanx in advance, Amy
#!C:\Perl\bin\perl.exe -wT
use Win32::ODBC;
use CGI ':standard';
#accept HTML form data
my $strDSN = "TEST";
my $strFName = param(txtFName);
my $strLName = param(txtLName);
my $strRLoc = param(txtRLoc);
my $intTestID;
#initial query to obtain primary key to be used in insert statement
#TestID is an autonumber - I obtain the highest autonumber #already in the database and add one to that for the next record
my $strQuery = "select TestID from Tester order by TestID";
print header;
if (!($Data = new Win32::ODBC($strDSN))) {
print "Error connecting to $strDSN\n";
print "Error: " . Win32::ODBC::Error() . "\n";
exit;
}
if ($Data->Sql($strQuery)) {
print "SQL failed.\n";
print "Error: " . $Data->Error() . "\n";
$Data->Close();
exit;
}
while ($Data->FetchRow()) {
%Data = $Data->DataHash();
$intTestID = $Data{TestID};
}
$intTestID = $intTestID + 1;
$Data->Close();
#Now, I form my queries..
#They are set up so that a blank field may be entered
my $strQuery1 = "insert into Tester ";
my $strQuery2 = "( TestID";
my $strQuery3 = ") values ( " . $intTestID;
if ($strFName ne "") {
$strQuery2 = $strQuery2 . ", FirstName";
$strQuery3 = $strQuery3 . ", '$strFName'";
}
if ($strLName ne "") {
$strQuery2 = $strQuery2 . ", LastName";
$strQuery3 = $strQuery3 . ", '$strLName'";
}
if ($strRLoc ne "") {
$strQuery2 = $strQuery2 . ", RentalLocation";
$strQuery3 = $strQuery3 . ", '$strRLoc'";
}
$strQuery3 = $strQuery3 . ")";
$strQuery1 = $strQuery1 . $strQuery2 . $strQuery3;
if (!($Data = new Win32::ODBC($strDSN))) {
print "Error connecting to $strDSN\n";
print "Error: " . Win32::ODBC::Error() . "\n";
exit;
}
if ($Data->Sql($strQuery1)) {
print "SQL failed.\n";
print "Error: " . $Data->Error() . "\n";
print $strQuery1;
$Data->Close();
exit;
}
#HTML page is now displayed indicating a successful update
#note that this page does not display if there was an error in the #sql, instead the error will be displayed (which I copied above)
print << "HTML code0";
<html>
<head><title></title>
</head>
<body>
<strong>Update successful!</strong>
HTML code0
print end_html;
$Data->Close();
These are the results if a single quote (apostrophe) is used in a field, in this case, O'Hare (oh, and by the way double quotes work fine).
SQL failed. Error: [-3100] [2] [0] "[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''O'Hare')'." insert into Tester ( TestID, FirstName, LastName, RentalLocation) values ( 7, 'Amy', 'Gatewood', 'O'Hare')
It is imperative that a single quote in a field work. Please help! This is the Perl code (the entire test code, with only a 4 fields in the database - TestID, FirstName, LastName, RentalLocation). If needed, I can attach the test database, and copy the HTML code for full testing. Although at that time you would have to set up the ODBC driver name to TEST, etc. I have added comments to help guide you through the code. Thanx in advance, Amy
#!C:\Perl\bin\perl.exe -wT
use Win32::ODBC;
use CGI ':standard';
#accept HTML form data
my $strDSN = "TEST";
my $strFName = param(txtFName);
my $strLName = param(txtLName);
my $strRLoc = param(txtRLoc);
my $intTestID;
#initial query to obtain primary key to be used in insert statement
#TestID is an autonumber - I obtain the highest autonumber #already in the database and add one to that for the next record
my $strQuery = "select TestID from Tester order by TestID";
print header;
if (!($Data = new Win32::ODBC($strDSN))) {
print "Error connecting to $strDSN\n";
print "Error: " . Win32::ODBC::Error() . "\n";
exit;
}
if ($Data->Sql($strQuery)) {
print "SQL failed.\n";
print "Error: " . $Data->Error() . "\n";
$Data->Close();
exit;
}
while ($Data->FetchRow()) {
%Data = $Data->DataHash();
$intTestID = $Data{TestID};
}
$intTestID = $intTestID + 1;
$Data->Close();
#Now, I form my queries..
#They are set up so that a blank field may be entered
my $strQuery1 = "insert into Tester ";
my $strQuery2 = "( TestID";
my $strQuery3 = ") values ( " . $intTestID;
if ($strFName ne "") {
$strQuery2 = $strQuery2 . ", FirstName";
$strQuery3 = $strQuery3 . ", '$strFName'";
}
if ($strLName ne "") {
$strQuery2 = $strQuery2 . ", LastName";
$strQuery3 = $strQuery3 . ", '$strLName'";
}
if ($strRLoc ne "") {
$strQuery2 = $strQuery2 . ", RentalLocation";
$strQuery3 = $strQuery3 . ", '$strRLoc'";
}
$strQuery3 = $strQuery3 . ")";
$strQuery1 = $strQuery1 . $strQuery2 . $strQuery3;
if (!($Data = new Win32::ODBC($strDSN))) {
print "Error connecting to $strDSN\n";
print "Error: " . Win32::ODBC::Error() . "\n";
exit;
}
if ($Data->Sql($strQuery1)) {
print "SQL failed.\n";
print "Error: " . $Data->Error() . "\n";
print $strQuery1;
$Data->Close();
exit;
}
#HTML page is now displayed indicating a successful update
#note that this page does not display if there was an error in the #sql, instead the error will be displayed (which I copied above)
print << "HTML code0";
<html>
<head><title></title>
</head>
<body>
<strong>Update successful!</strong>
HTML code0
print end_html;
$Data->Close();
Comment