asked on

select rows from db insert into one field in sql server db

Hey guys, what i have is a c# .net app, what i have is a table called classifieds_Ads table, in that table i need to select all records that correspond to the sessioned MemberId, this checks to see that the session MemberId is the same as an MemberId stored in classifieds_Ads, and for all records that correspond to that Id i need to select every field called Description, once thats done i need an automatic statement to insert all the description rows that correspond to MemberId into a field called MyStory in a table called classified_Story. So what i will have is one field that contains all the descriptions from classifieds_Ads from that member.

I dont know how to start writing this in .net, basically i need a button called "process" on a .net page that once pressed checks memberid and does all that above!?

Any idea?

Thanks!

inter

Would you please elaborate more on your schema? For example:
table classifieds_Ads (
MemberId int,
Description text
)
table classified_Story (
MemberId int,
MyStory text
)
so we pick all descriptions from a given member id and (possibly?) append all descriptions to form a single text field (and use CRLF pair to seperate them) and insert it into classified_Story. But then, duplication may occur; so shall we need to update MyStory if MemberId already exits in Story table?.. please give more info.

Ianaldo

ASKER

You have it exactly, except its ntext for both MyStory and Description, and duplication is fine to occur, as i will have it dated, which i want to insert a date into a field called MyStoryDate aswell inside classifieds_Story.

So how do i do this?

Cheers

Ianaldo

ASKER

I guess the best way maybe to find all descriptions by memberid in classifieds_ads with the codebehind, put all the descriptions into a string and then put that string into the MyStory field in classifieds_Story. But how?

Ianaldo

ASKER

I have this working so far, in which it displays the first row in the table into a label field in my aspx, but what i need it to do is to display is, all the rows not just the first one, then all i need to do is set up an insert statement...

protected void CurrentAdsButton_Click(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["classifiedsConnection"].ConnectionString);
 
        SqlCommand cmd = new SqlCommand("SELECT Description FROM classifieds_Ads", conn);
 
        string value;  
        try
        {
            conn.Open();
         
            value = cmd.ExecuteScalar().ToString();
 
            Category1.Text = value;
        }
        catch (Exception ex)
        {
            Response.Write(ex.ToString());           
        }
        finally
        {
            conn.Close();
        }  
    }

Open in new window

ASKER CERTIFIED SOLUTION

inter

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

inter

Hi, I had an argument order mismatch, please replace

"VALUES({0:d},'{3:yyyyMMdd HH:mm}',N'{2:s}')";

with

"VALUES({0:d},'{1:yyyyMMdd HH:mm}',N'{2:s}')";

in code snipped above.

Ianaldo

ASKER

Hi inter,

I had to fix a cuple of things there, but you mostly got it!

Thanks for your help, very appreciated! :)

inter

you are wellcome, and thanks for your kind words :)