SQL Stored procedure into a datagrid

A

Amy

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

Larry Lard

Amy said:
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
Click to expand...

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]
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>
Click to expand...

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

Amy

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


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
Click to expand...

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

Larry Lard

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

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


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
Click to expand...

This stored procedure will return *multiple* resultsets. Since a
DataTable can only hold one resultset, only the first resultset will be
in your dtProject.
Click to expand...
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.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

System.StackOverflowException 12
Getting multiple SELECTED values from stored procedure into an array? 4
GridView - Update using stored procedure 0
How to link to text files in datagrid? 2
DataGrid DropDown Template Column 1
datagrid - edit problem 2
"Cast" error with VB code in .aspx page, with a twist... 10
Dropdownlist in Datagrid 3

Top