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+"’ "
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
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.
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
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
Hello guys I have a similar problem. I have a Execute NonQuery activity that updated records on database, after this I need use a first NonQuery parameter to update in another database table. I add other Execute NonQuery with a second store procedure. When run the robot, the first query isn’t update all fields.
I try join the two store procedures and I have the same problem. Anyone think what happened? Thanks for the support
Anil_G
ashokkarale
jinal.shah
Gautham_Pattabiraman
postwick
chandreshsinh.jadeja
vrdabberu
Ajay_Mishra
sven.wullum1
Vyshnavi_Nalumachu