in reply to Inserting values into MS Access

What is the error message from Win32::ODBC? Also, what are the values of $srv_name (i suspect bad quotes or something along those lines)? Try adding this debugging code (use print instead of warn if you prefer) around your Sql() call: 
my $sql = "INSERT INTO Servers (Server_Name) VALUES ('$srv_name')";
warn "SQL: " . $sql;
my $rc = $EDS_DB->Sql($sql);
warn "Return Code: '$rc'";
warn "Error String: " . $EDS_DB->Error;

[download]

Replies are listed 'Best First'.
Re^2: Inserting values into MS Access
by Anonymous Monk on Jun 06, 2005 at 16:23 UTC
    There is no errors from ODBC, But I am trying to insert Srv values obtained from the Master table into another table called Servers.

    Now, If I use this bit of the code 
    while ($EDS_DB->FetchRow())
{
        my %data = $EDS_DB->DataHash();
        $srv_name =  $data{"Server_Name"};
        $San = $data{"SAN"};
        $platform = $data{"Platform"};
        print qq(Server Name is $srv_name,$platform,$San\n);    
        
}

    [download]
    then I do get out all the data outputed from the Master table. But If is stick this line$EDS_DB->Sql("INSERT INTO Servers (Server_Name,Platform,SAN) VALUES ('$srv_name','$platform','$San')"); just before the the end of the loop (i.e. before the last curly bracket) then only one entry (i.e. record) will be inserted into my Servers table!!

    I hope this is clear.
[reply]
[d/l]
[select]
      Since only one record is inserted, my first suspicion is that the second item you try to insert is bad in some way, hence the need for the more detailed debugging above (especially the print'ing of $sql -- what does that show? if you paste directly into access does it work?)

      My second suspicion is that the nested Sql() call is destroying the outer recordset (which is also consistent w/your 1-insert observations). To validate this, rework your code to something like this: 
      my @data;
while ($EDS_DB->FetchRow()){
  my %data = $EDS_DB->DataHash();
  push @data, \%data;
}
# if @data doesn't have > 1 element here, check $EDB_DB->Error for the
+ reason
foreach my $data (@data){
  my $sql = sprintf "INSERT INTO Servers (Server_Name) VALUES ('%s')",
+ $data->{Server_Name};
  warn "SQL: " . $sql;
  my $rc = $EDS_DB->Sql($sql);
  warn "Return Code: '$rc'";
  warn "Error String: " . $EDS_DB->Error;
}

      [download]
[reply]
[d/l]
        Indeed, I can definitly see the value in this code. In fact I will try this code first to see if there are any problems with my source data....

        I will post the error, because I did get some, if i remember correctly!!!

        Humbled with your knowledge Sir....Thanks very much.
        Blackadder
[reply]

In Section Seekers of Perl Wisdom