Return All Rows

Hello Again,

Here is my situation..

I have a datareader that reads through all the records that satisfy my query condition... Once i have my records in the datareader i want to run through all of the values from it and form HTML rows, how do i do it? Following is the code that i have written

CustID = Session("CustCode")
viewSqlStr = "Select [Lead Title], [Lead Date] from [Lead Info] Where [Customer ID] = '" & CustID & "'"
objCommand = New SqlCommand(viewSqlStr, objConn)
objConn.Open()
objDR = objCommand.ExecuteReader()

Then in the HTML i do:

<% While objDR.Read() %>
<tr>
<td class="f10" bgcolor="#ffffff" valign="top">Edit</td>
<td class="f10" bgcolor="#ffffff" valign="top"><%= objDR("Lead Title") %></td>
<td class="f10" bgcolor="#ffffff" valign="top"><%= objDR("Lead Date") %></td>
</tr>
<% End While %
When i do this... inspite of having more than one records in the database.. only the last record is displayed on the screen. how do i make it display all the records here? i know in asp ado there was a movenext statement... what goes here? can someone pls help me with this so that all the records can be displayed here?

Thanks in advance,
~ssBinding this to a datagrid would probably be much faster...|||What this will do is to read through all of them - - forward only - - then, the last one in the list gets left on the screen for you to see.

What you want can be done with a Repeater or a DataList....Create the Item Template, then, instead of objDR.Read - - just bind the dataReader to the DataList or Repeater.

Check out:
http://aspnet101.com/aspnet101/aspnet/codesample.aspx?code=dListdReader

It's not exactly your scenario, with the Table, etc, but it will show you how to use a DataList with a DataReader. You can add a HeaderTemplate for the Column HEaders and then one row and a cell for each item you want to show, in the ItemTemplate.

However, it's MUCH easier to use a DataGrid - - - skipping all the Table/Row/Cell stuff - - it's done automatically with a DataGrid

Retrieving XML data using OpenXML

Hello Everyone:
I have a piece of function that reads through the XML file and updates the
table with the contents.
I am working on to retrieve a specific elemental data, but am not able to do
so.
Below is my Code
CREATE PROCEDURE [dbo].[xmltest]
AS
BEGIN
--Local var for statement header/detail messages
DECLARE @.hDoc int --document handle
DECLARE @.Count int
DECLARE @.errNo int, @.doc nvarchar(4000) , @.Msgid varchar(20)
set @.doc = ' <VendorMasterData>
<VendorInfo>
<MessageId type="A">0000000018089158</MessageId>
<Date>2005-12-07</Date><Time zone="PST">05:02:31.000</Time>
<MessageType>C</MessageType>
<Sort type="SORT1">ABC</Sort>
<Sort type="SORT2">XYZ</Sort>
</VendorInfo>
</VendorMasterData>'
--Get the XML doc handle
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.doc
IF @.@.ERROR <> 0
BEGIN
return @.@.ERROR
END
SELECT * FROM OPENXML(@.hdoc, '/VendorMasterData/VendorInfo',3) WITH
([Sort] varchar(30),type varchar(30))
EXEC sp_xml_removedocument @.hdoc
RETURN (0)
END
GO
I would like to retrieve both the values of Sort (both Sort1 and Sort2
types). How can I do that. Right now I am able to retrieve only 1 value.
Thanks for you help.
Regards/Shriram.Hello shriram2977,

> I have a piece of function that reads through the XML file and updates
> the table with the contents.
> I would like to retrieve both the values of Sort (both Sort1 and Sort2
> types). How can I do that. Right now I am able to retrieve only 1
> value.
Does this give you what you were looking for?
SELECT * FROM OPENXML(@.hdoc, '/VendorMasterData/VendorInfo/Sort',2) WITH
([Sort] varchar(30) 'text()',type varchar(30) '@.type')
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Kent Tegels, Thanks much. It does.
Jus curious, what is this text() and where can you use them in OpenXML.
Thanks/Shriram.
"Kent Tegels" wrote:

> Hello shriram2977,
>
> Does this give you what you were looking for?
> SELECT * FROM OPENXML(@.hdoc, '/VendorMasterData/VendorInfo/Sort',2) WITH
> ([Sort] varchar(30) 'text()',type varchar(30) '@.type')
>
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||Hello shriram2977,
text() is an xpath function that returns the lexical value of an element's
inner-text. You can use it (and some other functions) as what's known as
a metaproprety. This is covered in Books-On-Line.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Retrieving XML data using OpenXML

Hello Everyone:
I have a piece of function that reads through the XML file and updates the
table with the contents.
I am working on to retrieve a specific elemental data, but am not able to do
so.
Below is my Code
CREATE PROCEDURE [dbo].[xmltest]
AS
BEGIN
--Local var for statement header/detail messages
DECLARE @.hDoc int--document handle
DECLARE @.Count int
DECLARE @.errNo int, @.doc nvarchar(4000) , @.Msgid varchar(20)
set @.doc = ' <VendorMasterData>
<VendorInfo>
<MessageId type="A">0000000018089158</MessageId>
<Date>2005-12-07</Date><Time zone="PST">05:02:31.000</Time>
<MessageType>C</MessageType>
<Sort type="SORT1">ABC</Sort>
<Sort type="SORT2">XYZ</Sort>
</VendorInfo>
</VendorMasterData>'
--Get the XML doc handle
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.doc
IF @.@.ERROR <> 0
BEGIN
return @.@.ERROR
END
SELECT * FROM OPENXML(@.hdoc, '/VendorMasterData/VendorInfo',3) WITH
([Sort] varchar(30),type varchar(30))
EXEC sp_xml_removedocument @.hdoc
RETURN (0)
END
GO
I would like to retrieve both the values of Sort (both Sort1 and Sort2
types). How can I do that. Right now I am able to retrieve only 1 value.
Thanks for you help.
Regards/Shriram.
Hello shriram2977,

> I have a piece of function that reads through the XML file and updates
> the table with the contents.
> I would like to retrieve both the values of Sort (both Sort1 and Sort2
> types). How can I do that. Right now I am able to retrieve only 1
> value.
Does this give you what you were looking for?
SELECT * FROM OPENXML(@.hdoc, '/VendorMasterData/VendorInfo/Sort',2) WITH
([Sort] varchar(30) 'text()',type varchar(30) '@.type')
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Kent Tegels, Thanks much. It does.
Jus curious, what is this text() and where can you use them in OpenXML.
Thanks/Shriram.
"Kent Tegels" wrote:

> Hello shriram2977,
>
> Does this give you what you were looking for?
> SELECT * FROM OPENXML(@.hdoc, '/VendorMasterData/VendorInfo/Sort',2) WITH
> ([Sort] varchar(30) 'text()',type varchar(30) '@.type')
>
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>
|||Hello shriram2977,
text() is an xpath function that returns the lexical value of an element's
inner-text. You can use it (and some other functions) as what's known as
a metaproprety. This is covered in Books-On-Line.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/