How to read a single row in a strongly types dataset?

I'm using strongly typed datasets in my first ASP.NET 2.0 web application. I come from ASP Classic, not an earlier version of .NET, and feel like I'm in another world. I'm slowly getting my head around datasets, but one thing I can't find any information on is how to read the data in a single record?

I'm not talking about for next loops, or accessing row information as a repeater or other control is filled. I'm talking about reading the data returned by a method that returns a table containing a single record. This is what I have:

Dim BookAdapter As New BooksTableAdapters.BooksBBTableAdapter
Dim organizations As Books.BooksBBDataTable
Dim organization As Books.BooksBBRow

organizations = BookAdapter.GetDataByOneBook(sBookID)

Dim sDropOff As String
Dim sOrganization As String
Dim sContact
For Each organization In organizations
If organization.DropOff = 1 Then
sDropOff = "True"
Else
sDropOff = "False"
End If
sOrganization = organization.Organization
sContact = organization.ContactName
Next

sBody = "Books Listing" & Chr(10) & Chr(10)
sBody = sBody & "Organization: " & sOrganization & Chr(10)
sBody = sBody & "Contact Name: " & sContact & Chr(10)
sBody = sBody & "Email: " & organization.Email & Chr(10)
sBody = sBody & "Phone Number: " & organization.Phone & Chr(10)

I'm using the FOR NEXT, but this is silly since I only have one record. GetDataByOneBook(sBookID) does exactly what is says, it returns a single book with a specific bookID.

Not only is this silly, it doesn't work. Using sContact as an example, it's Dim'd as a string. In the FOR NEXT loop, organization.ContactName has the right value, and it appears to be assigned to sContact correctly, but when I try to use sContact in sBody, I get an error saying that sContact has been used before it is assigned a value. Maybe the variables lose their scope outside the loop? Maybe I could get around this by building sBody inside the loop, but there has to be a better way!

Diane

I believe you want to do something like the following with your code:

1Dim BookAdapterAs New BooksTableAdapters.BooksBBTableAdapter2Dim organizationsAs Books.BooksBBDataTable3Dim organizationAs Books.BooksBBRow45 organizations = BookAdapter.GetDataByOneBook(sBookID)67If (organizations.Count > 0)Then89 sBody ="Books Listing" & Chr(10) & Chr(10)10 sBody = sBody &"Organization: " & organizations[0].Organization & Chr(10)11 sBody = sBody &"Contact Name: " & organizations[0].ContactName & Chr(10)12 sBody = sBody &"Email: " & organizations[0].Email & Chr(10)13 sBody = sBody &"Phone Number: " & organizations[0].Phone & Chr(10)1415End If

I do not have your XSD, so I can not test this.

|||

Thank you!

Diane

How to query for master-detail output on same page

I'd like to create a master-detail output from SQL2005 to display in a page using classic ASP. Ideally, I want the output to contain a <div> to show/hide the order details beneathe the order header, as shown below.

OrderNo OrderDate

1001 7/27/2007

+ <div to show/hide>

Item Description

AAA desc_for_itemAAA

BBB desc_for_itemBBB

+ </div>

1002 7/26/2007

+ <div to show/hide>

Item Description

CCC desc_for_itemCCC

+ </div>

I currently just have a stored procedure returning the results and it is very slow as I'm simply querying the details section for each order header. Does anyone have any ideas on how I can create an efficient and fast method for returning these results in the format above?

It’s always better to do these rendering on the ASP/UI page,

For your courtesy,

Code Snippet

Create Table #order (

[OrderNo] int ,

[OrderDate] datetime

);

Insert Into #order Values('1001','7/27/2007');

Insert Into #order Values('1002','7/26/2007');

Create Table #orderdetails (

[OrderNo] int ,

[Item] Varchar(100) ,

[Description] Varchar(100)

);

Insert Into #orderdetails Values('1001','AAA','desc_for_itemAAA');

Insert Into #orderdetails Values('1001','BBB','desc_for_itemBBB');

Insert Into #orderdetails Values('1002','CCC','desc_for_itemCCC');

Select

OrderNo,

OrderDate,

Replace(Replace((Select '<td>' + Item + '</td><td>' + Description + '</td>' as [text()] From #orderdetails OD Where OD.[OrderNo] = O.[OrderNo] For XML Path('tr')),'&gt;','>'),'&lt;','<')

From

#order O

<!--

On ASP/UI page,

While NOT rs.eof

Response.Write(“<tr><td>”)

Response.Write(rs(0))

Response.Write(“</td><td>”)

Response.Write(rs(1))

Response.Write(“</td></tr><tr>”)

Response.Write(“<td colspan=2><span style='cursor:hand' click=ExpandOrCollopse(‘div_” + rs(0) + “’)>+</span>”)

Response.Write (“<div style='display:none' id=’div_”)

Response.Write(rs(0))

Response.Write(“’><table>”)

Response.Write(rs(2))

Response.Write(“</table></div>”)

Wend

-->

|||

What you are proposing is clearly a presentation side issue and should not be 'forced' on SQL Server.

I suggest that you may find utility in the new XML output functionality.

|||

Manivannan, thanks for the sample code. I was able to create an output as you explained.

Arnie, can you point me to an easy to understand resource for this xml output functionality? I haven't really had any exposure to xml.

|||

Here are a couple of resources to get you started:

http://www.topxml.com/sql/

http://technet.microsoft.com/en-us/library/ms175024.aspx

Using XML data is perfect for web applications.