Why I can't display data from database in HTML table in ASPNet.aspx

Question

I'm trying to display some data from an SQLserver to an HTML table in ASPNet, this is my code so far:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Text;
using System.Configuration;
using System.Data;


namespace WebApplication17
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
       {
        if (!this.IsPostBack)
        {
            //Populating a DataTable from database.
            DataTable dt = this.GetData();

            //Building an HTML string.
            StringBuilder html = new StringBuilder();

            //Table start.
            html.Append("<table border = '1'>");

            //Building the Header row.
            html.Append("<tr>");
            foreach (DataColumn column in dt.Columns)
            {
                html.Append("<th>");
                html.Append(column.ColumnName);
                html.Append("</th>");
            }
            html.Append("</tr>");

            //Building the Data rows.
            foreach (DataRow row in dt.Rows)
            {
                html.Append("<tr>");
                foreach (DataColumn column in dt.Columns)
                {
                    html.Append("<td>");
                    html.Append(row[column.ColumnName]);
                    html.Append("</td>");
                }
                html.Append("</tr>");
            }

            //Table end.
            html.Append("</table>");

            //Append the HTML string to Placeholder.
            PlaceHolder1.Controls.Add(new Literal { Text = html.ToString() });
        }
    }


    private DataTable GetData()
    {
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT id, Nome, Cognome FROM Anagrafica"))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.Connection = con;
                    sda.SelectCommand = cmd;
                    using (DataTable dt = new DataTable())
                    {
                        sda.Fill(dt);
                        return dt;
                    }
                }
               }
            }
        }
   }
}

and this is my HTML markup so far:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebApplication17.WebForm1" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
     <title></title>
</head>
<body>
        <asp:PlaceHolder ID = "PlaceHolder1" runat="server" />
</body>
</html>

The proble is: I keep on getting an NullReferenceException error on constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

I can't fix it...any ideas please? Thanks in advice.

Make sure you have defined `constr` in Web.Config file. Also, check this http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it to know about the error. — Rahul Singh, Mar 19 '15 at 09:19
@RahulSingh can you please provide me a sort of code for declaring it? I'm very new to this. — FabioEnne, Mar 19 '15 at 09:47

score 1 · Answer 1 · answered Mar 19 '15 at 11:15

1

Ok I found a solution that works:

private DataTable GetData()
    {

        SqlConnection con = new SqlConnection("Data Source=NB465\\SQLEXPRESS;Initial Catalog=Movie;Integrated Security=True");
        con.Open();
        SqlCommand cmd = new SqlCommand("SELECT id, Nome, Cognome FROM Anagrafica");
        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        cmd.Connection = con;
        sda.SelectCommand = cmd;
        DataTable dt = new DataTable();
        sda.Fill(dt);
        return dt;
    }

answered Mar 19 '15 at 11:15

FabioEnne

732
1
14
43

Some quick advice, maybe use SqlDataReader instead of Adapter. (Data Adapter is usually used for manipulating data, Reader will be quicker) http://stackoverflow.com/questions/1676753/sqldataadapter-vs-sqldatareader – JLane Mar 19 '15 at 13:23

score 0 · Accepted Answer · answered Mar 19 '15 at 09:59

0

You need to add Connection String in your Web.Config file something like this in the configuration node:-

Working with Configuration file & Connection Strings

<configuration>
    <connectionStrings>
      <add name="constr" 
       providerName="System.Data.SqlClient" 
       connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=
             |DataDirectory|\Database1.mdf;Integrated Security=True;" />
    </connectionStrings>
  </configuration>

These are basics, you should refer to some tutorails from MSDN.

answered Mar 19 '15 at 09:59

Rahul Singh

21,585
6
41
56

thanks! It worked! but now for some other reasons I got this error: An exception of type 'System.InvalidOperationException' occurred in System.Data.dll but was not handled in user code on sda.Fill(dt); – FabioEnne Mar 19 '15 at 10:18
@FabioEnne - Don't create DataTable using `using` like this - `using (DataTable dt = new DataTable())`, create it as a normal instance like this: - `DataTable dt = new DataTable(); sda.Fill(dt);` – Rahul Singh Mar 19 '15 at 10:30
@ RahulSingh Done it, but keep on getting the same error on sda.Fill(dt); – FabioEnne Mar 19 '15 at 10:52
@FabioEnne - I am not sure whats going wrong. Please search for this error, it happens when connection is not open or when a connection is already open. – Rahul Singh Mar 19 '15 at 11:29

Why I can't display data from database in HTML table in ASPNet.aspx

2 Answers2