CODEWITHSUNDEEP
c#asp.netsql-serversql-server-2008webforms
Bert Gregor
Bert Gregor

Reputation: 121

Looping through SQL Server results on my .aspx page

I'm new to ASP.NET and C#. I'm conversant in SQL Server and am having a heck of a time moving over from the traditional ASP world.

CREATE TABLE dbo.Chapters 
(
     ChapterID NUMERIC, 
     ChapterName NVARCHAR, 
     ChapterDesc NVARCHAR
)

SELECT mChapterID, ChapterName, ChapterDesc 
FROM Chapters

<table>
    <tr>
        <th>ID</th>
        <th>CHAPTER NAME</th>
        <th>DESCRIPTION</th>
    </tr>
    <tr>
        <td><input type="checkbox" name="chaptid" value="<%=Chapters.ChapterID %>"></td>
        <td><%=Chapters.ChapterName %></td>
        <td><%=Chapters.ChapterDesc %></td>
    </tr>
    <tr>
        <td>...</td>
    </tr> // 10 rows
</table>

There are 10 rows returned from the query. How do I loop through them in my table rows so I can display all 10?

EDIT: I added a checkbox as the first field

Upvotes: 2

Views: 309

Answers (2)

Bert Gregor
Bert Gregor

Reputation: 121

John Cappelletti pointed me in a direction and here's what I eventually came up with. THANKS JOHN!

CREATE TABLE dbo.Chapters 
(
     ChapterID NUMERIC, 
     ChapterName NVARCHAR, 
     ChapterDesc NVARCHAR
)

DECLARE @body NVARCHAR(MAX)
SET     @body = N'<table>'
    + N'<tr><th>Check</th><th>Chapter Name</th><th>Chapter Desc</th></tr>'
    + CAST((
        SELECT  '<input type="checkbox" name="ChapterID" value="'+CONVERT(varchar(10),[c].[mChapterID])+'">' AS td,
                '<span style="font-weight: bold;">'+[c].[ChapterName]+'</span>' AS td, 
                [c].[ChapterDesc] AS td
        FROM    [ModuleChapters] c
        WHERE   [c].[inActive] = 0
          AND   [c].[isDeleted] = 0
        FOR XML RAW('tr'), ELEMENTS
    ) AS NVARCHAR(MAX))
    + N'</table>'

SELECT tBody = @body

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81990

If by chance you want to create the XML/HTML in SQL Server in one shot...

Example

Declare @YourTable Table ([ChapterID] int,[ChapterName] varchar(50),[ChapterDesc] varchar(50))
Insert Into @YourTable Values 
 (1,'Chapter 1','This is the 1st chapter')
,(2,'Chapter 2','This is the 2nd chapter')

Select (Select th='ID'
              ,null
              ,th='Chapter Name'
              ,null
              ,th='Description'
              ,null 
          For XML Path('tr'),type)
      ,(Select td= ( Select [@type]='checkbox'
                           ,[@name]='chaptid'
                           ,[@value]=[ChapterID]
                      For XML Path('input'),type)
              ,null
              ,td=[ChapterName]
              ,null
              ,td=[ChapterDesc] 
          From @YourTable 
           For XML Path('tr'),Type) 
 For XML Path(''),Root('table')

Returns

<table>
  <tr>
    <th>ID</th>
    <th>Chapter Name</th>
    <th>Description</th>
  </tr>
  <tr>
    <td>
      <input type="checkbox" name="chaptid" value="1" />
    </td>
    <td>Chapter 1</td>
    <td>This is the 1st chapter</td>
  </tr>
  <tr>
    <td>
      <input type="checkbox" name="chaptid" value="2" />
    </td>
    <td>Chapter 2</td>
    <td>This is the 2nd chapter</td>
  </tr>
</table>

Upvotes: 2

Related Questions