Insert query giving error, "Insert: One or more errors occurred. (Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.)"

[Ankit_Chauhan (Ankit Kumar)]

When I’m trying to insert data into MySQL using insert Activity then this error comes, Insert: One or more errors occurred. (Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.) (Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.)

My BOT is creating this data table:-

This is my MySQL Table:-

This is my Database flow:-

This error is coming:-

Please help me how can I solve this error?

[ashokkarale]

@Ankit_Chauhan,

Try using Run Command activity instead of Insert.

Activities - Run Command

[Ankit_Chauhan (Ankit Kumar)]

okay, but can you tell me how can I convert datatable into string SQL Query?

I have one data table, that I have to insert into mysql, so I’m using Insert activity, and directly passing datatable variable, but please tell me how can I convert datatable data into sql query as a string type variable?

[Anil_G (Anil Gorthi)]

@Ankit_Chauhan

Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information

@Ankit_Chauhan

This issue generally occurs when the table dors not have a peimary key column…can you set a primary key in table and check if the issue is resolved

Cheers

[Ankit_Chauhan (Ankit Kumar)]

okay, I’ll try this one, but when I’m reading same data from Excel file and inserting data into MySQL using Insert query then it is inserting but when BOT is generating same data using Build Data Table activity then error is coming.

[Anil_G (Anil Gorthi)]

@Ankit_Chauhan

Check the difference in column types for both excel and generating may be date format or others might be an issue

Cheers

[Ankit_Chauhan (Ankit Kumar)]

all column type is string using Build data table and MySQL column type is also varchar.

[Anil_G (Anil Gorthi)]

@Ankit_Chauhan

When you read data from excel the column type might not be same as your build datatable

Cheers

[ashokkarale]

@Ankit_Chauhan,

You can use For Each Row in dataTable Activity. Inside it use assign activity to get the insert query like this:

insertQuery = "INSERT INTO finalexcel (Txn_Date, Value_Date, Description, Ref_No, PP_NO, Debit, credit, Balance) " &
                 "VALUES ('" & row("Txn_Date").ToString() & "', '" &
                             row("Value_Date").ToString() & "', '" &
                             row("Description").ToString() & "', '" &
                             row("Ref_No").ToString() & "', '" &
                             row("PP_NO").ToString() & "', '" &
                             row("Debit").ToString() & "', '" &
                             row("credit").ToString() & "', '" &
                             row("Balance").ToString() & "')"

Use Run Command activity to insert one row by one

[Ankit_Chauhan (Ankit Kumar)]

After so many tries, I have got the solution to my problem, @ashokkarale Thanks for giving the Idea.

I have used the Assign activity to convert all column details into a String type of SQL query and pass this variable into the Run Command activity.

“INSERT INTO finalexcel (Txn_Date, Value_Date, Description, Ref_No, PP_NO, Debit, credit, Balance ) VALUES (”+“'”+CurrentRow(“Txn_Date”).ToString()+“',”+ “'”+CurrentRow(“Value_Date”).ToString()+“',”+ “'”+CurrentRow(“Description”).ToString()+“',”+ “'”+CurrentRow(“Ref_No”).ToString()+“',”+ “'”+CurrentRow(“PP_NO”).ToString()+“',”+ “'”+CurrentRow(“Debit”).ToString()+“',”+ “'”+CurrentRow(“credit”).ToString()+“',”+“'”+CurrentRow(“Balance”).ToString()+“')”

it will give the output like this:-

INSERT INTO finalexcel (Txn_Date, Value_Date, Description, Ref_No, PP_NO, Debit, credit, Balance ) VALUES (‘04-Jan-24’,‘04-Jan-24’,‘TO T-U/DR/456474372392/mayuri j/FINO/935858225@/Payme–’,‘T TO 4897692317377’,‘S99657483’,‘4224.00’,‘9737.00’,‘41466.11’)

Thank you so much all