Re^2: Inserting values into MS Access

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.

Comment on Re^2: Inserting values into MS Access Select or Download Code

Replies are listed 'Best First'.
Re^3: Inserting values into MS Access by davidrw (Prior) on Jun 06, 2005 at 16:43 UTC
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]
Re^4: Inserting values into MS Access by blackadder (Hermit) on Jun 06, 2005 at 21:16 UTC
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]