CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Beginner:
Need help in updating the record

 



datlaravi
Deleted

Jun 29, 2000, 8:01 AM

Post #1 of 3 (466 views)
Need help in updating the record Can't Post

 
Hi,
I have a form which retrieves data from the database. It has 3 checkboxes. checkboxes are displayed
as unchecked if the value of that item is 0 in the database and checked if the value of the item is 1.
I have written code for this. This is working
For example,
I have put the database columns in an array
@fields =("0verHead, "tv_Vcr","slideProj", "videoConference","seats"); #form has same names
print <<"html"; <tr><td><B>OverHead:</B></td>
html
if ($fieldvals{'overHead'} == 1) {
print <<"html";
<td><input type = "checkbox" name="overHead" value="$fieldvals{'overHead'}"checked></td>
html
}
else {
print <<"html";
<td><input type ="checkbox" name="overHead" value=0></td>
html
}
The above code is working for getting data from the database.
I want to update the information from the form.
If the user checks the checkbox, i want to add the value '1' to the database. if the checkbox is
unchecked i want to add 0 to the database.
I have written code for it.
Please help me. It is not updating the record.
sub doUpdate {
my $updStr= " ";
my $val =" ";
my $colStr = " ";
$value1 = $query->param('hiddenvalue');
for ($i=0;$i<scalar(@fields);$i++) {
if ($query->param($fields[$i])) {
$val = $query->param($fields[$i]);
}
$colStr .="$fields[$i]\=$val";
$colStr .= "," if ($i <(scalar(@fields)-1));
#print $colStr;
}
$updStr ="update ConferenceRooms set $colStr where conferenceRoomId =$value1";
$sybHandle=$syb->prepare($updStr);
$sybHandle->execute() | | &writeError('Conference Room cannot be updated');
$rc = $sybHandle->finish();
$message = "<b>Conference Room has been updated</b><p>";
}
It is displaying the error,
Incorrect syntax near 'where'
I didn't find any syntax error.
I tried printing the $colStr.
It is printing
overHead=1, overHead=1,slideProj=1, overHead=1,slideProj=1,tv_Vcr=1,
overHead=1,slideProj=1,tv_Vcr=1,videoConference=1,
overHead=1,slideProj=1,tv_Vcr=1,videoConference=1,seats=40
I don't understand this. It is printing so many times.


TheGame+
Deleted

Jun 30, 2000, 4:55 AM

Post #2 of 3 (466 views)
Re: Need help in updating the record [In reply to] Can't Post

Look at where $colStr is filled in...
You start with a space (unless that's something added by this board) :

my $colStr = " ";

Then you loop over every field and add field=val to it for each field :

for ($i=0;$i<scalar(@fields);$i++) {
...
$colStr .="$fields[$i]\=$val";
$colStr .= "," if ($i <(scalar(@fields)-1));
#print $colStr;
}

So in the first loop, $colStr contains ' overhead=1,' (I assume the @fields you show doesn't really match your REAL @fields).
The second time, it contains ' overHead=1,slideProj=1,'.
The third time, ' overHead=1,slideProj=1,tv_Vcr=1,'
Etc.
And that's what you see when you print out $colStr inside the loop.

There is a little problem in your code if one of the fields is not returned or 0, BTW. The colStr will contain field=val_of_the_previous_field here.

As for the Sybase syntax error - you might want to make sure that you're using the right SQL syntax for Sybase...


datlaravi
Deleted

Jun 30, 2000, 6:58 AM

Post #3 of 3 (466 views)
Re: Need help in updating the record [In reply to] Can't Post

Thanks for your reply.
I was able to make part of the problem working with your help.
By your suggestion,
I took out the $colStr="" which starts with a space.
I checked the @fields and they are matching the
names same as they are in the database.
I checked the sybase syntax.

The update is working when i uncheck the checkbox. It is adding 0 to the database. The problem is when I check it, it is not working. It is not adding 1 to the database. I checked some of the checkboxes and tried to print the $colStr.
It is printing
overHead=null,slideProj=null,tv_Vcr=null,videoConference=1,seats=20.
seats value is printing correctly. Though the above checkboxes are checked, they are printing as null. Please help me fix this problem. I am posting the following program
I apppreciate your help.
#modify1.cgi
#!/mpi/usr/bin/perl
require "/web/ns-home/docs/perl5FuncsLib.pl";
require "conf.incl";
use CGI;
use DBI;
CGI::ReadParse();
use CGI qw(:standard);
$query = new CGI;
$smfont = "<font face=\"arial,helvetica\" size=-1>";
&guestSybLogin;
$syb = $dbh;
&printform;
exit;
sub printform {
print "Content-type: text/html \n\n";
print << "html";
<body bgcolor="ffffff">
<CENTER><H1>Modify Conference Room </H1> </center>
html
@fields = ("overHead","slideProj","tv_Vcr","videoConference","seats"); #database columns
$value =$query->param('conferenceRoomId');
if ($value) #if the user selects an option
{
&getinf; #retrieves data from the database
&printform1; # prints the form with the retrieved information from the database
print <<"html";
<form method ="post" action="modify1.cgi">
<BR> <font size =5><B><input type ="submit" name= "modify" value= "Modify"></B></font>
<input type ="hidden" name="hiddenvalue" value="$value">
html
}
$getit = $query->param('modify');
if ($getit eq 'Modify') {
&doUpdate; #when the user clicks the modify button, the form information gets updated
}
} #printform ends here
sub getinf {
$value =$query->param('conferenceRoomId');
my $selStr ='';
my $colList = '';
my $recset = '';
my @vals = ();
for ($i=0; $i < scalar(@fields); $i++)
{
if ($fields[$i] =~ /date/) {
$colList .= "to_char($fields[$i], \'MMDDYYYY\') as $fields[$i]";
} #end if
else {
$colList .= "$fields[$i]"}
if ($i < scalar(@fields)-1)
{$colList .= ","}
}
$selStr ="Select $colList from ConferenceRooms where conferenceRoomId=$value";
$recset = $syb->prepare($selStr);
$recset->execute();
@vals =$recset->fetchrow;
$recset->finish;
for ($i =0; $i < scalar(@fields); $i++) {
$fieldvals{$fields[$i]} = $vals[$i];
}
return %fieldvals;
}
sub printform1 {
print <<"html";
<form method ="post" action ="modify1.cgi">
<table><tr><td><B><font size =6>OverHead: </font></B></td>

html
if ($fieldvals{'overHead'} == 1) {
print <<"html";
<td><input type = "checkbox" name="overHead" value="$fieldvals{'overHead'}" checked></td>

html
}
elsif ($fieldvals{'overHead'} == 0){
print <<"html";
<td><input type ="checkbox" name="overHead" value="$fieldvals{'overHead'}"></td>
html
}
else {
print <<"html";
<td><input type ="checkbox" name ="overHead" value="$fieldvals{'overHead'}"> </td>
html
}
print <<"html";
</tr></table>
<table><tr><td><B><font size =6>TV/VCR: </font></B></td>
html
if ($fieldvals{'tv_Vcr'} == 1) {
print <<"html";
<td><input type = "checkbox" name="tv_Vcr" value="$fieldvals{'tv_Vcr'}" checked> </td>

html
}
else {
print <<"html";
<td><input type ="checkbox" name="tv_Vcr" value="0"></td>
html
}
print <<"html";
</td></table>
<table><tr><td><B><font size =6>SlideProj: </font></B></td>
html
if ($fieldvals{'slideProj'} == 1) {
print <<"html";
<td><input type = "checkbox" name="slideProj" value="$fieldvals{'slideProj'}" checked></td>
html
}
else {
print <<"html";
<td><input type ="checkbox" name="slideProj" value=0> </td>
html
}
print <<"html";
</tr></table>
<table><tr><td><B><font size =6>Video Conf: </font></B></td>
html
if ($fieldvals{'videoConference'} == 1) {
print <<"html";
<td><input type = "checkbox" name="videoConference" value="$fieldvals{'videoConference'}" checked></td>
html
}
else {
print <<"html";
<td><input type ="checkbox" name="videoConference" value=0> </td>
html
}
print <<"html";
</tr></table>
html
print <<"html";
</tr></table>
<table><tr><td><B><font size =6>Seats: </font></B></td>
<td><input type ="text" name="seats" size=10 value ="$fieldvals{'seats'}"></td></tr></table>

html
}
sub doUpdate {
@fields = ("overHead","slideProj","tv_Vcr","videoConference","seats");

my $updStr= " ";
my $val =" ";
#my $colStr = " ";
$value1 = $query->param('hiddenvalue');
for ($i=0;$i<scalar(@fields);$i++) {
if ($query->param($fields[$i])) {
$val = $query->param($fields[$i]);
}
else { $val = 'null';
}

$colStr .="$fields[$i]\=$val";
$colStr .= "," if ($i < (scalar(@fields)-1));
}
print "$colStr";
$updStr ="update ConferenceRooms set $colStr where conferenceRoomId =$value1";
$sybHandle=$syb->prepare($updStr);
$sybHandle->execute() &#0124; &#0124; &writeError('Conference Room cannot be updated');
$rc = $sybHandle->finish();
$message = "<b>Conference Room has been updated</b><p>";
}
The values for the fields in the database are, they can be 0, 1 or null.

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives