Problem wwriting data to table

[amateursRus]

Hi Folks,

I'm having a problem writing to an Access table.
I have a reporting app (front end is Borland Delphi, back end is Access, connection via ADOX) which is behaving very strangely.
We have a process-based automated workflow system and the app is used to analyse and report on user workloads and throughput (among other things).
When a report is first run on a selected user everything works fine.
However, when a subsequent report is run, say for a different user, nothing is being written to the Access table.
I've spent the last two days following execution through line by line and have checked Connection, Query and RecordSet status and all report as OK.
The app is running the same code on each occasion and I'm pretty sure that the problem is at the Access table end.
Anyone come across a similar problem and if so how did you fix it?

ANY help most appreciated!!!

Regards

Steve

 

[lespaul]

what's your Delphi code for the update?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[amateursRus]

Hi,

Thanks for the quick response.
Apologies for not enclosing code at first instance.

This is where it all comes apart - please excuse all the junk in there but most of this is temp while I try to figure out what the problem is....

{ Write record from RecArray to AdoQuery (CareerTemp Table) }
{ ==================================================================== }
procedure TCareerRep.WriteFromRecArray(const RecArray: array of TRowRec;
const cntr, tRjctCount: integer);
var
i: integer;
begin

{i:= High(Recarray);
ShowMessage('RecArray count = ' + IntToStr(i));}

DataModule1.GetCareerTemp; // 'select * from CareerTemp..'

with DataModule1.ADOQuery1 do begin // AdoQ1 - CareerTemp table
Open;
First;
Insert;
{if (DataModule1.ADOQuery1.Connection.Connected) then
ShowMessage('AdoConnection1 is connected');}
{if (DataModule1.ADOQuery1.RecordsetState=[stOpen]) then} // TEST
//if (DataModule1.ADOQuery1.State=dsInsert) then
InsertRecord([RecArray[cntr].Processno,
RecArray[cntr].ProcName,
RecArray[cntr].UserID,
RecArray[cntr].isAdmin,
tRjctCount]);
//else ShowMessage('RecordSet not open'); // TEMP
i:= DataModule1.ADOQuery1.RecordCount;
ShowMessage('AdoQ1 - CareerTemp, record count = ' + IntToStr(i));
end;
DataModule1.UpdateAdoBatch;
end;

Hope this helps!

Regards

Steve

 

[lespaul]

I don't do much with the ADO queries, so bear with me.... The Open command runs the SQL query that is the default. Then you move to the First record (which is where it should be), then set the state of the dataset to Insert. Then you insert the record. Are you doing anything specific with the information you are retreiving from the query when you Open it?

How does this work for you (the SQL field names can be removed if these are ALL the fields in the table, but you'll have to make sure the names are correct, and add QuotedStr around the variables that are strings):

with DataModule1.ADOQuery1 do
begin
  SQL.Clear;
  SQL.Add('INSERT INTO [b]tablename[/b] (ProcessNo, ProcName, UserID, isAdmin, Count) VALUES (' + RecArray[cntr].Processno + ',' + QuotedStr(RecArray[cntr].ProcName) + ',' + RecArray[cntr].UserID + ',' + RecArray[cntr].isAdmin + ',' + tRjctCount);
  ExecSQL;
end;

I prefer this method because the SQL is actually in the code and you can see what is suppose to be happening instead of having to go to the ADOQuery component and look up the SQL.

HTH

leslie