> thanks for all the help. i've changed various things and although my
> program is working slightly better there are still issues.
>
> i renamed the $sth to prevent from going out of scope. i've also tried
> using placeholders with no success. here's the latest code and errors.
>
> still stumped..
> -adam
>
> errors:
> DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server
> Driver]Connection is b
> usy with results for another hstmt (SQL-HY000)(DBD:
> st_execute/SQLExecute
> err=-1
> ) at C:\Inetpub\wwwroot\cgi-bin\payorlist.cgi line 987.
> DBD::ODBC::st fetchrow_array failed: (DBD: no select statement currently
> executi
> ng err=-1) at C:\Inetpub\wwwroot\cgi-bin\payorlist.cgi line 989.
> DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server
> Driver]Connection is b
> usy with results for another hstmt (SQL-HY000)(DBD:
> st_execute/SQLExecute
> err=-1
> ) at C:\Inetpub\wwwroot\cgi-bin\payorlist.cgi line 1001.
> DBD::ODBC::st fetchrow_array failed: (DBD: no select statement currently
> executi
> ng err=-1) at C:\Inetpub\wwwroot\cgi-bin\payorlist.cgi line 1005.
> DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server
> Driver]Connection is b
> usy with results for another hstmt (SQL-HY000)(DBD:
> st_execute/SQLExecute
> err=-1
> ) at C:\Inetpub\wwwroot\cgi-bin\payorlist.cgi line 987.
> DBD::ODBC::st fetchrow_array failed: (DBD: no select statement currently
> executi
> ng err=-1) at C:\Inetpub\wwwroot\cgi-bin\payorlist.cgi line 989.
> DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server
> Driver]Connection is b
> usy with results for another hstmt (SQL-HY000)(DBD:
> st_execute/SQLExecute
> err=-1
> ) at C:\Inetpub\wwwroot\cgi-bin\payorlist.cgi line 1001.
> DBD::ODBC::st fetchrow_array failed: (DBD: no select statement currently
> executi
> ng err=-1) at C:\Inetpub\wwwroot\cgi-bin\payorlist.cgi line 1005.
>
>
>
> code:
>
> sub sql_custtracker
> {
>
> my @list = ();
> my $color = "";
> $cnt = 1;
> my $where = "";
> $where = $_[0];
> my $sql = "";
> my $sql2 = "";
> my $sql3 = "";
> my $group = "";
> my $payorid = "";
> my $multistateid = "";
> my $customername = "";
>
> #if ($where =~ /\w+/)
> if ($where)
> {
> $sql = "
>
> select txnid,
> datestarted,
> ISNULL(projectlead, ' ') as projectlead,
> ISNULL(status, ' ') as status,
> ISNULL(biller, ' ') as biller,
> ISNULL(formatstatus, ' ') as formatstatus,
> ISNULL(unionid, '0') as unionid,
> ISNULL(customercase, ' ') as customercase,
> ISNULL(enrollmentstatus, ' ') as enrollmentstatus,
> ISNULL(routestatus, ' ') as routestatus,
> ISNULL(complianceerrors, ' ') as complianceerrors,
> mandateid,
> custid,
> ISNULL(formatname, ' ') as formatname,
> expectedstartdate,
> expectedcompdate,
> ISNULL(testfilename, ' ') as testfilename,
> testfiledate,
> ISNULL(trainingstatus, ' ') as trainingstatus,
> ISNULL(kickoffstatus, ' ') as kickoffstatus,
> ISNULL(websitestatus, ' ') as websitestatus,
> ISNULL(paymentverification, ' ') as paymentverification,
> ISNULL(productioncontrol, ' ') as productioncontrol,
> ISNULL(fileacknowledged, ' ') as fileacknowledged,
> ISNULL(customercontact, ' ') as customercontact
> from customertracker
> $where
> ";
> }
> else
> {
> $sql = "
>
> select txnid,
> datestarted,
> ISNULL(projectlead, ' ') as projectlead,
> ISNULL(status, ' ') as status,
> ISNULL(biller, ' ') as biller,
> ISNULL(formatstatus, ' ') as formatstatus,
> ISNULL(unionid, '0') as unionid,
> ISNULL(customercase, ' ') as customercase,
> ISNULL(enrollmentstatus, ' ') as enrollmentstatus,
> ISNULL(routestatus, ' ') as routestatus,
> ISNULL(complianceerrors, ' ') as complianceerrors,
> mandateid,
> custid,
> ISNULL(formatname, ' ') as formatname,
> expectedstartdate,
> expectedcompdate,
> ISNULL(testfilename, ' ') as testfilename,
> testfiledate,
> ISNULL(trainingstatus, ' ') as trainingstatus,
> ISNULL(kickoffstatus, ' ') as kickoffstatus,
> ISNULL(websitestatus, ' ') as websitestatus,
> ISNULL(paymentverification, ' ') as paymentverification,
> ISNULL(productioncontrol, ' ') as productioncontrol,
> ISNULL(fileacknowledged, ' ') as fileacknowledged,
> ISNULL(customercontact, ' ') as customercontact
> from customertracker
> ";
> }
>
> print FILE "$sql\n";
>
> $sth = $dbh->prepare($sql);
> $sth->execute();
>
> while( my @data = $sth->fetchrow_array)
> {
> my $txnid = $data[0];
> my $datestarted = $data[1];
> my $projectlead = $data[2];
> #print "$projectlead\n";
> my $status = $data[3];
> my $biller = $data[4];
> my $formatstatus = $data[5];
> my $unionid = $data[6];
> my $customercase = $data[7];
> my $enrollmentstatus = $data[8];
> my $routestatus = $data[9];
> my $complianceerrors = $data[10];
> my $mandateid = $data[11];
> my $custid = $data[12];
> my $formatname = $data[13];
> my $expectedstartdate = $data[14];
> my $expectedcompdate = $data[15];
> my $testfilename = $data[16];
> my $testfiledate = $data[17];
> my $trainingstatus = $data[18];
> my $kickoffstatus = $data[19];
> my $websitestatus = $data[20];
> my $paymentverification = $data[21];
> my $productioncontrol = $data[22];
> my $fileacknowledged = $data[23];
> my $customercontact = $data[24];
>
> ###inner queries for custid, payorid, and multistate
>
> $sql2 = "select [group], customer from customer where custid =
> '$custid'";
> #$sql2 = "select [group], customer from customer where custid = ?";
>
> my $sth2 = $dbh->prepare($sql2);
> #$sth2->execute($custid);
> $sth2->execute();
>
> while( my @row = $sth2->fetchrow_array)
> {
> $group = $row[0];
> #print "$group\n";
> $customername = $row[1];
> #print "$customername\n";
> }
>
> $sql3 = "select payorid, multistateid from payor where payid = (select
> payid from payortxn where mandateid = '$mandateid')";
> #$sql3 = "select payorid, multistateid from payor where payid =
> (select
> payid from payortxn where mandateid = ?)";
>
> my $sth3 = $dbh->prepare($sql3);
> $sth3->execute();
> #$sth->execute($mandateid);
>
>
> while( my @row = $sth3->fetchrow_array)
> {
> $payorid = $row[0];
> #print "here";
> print "$payorid\n";
>
> $multistateid = $row[1];
> print "$multistateid\n";
> }
>
> $multistateid = "<a
> href=http://eggpayorteam/cgi-bin/payorlist.cgi?msid="; . $multistateid .
> "&stat=payor>" . $multistateid . "</a>";
>
> if ($cnt%2 == 0)
> {
> $color = "bgcolor=#eeeecc";
> }
> else
> {
> $color = "bgcolor=#ffffff";
> }
>
>
>
> my %rows = ( num=>$cnt,
> color=>$color,
> datestarted=>$datestarted,
> projectlead=>$projectlead,
> status=>$status,
> biller=>$biller,
> formatstatus=>$formatstatus,
> unionid=>$unionid,
> customercase=>$customercase,
> enrollmentstatus=>$enrollmentstatus,
> routestatus=>$routestatus,
> complianceerrors=>$complianceerrors,
> formatname=>$formatname,
> expectedstartdate=>$expectedstartdate,
> expectedcompdate=>$expectedcompdate,
> custid=>$customername,
> group=>$group,
> payorid=>$payorid,
> multistate=>$multistateid,
> testfilename=>$testfilename,
> testfiledate=>$testfiledate,
> trainingstatus=>$trainingstatus,
> kickoff=>$kickoffstatus,
> websitestatus=>$websitestatus,
> customercontact=>$customercontact,
> );
>
>
>
> push (@list, \%rows);
>
>
>
>
>
> $cnt++;
>
> }
>
> return @list;
>
> }
>
>
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
