Database relationship issue when rendered after a SELECT (C#)

[cgoasduff]

Hi There,

I have been scouring the web for an asnwer on something I would have thought was quite common. Any help would be greatly appreciated.

I have a database which contain job ads/

I have one table "dbo.tbl_jobadvert" which contains the ad itself and another table "dbo.tbl_jobFiles" which contains the path of the document(s) uploaded in relations to the job advert ( application form, job description, etc )

dbo.tbl_jobadvert has this structure:

id, jobTitle,Salary,Organisation .....and a few other fields

dbo.tbl_jobFiles has this structure:

filepath, filename, id, VacancyRef (which is a foreign key from the dbo.tbl_jobadvert (id)) .....and a few other fields

I would like to query those tables and get the result in a datalist or datagrid which would display like that.

-----------------------------------------------------

Job Title: Job1

Salary: £25000

Job description: http://..whatever the path in the db is for this doc

Appliction form: http://..whatever the path in the db is for this doc

Job Profile: http://..whatever the path in the db is for this doc

==============================================================

My Problem is that currently I return this:

Job Title: Job1

Salary: £25000

Job description: http://..whatever the path in the db is for this doc

Job Title: Job1

Salary: £25000

Appliction form: http://..whatever the path in the db is for this doc

Job Title: Job1

Salary: £25000

Job Profile: http://..whatever the path in the db is for this doc

==============================================================

Here is my C# code behind:

protected void Page_Load(object sender, EventArgs e)
    {
        string connectionString = ConfigurationManager.ConnectionStrings["recruitmentHubConnectionString"].ConnectionString;

        // Initialise Connection
        conn = new SqlConnection(connectionString);
        comm = new SqlCommand("SELECT DISTINCT jobTitle,filePath,fileName,jobBand_Salary FROM dbo.tbl_JobAdvert, dbo.tbl_jobFiles WHERE dbo.tbl_jobFiles.VacancyRef = dbo.tbl_JobAdvert.id", conn);
        // Open the connection
        conn.Open();


        myDataReader = comm.ExecuteReader();

        //HERE ???        
        jobDetailsDL.DataSource = myDataReader;
        jobDetailsDL.DataBind();

    }

and the front end:

<asp:DataList ID="jobDetailsDL" runat="server">
        <ItemTemplate>
            <strong>Job Title:</strong>
            <asp:Label ID="jobTitleLabel" runat="server" Text='<%# Eval("jobTitle") %>'></asp:Label><br />
            <strong>Salary:</strong>
            <asp:Label ID="Label1" runat="server" Text='<%# Eval("jobBand_Salary") %>'></asp:Label><br />
            
            <asp:HyperLink ID="HyperLink2" NavigateUrl='<%# Eval("filePath")%>' Text='<%# Eval("fileName") %>' runat="server"></asp:HyperLink>
                     
        </ItemTemplate>
    </asp:DataList>

Thanks a lot

Chris

[dnanetwork]

Job Title: Job1..... from Database your Fetching jobTitle

Salary: £25000..... from Database your Fetching jobBand_Salary

// wot abt these lil puppets..

Job description: http://..whatever the path in the db is for this doc..... from Database your Fetching filepath

Appliction form: http://..whatever the path in the db is for this doc..... wot about this guy..?

Job Profile: http://..whatever the path in the db is for this doc..... and this guy..?

and your markup is 70% correct..

so you need to add more..

<ItemTemplate>
            <strong>Job Title:</strong>
            <asp:Label ID="jobTitleLabel" runat="server" Text='<%# Eval("jobTitle") %>'></asp:Label><br />
            <strong>Salary:</strong>
            <asp:Label ID="Label1" runat="server" Text='<%# Eval("jobBand_Salary") %>'></asp:Label><br />
            <strong>Job description:</strong>
            <asp:HyperLink ID="HyperLink2" NavigateUrl='<%# Eval("filePath")%>' Text='<%# Eval("fileName") %>' runat="server"></asp:HyperLink>

<strong>App Form:</strong>
            <asp:HyperLink ID="HyperLink3" NavigateUrl='<%# Eval("filePath")%>' Text='<%# Eval("fileName") %>' runat="server"></asp:HyperLink>
                     
<strong>Job Profile:</strong>
            <asp:HyperLink ID="HyperLink4" NavigateUrl='<%# Eval("filePath")%>' Text='<%# Eval("fileName") %>' runat="server"></asp:HyperLink>
        </ItemTemplate>

try this code all links will return the same path..
i think you need to focus on your query..!

[cgoasduff]

Hi dnanetwork

Thanks for replying, the problem is that one job can have one link , or many links.

I think I might have to count the number of url allocated for one ID and perhaps create the urls on the fly through a loop. but not too sure how to do.

The fact that it might be slightly complicated (for my level anyway) is not surprising but I really thought that returning multiple values depending of a common ID would be something quite common. Can't find any ressources. strange.

[dnanetwork]

if jobs have one or more than one link..
then you should create other table to manage them
after which you can map them easily..

and i'm sure u have not done that part..

fix your database, rest will be okey..

[cgoasduff]

If you read my database structure, that's what I have done:

I have one table "dbo.tbl_jobadvert" which contains the ad itself and another table "dbo.tbl_jobFiles" which contains the path of the document(s) uploaded in relations to the job advert ( application form, job description, etc )

dbo.tbl_jobadvert has this structure:

id, jobTitle,Salary,Organisation .....and a few other fields

dbo.tbl_jobFiles has this structure:

filepath, filename, id, VacancyRef (which is a foreign key from the dbo.tbl_jobadvert (id)) .....and a few other fields

Thanks

Chris

[dnanetwork]

give me some time

[cgoasduff]

Ok, much appreciated

[dnanetwork]

man your problem is wow..

[nick.crane]

I have not used ASP.Net but I do a lot with databases.
Your select statement retrieves data from both tables.
Try spliting in to two queries and put the returned data in to seperate DataLists.

Edit: Sorry, this will not work. I just realised what you are trying to do.

[nick.crane]

I have no experience with using it but perhaps the DetailsView is what you need.

[dnanetwork]

man your problem is wow..

but i've made the slight adjustment to your defination..

i created 2 tables for simple Demo...

user_info (user information)
id
fname

add_info (address information)
id
addid (act as refrence to user_info table)

output is like

fname cities
ashish city1,city2,city3,
ashar city6,city7,city3,
Gangs city5,city8,city3,

and the query is ...

SELECT a.*, (SELECT distinct cast(d.address + ',' as varchar(max))
FROM dbo.user_info AS c ,add_info as d
Where d.addid = a.id for xml path('')) cities
FROM dbo.user_info AS a

now loking at your case you are expecting filepath
which is bound to 3 other fields which you have mentioned earlier..

Job description: http://..whatever the path in the db is for this doc

Appliction form: http://..whatever the path in the db is for this doc

Job Profile: http://..whatever the path in the db is for this doc

will fit in the same row..

try the query..you can make lil modification and get the output..

hope that helps..

[cgoasduff]

I am going to try, thanks a lot for your efforts. I'll post to let you know how it goes.