SQL Server Post Problem

Does "MyTable.Resync([])" or "MyTable.Resync([rmExact])" make any diff?  Do the rest of the fields (MyField1 & MyField2) contain the correct values?

JB

I am assuming that you know how to set up the auto increment field in the MSSQL table.  (Identity is checked)

Instead of doing the Append do this

Table1.InsertRecord(['', 10202, 'This is a good question']);
Table1.Refresh;

The Append inserts the record but then clears all the fields.  I get a "Field 'IncrementalVaule' need a value" error when I try it.  

Damn this doesn't exactly answer your question...it might get you by but I see a problem.  The value for the newly inserted record doesn't really become visible until you do the refresh on the table and that sets it back to the first record.  You could use bookmarks but that's not what you (or I) want. This kinda sucks because the autoincrement is probably your primary index for this so you can't do a FindKey to get back...Need to find a way to get that SQL server to tell us what the next value of the AutoIncrement is going to be.  I bet there is a way because in the help it has this for Identity:
Enables columns to contain system-generated values that uniquely identify each row within a table. When inserting values into a table that has an identity column, SQL Server automatically generates the next identifier based on the last used identity value and the increment value specified when the column was created.  

So I'd bet there is a way to ask the server for this info...maby try Mirco$haft's web page.

I'm only getting around 12 inserts a second with this code.  Does anyone know how to tell the MSSQL server to reset it's counter...like if you delete all the recodrs out...see it's the fact that it knows what that number is even when you delete out all the records that makes me think this value should have a access method...I'll try this insert with the query and see if it'll speed up.

procedure TForm1.Button1Click(Sender: TObject);
var
  i : integer;
begin
  Table1.DisableControls;
  for i := 1 to 100 do
  begin
    Table1.InsertRecord(['', 102002, 'Question is good']);
    Table1.Refresh;
    Table1.Last;
    Label1.Caption := IntToStr(Table1.FieldByName('IncrementalValue').AsInteger);
    Application.ProcessMessages;
  end;
  Table1.EnableControls;
end;

check out the IDENTITY Property help page in the Transact-SQL Reference Help...it has all kinds of stuff on how to read this value.  I think you'll have to use a query component...I'm still readding it myself.

It makes it much better to use the query
the following code never took more than 2 seconds

procedure TForm1.Button1Click(Sender: TObject);
var
  i : integer;
begin
  Table1.DisableControls;
  for i := 1 to 100 do
  begin
    Table1.InsertRecord(['', 102002, 'Question is good']);
    Query1.Active := False;
    Query1.Active := True;
    Label1.Caption := IntToStr(Query1.Fields[0].AsInteger);
    Application.ProcessMessages;
  end;
  Table1.EnableControls;
end;

I used SELECT MAX(IDENTITYCOL) FROM MyTestDB for my SQL property

I wonder if it's possible to figure out the value of @@IDENTITY with a query component?  Because I can do this

INSERT MyTestDB DEFAULT VALUES
select @@IDENTITY

with ISQL but I can't figure out how to read that value in DELPHI.

This is actually most annoying.
I just want to ask one small thing before I grade this question. I am not really happy but here is the question.

Are you guys sure that you cannot refresh the contents of the current table object using the BDE API. I have a suspicion that a solution like that would be better than the query option. I say this because then the database cursor would remain unmoved (on the current record). The TQuery solution could go wrong in severe multi user conditions. It's inherently dangerous.

If there is no other way, then I guess I'll have to settle for it.

Cmain,

   I've been working with Delphi since version 1.0 and have yet to see Borland come out with a fix for this bug. Believe me, I use Delphi for C/S app development in a Fortune 500 company and this bug annoys me a great deal. I have yet to be able to refresh only the "current" record because the "image" that exists in memory of the posted record (without the ID) does not match that of the record stored in SQL Server (with the new ID).

  The "cleanest" multiuser workaround I've found is a stored procedure on the server that gives ME the next available number, I slap in on the record, then post it. This assures me that the cached (client side) record and the posted SQL Server record are indeed the same. The mini-table containing the "next available number" is locked during the get/increment phase to insure no two people get the number.

Micro$oft has the stored procedure on thier website to address this issue.

It's not pretty, I'm not happy about it, but I play the hand that Borland & Micro$oft gave me. Good luck with your project and let me know if I can be of further assistance.

Pegasus

Pegasus,

Thanks for the response.
I have graded your answer. Perhaps you would be so kind as to mail me the URL for the work around stored procedure at the microsoft site, if you still have it.
email : craig-m@iafrica.com

I appreciate your response. Lets hold fingers for the Delphi 4.00 release.

Thanks
Kind Regards
Craig.

Craig,

   I'll mail you the URL for the stored procedure BUT...

I recently asked a "Record/Key Deleted" related question and was given a "fix" for it that seems to also have solved the autoincrement problem. It may be worthwhile for you to give it a try:

Set the BDE Sybase (or SQL Server) Language driver to Sybase SQL Dic850 in the BDE

Let me know if it improves your application's relationship with SQL Server, it certainly did on my project!

Regards,
Pegasus