Column 'link_url' does not belong to table links.

[Patrick Olurotimi Ige]

I have a simple Stored Procedure with multiple select statements..doing
select * from links for example.
I created a DataTable and then fill the tables
But the first dtTemplate DataTable doesn't give the error but the links
does!
I get the error on this LINE(when looping):-

PageLinks.Text = PageLinks.Text & dtLinks.Rows(iLoop)("link_url")


cmdAccess.CommandType = CommandType.StoredProcedure
cmdAccess.CommandText = "mystocprocedure"

Dim daTemplate As SqlDataAdapter = New SqlDataAdapter(cmdAccess)
Dim daLinks As SqlDataAdapter = New
SqlDataAdapter(cmdAccess)
Dim daCategory As SqlDataAdapter = New
SqlDataAdapter(cmdAccess)

Dim dsAccess As DataSet = New DataSet
Dim dtTemplate As DataTable = New DataTable
Dim dtLinks As DataTable = New DataTable
Dim dtCategory As DataTable = New DataTable
'filling DataSet with links table
daLinks.Fill(dsAccess, "links")
dtLinks = dsAccess.Tables("links")

dtLinksCount = dtLinks.Rows.Count
PageLinks.Text = ""

While iLoop < dtLinksCount
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 Then
PageLinks.Text = PageLinks.Text & " | "
End If


System.Math.Min(System.Threading.Interlocked.Increment(iLoop), iLoop -
1)

End While

Any ideas?

 

[MasterGaurav]

What error do you get?
btw, does the table has that column? Recheck the design..

--
Cheers,
Gaurav Vaish
http://mastergaurav.blogspot.com
http://mastergaurav.org
-------------------------

 

[Patrick Olurotimi Ige]

Thx Gaurav for the response!
I get the Error :- Column 'link_url' does not belong to table links
The column exists for sure.

If i do :-

string ssLinks = "Select * From links";
SqlDataAdapter daLinks = new SqlDataAdapter(ssLinks, dcAccess);
DataTable dtLinks = new DataTable();

I get the desired result but when i use stored procedure i get the
error.
I don't want to use string for calling my sql statements.
But i think the problem is that my select satements in the stored
procedure doesn't feed the DataTable correctly
when i LOOP through the records
Any workarounds?

 

[Elton W]

Hi Patrick,

If your SP has multiple select queries, you don't need to
multi-fill dataset. Only one SqlDataAdapter.Fill(dataset)
call will fill multi-tables in the dataset.

It's similar to following example:

Dim dap As New SqlDataAdapter("Select * From Table1;
Select * From Table2", CONNECTION_STRING)
Dim ds As New DataSet
dap.Fill(ds)

Two datatables are filled to the dataset.

HTH

Elton Wang
(e-mail address removed)

 

[Patrick Olurotimi Ige]

Thx for the reply Elton

This is what 'm trying to do:-

Tried using one DataAdapter but i still get Error on line:-

PageLinks.Text = PageLinks.Text & dtLinks.Rows(iLoop)("link_url")


Code below
-------------
Dim cmdAccess As New SqlCommand("webpage", dcAccess)
cmdAccess.CommandType = CommandType.StoredProcedure

Dim daTemplate As SqlDataAdapter = New SqlDataAdapter(cmdAccess)

Dim dsAccess As DataSet = New DataSet
Dim dtTemplate As DataTable = New DataTable
Dim dtLinks As DataTable = New DataTable
Dim dtCategory As DataTable = New DataTable
Dim dtPage As DataTable = New DataTable

Dim dtLinksCount As Integer
Dim dtCategoryCount As Integer
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

daTemplate.Fill(dsAccess, "links")

dtLinks = dsAccess.Tables("links")

dtLinksCount = dtLinks.Rows.Count
PageLinks.Text = ""


Dim iLoop As Integer
iLoop = 0

'Pulls out the category TOP links from the Database by
looping through dtLinks

While iLoop < dtLinksCount
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 Then
PageLinks.Text = PageLinks.Text & " | "
End If


System.Math.Min(System.Threading.Interlocked.Increment(iLoop), iLoop -
1)

End While

daTemplate.Fill(dsAccess, "category")

dtCategory = dsAccess.Tables("category")
dtCategoryCount = dtCategory.Rows.Count
PageCategory.Text = ""


iLoop = 0

While iLoop < dtCategoryCount
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><br>"

System.Math.Min(System.Threading.Interlocked.Increment(iLoop), iLoop -
1)

End While

'Displaying the Category Headers
'daPage.Fill(dsAccess, "pages")

daTemplate.Fill(dsAccess, "pages")

dtPage = dsAccess.Tables("pages")
PageContent.Text = ""
PageContent.Text = PageContent.Text & "<center><b>"
PageContent.Text = PageContent.Text &
dtPage.Rows(0)("page_title")
PageContent.Text = PageContent.Text & "<br>"
PageContent.Text = PageContent.Text &
dtPage.Rows(0)("page_subtitle")
PageContent.Text = PageContent.Text & "</b><br></center>"
PageContent.Text = PageContent.Text &
dtPage.Rows(0)("page_text")


If Not (Request.QueryString("category") Is Nothing) Then
Dim ssCatPage As String = "Select page_id, page_title,
page_subtitle From pages Where page_category = " &
Request.QueryString("category")
Dim daCatPage As SqlDataAdapter = New
SqlDataAdapter(ssCatPage, dcAccess)
Dim dtCatPage As DataTable = New DataTable
Dim dtCatPageCount As Integer

daCatPage.Fill(dsAccess, "catpages")


dtCatPage = dsAccess.Tables("catpages")
dtCatPageCount = dtCatPage.Rows.Count

iLoop = 0

'Displaying Content in the inner page
While iLoop < dtCatPageCount
PageContent.Text = PageContent.Text & "<p>"
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><br>"
PageContent.Text = PageContent.Text &
dtCatPage.Rows(iLoop)("page_subtitle")
PageContent.Text = PageContent.Text & "</p>"

System.Math.Min(System.Threading.Interlocked.Increment(iLoop), iLoop -
1)
End While
End If

 

[Patrick Olurotimi Ige]

And Elton the simple store proc looks like this:-
CREATE PROCEDURE dbo.webpage
AS
Select template_id,template_header,template_footer from templates
Select link_id,link_text,link_url from links
Select category_id,category_text,category_url from category
Select page_id,page_category,page_title,page_subtitle,page_text from
pages
RETURN
GO

 

[Guest]

As I mentioned, you only need fill dataset once:

Dim cmdAccess As New SqlCommand("webpage", dcAccess)
cmdAccess.CommandType = CommandType.StoredProcedure

Dim daTemplate As SqlDataAdapter = New SqlDataAdapter(cmdAccess)

Dim dsAccess As DataSet = New DataSet
Dim dtTemplate As DataTable = New DataTable
Dim dtLinks As DataTable = New DataTable
Dim dtCategory As DataTable = New DataTable
Dim dtPage As DataTable = New DataTable


daTemplate.Fill(dsAccess)
' It fills templates, links, category, pages datatable in one time
' Then you can refer to these tables by
dtTemplate = dsAccess.Tables(0)
dtTemplate.TableName = "templates"

dtLinks = dsAccess.Tables(1)
dtLinks.TableName = "links"

dtCategory = dsAccess.Tables(2)
dtCategory.TableName = "category"

dtPage = dsAccess.Tables(3)
dtPage.TableName = "pages"


HTH

 

[Patrick Olurotimi Ige]

Thx for the reply....
I got it working having each Stored Proc to each DataADapter before.
But with this new trick its cool
Thx Elton