Hi everyone,

I am trying to populate a gridview.

this is my table structure:
Modules - M_ID, M_title (this is where the module info is stored)
Module_course_link - MC_ID, M_ID, C_ID (this table links the Modules and Courses table)
Courses - C_ID, C_title (this is where the course info is stored)

this is where I get the C_ID
<code>string C_ID = Request.QueryString["C_ID"]; (this works)</code>

what I need help with is, to populate the gridview, at the moment I'm using this syntax,
I want the MC_ID to be the DataKeyName of the gridview, at the moment only M_title AS Modules are displayed.

How can I change the select query to do this and not return the error:
At most one record can be returned by this subquery.

AccessDataSource1.SelectCommand =
        "SELECT M_title AS Modules FROM Modules WHERE M_ID IN (Select MC_ID From Module_course_link Where C_ID= " + C_ID + ")";

Try to execute below modified query :

string C_ID = Request.QueryString["C_ID"]; (this works)

SELECT mcl.MC_ID as MC_ID, m.M_title as Modules 
From Modules m
Join Module_course_link mcl on mcl.M_ID = m.M_ID
Where
mcl.C_ID = C_ID

Now you can set MC_ID as DataKeyNames of your GridView..

Try by this and let us know..

Hi everyone,

I am trying to populate a gridview.

this is my table structure:
Modules - M_ID, M_title (this is where the module info is stored)
Module_course_link - MC_ID, M_ID, C_ID (this table links the Modules and Courses table)
Courses - C_ID, C_title (this is where the course info is stored)

this is where I get the C_ID
<code>string C_ID = Request.QueryString["C_ID"]; (this works)</code>

what I need help with is, to populate the gridview, at the moment I'm using this syntax,
I want the MC_ID to be the DataKeyName of the gridview, at the moment only M_title AS Modules are displayed.

How can I change the select query to do this and not return the error:
At most one record can be returned by this subquery.

AccessDataSource1.SelectCommand =
        "SELECT M_title AS Modules FROM Modules WHERE M_ID IN (Select MC_ID From Module_course_link Where C_ID= " + C_ID + ")";

Try to execute below modified query :

string C_ID = Request.QueryString["C_ID"]; (this works)

SELECT mcl.MC_ID as MC_ID, m.M_title as Modules 
From Modules m
Join Module_course_link mcl on mcl.M_ID = m.M_ID
Where
mcl.C_ID = C_ID

Thank you, but I received an error:
Syntax error in FROM clause.

AccessDataSource1.SelectCommand = "SELECT mcl.MC_ID as MC_ID, m.M_title as Modules From Modules m Join Module_course_link mcl on mcl.M_ID = m.M_ID Where mcl.C_ID = C_ID "

that's all...

Try to execute below modified query :

string C_ID = Request.QueryString["C_ID"]; (this works)

SELECT mcl.MC_ID as MC_ID, m.M_title as Modules 
From Modules m
Join Module_course_link mcl on mcl.M_ID = m.M_ID
Where
mcl.C_ID = C_ID

Thank you, but I received an error:
Syntax error in FROM clause.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.