Rectangle 27 0

You need CLR stored procedure to do this and in pure Delphi is impossible to create the assembly. However you can use Delphi Prism to develop it. Check out this article written by RRUZ.

about Delphi Prism: why should I buy Delphi Prism when I can use for free the visual studio Express version of c#? It is really true that in the Delphi world we are ready to pay for everything while in the other "worlds" they are free or at least also free.

@user193655 - in that case use .NET; you've been asking how to do it in Delphi :)

Is it possible to create a SQL Server UDF with Delphi for win32? - Sta...

sql-server delphi sql-server-2008 user-defined-functions
Rectangle 27 0

qry.parameters.parambyname('a').value := unassigned;

I tried this but i get the error 'Parameter object is improperly defined. Inconsistent or incomplete information was provided.'

Insert NULL with ADO from Delphi to SQL Server 2000 - Stack Overflow

delphi sql-server-2000 ado
Rectangle 27 0

qry.parameters.parambyname('a').Clear;

Insert NULL with ADO from Delphi to SQL Server 2000 - Stack Overflow

delphi sql-server-2000 ado
Rectangle 27 0

Do not write null; it is nil:

<TAdoDataSet>.FieldByName('a').AsVariant:= Nil;

Insert NULL with ADO from Delphi to SQL Server 2000 - Stack Overflow

delphi sql-server-2000 ado
Rectangle 27 0

add in uses --> Variants;

after you can replace with theses lines

qry.parameters.parambyname('a').value := Null;
qry.parameters.parambyname('b').value := Null;

Insert NULL with ADO from Delphi to SQL Server 2000 - Stack Overflow

delphi sql-server-2000 ado
Rectangle 27 0

First to open the sdf file you must use a provider compatible with the version of the sdf file. since you mention in your comments the version 3.5 you must use this provider Microsoft.SQLSERVER.CE.OLEDB.3.5

Then you must ensure which the provider is installed

{$APPTYPE CONSOLE}

{$R *.res}

uses
  ActiveX,
  ComObj,
  AdoDb,
  SysUtils;

procedure Test;
Var
  AdoQuery : TADOQuery;
begin
  AdoQuery:=TADOQuery.Create(nil);
  try
    AdoQuery.ConnectionString:='Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\Datos\Northwind.sdf';
    AdoQuery.SQL.Text:='Select * from Customers';
    AdoQuery.Open;
    While not AdoQuery.eof do
    begin
      Writeln(Format('%s %s',[AdoQuery.FieldByName('Customer ID').AsString,AdoQuery.FieldByName('Company Name').AsString]));
      AdoQuery.Next;
    end;
  finally
    AdoQuery.Free;
  end;
end;

begin
 try
    CoInitialize(nil);
    try
      Test;
    finally
      CoUninitialize;
    end;
 except
    on E:EOleException do
        Writeln(Format('EOleException %s %x', [E.Message,E.ErrorCode]));
    on E:Exception do
        Writeln(E.Classname, ':', E.Message);
 end;
 Writeln('Press Enter to exit');
 Readln;
end.

It was indeed installed, but for the provider it set for me the provider "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5", which is not a provider, "Microsoft.SQLSERVER.CE.OLEDB.3.5" is. When I implimented a line similar to the "AdoQuery.ConnectionString:='Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\Datos\Northwind.sdf';" it put the correct provider and did not yell at me that time. Thanks for the help.

sql server ce - Connecting to a SQL Compact file (.sdf) using an ADO c...

delphi sql-server-ce
Rectangle 27 0

qry.parameters.parambyname('a').AsVariant:= Null;
<TAdoDataSet>.FieldByName('a').AsVariant:= Null;

Yes, it's rigth. I'm overlooking the property. It must be the 'FieldByName'

Insert NULL with ADO from Delphi to SQL Server 2000 - Stack Overflow

delphi sql-server-2000 ado
Rectangle 27 0

you can replace with theses lines

qry.parameters.parambyname('a').value := Null;
qry.parameters.parambyname('b').value := Null;

Insert NULL with ADO from Delphi to SQL Server 2000 - Stack Overflow

delphi sql-server-2000 ado
Rectangle 27 0

Use an ADOQuery, Write a "Select" for your table. I use that a lot.

Select * From MyTable Where 0=1

And double click on the ADOquery component. A field list window will be appeared. Add all fields (right click). This way you can use your fields within your Delphi code.

MyTable.Open;
MyTable.Append;
MyTableAFieldFromMyTable.AsInteger := 0;
MyTableAStringFieldFromMyTable.AsString := 'Hello, World';
MyTable.Post;
MyTableAnotherFieldFromMyTable.Clear;

will set the value to NULL as we know it in SQL server. Edit: I just checked a working code. I clear the field Video from a table in following code.

Ornek.Open;
Ornek.Edit;
OrnekVideo.Clear;

Query is just "select * from Ornek Where 0=1" and I do the assignments in Delphi code using fields' .AsInteger, .AsString etc properties.

I just checked some working code of mine and a method named clear exists. Did you just add fields and use the field's methods? I mean not tables or parameters. Just write a select statement, write Delphi code to fill the field values instead of writing insert statements to the query component. ADO will do the rest and it will automatically create an insert query behind.

Maybe mine is different, If i check the class it says TCustomADODataSet. Sorry but I am a somewhat newb.

You should use TADOQuery instead. It is clever enough to create Delete and Insert queries from a Select query and this process is totally transparent to the programmer.

Insert NULL with ADO from Delphi to SQL Server 2000 - Stack Overflow

delphi sql-server-2000 ado
Rectangle 27 0

ExecSQL
AdoQuery.Active
True
AdoQuery.Open
SELECT

After changing your code we see the real error which is DB_E_OBJECTOPEN.

After digging deeper it seems that this is a known bug in the OLE DB provider and nvarchar fields bigger than 127 characters.

Thanks for the advice. Replacing adoQuery.ExecSQL with adoQuery.Open got rid of the "Multiple-step" error but replaced it with a new error that reads: "Object was open" adoQuery.Active := True gives the same result.

After replacing the adoQuery.ExecSQL with your suggested replacements, my sample code gives the "Object was open" error. In an effort to simplify things and try to dig a little deeper, I created a new project that creates a TADOQuery object, sets the ConnectionString, sets the SQL text, then opens the TADOQuery object (instead of using the TADOConnection object). The results are the same, when the application tries to open the TADOQuery object, it returns the "Object was open" error.

That was it. The table had a column that was set to 500 characters. After reducing the column to 127 characters I was able to run the code with no errors. Adjusting the column to 128 characters caused the error to return. I really appreciate you finding this for me. Unfortunately, I'm going go have to go back to the drawing board because a limitation like this is not acceptable. I just want to go on the record and state that it is bad enough that the limitation is there, but it is also pretty pathetic that the error message was completely misleading. Thanks again for your help.

Getting Delphi 7 to play with SQL Server Compact 3.5 - Stack Overflow

delphi sql-server-ce delphi-7 sql-server-ce-3.5
Rectangle 27 0

qry.parameters.parambyname('a').value := Null;
qry.parameters.parambyname('b').value := Null;

Null is a special type of Variant (uses Variants), which defines an empty value (has no type).

I tried this but i get the error 'Parameter object is improperly defined. Inconsistent or incomplete information was provided.'

Insert NULL with ADO from Delphi to SQL Server 2000 - Stack Overflow

delphi sql-server-2000 ado