0

Possible Duplicate:
SQL Server - Return value after INSERT

I'm a bit of beginner with SQL and SQL Server. I have a table with 2 columns.

CREATE TABLE [dbo].[Log](
    [FileName] [varchar](100) NOT NULL,
    [FileID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED 
(
    [FileID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

So if I insert a string to Filename a FileID is automatically generated. It works fine from SQL Studio but not from code.

function TfrmMainForm.SaveFileNameToDB(const aName: String): Integer;
var
  vResult: Variant;
begin
  SQLQuery.Close;
  SQLQuery.SQL.Text := Format('INSERT INTO Log (FileName) VALUES(''%s'')', [aName]);
  SQLQuery.ExecSQL;

  SQLQuery.Close;
  SQLQuery.SQL.Text := Format('SELECT FileID FROM Log WHERE FileName = ''%s''', [aName]);
  SQLQuery.Open;
  SQLQuery.First;
  vResult := SQLQuery.FieldValues['FileID'];
  if VarIsNull(vResult) then  
    Result := -1
  else
    Result := vResult;
end;

The result from this method is always -1. What is wrong here ?

Community
  • 1
  • 1
Roland Bengtsson
  • 4,970
  • 8
  • 56
  • 93
  • 3
    Please don't use injectable queries, use Parameters instead!!! http://stackoverflow.com/a/11834743/800214 – whosrdaddy Jan 15 '13 at 08:01
  • 1
    "VarIsNull(vResult)" does not mean there was no result - that is may be exactly the result. You'd check for `SQLQuery.Empty` or `SQLQuery.EOF` and `SQLQuery.BOF` – Arioch 'The Jan 15 '13 at 08:17
  • 1
    "SQLQuery.FieldValues['FileID'];" -> `SQLQuery.FieldByName('FileID').AsInteger` or even `SQLQuery.Fields[0].AsInteger` – Arioch 'The Jan 15 '13 at 08:18
  • 3
    Since there's no apparent `UNIQUE` constraint on `FileName`, your logic is going to be broken as soon as a duplicate file name is added. Your `SELECT` will return *multiple* rows, in some arbitrary order, and you'll get the `FileID` from the (undefined) first one. – Damien_The_Unbeliever Jan 15 '13 at 08:31
  • `Select IDENT_CURRENT('Log')` will return last inserted value of identity field of table Log, e.g. it will be last inserted FileId field value – kutsoff Jan 15 '13 at 13:21

0 Answers0