Unexpected error can't convert system.object to a blue prism data type

GiriGurijala — Thu, 28 Dec 2023 07:56:00 GMT

I intended to do the following

Connect to Blue prism data base from process studio
Execute certain query
Retrieve the output as collection able to connect successfully to database but facing below error at 'get collection ' stage-- Internal: Unexpected error can't convert system.object to a blue prism data type.

------------------------------
Giri Gurijala
------------------------------

RE: Unexpected error can't convert system.object to a blue prism data type

Michael_S — Thu, 28 Dec 2023 08:46:00 GMT

[Mod Note: Moved to Blue Prism RPA forum]

------------------------------
💙 Michael
(I'm part of the SS&C Blue Prism Community Team)
------------------------------

RE: Unexpected error can't convert system.object to a blue prism data type

LeonardoSQueiroz — Sat, 06 Jan 2024 21:34:00 GMT

Hello,

You will probably need to handle the output data type, see this article: https://community.blueprism.com/communities/community-home/digestviewer/viewthread?GroupId=145&MID=4443&CommunityKey=3743dbaa-6766-4a4d-b7ed-9a98b6b1dd01&tab =digestviewer

The solution is very similar.

Regards,

------------------------------
Leonardo Soares
RPA Developer Tech Leader
Bridge Consulting
América/Brazil
------------------------------

Re: RE: Unexpected error can't convert system.object to a blue prism data type

andber — Tue, 13 Aug 2024 07:12:22 GMT

I'm getting "Page not found"

Re: Unexpected error can't convert system.object to a blue prism data type

John__Carter — Tue, 13 Aug 2024 08:22:26 GMT

It's likely that there are data types from the DB that do not naturally match the BP datatypes, and some sort of manipulation is required. GUID is a typical example, it's everywhere in the DB and needs to be converted to make it output-able as the Text BP data type, e.g. CONVERT(NVARCHAR(36), ProcessID).

Re: Unexpected error can't convert system.object to a blue prism data type

smssubhani — Mon, 28 Oct 2024 20:47:21 GMT

I fixed my case by removing typeof(object) from red line below and I am able to get the result into BP Collection.

my code e.g.
foreach (var field in fields)
{
//if (!dataTable.Columns.Contains(field.Name) && !field.Name.StartsWith("@odata.etag"))
if (!dataTable.Columns.Contains(field.Name))
{
dataTable.Columns.Add(field.Name, typeof(object)); // Add column if it doesn't exist
}
}
break;

hope this will give you some idea.

Re: Unexpected error can't convert system.object to a blue prism data type

andber — Thu, 23 Jan 2025 08:52:04 GMT

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?

Re: Unexpected error can't convert system.object to a blue prism data type

andber — Thu, 23 Jan 2025 09:27:55 GMT

Actually. I figured my issue out.
Some of the columns had NULL values, which caused the issue.
I put a IFNULL(<column>,'') to change NULL to no value, before casting it to text, and that worked.

topic RE: Unexpected error can't convert system.object to a blue prism data type in Product Forum

Unexpected error can't convert system.object to a blue prism data type

RE: Unexpected error can't convert system.object to a blue prism data type

RE: Unexpected error can't convert system.object to a blue prism data type

Re: RE: Unexpected error can't convert system.object to a blue prism data type

Re: Unexpected error can't convert system.object to a blue prism data type

Re: Unexpected error can't convert system.object to a blue prism data type

Re: Unexpected error can't convert system.object to a blue prism data type

Re: Unexpected error can't convert system.object to a blue prism data type