I want to selected multiple records multiple times and copy to tables using ADO.NET

Question

User1909155429 posted

i have a list of parameter values that i want to pass on to query to abstract record sets from both a messages and images related table. Once selected save them into two other tables,namely: messages2 and images2 tables respectively. and them continue next loop until complete?

For Each p As String In products

GetRecords(p)

next

What is the recommended route to take?

Answer 1

User475983607 posted

What exactly are you having troubles with?  Writing the UI?  Posting multiple records?  Looping over multiple records? Writing ADO.NET/TSQL to save records?

Can you shared your code?

Answer 2

User1909155429 posted

I had not commenced writing the code to save data. I just had the collection of data that i needed to loop through in order to save to database.

Considering i had two tables to save multiple records too i wondered what the most effective method was to solve.

Answer 3

User475983607 posted

It's pretty simple... 

• Open a connection. 
• Loop over the collection and save the data in each loop iteration. 
• Close the connection.   

Inserting/updating two, three, n tables is not any more difficult than inserting into one table.  Is the problem the tables have a primary-foreign key relationship?

Can you at least try to write the code?  Post a question if you run into problem.

Answer 4

User1535942433 posted

Hi peterthegreat,

How do you save records into database?What's the relation of records and tables?

If you need to insert different tables,you could put all the statements into one command separated by semicolon.Just like this:

using (var connection = new SqlConnection(ConnectionString))
using (var command = connection.CreateCommand())
{
    connection.Open();
    command.CommandText = @"insert into Stock([Product]) values (@Product);
                        insert into LandhuisMisje([Product]) values (@Product);
                        insert into TheWineCellar([Product]) values (@Product);"
    command.Parameters.AddWithValue("@Product", AddProductTables.Text);
    command.ExecuteNonQuery()
}

Best regards,

Yijing Sun

Answer 5

User1909155429 posted

How is @Product,AddProductTables defined? never seen that approach before?

I have come up with another idea. instead of copying records from two tables, why dont i add a counter.

The reason is i added a drag and drop function to save both selected records and images.

If you know of a better solution i would like to know? in the meantime i shall experiment with the idea as there are many conditions attached and i dont know if it will work effectively as yet?

Answer 6

User475983607 posted

peterthegreat

If you know of a better solution i would like to know? in the meantime i shall experiment with the idea as there are many conditions attached and i dont know if it will work effectively as yet?

We do not understand why you are having trouble inserting records in the first place.  If you can provide sample code that would go a long way in helping us to help you.  We need items like the table schema and sample data.  Otherwise, we are left guessing what problem you are trying to solve.

Answer 7

User753101303 posted

Hi,

peterthegreat

instead of copying records from two tables, why dont i add a counter

peterthegreat

If you know of a better solution

The problem is that you never told which problem you are trying to solve.

If not using EF you are perhaps looking for https://www.w3schools.com/sql/sql_insert_into_select.asp or https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver15 if using SQL Server (it can help to keep track of which primary key in the target table table is matching which primary key in the source table and use that information to copy rows from the source child table to the target child table).

But first the big picture could help. At first it looked liked you were trying to implement a copy/paste operation user selected rows (for parent rows and then child rows).

Now it seems you are wondering if you could perhaps achieve your unkown goal without doing a copy of those rows to what appears maybe as work tables ???

Edit: from a previous post could it be that you are trying to implement a shopping cart?

Answer 8

User1909155429 posted

i experimented with a table counter  column and it is ok solution, although has limitations  for my purpose like deleting images but it is better than keep duplicating data every save operation!!

if i change my mind i shall get back to you.

Thanks for your time,much appreciated as ever. That's why i like this forum.