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

Can someone tell me what's wrong here.... it's driving me nuts ... please any help would be greatly appreciated 
#################################
use OLE; # use Win32::OLE if using the Std Distribution
$Conn = CreateObject OLE "ADODB.Connection";
$Conn->Open("Provider=SQLOLEDB;Data Source=coa-iisserver2;Initial Cata
+log=ETM;User Id=sa;Password=;");

$an = $Form{'Affiliate_Referrer'};
$ct = $Form{'Courtesy Title'};
$fn = $Form{'First Name'};
$ln = $Form{'Last Name'};
$ea = $Form{'Email Address'};
$wa = $Form{'Website Address'};
$cn = $Form{'Company Name'};
$ad = $Form{'Address'};
$ci = $Form{'City'};
$sp = $Form{'State/Province'};
$zc = $Form{'Zip/Postal Code'};
$co = $Form{'Country'};
$pn = $Form{'Phone Number'};
$ex = $Form{'Ext'};
$fa = $Form{'Fax'};
$re = $Form{'Reffered'};
$de = $Form{'Description'};
$ke = $Form{'Keywords'};

$Conn->Execute($sql);

$RS = $Conn->Execute("SELECT * FROM affiliate where Affiliate_Number="
+.$an);

if( $RS->EOF ) { 

        &error('bad_affiliate_number');

        }

        else {


$sql="update affiliate set Affiliate_Refferals=Affiliate_Refferals+1 w
+here Affiliate_Number=".$an;


$Conn->Execute($sql);

$sql = "INSERT INTO clients (Affiliate_Referrer,Courtesy_Title,First_N
+ame,Last_Name,Email_Address,Website_Address,Address,Company_Name,City
+,Postal_Code,State_Province,Country,Phone_Number,Ext,Fax,Reffered,Des
+cription,Keywords) 
    VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",$an,$ct,$fn,$ln,$ea,$wa,
+$cn,$ad,$ci,$sp,$zc,$co,$pn,$ex,$fa,$re,$de,$ke;


$RS->Close;
$Conn->Close;

#####################################
}
}

[download]

Edit by tye

Replies are listed 'Best First'.
Re: Undescribed problem with Win32::OLE
by VSarkiss (Monsignor) on Dec 18, 2001 at 23:18 UTC

    Using the incredibly powerful PSI::ESP module, I think I've figured out your problem. Your last $sql variable contains placeholders, but you can't bind values by just listing them after the string! Change that to something like this: 

    $sql = "INSERT INTO blah (blah, blah...)"
. "VALUES ($an, $ct, ...)";

    [download]
    which will interpolate the variables right into your VALUES clause. If any of them are strings, you'll have to put quotes around the value in the SQL string: "VALUES ('$str1', '$str2', $num)"And don't forget to Execute the resulting SQL. Right now it looks like you're just throwing the string away.

    This is all guesswork, BTW ;-) Hope it helps.

    Update
    tilly points out that the fact that I'm interpolating the values may be mis-interpreted as saying "don't use placeholders". You can continue to use placeholders rather than interpolating -- a better idea in general. You'd have to create ADO Command and Parameters objects, then Execute them. I haven't used ADO in Perl, and I don't even know if that's supported, so I'm not going to elaborate any further. In DBI-land it would be as easy as: 

    $sth = $dbh->prepare($sql) or warn(...);
$sth->bind_columns(\($an, $ct, ...));

    [download]

[reply]
[d/l]
[select]
What's the problem?
by dragonchild (Archbishop) on Dec 18, 2001 at 22:50 UTC
    It would help if you told us what you were seeing as a problem. Also, things like what version of Perl, what OS, and what the basic purpose of what you're doing ...

    The most obvious problem I'm seeing is that $sql is defined well after any time you use it.

    A few helpful pointers:

    • use strict and warnings. They will help catch a number of silly errors/typos that a human cannot see.
    • Try using print as a debugging tool. I notice you use $sql a lot. Are you sure you know what its value is at any given point?

    ------
    We are the carpenters and bricklayers of the Information Age.

    Don't go borrowing trouble. For programmers, this means Worry only about what you need to implement.

[reply]
[d/l]
[select]
Re: Can someone tell me what's wrong here.... it's driving me nuts ... please any help would be greatly appreciated
by runrig (Abbot) on Dec 18, 2001 at 22:50 UTC
    Can you describe what the problem is?? You seem to have a problem with that insert statement, and you don't seem to be even attempting to execute it. Could that be the problem?
[reply]
Re: Undescribed problem with Win32::OLE
by talexb (Chancellor) on Dec 19, 2001 at 00:38 UTC
    If your form field names and the database field names are the same (something I've always found handy) you can do something like this: 
      my $Form = new CGI;
  my $Vars = $Form->Vars;

  my @Fields =  qw(short_descr class_id long_descr contact_org contact
+_web chapter_id contact_name contact_phone contact_email est_value ot
+her comments);

  my $InsertCmd = "insert into item (" . join (",",@Fields) . ") value
+s(" .  join ( ",", map { "'$Vars->{ $_ }'" } @Fields ) . ")";

  $hDB->do ( $InsertCmd ) || die "DBI failed: $DBI::errstr";

    [download]
    This takes fields from the form, builds an insert statement and executes it. This was one of those situations where map was really called for.

    (In the interest of brevity I've not included all of the error checking, connecting to the database, various comments etc.)

    "Excellent. Release the hounds." -- Monty Burns.

[reply]
[d/l]
Re: Undescribed problem with Win32::OLE
by MiRaGe 508 (Novice) on Dec 19, 2001 at 01:29 UTC
    you are all kings of kinds ;) i got it to work ... thanx for the help ... well appreciated!
[reply]

Back to Seekers of Perl Wisdom