After fetching the records from an SQL server database into a HTML form with select for update via a CGI program, the multiple selected items are not being displayed as selected. I am using Perl.
use CGI;
use CGI qw/:standard/;
use CGI::Carp qw(warningsToBrowser fatalsToBrowser);
my $q = new CGI;
my $query = new CGI;
my @list =new CGI;
my $val =new CGI;
my $var =new CGI;
use DBI;
use CGI qw(:all);
print "Content-Type: text/html\n\n";
$query = $ENV{'QUERY_STRING'};
@list = split( /\&/, $query);
foreach (@list) {
($var, $val) = split(/=/);
$val =~ s/\'//g;
$val =~ s/\+/ /g;
$val =~ s/%(\w\w)/sprintf("%c", hex($1))/ge;
($var, ' = ', $val,);
}
my $db_instance = "My server name";
my $db_name = "Users";(My Database name)
my $db_user = "";
my $db_pass = "";
my $dbh = DBI->connect("DBI:ODBC:Driver={SQL
Server};Server=$db_instance;Database=$db_name;UID=$db_user;PWD=$db_pass",
{'RaiseError' => 1, 'AutoCommit' => 0});
my $sth = $dbh->prepare("SELECT * FROM UserForm WHERE UserId=$val");
$sth->execute;
while (@row = $sth->fetchrow_array()) {
my $User_Name=$row[1];
my $User_Role=$row[2];
my $User_Permission=$row[3];
my $User_Department = $row[4];
my $User_Role_html = "";
my $sql = "select RoleName from Roles";
my $sth = $dbh->prepare($sql);
$sth->execute;
while (my $User_Role_option= $sth->fetchrow_array)
{
$User_Role_html .= "<option value=\"$User_Role_option\"";
$User_Role_html .= " selected" if ( $User_Role_option eq $User_Role );
$User_Role_html .= ">$User_Role_option</option>";
}
my $User_Permission_Add_sel = $User_Permission eq "Add" ? " checked" : "";
my $User_Permission_Edit_sel =$User_Permission eq "Edit" ? " checked" : "";
my $User_Permission_Delete_sel =$User_Permission eq "Delete" ? " checked" : "";
my $User_Permission_View_sel =$User_Permission eq "View" ? " checked" : "";
my $User_Department_html = "";
my $sql = "select DepartmentName from Departments order by DepartmentName";
my $sth = $dbh->prepare($sql);
$sth->execute;
while (my $User_Department_option = $sth->fetchrow_array) {
$User_Department_html .= "<option value=\"$User_Department_option\"";
$User_Department_html .= " selected" if ($User_Department_option eq
$User_Department);
$User_Department_html .= ">$User_Department_option</option>";
}
print <<END_HTML;
<html>
<head><title></title></head>
<body>
<form action="DataUpdate.cgi" method="get">
<input type="hidden" name="UserId" value="$val">
<input type="hidden" name="submit" value="Submit">
<TABLE BORDER="1" align="center">
<TR>
<TD>User name</TD>
<TD> <input type="text" name="User_Name" value="$User_Name"></TD>
</TR>
<TR>
<TD>Role</TD>
<TD colspan="2"><select name="User_Role">$User_Role_html</select></TD>
</TR>
<TR>
<TD>Permission</TD>
<TD><input type="radio" name="User_Permission"
value="Add"$User_Permission_Add_sel>Add<input type="radio" name="User_Permission"
value="Edit"$User_Permission_Edit_sel>Edit<input type="radio" name="User_Permission"
value="Delete"$User_Permission_Delete_sel>Delete<input type="radio"
name="User_Permission" value="View"$User_Permission_View_sel>View</TD>
</TR>
<TR>
<TD>Department</TD>
<TD colspan="2"> <select name="User_Department" multiple="multiple" SIZE=4
>$User_Department_html</select></TD>
</TR>
</TR>
<TR>
<TD align="center" colspan="2">
<input type="submit" name="update" value="UPDATE">
</TD>
</TR>
</TABLE>
</form>
</body></html>
END_HTML
}
$dbh->commit or die $DBI::errstr;
$dbh->disconnect;
This is COMPLETE code, please, and do help me out.
Initially I have a form in a page called UserForm.cgi. In that I have a select list where I can select multiple departments and am fetching selected values by
my $User_Department = join(", ", $q->param("User_Department"));
where $User_Department is the name of the select box. After I click submit then these values will get inserted into a table like Department1, Department2 for each user. After that I have a datagrid in a page Datagrid.cgi into which I fetch all the table data. The rest of the details is the same as what I have described in my question above.
Actually I have a datagrid in a CGI page, I have an edit hyperlink to each row of the datagrid. When I click the hyperlink then it goes to the next page where I have a form in which whatever data I have in the database confined to a particular user is displayed. Here in the select list of the form I have a list of departments. If the user has one department assigned then it is displaying as selected, but if mutiple departments are assigned, then the departments which are selected are not being displayed.
Actually the problem I am facing is near the selected if(), whenever I am fetching the user with only one department assigned then it is getting displayed as selected in the select list of the form, but when there are multiple departments split with comma between them then it is not showing any selected sign. I tried the foreach loop, removed the comma also, but I am not getting what to do and how. $val is nothing but the $UserID I am fetching it using the Query string method and something.
ANYBODY COULD PLEASE HELP ME OUT AS SOON AS POSSIBLE..............