ADO

Business Central/NAV Technical Forum
1

I am writing from Navision to a SQL Database using ADO 2.8. I can do this using ADOConnection.Execute but would like to do this using ADOCommand.Execute. Does anyone know how to instantiate the command object?

By the way ADOCommand.ActiveConnection := ADOConn.State; does not work.

Also what is the fastest way to insert records into SQL with ADO? Do I use ADORecordset.AddNew, ADOCommand.Execute(with an insert into query) or some other method?

2

Hi,

I have a set of Navision Objects that query the SQL system to get the DBCC Showcontig details into a newly created “Table” in sql server.

So the code creates a table in SQL, populates it with the DBCC Showcontig details, uses that to do a DBCC Reindex etc… then deletes it.

If that sounds like the kind of thing you are trying to acheive, PM me an email address and I will send you the objects.

I would need to look through it all and work out if I am able to publicly release it here before adding it to the downloads section etc…

/TH

3

What method are you using to get the records back into SQL? ADORecordSet.Addnew, Innsert Into or something else?

4

Hi, Sorry took so long to get back to you went on a last minute trip.

RecordsAffected := 0;

// Command Option Values:
// adCmdFile : 256 = Evaluate as a previously persisted file
// adCmdStoredProc : 4 = Evaluate as a stored procedure
// adCmdTable : 2 = Have the provider generate a SQL query and return all rows from the specified table
// adCmdTableDirect : 512 = Return all rows from the specified table
// adCmdText : 1 = Evaluate as a textual definition
// adCmdUnknown : 8 = The type of the CommandText parameter is unknown
// adCmdUnspecified : -1 = Default, does not specify how to evaluate

Options := 1;

IF ADOConnection.State = 1 THEN BEGIN
IF CurrentDB = ‘’ THEN
CurrentDB := GetCurrentDB;

ADOConnection.DefaultDatabase(CurrentDB);
ADOConnection.CommandTimeout(0); // Timeout set to indefinitely

ADOConnection.Execute(Statement,RecordsAffected,Options);
END ELSE
ERROR(Text002);

Try that.

/TH

5

use variant data type variable to cast type:

var_variant := adoConnection;
cmd.ActiveConnection := var_variant;
cmd.CommandText := ‘Select * …’;
cmd.Execute();

6

Moved to SQL Forum .