CODEWITHSUNDEEP
c#asp.netsql
Green_qaue
Green_qaue

Reputation: 3661

Insert selected values from one Table to another

I am trying to get the information from my table that contains info about my books, and store it into a database called Orders when you press "Add to cart". But I am having trouble figuring out how to do this. Ive searched all over and tried everything I can think of, so there is obv something I dont know. Can you please help me correct this statement to give it the decired effect?

SqlCommand cmd2 = new SqlCommand("INSERT INTO Orders (ISBN, Title, Author, Price)"+ 
                                 "SELECT ISBN, Title, Author, Price FROM BooksInfo"+
                                 "WHERE BooksInfo.ISBN=@Id", con);
cmd2.Parameters.AddWithValue("@Id", Id);
cmd2.ExecuteNonQuery();

Upvotes: 1

Views: 146

Answers (5)

D Stanley
D Stanley

Reputation: 152594

Well it doesn't look like you've gotten very far but you need spaces at the end of your strings when you concatenate

                         // note addition of space to end of each string fragment
SqlCommand cmd2 = new SqlCommand("INSERT INTO Orders (ISBN, Title, Author, Price) "+ 
                                 "SELECT ISBN, Title, Author, Price FROM BooksInfo "+
                                 "WHERE BooksInfo.ISBN=@Id", con);

Otherwise your SQL statement would be:

INSERT INTO Orders (ISBN, Title, Author, Price)SELECT ISBN, Title, Author, Price FROM BooksInfoWHERE BooksInfo.ISBN=@Id

Which would be a syntax error since there is no whitespace between the column list and the SELECT keyword

Some other things I see:

  • Is there not an OrderID on the Orders table?
  • Why do you need the Title and Author in the Order table? Ideally those should be foreign keys to BooksInfo
  • Shouldn't Order Items be in a separate table? (what if someone orders two books?)

Upvotes: 2

Christos
Christos

Reputation: 53958

Try this one:

SqlCommand cmd2 = new SqlCommand("INSERT INTO Orders (ISBN, Title, Author, Price) "+ 
                                 "SELECT ISBN, Title, Author, Price "+
                                 "FROM BooksInfo "+
                                 "WHERE BooksInfo.ISBN=@Id", con);

Upvotes: 1

Rob Aston
Rob Aston

Reputation: 816

You don't currently have the @Id parameter in the insert statement so the ID parameter is redundant.

Also, to insert the values you would need the corresponding Values (@ISBN, @Title, @Author, @Price) statement with the Insert statement. Then you will also need the parameters added with value in a similar fashion to how you have done the ID one.

As per the comment below, the SQL would become:

insert into DatabaseA..Orders (ISBN, Title, Author, Price) 
SELECT ISBN, Title, Author, Price from DatabaseB..BooksInfo
where BooksInfo.ISBN = @Id

if you wanted to control the databases from C# then you can add these in as part of the string instead of hard coding them.

Upvotes: 0

arbo77
arbo77

Reputation: 599

it's about string concatenation. 

SqlCommand cmd2 = new SqlCommand("INSERT INTO Orders (ISBN, Title, Author, Price)"+ 
                                 "SELECT ISBN, Title, Author, Price FROM BooksInfo"+
                                 "WHERE BooksInfo.ISBN=@Id", con);

SqlCommand will produce 

INSERT INTO Orders (ISBN, Title, Author, Price)SELECT ISBN, Title, Author, Price FROM BooksInfoWHERE BooksInfo.ISBN=@Id

instead 

INSERT INTO Orders (ISBN, Title, Author, Price)
SELECT ISBN, Title, Author, Price FROM BooksInfo
WHERE BooksInfo.ISBN=@Id

you can add space or linebreak after price) and BooksInfo

Upvotes: 0

curiousity
curiousity

Reputation: 4741

Why not separate this task on two different tasks 1. SELECTing values from one database and put them into some object orderCart; 2. INSERTing or update values in other database with the values of orderCart Firstly - it is more easy to debug and understand a code in that case

Upvotes: 0

Related Questions