I am trying to automate a report generation i want to get the input data from en excel sheet column and pass this in the SQL query
Steps Performed
Step 1 – Read the data from excel and store in datatable “account”
Step 2 - Database Connection done
Steps Failed
Step 3 - Use Execute Query and pass the unique emp_id in the sql query
Query : select * from emp where emp_id in (data from datatable)
Note : emp_id is of number data type in DB
Sheet :
AshwinS2
(Ashwin S)
2
Hi @Muhammad_Abdul_Basee
Use
“Select * from emp where emp_id='+Row(EmpID”).ToString+'"
Thanks
Ashwin S
“Row” which you have mentioned is it the output variable of the read range activity of excel
AshwinS2
(Ashwin S)
4
hi @Muhammad_Abdul_Basee
Yes based on for each i have passed it as it is
Thanks
Ashwin S
I am getting the below exception
Note : emp_id is a an number datatype in DB and i have multiple emp_id in sheet
RemoteException wrapping System.Data.OracleClient.OracleException: ORA-01722: invalid number
Server stack trace: at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc) at System.Data.OracleClient.OracleDataReader.ReadInternal() at System.Data.OracleClient.OracleDataReader.Read() at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping) at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) at System.Data.Common.DataAdapter.Fill(DataTable dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) at System.Data.Common.LoadAdapter.FillFromReader(DataTable dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler) at UiPath.Database.DatabaseConnection.ExecuteQuery(String sql, Dictionary`2 parameters, Int32 commandTimeout, CommandType commandType) at UiPath.Database.Activities.ExecuteQuery.<>c__DisplayClass38_0.b__0() at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object args, Object server, Object& outArgs) at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink replySink)Exception rethrown at [0]: at UiPath.Database.Activities.ExecuteQuery.EndExecute(AsyncCodeActivityContext context, IAsyncResult result) at System.Activities.AsyncCodeActivity.System.Activities.IAsyncCodeActivity.FinishExecution(AsyncCodeActivityContext context, IAsyncResult result) at System.Activities.AsyncCodeActivity.CompleteAsyncCodeActivityData.CompleteAsyncCodeActivityWorkItem.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)
Solution :
Step 1 – Read data from excel column and assign a output variable – Read Column Activity
Step 2 – Convert the ienumerated output variable to comma separated string – Assign Activity
Step 3 – Connect to database – Connect Activity
Step 4 – Pass the string variable to the extract query – Extract Sql
SQL – “select * from abasir.profile_correction09042019 where account_id in (” + strAccountID+ “)”
strAccountID – converted string variable
Attached Robot for details databaseConnection.zip (25.1 KB)
1 Like
system
(system)
Closed
7
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.