Execute Non Query not inserting/updating all values

datatable
insert
query

#1

I am using Execute non query activity to insert or update data into a datatable. All data appears except for one column the data is not inserted or updated. I do not receive any errors from Ui Path. When I use the exact same activities in a single UiPath Project and not in my State Machine it works.

I let the values write out before the queries and there are all there. I copied the whole state machine in a new UiPath Project and it didnt work.

Has anyone experienced the same issues or knows about a reason for this?

Thanks a lot for your help!

Here the statements:

“INSERT INTO TABLE_NAME (VAR1,VAR2, VAR3) VALUES (’”+var1+"’,’"+var2+"’, ‘"+var3+"’) "

“UPDATE TABLE_NAME SET var1= '”+var1+"’ WHERE var2 = ‘"+var2+"’ AND var3= ‘"+var3+"’ "


#2

instead of inline query you can use stored procedure then it will easy for you,i think and in command type you can change commantype:“Text” to “StoredProcedure” it may helps you

Create Procedure Sp_test
(
@var1 varchar(50)=’’,
@var2 varchar(50)=’’,
@var3 varchar(50)=’’,
@flag varchar(10)=’’
)
as
begin
if(@flag=‘I’)
begin
insert into TABLE_NAME (VAR1,VAR2, VAR3) VALUES (@var1,@var2,@var3)
end
if(@flag=‘U’)
begin
UPDATE TABLE_NAME SET var1=@var1 WHERE var2 =@var2 AND var3= @var3
END
end


#3

Hi Sampad,

thanks for your advice. Can you explain me in more detail what “Stored/Procedure” does?
What would i need to change in the query? I cant find a helpful link to it.

Thanks


#4

Ok sure


#5

Hi LiB,

For Database/Backend what you used?Sql Server/Oracle

If it is sql server,log in to sql server
Create StoredProcedure by using this below query

Create Procedure Sp_test
(
@var1 varchar(50)=’’,
@var2 varchar(50)=’’,
@var3 varchar(50)=’’,
@flag varchar(10)=’’
)
as
begin
if(@flag=‘I’)
begin
insert into TABLE_NAME (VAR1,VAR2, VAR3) VALUES (@var1,@var2,@var3)
end
if(@flag=‘U’)
begin
UPDATE TABLE_NAME SET var1=@var1 WHERE var2 =@var2 AND var3= @var3
END
end

In For Connect to database use connect activity and use ExecuteQuery for Select and ExecuteNon Query for Update

Inside Stored Procedure we can write multiple queryi.e. insert,select,update,delete etc. at a time and can execute one by one and easy to under stand and more securable

You are writing multiple inline query instead of that you can use single store procedure.sqlstoreprocedure.xaml (9.5 KB)

In Execute query activities the command type should be StoredProcedure and in sql you will pass stored procedure name

i have attached the xaml file you can try it


#6

HI @sampad
.Net SqlClient Data Provider : Procedure or function ‘update_atmcomplaints’ expects parameter ‘@cardnumber’, which was not supplied.

i am getting this error

create procedure update_atmcomplaints
(
@cardnumber varchar(100),
@remarks varchar(100),
@status varchar(100)
)
as
begin
update atm_complaints
set[Remarks]=@remarks,[Status]=@status
where [Card_Number]=@cardnumber
end
GO
and this is my update procedure

i wanna update in the data base how can i

above is ss of my the paramters
pls help i am new in uipath

Thanks & Regards
Arjun Parashar


#7

Hi,
Can you check this .It may help you

Regards
Balamurugan.S


#8

Thanks @balupad14
For the help
Thanks
Arjun


#9

Nice… :slight_smile: