Read data from DataTable on page_load. How?

[D. Shane Fowlkes]

I have a function that is called in page_load and the purpose of this
function is to look up basic data in a MSSQL table and return it in the form
of a datatable. The page_load will read the data and then fill a few simple
labels.

**Assuming** that I wrote the function properly, how exactly can I write the
page_load sub to read the data from the function? I've tried a few examples
from a couple of books I have but can't seem to get a working model. I'll go
back and add try/catch to the function later once the basics are in place
and working.

Help and assistance would GREATLY be appreciated! I'm having trouble
reading the data in the page_load sub. Using ASP/VB .NET 2.




Protected Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs)

Dim dtAppData As DataTable
Dim dtRow As DataRow
Dim dsCapData As DataSet
Dim intDaysLeft As Integer
Dim strCloseDate As String

dsCapData = New DataSet()
dtAppData = DaysLeftInAppSeason(1)

'TROUBLE HERE
For Each dtRow In dtAppData
intDaysLeft = dtAppData("DaysLeft")
strCloseDate = dtAppData("CloseDate")
Next

lblLabel1.Text = intDaysLeft
lblLabel2.Text = strCloseDate

End Sub







Protected Function DaysLeftInAppSeason(ByVal intAppID As Integer) As
DataTable

Dim objConnection As SqlConnection
Dim cmdSelect As SqlCommand
Dim drAppData As SqlDataReader
Dim dtResponse As DataTable
Dim dtColumn As DataColumn
Dim dtRow As DataRow
Dim strConnectString As String
Dim strSQL As String

strConnectString = System.Web.Configuration.....etc....
strSQL = "SELECT CloseDate, DateDiff(Day, GetDate(), CloseDate) AS DaysLeft
FROM ListAppTypes WHERE ID = " & intAppID

objConnection = New SqlConnection(strConnectString)
cmdSelect = New SqlCommand(strSQL, objConnection)
dtResponse = New DataTable("CapitalData")
dtColumn = New DataColumn("CloseDate", GetType(String))
dtColumn = New DataColumn("DaysLeft", GetType(Integer))

objConnection.Open()
drAppData = cmdSelect.ExecuteReader()
drAppData.Read()
dtRow = dtResponse.NewRow()
dtRow("CloseDate") = drAppData("CloseDate")
dtRow("DaysLeft") = drAppData("DaysLeft")
drAppData.Close()
objConnection.Close()

Return dtResponse

End Function

 

[Marina Levit [MVP]]

It would probably be harder to have this question be more vague or
non-precise.

"Having trouble" does not mean anything. You need to show us relevant code
snippets, and say what you expect to happen vs exactly what is happening -
meaning exact error messages, which line #, etc.

 

[Guest]

See below:

'TROUBLE HERE
For Each dtRow In dtAppData
intDaysLeft = dtAppData("DaysLeft")
strCloseDate = dtAppData("CloseDate")
Next


'TROUBLE HERE
For Each dtRow In dtAppData
intDaysLeft = dtRow("DaysLeft")
strCloseDate = dtRow("CloseDate")
Next


' you are writing for each dtRow (DataRow), so you need
to use the dtRow object to get your data, not the dtAppData( the datatable)

peter

 

[D. Shane Fowlkes]

Thanks.

Let me ask you this. Assuming my function works properly, how would YOU,
being a more experienced .NET developer, write a page_load sub to read the
datatable? That's where I'm stuck.

 

[Marina Levit [MVP]]

What do you mean 'read the datatable'? What does that actually mean? The
datatable is there and presumably ready for use.

Does the function return a datatable? Populate a datatable declared at the
class level?

Again, it's impossible to understand what you are trying to do without some
code snippets.

 

[Marina Levit [MVP]]

You know, I apologize. The size of my outlook window with posts, it seemed
like the end of message was your description, and I did not see that there
was a code snippet below that.

You have a couple of problems:

1. You never add the row to the datatable. You call NewRow, assign values
to the row - but you don't add the row to the actual datatable. So it never
has any rows.
2. As peter pointed out, 'dtAppData("DaysLeft")' accesses the column
definition for DaysLeft. Not the row value. You need to call
'dtRow("DaysLeft")' for the row's value for that column.

A couple of asides:

I am not sure why you are using a datareader, just to manually create a
datatable, manually create columns in it, and populate the datatable. Why
not fill the datatable directly from the database to begin with and avoid
the datareader?

Secondly, please turn Option Strict On. It will catch a lot of run time
problems at compile time instead of runtime.

 

[Guest]

One final comment:
We see (meaning Marina, I and many others) this happening all the time. The
MS people put a huge investment into the Quickstarts and Samples
applications; they install with either Visual Studio .NET or the .NET
Framework SDK.

The examples are progressive, easy to use, and very comprehensive. And they
are all available in your choice of language. So, if you don't start at the
Quickstarts, you are doing yourself a disservice in getting to "First Base"
quickly and with less frustration.
Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com

 

[D. Shane Fowlkes]

I understand and I thank you for your time. I've looked into 2 books and
searched the asp.net forums and still haven't had much luck. I'm having
trouble connecting the dots here and I legitimately am stuck. I have found
numerous datatable examples and how to read them but they all deal with
binding to a repeater or something like that. I'm not trying to bind - I
just want to read the values from the first and only row in the DT.

Let me simplify my question - How can I read a column 1 and column 2 from a
single row, 2 column datatable? It's that simple (I think). The function
returns a 2 column, 1 row record and I'm trying to get the page_load sub to
read it.

I tried your suggestion but it didn't work. VWD is still telling me
"Expression is of type System.Data.DataTable, which is not a collection
type". It is telling me this at "For Each dtRow In dtAppData".

Thanks again.


Dim dtAppData As DataTable
Dim dtRow As DataRow
Dim intDaysLeft As Integer
Dim strCloseDate As String

dtAppData = MyFunction()

For Each dtRow In dtAppData
intDaysLeft = dtRow("DaysLeft")
strCloseDate = dtRow("CloseDate")
Next

 

[Guest]

You have to type everything:

If it's only one row you certainly don't need a for each since that would
just be a waste of CPU cycles:


lblLabel1.Text = dtAppData.Tables(0).Rows(0)("DaysLeft")
lblLabel2.Text = dtAppData.Tables(0).Rows(0)("CloseDate")

Hope that helps.

peter




--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com

 

[Marina Levit [MVP]]

It needs to be:

For Each dtRow in dtAppData.Rows

Since you want to go through the collection. You should use intellisense in
helping you find the right properties and methods. There are numerous
examples out there of how to loop through all the rows in a datatable. Not
that this is what you want - because you only have 1 row coming back.

Ditch the datareader, and read up on using a SqlDataAdapter to fill a
datatable.

And finally, searching ASP.NET forums is not a good idea, when you are
looking for ADO.NET information. It makes sense that ASP.NET forums would
have examples of databinding, since that is what you do a lot in web
applications.

 

[D. Shane Fowlkes]

That's it!

Thanks everyone!

"dtAppData.Tables(0).Rows(0)("DaysLeft")"