[SOLVED] Export Data in SQLlite

massimopasquali

Hello,

I'm developing an APP with xamarin, I have the necessity to create a codeunit to export same tables like "Customer", "Item", "Sales Header", "Sales Lined", etc

Have you same expirieces aout it? what do you suggest me?

bye

kylehardin

Are you trying to copy data from NAV into SQLLite, or do you just want the NAV data to be available to your Xamarin app?

massimopasquali

Hi, I wanto to copy data from NAV into SQLlite DB.

I done that function, but at line "IF(SQLliteCommand.ExecuteNonQuery() = 1) then" it gives me an error said me ""A call to System.Data.SQLite.SQLiteCommand.ExecuteNonQuery failed with this message: unknown error\nInsufficient parameters supplied to the command"}"

can you suggest me somethings?

CreateDBSqlLite()
BEGIN
DBFileName := 'MobileFashion.sqlite';
DBFilePath := 'C:\\Users\\massimo\\Desktop\\DBSQLliteMOBILE\\';
IF(EXISTS(DBFileName) = TRUE) THEN
FILE.ERASE(DBFileName);
DBFileName := Path.Combine(DBFilePath, DBFileName);
SQLliteConnection := SQLliteConnection.SQLiteConnection();
SQLliteConnection.CreateFile(DBFileName);
DBConnectionString := 'Data Source=' + DBFileName + '; Version=3';
SQConnect := SQLliteConnection.SQLiteConnection(DBConnectionString);
SQConnect.Open();

Sql.ADDTEXT('CREATE TABLE Customer (ID INTEGER, No_ TEXT, Name TEXT, Name2 TEXT, [Search Name] TEXT,');
Sql.ADDTEXT('Contact TEXT, [Country_Region Code] TEXT, Address TEXT, City TEXT, lat TEXT, lon TEXT,');
Sql.ADDTEXT('[E-Mail] TEXT, [Post Code] TEXT, [Phone No_] TEXT, [VAT Registration No_] TEXT, [Payment Terms Code] TEXT,');
Sql.ADDTEXT('[Payment Method Code] TEXT, [Global Dimension 1 Code] TEXT, [Global Dimension 2 Code] TEXT, [Salesperson Code] TEXT, [WEB No_] TEXT,');
Sql.ADDTEXT('PRIMARY KEY (ID));');
SQLliteCommand := SQLliteCommand.SQLiteCommand(Sql,SQConnect);
SQLliteCommand.ExecuteNonQuery();
SQLParameter := SQLParameter.SQLiteParameter();

CLEAR(Sql);

// Get Recordset
QryCustomer.SETFILTER(Salesperson_Code, '=%1','079');
QryCustomer.OPEN();
WHILE QryCustomer.READ DO BEGIN
Sql.ADDTEXT('INSERT INTO Customer (');
Sql.ADDTEXT(' [No_], ');
Sql.ADDTEXT(' Name,');
Sql.ADDTEXT(' Name2,');
Sql.ADDTEXT(' [Search Name],');
Sql.ADDTEXT(' Contact, ');
Sql.ADDTEXT(' Address, ');
Sql.ADDTEXT(' [Country_Region Code], ');
Sql.ADDTEXT(' City, ');
Sql.ADDTEXT(' Lat, ');
Sql.ADDTEXT(' Lon, ');
Sql.ADDTEXT(' [E-Mail], ');
Sql.ADDTEXT(' [Post Code], ');
Sql.ADDTEXT(' [VAT Registration No_], ');
Sql.ADDTEXT(' [Payment Terms Code], ');
Sql.ADDTEXT(' [Payment Method Code], ');
Sql.ADDTEXT(' [Global Dimension 1 Code], ');
Sql.ADDTEXT(' [Global Dimension 2 Code], ');
Sql.ADDTEXT(' [Salesperson Code], ');
Sql.ADDTEXT(' [WEB No_] )');
Sql.ADDTEXT(' VALUES (@No,@Name,@Name2,@SearchName,@Contact,@Address,@CountryRegionCode,@City,@Lat,@Lon,@Email,@PostCode,@VAT,@PaymentTermsCode,@PaymentMethodCode,@GlobalDimensionCode1,@GlobalDimensionCode2,@SalespersonCode,@WebNo)');

SQLliteCommand := SQLliteCommand.SQLiteCommand(Sql,SQConnect);

SQLParameter := SQLliteCommand.CreateParameter();

// AGGIUNGO I PARAMETRI
SQLParameter.ParameterName := '@No';
SQLParameter.SourceColumn('No_');
SQLParameter.Value := QryCustomer.No;

SQLParameter.ParameterName := '@Name';
SQLParameter.SourceColumn('Name');
SQLParameter.Value := QryCustomer.Name;

SQLParameter.ParameterName := '@Name2';
SQLParameter.SourceColumn('Name_2');
SQLParameter.Value := QryCustomer.Name_2;

SQLParameter.ParameterName := '@SearchName]';
SQLParameter.SourceColumn('[Search Name]');
SQLParameter.Value := QryCustomer.Search_Name;

SQLParameter.ParameterName := '@Contact';
SQLParameter.SourceColumn('Contact');
SQLParameter.Value := QryCustomer.Contact;

SQLParameter.ParameterName := '@Address';
SQLParameter.SourceColumn('Address');
SQLParameter.Value := QryCustomer.Address;

SQLParameter.ParameterName := '@CountryRegionCode';
SQLParameter.SourceColumn('[Country_Region Code]');
SQLParameter.Value := QryCustomer.Country_Region_Code;

SQLParameter.ParameterName := '@City';
SQLParameter.SourceColumn('City');
SQLParameter.Value := QryCustomer.City;

SQLParameter.ParameterName := '@Lat';
SQLParameter.SourceColumn('Lat');
SQLParameter.Value := '000000000000000';

SQLParameter.ParameterName := '@Lon';
SQLParameter.SourceColumn('Lon');
SQLParameter.Value := '000000000000000';

SQLParameter.ParameterName := '@Email';
SQLParameter.SourceColumn('[E-Mail]');
SQLParameter.Value := QryCustomer.E_Mail;

SQLParameter.ParameterName := '@PostCode';
SQLParameter.SourceColumn('[Post Code]');
SQLParameter.Value := QryCustomer.Post_Code;

SQLParameter.ParameterName := '@VAT';
SQLParameter.SourceColumn('[VAT Registration No_]');
SQLParameter.Value := QryCustomer.VAT_Registration_No;

SQLParameter.ParameterName := '@PaymentTermsCode';
SQLParameter.SourceColumn('[Payment Terms Code]');
SQLParameter.Value := QryCustomer.Payment_Terms_Code;

SQLParameter.ParameterName := '@PaymentMethodCode';
SQLParameter.SourceColumn('[Payment Method Code]');
SQLParameter.Value := QryCustomer.Payment_Method_Code;

SQLParameter.ParameterName := '@GlobalDimensionCode1';
SQLParameter.SourceColumn('[Global Dimension 1 Code]');
SQLParameter.Value := QryCustomer.Global_Dimension_1_Code;

SQLParameter.ParameterName := '@GlobalDimensionCode2';
SQLParameter.SourceColumn('[Global Dimension 2 Code]');
SQLParameter.Value := QryCustomer.Global_Dimension_2_Code;

SQLParameter.ParameterName := '@SalespersonCode';
SQLParameter.SourceColumn('[Salesperson Code]');
SQLParameter.Value := QryCustomer.Salesperson_Code;

SQLParameter.ParameterName := '@WebNo';
SQLParameter.SourceColumn('[WEB No_]');
SQLParameter.Value := QryCustomer.WEB_No;

IF(SQLliteCommand.ExecuteNonQuery() = 1) then
ERROR('pippo')
ELSE
ERROR('pluto');
END;
QryCustomer.CLOSE();
SQConnect.Close();
END