I am also having this error.
I don't have "typeof(object)" anywhere in my code.
If I perform a simple Select * from <table>, it works fine.
And the query I'm using has worked fine many times before, and works fine in DB Browser for SQLite.
The query explicitly casts all fields as text to avoid type issues.
The codestage looks like this:
message = string.Empty;
success = false;
result = new DataTable();
using (SQLiteConnection dbConnection = new SQLiteConnection(string.Format("Data Source={0}", db)))
{
try
{
dbConnection.Open();
//Execute query and load result into the result datatable. Datatable will be empty if query is not a SELECT.
SQLiteCommand sqlc = new SQLiteCommand(query, dbConnection);
SQLiteDataReader r = sqlc.ExecuteReader();
result.Load(r);
// If not SELECT statement, get affected rows, else if select, print rows returned
bool contains = query.IndexOf("SELECT", StringComparison.OrdinalIgnoreCase) >= 0;
if(contains == false){
sqlc = new SQLiteCommand("SELECT CHANGES()", dbConnection);
r = sqlc.ExecuteReader();
while (r.Read())
{
//Return message for everything else than SELECT
message = string.Format("Query completed. Rows affected: {0}", r[0].ToString());
success = true;
}
}
else
{
//Return message for SELECT queries
message = string.Format("Query completed. Rows returned: {0}", result.Rows.Count);
success = true;
}
}
catch (Exception ex)
{
message = (ex.Message);
success = false;
}
dbConnection.Close();
}
And the query looks like this:
/*
##TABLE A## Is replaced by the Current Periode Voucher Row Analysis table
##TABLE B## Is replaced by the newest Sugar Report table
*/
SELECT
CAST( a.[Vou Type] AS TEXT) AS [Vou Type]
,CAST( a.[Function Group] AS TEXT) AS [Function Group]
,CAST( a.[Vou No] AS TEXT) AS [Vou No]
,CAST( a.[Row No] AS TEXT) AS [Row No]
,CAST( a.[Voucher Date] AS TEXT) AS [Voucher Date]
,CAST( a.[Year] AS TEXT) AS [Year]
,CAST( a.[Period] AS TEXT) AS [Period]
,CAST( a.[Year period key] AS TEXT) AS [Year period key]
,CAST( a.[Entry Date] AS TEXT) AS [Entry Date]
,CAST( a.[Entered By User Group] AS TEXT) AS [Entered By User Group]
,CAST( a.[Entered By User] AS TEXT) AS [Entered By User]
,CAST( a.[Approved By User Group] AS TEXT) AS [Approved By User Group]
,CAST( a.[Approved By User] AS TEXT) AS [Approved By User]
,CAST( a.[Correction] AS TEXT) AS [Correction]
,CAST( a.[Multi Company Voucher] AS TEXT) AS [Multi Company Voucher]
,CAST( a.[Simulation Voucher] AS TEXT) AS [Simulation Voucher]
,CAST( SUBSTR( a.[Account], 1, INSTR(a.[Account], ' - ') - 1) AS TEXT) AS [Account TMP]
,CAST( SUBSTR( a.[Account], INSTR(a.[Account], ' - ') + 3) AS TEXT) AS [Account Description TMP]
,CAST( SUBSTR( a.[Cost Center], 1, INSTR(a.[Cost Center], ' - ') - 1) AS TEXT) AS [Cost Center TMP]
,CAST( b.[Sugar project ID] AS TEXT) AS [Project Id Ax]
,CAST( a.[Amount] AS TEXT) AS [Amount]
,CAST( SUBSTR( a.[Operations], 1, INSTR(a.[Operations], ' - ') - 1) AS TEXT) AS [Operations TMP]
,CAST( SUBSTR( a.[Operations], INSTR(a.[Operations], ' - ') + 3) AS TEXT) AS [Operations Description TMP]
,CAST( SUBSTR( a.[Cost Center], INSTR(a.[Cost Center], ' - ') + 3) AS TEXT) AS [Cost Center Description TMP]
,CAST( SUBSTR( a.[Node], 1, INSTR(a.[Node], ' - ') - 1) AS TEXT) AS [Node TMP]
,CAST( SUBSTR( a.[Node], INSTR(a.[Node], ' - ') + 3) AS TEXT) AS [Node Description TMP]
,CAST( SUBSTR( a.[Project], 1, INSTR(a.[Project], ' - ') - 1) AS TEXT) AS [Project TMP]
,CAST( SUBSTR( a.[Project], INSTR(a.[Project], ' - ') + 3) AS TEXT) AS [Project Description TMP]
,CAST( SUBSTR( a.[Cust type], 1, INSTR(a.[Cust type], ' - ') - 1) AS TEXT) AS [Cust type TMP]
,CAST( SUBSTR( a.[Cust type], INSTR(a.[Cust type], ' - ') + 3) AS TEXT) AS [Cust type Description TMP]
,CAST( SUBSTR( a.[Asset group], 1, INSTR(a.[Asset group], ' - ') - 1) AS TEXT) AS [Asset group TMP]
,CAST( SUBSTR( a.[Asset group], INSTR(a.[Asset group], ' - ') + 3) AS TEXT) AS [Asset group Description TMP]
,CAST( SUBSTR( a.[F.Asset], 1, INSTR(a.[F.Asset], ' - ') - 1) AS TEXT) AS [F.Asset TMP]
,CAST( SUBSTR( a.[F.Asset], INSTR(a.[F.Asset], ' - ') + 3) AS TEXT) AS [F.Asset Description TMP]
,CAST( SUBSTR( a.[Currency], 1, INSTR(a.[Currency], ' - ') - 1) AS TEXT) AS [Currency TMP]
,CAST( SUBSTR( a.[Currency], INSTR(a.[Currency], ' - ') + 3) AS TEXT) AS [Currency Description TMP]
,CAST( a.[Currency Code] AS TEXT) AS [Currency Code]
,CAST( a.[Currency Rate] AS TEXT) AS [Currency Rate]
,CAST( a.[Conversion Factor] AS TEXT) AS [Conversion Factor]
,CAST( a.[Debit Amount] AS TEXT) AS [Debit Amount]
,CAST( a.[Credit Amount] AS TEXT) AS [Credit Amount]
,CAST( a.[Currency Debit Amount] AS TEXT) AS [Currency Debit Amount]
,CAST( a.[Currency Credit Amount] AS TEXT) AS [Currency Credit Amount]
,CAST( a.[Currency Amount] AS TEXT) AS [Currency Amount]
,CAST( a.[Debit Amount in Parallel Currency] AS TEXT) AS [Debit Amount in Parallel Currency]
,CAST( a.[Credit Amount in Parallel Currency] AS TEXT) AS [Credit Amount in Parallel Currency]
,CAST( a.[Amount in Parallel Currency] AS TEXT) AS [Amount in Parallel Currency]
,CAST( a.[Parallel Currency Rate] AS TEXT) AS [Parallel Currency Rate]
,CAST( a.[Parallel Curr Conv Factor] AS TEXT) AS [Parallel Curr Conv Factor]
,CAST( a.[Text] AS TEXT) AS [Text]
,CAST( a.[Quantity] AS TEXT) AS [Quantity]
,CAST( a.[Process Code] AS TEXT) AS [Process Code]
,CAST( SUBSTR( a.[Delivery Type], 1, INSTR(a.[Delivery Type], ' - ') - 1) AS TEXT) AS [Delivery Type TMP]
,CAST( SUBSTR( a.[Delivery Type], INSTR(a.[Delivery Type], ' - ') + 3) AS TEXT) AS [Delivery Type Description TMP]
,CAST( a.[Tax Code] AS TEXT) AS [Tax Code]
,CAST( a.[Proj Activity Seq No] AS TEXT) AS [Proj Activity Seq No]
,CAST( a.[Identity] AS TEXT) AS [Identity]
,CAST( a.[Name] AS TEXT) AS [Name]
,CAST( a.[Transaction Code] AS TEXT) AS [Transaction Code]
,CAST( a.[Corrected] AS TEXT) AS [Corrected]
,CAST( a.[Reference Series] AS TEXT) AS [Reference Series]
,CAST( a.[Reference Number] AS TEXT) AS [Reference Number]
,CAST( a.[Matching Date] AS TEXT) AS [Matching Date]
,CAST( a.[Matching Period] AS TEXT) AS [Matching Period]
,CAST( a.[Matching ID] AS TEXT) AS [Matching ID]
,CAST( a.[Settlement Date] AS TEXT) AS [Settlement Date]
,CAST( a.[Journal ID] AS TEXT) AS [Journal ID]
,CAST( a.[Sequence No] AS TEXT) AS [Sequence No]
,CAST( a.[Exclude from Periodical Capitalization] AS TEXT) AS [Exclude from Periodical Capitalization]
,CAST( b.[Case] AS TEXT) AS [Sugar Case]
,CAST( b.[Internal Project Status] AS TEXT) AS [Sugar status]
,CAST( b.[Segment] AS TEXT) AS [Sugar Segment]
,CAST( b.[Approved costs] AS TEXT) AS [Sugar Approved Cost]
,CAST( b.[Financially complete date] AS TEXT) AS [Fin.compl.date]
,CAST( '' AS TEXT) AS [Aktivert tidligere]
,CAST( '' AS TEXT) AS [Aktiveres]
,CAST( '' AS TEXT) AS [Object id]
FROM [##TABLE A##] AS a
LEFT JOIN(
SELECT DISTINCT
[IFS project ID]
,[Sugar project ID]
,[Internal Project Status]
,[Segment]
,[Financially complete date]
,[Approved costs]
,[Case]
FROM [##TABLE B##] /* Sugar Report */
) AS b
ON SUBSTR(a.[Project], 1, INSTR(a.[Project], ' - ') - 1) = b.[IFS project ID]
Does anyone have a clue?
