Returning a single record

Doyle has asked for the wisdom of the Perl Monks concerning the following question:

I want to return a single record from an access database and be able to go to the next record by clicking on a button. Here is my code...

 #!/usr/bin/perl -w
    #################################################
    ## This is an Example of how to Connect to an
    ## Access Database using a DSN-less Connection
    #################################################
    use POSIX;
    use strict;
    use CGI qw(:standard);
    use CGI::Carp qw(fatalsToBrowser);
    use DBI;
   
        
    #########################################
    ## Create the Connection String 
    ## To use a DSN just replace the string
    ## with the DSN name.
    #########################################
    my $DSN = 'driver=Microsoft Access Driver (*.mdb);dbq=c:\\Inetpub\
+\domains\\rvnuccio.com\\doytest\\cgi-bin\\Clients.mdb';
    my $dbh = DBI->connect("dbi:ODBC:$DSN", '','') 
        or die "$DBI::errstr\n"; 
        
    ###############################
    ## Generate SQL Statement
    ###############################

     my $sql = "Select 'John Smith' from TblOne";
    # my $sql ="Select ClientName from Billing";
    ###############################
    ## Prepare and execute the 
    ## SQL Statement
    ###############################
    
    my $loadHandle = $dbh->prepare($sql);
  
    $loadHandle->execute|| 
       die "Could not execute SQL statement ... maybe invalid?";
       
      
  print header;    
   print "<html><head><title>SUBMITTED QUOTES</title></head>\n";
   print "<body BGCOLOR=\"#D3F2FC\"><CENTER><h3>Submitted Quotes</h3><
+/CENTER>\n";
   print "<form action=\"http://www.rvnuccio.com/doytest/cgi-bin/getur
+l.pl\" method=\"GET\">\n";
   print "<BR>\n";
   print "<TABLE id=Table1 cellSpacing=1 cellPadding=1 width=300 align
+=center border=0>";
   print "<TR>";   
   print "<TD></TD>";
   print "</TR>";
   print "<TR>";
   print "<TD align=left>Name</TD>";
   print "<TD></TD>";
   print "<TD></TD>";
   print "</TR>";
   print "<TR>";   
   print "<TD></TD>";
   print "<TD></TD>";
   print "</TR>";
######################################################################
+##########################    
     
        
    my @row;            
   
     while (@row=$loadHandle->fetchrow_array)
  {
  
   print "<TR>";
      print "<TD align=Left><input type=\"checkbox\" name = @row>@row<
+/TD>";
      print "</TR>";
      
################################
       
   }
   print "<TR>";
   print "<TD></TD>";
   print "</TR>";
   print "<TR>";
   print "<TD><input type=\"submit\" value=\"Send Information\"></TD>"
+;
   print "<TD></TD>";
   print "<TD></TD>";
   print "</TR>";
   print "<TD align=right><INPUT type=button value=Return name=Button1
+ onClick=\"parent.location='http://www.rvnuccio.com/doytest/applicati
+ons/pec/application/online/Admin.html'\"></TD>";
   print "</TABLE>";
   print "&nbsp\;"; 
   print "&nbsp\;"; 
   print "</form>\n";
   print "\n</body></html>\n";    
 
  #########################################
    ## Close the connection when finished:
    #########################################
    $dbh->disconnect;  


#!/usr/bin/perl

#geturl.pl

#Get header, ender, define the page title

##    require "c:\\Inetpub\\domains\\rvnuccio.com\\doytest\\cgi-bin\\g
+eturl.pl";
    $Title = "Get Information from a url";
    

#Get the query string

    $QueryString = $ENV{'QUERY_STRING'};
    
    
#Use split to make an array of name-value pairs

    @NameValuePairs = split (/&/, $QueryString);
#    @NameValuePairs = $QueryString;
#    print @NameValuePairs
    
#Put up an HTML header, page title and a rule


    print "Content-type: text/html\n\n";
    print "<BODY>\n";
    print "<H1>$Title</H1>\n";
    print "<HR>\n";
    
    
#Split each of the name-value pairs and print them on the page

    foreach $NameValue (@NameValuePairs)
    {
        ($Name, $Value) = split(/=/, $NameValue, 2);
        print "Name = $Name, value = $Value<BR>\n";
        
    }
[download]

Thanks guys

2005-03-02 Janitored by Arunbear - added readmore tags, as per Monastery guidelines

Comment on Returning a single record Download Code

Replies are listed 'Best First'.
Re: Returning a single record by rnahi (Curate) on Mar 01, 2005 at 15:44 UTC
First of all, this node may tell you why people don't take you seriously. To improve your request, please read How (Not) To Ask A Question Before asking a database related question ... Finally, you are asking why your query returns more than one record. Well, since your query is "Select 'John Smith' from TblOne", it is clear to anyone who knows some basic SQL that such query will return the string 'John Smith' for every row in your table. There is nothing wrong in your Perl code (besides horribly mixing bits and pieces from the programming logic and the presentation layer, and being a candidate for Cargo cult that's it). You should look for a SQL guide. Therefore, I invite you once more to read the basic rules, especially the part saying "know your tools". HTH	[reply]
Re: Returning a single record by halley (Prior) on Mar 01, 2005 at 15:22 UTC
I'm not exactly sure what your question is. Does your code work? Does it not work? What results do you get? What's the next step? Are you asking for critique, or do you want us to finish your project? There are a lot of good books on using the DBI and CGI modules, which will give solid examples from which to learn. I am not a major fan of templating systems for HTML, but this is exactly the sort of nightmare code which template tools help you remove from your programs. See HTML::Template for one system, though there are other popular ones as well. `print "<TR>"; print "<TD></TD>"; print "</TR>"; print "<TR>"; print "<TD align=left>Name</TD>"; print "<TD></TD>"; print "<TD></TD>"; print "</TR>"; print "<TR>"; print "<TD></TD>"; print "<TD></TD>"; print "</TR>";` [download] -- `[ e d @ h a l l e y . c c ]`	[reply] [d/l]
Re: Returning a single record by Joost (Canon) on Mar 01, 2005 at 15:23 UTC
So, what isn't working? Maybe you should use CGI, strict and warnings in geturl.pl, too. "What should it profit a man, if he should win a flame war, yet lose his cool?"	[reply]
Re: Returning a single record by Taulmarill (Deacon) on Mar 01, 2005 at 15:22 UTC
sort the output of your db by some values of your choice and remember the actual position inside the order by passing it as cgi-value. the rest should be simple sql.	[reply]
Re^2: Returning a single record by Doyle (Acolyte) on Mar 01, 2005 at 15:31 UTC
The code does work however, it gives me back more than one record which are all the same record.	[reply]
Re: Returning a single record by punkish (Priest) on Mar 01, 2005 at 20:00 UTC
There is so much wrong with this question -- let us begin at the begine... Read formatting tips Don't include more code than necessary to ask the question. State the error properly Now, wrt your question -- what error do you get? You say in a later email that you get more than one record. Well, then how about changing your SQL query so that it asks the database the correct question. See, suddenly your question is no longer a Perl question but a SQL question... all that extra html code and DBI connection info is just confusing and takes up space. The long code line (you have likely used the <pre> tags instead of the code tags) is screwing up my browser window (I hate horizontal scroll bars). -- when small people start casting long shadows, it is time to go to bed	[reply]