How do I display SQL query data in tabular format in HTML?

Question

I have a SQL query which retrieves two columns from a table based on a 'location'.

The following table:

content_id     content_title
234            Fink, James
90             Merylou, Jane
45             Marcy, Kim
112            Bower, John
34             Alset, Mike

was generated by the following query:

DECLARE @strLocation varchar(200)
SET @strLocation = 'Ridge'

SELECT [content_id], [content_title]
FROM [content]
WHERE [folder_id] = '188'
AND (content_html LIKE '%'+@strLocation+'%')

The query looks into the content_html column and looks for the variable.

My HTML looks like this:

<input type=text size=50 id="txtPhysByLoc" runat="server" /><input type=button value="Go" />
<br />
<div>
    <table border=0>
        <span id="writeTable"></span>
    </table>
</div>

My C# code-behind so far looks like this:

protected void Page_Load(object sender, EventArgs e)
{    
   string cString = "Provider=sqloledb;Data Source=myServer;Initial Catalog=Dbase;User Id=efv;Password=st@tl;";
   SqlConnection Conn = new SqlConnection(ConnectionString);
   Conn.Open();
}

How can I use the query along with my C# code to generate a table? Or use another ASP.net control to show the tabular data?

Simplest would be: Use a GridView, put that in ASPX page, in code behind execute your command, get a result set back in DataTable and then bind it to the GridView. — Habib
– Habib, Commented May 12, 2014 at 16:50
Can I just use one SQL command and separate by ; for each line? — SearchForKnowledge
– SearchForKnowledge, Commented May 12, 2014 at 16:52
Short Answer: Yes. If the only command you want to execute is the one in question, then you don't need multiple statements. Instead you should pass the parameter from your Code behind, construct the SQL command in code behind and then get the result. — Habib
– Habib, Commented May 12, 2014 at 16:53

huseyin tugrul buyukisik · Accepted Answer · 2014-05-12 16:52:51Z

1

   <asp:SqlDataSource runat="server" ID="sdsContent" ConnectionString="<%$ connectionStrings.connectionString %>" SelectCommandType="text" SelectCommand="your query here">
   </asp:SqlDataSource>
   <asp:Repeater runat="server" ID="rptContent" DataSourceID="sdsContent">
   <ItemTemplate>
   <%# Eval("content_title").ToString() %>
   <br/>
   <div style="clear:both;"></div>
   </ItemTemplate>
   </asp:Repeater>

This should write something like:

     Fink, James
     Merylou, Jane
     Marcy, Kim
     Bower, John
     Alset, Mike

The

     <%$ connectionStrings.connectionString %>

part selects the conn property from webconfig (if you have already set-up that.)

answered May 12, 2014 at 16:52

huseyin tugrul buyukisik

12k6 gold badges53 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

SearchForKnowledge Over a year ago

Can I pass this: Provider=sqloledb;Data Source=myServer;Initial Catalog=DBase;User Id=efv;Password=st@tl; as the connection string instead?

SearchForKnowledge Over a year ago

<asp:SqlDataSource runat="server" ID="sdsContent" ConnectionString="Data Source=server;Initial Catalog=database;User Id=efd;Password=test;" SelectCommandType="text" SelectCommand="DECLARE @strLocation varchar(200) SET @strLocation = 'Ridge' SELECT [content_id], [content_title] FROM [content] WHERE [folder_id] = '188' AND (content_html LIKE '%'+@strLocation+'%')"></asp:SqlDataSource><asp:Repeater runat="server" ID="rptContent" DataSourceID="sdsContent"><ItemTemplate><%# Eval("content_title").ToString() %><br/><div style="clear:both;"></div></ItemTemplate></asp:Repeater>

SearchForKnowledge Over a year ago

I get the following error: ` Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'content'.`

huseyin tugrul buyukisik Over a year ago

missplled table-name or initial-catalog name is wrong or table is not created?

huseyin tugrul buyukisik Over a year ago

You can give that sqldatasource some parameters(using brackets againg into its opening and closing) if you want to make it more "dynamic". Good game.

|

Collectives™ on Stack Overflow

How do I display SQL query data in tabular format in HTML?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related