Problem inserting a record into SQL Server database from Delphi

My environment is Delphi 2006 using dbGo components connected to SQL Server 2008 R2 Express.

I am trying to insert a new record into a table using a standard INSERT as below
ALTER TABLE LoggerPrice DISABLE TRIGGER ALL;

INSERT INTO LoggerPrice (LoggerCode, GangCode, ForestCode, Compartment, Stand, LogQuality, Market, LogGrade, LogLength, Price)
VALUES ('BLACK', 'G1', 'HARBOU', '1', '1', 'EPULP', 'Domestic', 'ALL', 'ALL', 1.00);

ALTER TABLE LoggerPrice ENABLE TRIGGER ALL

Open in new window

This works fine from SQL Management Studio, but running the same code from Delphi produces an error stating "Field LoggerCode must contain a valid value" Despite the error the record is added correctly.

The code used is as follows
 if not (DataSet.State in [dsInsert]) then
     exit;
 LoggerCode:= DataSet.FieldByName('LoggerCode').AsString;
 GangCode:= DataSet.FieldByName('GangCode').AsString;
 ForestCode:= DataSet.FieldByName('ForestCode').AsString;
 Compartment:= DataSet.FieldByName('Compartment').AsString;
 Stand:= DataSet.FieldByName('Stand').AsString;
 LogQuality:= DataSet.FieldByName('LogQuality').AsString;
 Market:= DataSet.FieldByName('Market').AsString;
 LogGrade:= DataSet.FieldByName('LogGrade').AsString;
 LogLength:= DataSet.FieldByName('LogLength').AsString;
 Price:= DataSet.FieldByName('Price').AsFloat;
 DataSet.Cancel;
 DataSet.Close;
 DataModule1.tblLoggerPrice.MasterSource:= nil;
 DataModule1.ADOCommand1.CommandText:= 'ALTER TABLE LoggerPrice DISABLE TRIGGER ALL';
 DataModule1.ADOCommand1.Execute;
 DataModule1.ADOQuery4.Close;
 DataModule1.ADOQuery4.SQL.Clear;
 DataModule1.ADOQuery4.SQL.Add('INSERT INTO LoggerPrice (LoggerCode, GangCode, ForestCode, Compartment, Stand, LogQuality, Market, LogGrade, LogLength, Price)');
 DataModule1.ADOQuery4.SQL.Add('VALUES (''' + LoggerCode + ''', '
                                            + '''' + GangCode + ''', '
                                            + '''' + ForestCode + ''', '
                                            + '''' + Compartment + ''', '
                                            + '''' + Stand + ''', '
                                            + '''' + LogQuality + ''', '
                                            + '''' + Market + ''', '
                                            + '''' + LogGrade + ''', '
                                            + '''' + LogLength + ''', '
                                            + '''' + FloatToStr(Price) + ''')');
//showmessage(DataModule1.ADOQuery4.SQL.Text);
 try
   DataModule1.ADOQuery4.ExecSQL;
 except
 end;
 DataModule1.ADOCommand1.CommandText:= 'ALTER TABLE LoggerPrice ENABLE TRIGGER ALL';
 DataModule1.ADOCommand1.Execute;
 DataModule1.tblLoggerPrice.MasterSource:= DataModule1.dsLoggerGang;
 DataSet.Open;

Open in new window

Doing a simple post to the tTable, ignoring the above code produces an error "Key column information is insufficient or incorrect. Too many rows were affected by the update". Again despite the error the record is added correctly.

The design of the table is as follows where the first 9 columns are included in the primary key.
LoggerCode	varchar(6)	Unchecked
GangCode	varchar(3)	Unchecked
ForestCode	varchar(12)	Unchecked
Compartment	varchar(10)	Unchecked
Stand		varchar(6)	Unchecked
LogQuality	varchar(6)	Unchecked
Market		varchar(8)	Unchecked
LogGrade	varchar(12)	Unchecked
LogLength	varchar(7)	Unchecked
Price		money		Unchecked
UpdateUserId	varchar(50)	Checked
UpdateTime	datetime	Checked

Open in new window

steve_mvsclAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
I have basically 2 comments:

1): ALTER TABLE LoggerPrice DISABLE/ENABLE TRIGGER ALL

this should no be in a regular application. either triggers are enabled or disabled, because with this technique, you will get bad results in your data whenever you have concurrent access to the table...
so you should rethink the trigger part/code to keep it enabled all the time in a way or another.

2): as you run that statement in a dedicated SQL, close/clear etc, I presume that somehow it will not be really effective for the insert... give my above comment, I think this should not be at all in your code, but if you put all the 3 sql into 1 single DataModule1.ADOQuery, it might work out
0
Pierre CorneliusCommented:
I suspect this is your problem:

''' + LoggerCode + ''', '
should be
'''' + LoggerCode + '''',

I prefer to use QuotedStr(LoggerCode) but just my preference.

But a better approach would be to use parameters.

E.G.
ADOQuery1.sql.text:= INSERT INTO SomeTable(SomeField) values(:SomeValue);
ADOQuery1.parameters[0].value:= 'SomeValue';
or
ADOQuery1.parameters.ParamByName('SomeField').value:= 'SomeValue';
0
Geert GOracle dbaCommented:
Pierre ... a typo ?
ADOQuery1.parameters.ParamByName('SomeValue').value:= 'SomeValue';
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Pierre CorneliusCommented:
No Geert, what do you mean? It was just an example.
0
Sinisa VukSoftware architectCommented:
- agree with angelIII/a3 - you shou never disable triggers - better write them to avoid - that you want to avoid.
- always use quotedstr function for character fields - this function prepare correctly texts like:
Rock'n'roll
- simple use of ''''' would go to disaster.
- most common error is to use FloatToStr function - in countries where ',' (comma) is a decimal separator - your sql query will 'think' that you have more columns than you need...
... values(1,'Name', 'surname', 4,56, 5) //float number 4,56

Open in new window

I use custom function to avoid this - usage is the same....
function FloatToStrPt(f: Double):String;
begin
  Result:=FloatToStr(f);
  Result:=StringReplace(Result,',','.',[]);
end;

Open in new window

0
steve_mvsclAuthor Commented:
The real issue here is the fact that a false error message is generated - the query actually works both from the application and SQL Management Studio.

Similarly, using a simple "Post" of the DataSet also works except for a false error message.

The disabling of the trigger is a red herring as the trigger is used to record userId and dateTime when a record is updated - disabling the triggers is used in dozens of places throughout the application without problem
0
steve_mvsclAuthor Commented:
It seems that there is a bug somewhere in the post logic in the dbGo component - everything works correctly by using the following procedure in response to the onPostError event

procedure TLoggerMaintenanceFrm.tblLoggerPricePostError(DataSet: TDataSet; E: EDatabaseError; var Action: TDataAction);
begin
 if copy(E.Message, 1, 51) = 'Key column information is insufficient or incorrect' then
     begin
      DataSet.Cancel;
      Action:= daAbort;
      DataSet.Close;
      DataSet.Open;
     end
   else
     begin
       MessageDlg(E.Message, mtError, [mbOk], 0);
       Action:= daAbort;
     end;
end;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Geert GOracle dbaCommented:
it's not good practice to disable triggers, especially in a multi-user environment

if an insert actually requires the trigger to be enabled ... consider this wrong situation, which probably happens in your case

your app (called appA from here-on)
another app: (called appB from here-on)
appA > call disable trigger
appB > insert row (> non-consistent insert because trigger is not enabled)
appA > insert row
appB > insert row (> non-consistent insert because trigger is not enabled)
appA > disable trigger


you could easily test this:
create an app which continuously inserts records and then run your app while it's doing that

as A3 said: don't do that
0
steve_mvsclAuthor Commented:
Although it is not really a proper solution it does provide a work around solution
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.