Column does not belong to table Error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Column does not belong to table Error

Author

Topic

naijacoder
Starting Member

5 Posts

Posted - 2005-05-07 : 20:58:03

Hello
I have such a Stored procedure below:-

CREATE PROCEDURE webpage
AS SELECT * FROM templates
SELECT * FROM links
SELECT * FROM category
SELECT * FROM pages
GO

And in my application below i'm using the stored procedure but after compiling i get the error
"Column 'link_url' does not belong to table links"
But if i comment out the string with SQL statement and code the SQL statement in my code it works..it seems my stored procedure isn't seeing the LINKS table.

IS MY STORED PROCEDURE CORRECT?:-

Any help will be appreciated!

-----------------------------
void Page_Load()
{
string csAccess = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];

SqlConnection dcAccess = new SqlConnection(csAccess);

SqlCommand cmdAccess = new SqlCommand("webpage",dcAccess);
cmdAccess.CommandType = CommandType.StoredProcedure;

//string ssTemplate = "Select * From templates Where template_id = 1";
//string ssLinks = "Select * From links";
//string ssCategory = "Select * From category";

string ssPage = "";

if (Request.QueryString["page"] == null)
{
ssPage = "Select * From pages Where page_id = 1";
}
else
{
ssPage = "Select * From pages Where page_id = " + Request.QueryString["page"];
}

//SqlDataAdapter daTemplate = new SqlDataAdapter(ssTemplate, dcAccess);

//SqlDataAdapter daLinks = new SqlDataAdapter(ssLinks, dcAccess);

//SqlDataAdapter daCategory = new SqlDataAdapter(ssCategory, dcAccess);

SqlDataAdapter daPage = new SqlDataAdapter(ssPage, dcAccess);

SqlDataAdapter daTemplate = new SqlDataAdapter(cmdAccess);
SqlDataAdapter daLinks = new SqlDataAdapter(cmdAccess);
SqlDataAdapter daCategory = new SqlDataAdapter(cmdAccess);
//SqlDataAdapter daPage = new SqlDataAdapter(cmdAccess);

DataSet dsAccess = new DataSet();
DataTable dtTemplate = new DataTable();
DataTable dtLinks = new DataTable();
DataTable dtCategory = new DataTable();
DataTable dtPage = new DataTable();
int dtLinksCount;
int dtCategoryCount;

try
{
dcAccess.Open();
daTemplate.Fill(dsAccess, "templates");
dtTemplate = dsAccess.Tables["templates"];
PageHeader.Text = dtTemplate.Rows[0]["template_header"].ToString();
PageFooter.Text = dtTemplate.Rows[0]["template_footer"].ToString();
daLinks.Fill(dsAccess, "links");
dtLinks = dsAccess.Tables["links"];
dtLinksCount = dtLinks.Rows.Count;
PageLinks.Text = "";
int iLoop;
for (iLoop=0; iLoop < dtLinksCount; iLoop++)
{
PageLinks.Text = PageLinks.Text + "<a href=";
PageLinks.Text = PageLinks.Text + dtLinks.Rows[iLoop]["link_url"];
PageLinks.Text = PageLinks.Text + ">";
PageLinks.Text = PageLinks.Text + dtLinks.Rows[iLoop]["link_text"];
PageLinks.Text = PageLinks.Text + "</a>";
if (iLoop < dtLinksCount-1)
{
PageLinks.Text = PageLinks.Text + " | ";
}
}

daCategory.Fill(dsAccess, "category");
dtCategory = dsAccess.Tables["category"];
dtCategoryCount = dtCategory.Rows.Count;
PageCategory.Text = "";
for (iLoop=0; iLoop < dtCategoryCount; iLoop++)
{
PageCategory.Text = PageCategory.Text + "<a href=";
PageCategory.Text = PageCategory.Text + dtCategory.Rows[iLoop]["category_url"];
PageCategory.Text = PageCategory.Text + ">";
PageCategory.Text = PageCategory.Text + dtCategory.Rows[iLoop]["category_text"];
PageCategory.Text = PageCategory.Text + "</a> ";
}

daPage.Fill(dsAccess, "pages");
dtPage = dsAccess.Tables["pages"];
PageContent.Text = "";
PageContent.Text = PageContent.Text + "<center>" ;
PageContent.Text = PageContent.Text + dtPage.Rows[0]["page_title"];
PageContent.Text = PageContent.Text + " ";
PageContent.Text = PageContent.Text + dtPage.Rows[0]["page_subtitle"];
PageContent.Text = PageContent.Text + " </center>";
PageContent.Text = PageContent.Text + dtPage.Rows[0]["page_text"];

if (Request.QueryString["category"] != null)
{
string ssCatPage = "Select page_id, page_title, page_subtitle From pages Where page_category = " + Request.QueryString["category"];
SqlDataAdapter daCatPage = new SqlDataAdapter(ssCatPage, dcAccess);
DataTable dtCatPage = new DataTable();
int dtCatPageCount;
daCatPage.Fill(dsAccess, "catpages");
dtCatPage = dsAccess.Tables["catpages"];
dtCatPageCount = dtCatPage.Rows.Count;
for (iLoop=0; iLoop < dtCatPageCount; iLoop++)
{
PageContent.Text = PageContent.Text + "";
PageContent.Text = PageContent.Text + "<a href=default.aspx?page=";
PageContent.Text = PageContent.Text + dtCatPage.Rows[iLoop]["page_id"];
PageContent.Text = PageContent.Text + ">";
PageContent.Text = PageContent.Text + dtCatPage.Rows[iLoop]["page_title"];
PageContent.Text = PageContent.Text + "</a> ";
PageContent.Text = PageContent.Text + dtCatPage.Rows[iLoop]["page_subtitle"];
PageContent.Text = PageContent.Text + "";
}
}

}
finally
{
dcAccess.Close();
}

}

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-09 : 16:36:56

Try using an explicit column list instead of select * in your stored procedure. Select * should not be used except for IF EXISTS and IF NOT EXISTS. Also, I'd recommend not returning 4 different result sets with one stored procedure. Instead, have four stored procedures that each return one result set.

Tara

naijacoder
Starting Member

5 Posts

Posted - 2005-05-10 : 01:23:54

Thanks Tara for the reply.
the select * was just an example..
I was thinking i could return 4 diff result sets with one store proc but no LUCK i ended up hsving 4 stored procedures returning each result set.

quote:
Originally posted by tduggan

Try using an explicit column list instead of select * in your stored procedure. Select * should not be used except for IF EXISTS and IF NOT EXISTS. Also, I'd recommend not returning 4 different result sets with one stored procedure. Instead, have four stored procedures that each return one result set.

Tara

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-05-10 : 12:20:16

Yes, you can return 4 different result sets with one stored proc. Tara, why should he use one proc per result set?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-10 : 12:30:16

I wasn't saying he couldn't, I was saying it isn't recommended.

Tara

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-05-10 : 12:30:20

"why should he use one proc per result set?"

1...avoid problems like this lot???
2...ability to request data from resultset #3 without having to process and read through #1+#2, etc.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-05-10 : 14:49:27

Thanks Andrew. I have used a stored proc to return 3 different result sets before, becuase when one is asked for, the business rules have it that all 3 will be asked for.

1) Problems seem to be on the ASP end, not SQL.
2) good point.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources