• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1187
  • Last Modified:

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

0
steve_mvscl
Asked:
steve_mvscl
  • 3
  • 2
  • 2
  • +2
1 Solution
 
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 GruwezOracle dbaCommented:
Pierre ... a typo ?
ADOQuery1.parameters.ParamByName('SomeValue').value:= 'SomeValue';
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Pierre CorneliusCommented:
No Geert, what do you mean? It was just an example.
0
 
Sinisa VukCommented:
- 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
 
Geert GruwezOracle 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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now