Column insert help

Hi,

I have some values I want put into a table, but the values are from other sources and I dont know how to retrieve them..

I'llshow my code, and the bold is explaining what I want inserted and wherefrom. I'd apprechiate if someone could help me with syntax etc. Thereare 2 about getting value from another table and one about just puttingin straight forward text..:

command.CommandText ="INSERT INTO Messages (sendername,recievername,message,Date,subject)VALUES (@.sendername,@.recievername,@.message,@.date,@.subject)";

command.Parameters.Add("@.sendername", System.Web.HttpContext.Current.User.Identity.Name)

command.Parameters.Add("@.recievername",every value of column named Usersname of the Transactions table, WHERE Itemid=Itemid in the gridview on this page);

command.Parameters.Add("@.message",the value of items table - column 'paymentinstructions' WHERE Username=System.Web.HttpContext.Current.User.Identity.Name);

command.Parameters.Add("@.subject",some text: IMPORTANT - Payment Required);

command.Parameters.Add("@.date", DateTime.Now.ToString());

command.ExecuteNonQuery();

Thanks alot if anyone can help me with those three things..

Jon

Pls help!

|||

command.Parameters.Add("@.recievername",every value of column named Usersname of the Transactions table, WHERE Itemid=Itemid in the gridview on this page);

Could you further explain that.

Thanks

|||

Hi, sorry not very well explained!

The bold writing means:

I have a talbe called 'Transactions' - and for every row in which Itemid = Itemid, the username is retrieved. So this could be 1 username, or many, depending on how many people have the same Itemid in their row. I guess the usernames would have to be separated by commas.

Thanks!

Jon

|||

command.CommandText = "INSERT INTO Messages (sendername,recievername,message,Date,subject) VALUES (@.sendername,@.recievername,@.message,@.date,@.subject)";

command.Parameters.Add("@.sendername", System.Web.HttpContext.Current.User.Identity.Name)

command.Parameters.Add("@.recievername",every value of column named Usersname of the Transactions table, WHERE Itemid=Itemid in the gridview on this page);

command.Parameters.Add("@.message",the value of items table - column 'paymentinstructions' WHERE Username=System.Web.HttpContext.Current.User.Identity.Name);

command.Parameters.Add("@.subject",some text: IMPORTANT - Payment Required);

command.Parameters.Add("@.date", DateTime.Now.ToString());

command.ExecuteNonQuery();

You are trying to do sub query's with your parameters. You cant do that. Also, I would think that you would want one record for each value. I would probably create a table with one record for each user, another Table (ex. Transactions with a UserId) with one record for each transactions and another table (ex. Items) with a TransactionId. If not you are going to have a hard time with your data. Then you could just simply do one select. Something like this:
SELECT ui.UserName, t.TransactionId, i.Item FROM UserId ui INNER JOIN Transactions t ON t.UserId = ui.UserID INNER JOIN Items i ON i.TransactionId = t.TransactionId WHERE ui.UserId = @.UserId

That would get you every record based on the userid with out having to do all that funky stuff. You dont really ever want to insert values(espcially with orders) by CSV's. Maintenance nightmare.

Does that make sense?

|||

Hi, I'll have to go through what you said and ask in more detail sorry.

What do you mean by sub query's?

What do you mean by one record for each value? Create a table for each value?

I will explain my scenario maybe it will help:

I have a message system within the site, and the method above is to send a 'bulk message' - that is sending the same message to many people.

My messages table has the columns @.sendername, @. recievername etc.. From this people check the messages through a formview which shoes the messages which reciever name is their username.

This mass message is sent by a user. He presses a button on a formview which contains an Itemid value. - then the button adds all the users in the transactions table which contain the same itemid value.

The second bold bit - this is to insert the value of the paymentinstructions (from 'items' table) of the user (who is sending the message).

Last bit - just universal text that gets inserted every time the same.

Hope this deeper explanation helps things, thanks for helping

Jon

|||

SO i would create a UserMessage(Or something like that) table that has a foreign key UserId in one table, and then create a second table called Messages which has a messageId (foreign key to UserMessage) and the other columns would be SenderName, ReceiverName, Message, PaymentInstructios etc...

Then you can get all messages by sending in one USerId and when you insert you can simply provide one UserId.

Make sense?

|||

Hi, before I try your method, can your hear this out -

I have a page with gridviews that retrieve the data that I want to insert - from 'SqlDataSource1' and 2 etc etc.

Can I just set the parameter to SqlDataSource1? Its on the same page..

I.e.:

command.CommandText = "INSERT INTO Messages (sendername,recievername,message,Date,subject) VALUES (@.sendername,@.recievername,@.message,@.date,@.subject)";
command.Parameters.AddWithValue("@.sendername", System.Web.HttpContext.Current.User.Identity.Name);
command.Parameters.AddWithValue("@.recievername", SqlDataSource2);
command.Parameters.AddWithValue("@.message", SqlDataSource3);
command.Parameters.AddWithValue("@.subject", TextBox1.Text);
command.Parameters.AddWithValue("@.date", DateTime.Now.ToString());
command.ExecuteNonQuery();

Thanks

Jon

|||

Yes, you can do it that way, but you are hitting the DB 3 times to retrieve the data you need. That is a sign of your tables not being normalized. I would think that you would want a one to many with Users and MessageId table and then a one to many table with MessageId table and Messages. This would allow you to only hit the db one time and retrieve the data you want.

|||

Hi thanks for your help. I think im nearly there so im going to post each individual error message that I have up, and see what people make of them!

Thanks again,

Jon