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

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..!

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.

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..

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

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.

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

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..

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