SQL Stored procedure into a datagrid

I'm having trouble getting some data into a datagrid. It works fine
for the results of the first SELECT in the stored procedure, but not
therafter.
Here's the error I get:
"A field or property with the name 'contactUID' was not found on the
selected datasource"
Relevant code below - any help woudl be appreciated.


I have this sql stored procedure:
CREATE PROCEDURE [dbo].[PC_Return_Project]

@pID int

AS

Select projName, pc_projects.freqID, requestedDeadline,
pc_projects.priorityID, dateProposed, pc_projects.statusID,
description, basecampURL, priority, freqtype, Status
from pc_projects
inner join pc_keyPriority kP on
pc_projects.priorityID=kP.priorityID
and pID = @pID
inner join pc_keyFrequency kF on pc_projects.freqID=kF.freqID
inner join pc_keyStatus kS on pc_projects.statusID=kS.statusID

Select contactUID
from PC_projectContact
where pID = @pid

Select analystUID
from PC_projectAnalyst
where pID = @pID

Select audID, audience
from PC_projectAudience
inner join pc_keyAudience kA on PC_projectAudience.audID=kA.audienceID
where projID = @pID
GO


And this method in C#:
public DataTable GetProjectTable (int pID)
{


SqlCommand cmdGetProject = new SqlCommand("PC_Return_Project", conn);
cmdGetProject.CommandType = CommandType.StoredProcedure;
cmdGetProject.Parameters.Add("@pID", pID);

conn.Open();
SqlDataAdapter daProject = new SqlDataAdapter(cmdGetProject);
DataTable dtProject = new DataTable();
daProject.Fill(dtProject);
conn.Close();

return dtProject;

}

I'm calling the method here:
int pID = (int) Session["pID"];

DataDealings sql = new DataDealings();

dgProject.DataSource = sql.GetProjectTable(pID);
dgProject.DataBind();


and this on the GUI:
<Columns>
<asp:BoundColumn HeaderText="Project Name" DataField="projName" />
<asp:BoundColumn HeaderText="Description" DataField="description" />
<asp:BoundColumn HeaderText="Frequency" DataField="freqType" />
<asp:BoundColumn HeaderText="Priority" DataField="priority" />
<asp:BoundColumn HeaderText="Date Proposed" DataField="dateProposed"
/>
<asp:BoundColumn HeaderText="Requested Deadline"
DataField="requestedDeadline" />
<asp:BoundColumn HeaderText="BaseCamp URL" DataField="basecampURL" />
<asp:BoundColumn HeaderText="Contact" DataField="contactUID" />
</Columns>

re: SQL Stored procedure into a datagrid

Amy wrote:[color=blue]
> I'm having trouble getting some data into a datagrid. It works fine
> for the results of the first SELECT in the stored procedure, but not
> therafter.
> Here's the error I get:
> "A field or property with the name 'contactUID' was not found on the
> selected datasource"
> Relevant code below - any help woudl be appreciated.
>
>
> I have this sql stored procedure:
> CREATE PROCEDURE [dbo].[PC_Return_Project]
>
> @pID int
>
> AS
>
> Select projName, pc_projects.freqID, requestedDeadline,
> pc_projects.priorityID, dateProposed, pc_projects.statusID,
> description, basecampURL, priority, freqtype, Status
> from pc_projects
> inner join pc_keyPriority kP on
> pc_projects.priorityID=kP.priorityID
> and pID = @pID
> inner join pc_keyFrequency kF on pc_projects.freqID=kF.freqID
> inner join pc_keyStatus kS on pc_projects.statusID=kS.statusID
>
> Select contactUID
> from PC_projectContact
> where pID = @pid
>
> Select analystUID
> from PC_projectAnalyst
> where pID = @pID
>
> Select audID, audience
> from PC_projectAudience
> inner join pc_keyAudience kA on PC_projectAudience.audID=kA.audienceID
> where projID = @pID
> GO[/color]

This stored procedure will return *multiple* resultsets. Since a
DataTable can only hold one resultset, only the first resultset will be
in your dtProject.

[snip][color=blue]
> and this on the GUI:
> <Columns>
> <asp:BoundColumn HeaderText="Project Name" DataField="projName" />
> <asp:BoundColumn HeaderText="Description" DataField="description" />
> <asp:BoundColumn HeaderText="Frequency" DataField="freqType" />
> <asp:BoundColumn HeaderText="Priority" DataField="priority" />
> <asp:BoundColumn HeaderText="Date Proposed" DataField="dateProposed"
> />
> <asp:BoundColumn HeaderText="Requested Deadline"
> DataField="requestedDeadline" />
> <asp:BoundColumn HeaderText="BaseCamp URL" DataField="basecampURL" />
> <asp:BoundColumn HeaderText="Contact" DataField="contactUID" />
> </Columns>[/color]

This GUI attempts to find columns named projName, description, ...,
contactUID in the DataTable it is bound to. However, there is no column
contactUID in the first resultset from your SP, so there is no such
column in the DataTable these columns are binding to, hence the error.

To fix: I can't be sure, but it looks from your SP as though the first
SELECT returns multiple rows? And you want to display them with the
contactUID (which will be the same for all the rows?) in the last
column. In which case you could just add a subquery:

, (Select contactUID from PC_projectContact where pID = @pid)

to the SELECT list of the first query in the SP and that will do the
trick.


Just to reiterate, if you want to get at the later resultsets of the
SP, you will need to change something, as at the moment GetProjectTable
returns a DataTable, which can only hold one resultset.

--
Larry Lard
Replies to group please

re: SQL Stored procedure into a datagrid

Thanks, that helps.

Unfortunately, it still doesn't solve the problem - the first SELECT
only returns one result, but the ContactUID returns multiple
(that is, 1 Project to several ContactUIDs).

I added the subquery, and was chided for this ("Subquery returned more
than 1 value. This is not permitted when the subquery follows =, !=,
<, <= , >, >= or when the subquery is used as an expression.")

Is there a way around this?

Thanks,

--Amy


On 19 Oct 2005 08:52:25 -0700, "Larry Lard" <larrylard@hotmail.com>
wrote:
[color=blue]
>
>Amy wrote:[color=green]
>> I'm having trouble getting some data into a datagrid. It works fine
>> for the results of the first SELECT in the stored procedure, but not
>> therafter.
>> Here's the error I get:
>> "A field or property with the name 'contactUID' was not found on the
>> selected datasource"
>> Relevant code below - any help woudl be appreciated.
>>
>>
>> I have this sql stored procedure:
>> CREATE PROCEDURE [dbo].[PC_Return_Project]
>>
>> @pID int
>>
>> AS
>>
>> Select projName, pc_projects.freqID, requestedDeadline,
>> pc_projects.priorityID, dateProposed, pc_projects.statusID,
>> description, basecampURL, priority, freqtype, Status
>> from pc_projects
>> inner join pc_keyPriority kP on
>> pc_projects.priorityID=kP.priorityID
>> and pID = @pID
>> inner join pc_keyFrequency kF on pc_projects.freqID=kF.freqID
>> inner join pc_keyStatus kS on pc_projects.statusID=kS.statusID
>>
>> Select contactUID
>> from PC_projectContact
>> where pID = @pid
>>
>> Select analystUID
>> from PC_projectAnalyst
>> where pID = @pID
>>
>> Select audID, audience
>> from PC_projectAudience
>> inner join pc_keyAudience kA on PC_projectAudience.audID=kA.audienceID
>> where projID = @pID
>> GO[/color]
>
>This stored procedure will return *multiple* resultsets. Since a
>DataTable can only hold one resultset, only the first resultset will be
>in your dtProject.[/color]
[color=blue]
>To fix: I can't be sure, but it looks from your SP as though the first
>SELECT returns multiple rows? And you want to display them with the
>contactUID (which will be the same for all the rows?) in the last
>column. In which case you could just add a subquery:
>
>, (Select contactUID from PC_projectContact where pID = @pid)
>
>to the SELECT list of the first query in the SP and that will do the
>trick.
>
>
>Just to reiterate, if you want to get at the later resultsets of the
>SP, you will need to change something, as at the moment GetProjectTable
>returns a DataTable, which can only hold one resultset.[/color]

re: SQL Stored procedure into a datagrid

Well, this is where my lack of knowledge about the database shows :)
What you need to do is modify the SP appropriately (using SQL Query
Analyzer or whatever) until it returns the results you want: that is,
it returns what you want to bind into the displayed table as the
*first* resultset. Then your databinding code will 'just work'.


Amy wrote:[color=blue]
> Thanks, that helps.
>
> Unfortunately, it still doesn't solve the problem - the first SELECT
> only returns one result, but the ContactUID returns multiple
> (that is, 1 Project to several ContactUIDs).
>
> I added the subquery, and was chided for this ("Subquery returned more
> than 1 value. This is not permitted when the subquery follows =, !=,
> <, <= , >, >= or when the subquery is used as an expression.")
>
> Is there a way around this?
>
> Thanks,
>
> --Amy
>
>
> On 19 Oct 2005 08:52:25 -0700, "Larry Lard" <larrylard@hotmail.com>
> wrote:
>[color=green]
> >
> >Amy wrote:[color=darkred]
> >> I'm having trouble getting some data into a datagrid. It works fine
> >> for the results of the first SELECT in the stored procedure, but not
> >> therafter.
> >> Here's the error I get:
> >> "A field or property with the name 'contactUID' was not found on the
> >> selected datasource"
> >> Relevant code below - any help woudl be appreciated.
> >>
> >>
> >> I have this sql stored procedure:
> >> CREATE PROCEDURE [dbo].[PC_Return_Project]
> >>
> >> @pID int
> >>
> >> AS
> >>
> >> Select projName, pc_projects.freqID, requestedDeadline,
> >> pc_projects.priorityID, dateProposed, pc_projects.statusID,
> >> description, basecampURL, priority, freqtype, Status
> >> from pc_projects
> >> inner join pc_keyPriority kP on
> >> pc_projects.priorityID=kP.priorityID
> >> and pID = @pID
> >> inner join pc_keyFrequency kF on pc_projects.freqID=kF.freqID
> >> inner join pc_keyStatus kS on pc_projects.statusID=kS.statusID
> >>
> >> Select contactUID
> >> from PC_projectContact
> >> where pID = @pid
> >>
> >> Select analystUID
> >> from PC_projectAnalyst
> >> where pID = @pID
> >>
> >> Select audID, audience
> >> from PC_projectAudience
> >> inner join pc_keyAudience kA on PC_projectAudience.audID=kA.audienceID
> >> where projID = @pID
> >> GO[/color]
> >
> >This stored procedure will return *multiple* resultsets. Since a
> >DataTable can only hold one resultset, only the first resultset will be
> >in your dtProject.[/color]
>[color=green]
> >To fix: I can't be sure, but it looks from your SP as though the first
> >SELECT returns multiple rows? And you want to display them with the
> >contactUID (which will be the same for all the rows?) in the last
> >column. In which case you could just add a subquery:
> >
> >, (Select contactUID from PC_projectContact where pID = @pid)
> >
> >to the SELECT list of the first query in the SP and that will do the
> >trick.
> >
> >
> >Just to reiterate, if you want to get at the later resultsets of the
> >SP, you will need to change something, as at the moment GetProjectTable
> >returns a DataTable, which can only hold one resultset.[/color][/color]