Help with column Insert

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'll show my code, and the bold is explaining what I want inserted and where from. I'd apprechiate if someone could help me with syntax etc. There are 2 about getting value from another table and one about just putting in 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

If 10 people are to receive the message, are there 10 message records or one?

If one, how are the 10 usernames supposed to be formatted? comma-separated values, separated by semi-colons?

|||

I was hoping to have 1 record, with a large list of usernames. I assume it will work if they are seperated by anything, but with a space - peoples messages are called up if the recievername column has their username in it. Will it work if it also has other writing (i.e. other peoples usernames)?

Thanks

Jon

|||

jbear123:

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);

I just took the time toreally pay attention to what you are doing in business terms instead of just the coding issues.

This is a collection's application? If you sent out a message with my name on it, telling hundreds of other people I hadn't paid up, I would be really angry.

Angry enough to tell you impolite things and never do business with you again.

Depending upon what country you live in or are sending the messages to, it might even be illegal. Particularly if you made a mistake and they did not owe you anything - that would be libel under US law.

That said, I have one other question. The payment instructions are tied to the user who is logged into the page, not the user receiving the message? That was a bit surprising. Or are they tied to the user receiving the message?

As for filling in the values, you just need a few strings to store them in.

Issue a query to get the list of usernames and loop thru them. Use the StringBuilder to concatenate them.

Issue another query to get the payment instructions.

|||

david wendelken:

As for filling in the values, you just need a few strings to store them in.

Issue a query to get the list of usernames and loop thru them. Use the StringBuilder to concatenate them.

Issue another query to get the payment instructions.

This is what I was after. Would you mind explaining this a bit more throughly for someone of a lower coding ability..? i.e. how do I issue the query to get the list of usernames? Compared to the code I posted, how would it be structured? Concatenate them?

Re: Legal issues - Thank you for your concern, but you shouldn't worry about it.

Thanks for your help!

Jon

|||

david wendelken:

As for filling in the values, you just need a few strings to store them in.

Issue a query to get the list of usernames and loop thru them. Use the StringBuilder to concatenate them.

Issue another query to get the payment instructions.

This is what I was after. Would you mind explaining this a bit more throughly for someone of a lower coding ability..? i.e. how do I issue the query to get the list of usernames? Compared to the code I posted, how would it be structured? Concatenate them?

Re: Legal issues - Thank you for your concern, but you shouldn't worry about it.

Thanks for your help!

Jon

|||

You already know how to issue a sql command via the SqlCommand object. Instead of an update command, you need to issue a query:

My guess as to your query would be "select distinct usersname from transactions where itemid = @.itemid"

You will have to pass in the itemid as a parameter, and issue an ExecuteQuery instead of an ExecuteNonQuery. You will be putting the results of the query into a DataReader and looping thru it. Just google or look it up any pretty much any asp book. This is basic, beginner level stuff and it's well documented all over the place - so I'm not going to do it again. :)

Inside the DataReader loop, you can concatenate the usernames you return into a string. It's best to use the StringBuilder class when you are looping. Again, google or look up StringBuilder in the manual.

I think you would be better served slogging thru this step yourself rather than having someone hand it to you. You'll learn it better, and this is bread-and-butter code that you'll use all the time, so it needs to be second nature. I'll keep an eye out on this thread in case you get stuck someplace.

|||

Hi,

I think I have the idea - could you confirm this for me:

I keep the insert commands, but before them I create a command to select all the info that I want inserted (from different tables etc), then using the insert command reference the results of the select command info?

If thats not what yopu meant, would that work anyway?

Thanks,

Jon

|||

I hope it will work as I have done it now..I created datalists showing the data I want to be inserted.

The only thing I cant do is link the results of the datalist (where i selected the data I want(visible=false)) etc to the add parameter section.

E.g:

command.Parameters.AddWithValue("@.sendername", System.Web.HttpContext.Current.User.Identity.Name);
command.Parameters.Add("@.recievername",how do I link to results of the datalist here? I'd have to have a comma in between each result);
command.Parameters.AddWithValue("@.message", TextBox2.Text);
command.Parameters.AddWithValue("@.subject", TextBox3.Text);
command.Parameters.AddWithValue("@.date", DateTime.Now.ToString());

Please could you briefly explain, as soon as I know that I can apply it to problemsall over the page and my site will be done!

Thanks alot

Jon

|||

Before you get to this point, create a string to hold the receivername values.

Loop thru the datalist, and for each entry in the datalist, append the next receiver.

|||

I'll give it a go. Just briefly before I leave you alone for a while (sorry!)-

If I create a string, how do I do that for many values (i.e. many usernames),

By loop thru, you mean just use find the string on the datalist?

And what do you mean by append the next receiver? use: + 'next receiver'?

Cheers,

Jon

|||

Hi,

I have:

SqlCommand command = new SqlCommand();
command.Connection = con;

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);
DataView dv = SqlDataSource2.Select(DataSourceSelectArguments.Empty) as DataView;
string receivername = dv[0]["receivername"].ToString();
command.Parameters.AddWithValue("@.recievername", receivername);
command.Parameters.AddWithValue("@.message", TextBox2.Text);
command.Parameters.AddWithValue("@.subject", TextBox3.Text);
command.Parameters.AddWithValue("@.date", DateTime.Now.ToString());
command.ExecuteNonQuery();

con.Close();
command.Dispose();
return true;

so far. How can I make values seperate by commas?

Thanks,

Jon

sql