scope identity - invalid ParamType

ASKtec · Post by **ASKtec** » Mon 13 Sep 2010 15:56

Hello,

i am new with uniDAC since yesterday

Now i have a problem with scope identity.

I have a Test-Application with a Insert INTO -Statement with one parameter... It will work perfect:

Code: Select all

 UniQuery1.Active := false;
  UniQuery1.SQL.Clear;
  UniQuery1.SQL.Add('INSERT INTO test (name) VALUES (:name)');
  UniQuery1.Params.ParamByName('name').Value := Edit1.Text;
   UniQuery1.SQL.Add('SET :id = SCOPE_IDENTITY()');
   UniQuery1.Params.ParamByName('id').DataType := ftInteger;
   UniQuery1.Params.ParamByName('id').ParamType := ptInputOutput;
  UniQuery1.Execute;

  Label1.Caption := UniQuery1.ParamByName('id').AsString;

Then i tried the same in another application with a SQL-Statement with 50 Parameters. The statement will work until i add the following lines:

Code: Select all

   UniQuery1.SQL.Add('SET :id = SCOPE_IDENTITY()');
   UniQuery1.Params.ParamByName('id').DataType := ftInteger;
   UniQuery1.Params.ParamByName('id').ParamType := ptInputOutput;

Now i get this exception:

Parameter[51]:id - invalid ParamType (Status = 1h).

I use MS SQL 2005. What will here going wrong?

I tried the code from the Testapplication with the same Database from the other application. No error... i really don`t understand it...

After searching in the forum, i tried to put the line:

Code: Select all

UniQuery1.Params.ParamByName('id').ParamType := ptInputOutput;

into the "BeforeUpdateExecute" event handler. The exception will be gone, but the id will always 0

What can i do to find the failure?

Thanks a lot for your help...

Andy

ASKtec · Post by **ASKtec** » Tue 14 Sep 2010 08:26

Hi again,

i found, that setting a parameter value to NULL will end in the exception...

If i set one parameter like this:

Code: Select all

Params.ParamByName('testvalue').Value := NULL;

the exception will raise.

How can i set a Parameter to NULL?

Regards
Andy

Dimon · Post by **Dimon** » Tue 14 Sep 2010 09:10

The point is that the SCOPE_IDENTITY function returns the last identity value only in the same scope. Therefore to solve the problem you should use the following SQL to insert the record:

Code: Select all

  INSERT INTO test (name) VALUES (:name);
  SET :id = SCOPE_IDENTITY()

ASKtec · Post by **ASKtec** » Wed 15 Sep 2010 17:24

Hello,

thank you, that will work... but where is the difference to my method?
Is it not the same, when i call SQL.ADD twice before i call the Execute command?

Best regards
Andy

Dimon · Post by **Dimon** » Thu 16 Sep 2010 07:18

I could not reproduce the problem.
Please send me a complete small sample to dmitryg*devart*com to demonstrate it.

Also supply me the following information:
- the exact version of SDAC. You can see it in the About sheet of TMSConnection Editor;
- the exact version of your IDE;
- the exact version of SQL server and client. You can see it in the Info sheet of TMSConnection Editor.