By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,677 Members | 1,890 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,677 IT Pros & Developers. It's quick & easy.

SQL Stored procedure into a datagrid

P: n/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>

Nov 17 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a

Amy wrote:
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
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>


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

Nov 17 '05 #2

P: n/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
On 19 Oct 2005 08:52:25 -0700, "Larry Lard" <la*******@hotmail.com>
wrote:

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

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.

Nov 17 '05 #3

P: n/a

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:
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" <la*******@hotmail.com>
wrote:

Amy wrote:
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


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

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.


Nov 17 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.