Bring data into DropDownList from SQL in ASP.NET / C#?

Question

Hi I am quite new to C# and my skills are very inadequate, I am looking to bring data from a SQL Table into my 'asp:DropDownList' within a table.

I am getting this error: System.NullReferenceException: Object reference not set to an instance of an object.

The front end html looks like this:

<asp:Table ID="Table1" runat="server" Font-Size="Medium" Width="960" BorderWidth="3" CellPadding="5" CellSpacing="5" CssClass="table table-bordered table-striped table-hover table-condensed table-responsive">
        <asp:TableHeaderRow runat="server" Font-Bold="true">
            <asp:TableHeaderCell Width="20%">Activity</asp:TableHeaderCell>
            <asp:TableHeaderCell>Hazards</asp:TableHeaderCell>
            <asp:TableHeaderCell Width="15%">Risk Level</asp:TableHeaderCell>
        </asp:TableHeaderRow>
        <asp:TableRow ID="TableRow1" runat="server" >
            <asp:TableCell>Camping</asp:TableCell>
            <asp:TableCell>Emergency evacuation</asp:TableCell>
            <asp:TableCell><asp:DropDownList ID="ddl1" runat="server"></asp:DropDownList></asp:TableCell>
        </asp:TableRow>
    </asp:Table>
        </div>

The C# code I have briefly gone through is this:

    public partial class _Default : Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!string.IsNullOrEmpty((string)Session["TripCode"]))
        {
            txtTripCode.Text = (string)Session["TripCode"];
        }
        else
        {
            txtTripCode.Text = "NOT PROVIDED";
        }

        if (!string.IsNullOrEmpty((string)Session["TripOrg"]))
        {
            txtTripOrg.Text = (string)Session["TripOrg"];
        }
        else
        {
            txtTripOrg.Text = "ERROR: NO DATA PROVIDED";
        }

        if (!string.IsNullOrEmpty((string)Session["TripDesc"]))
        {
            txtTripDesc.Text = (string)Session["TripDesc"];
        }
        else
        {
            txtTripDesc.Text = "ERROR: NO DATA PROVIDED";
        }

        if (!this.IsPostBack)
        {
            string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand("SELECT TOP 1000 [MainActivityID],[ID],[Activity],[Hazards],[Risk_Level],[Controls] FROM [STRIDES_SQL].[dbo].[STRIDES_RA_Activities]"))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = con;
                    con.Open();
                    ddl1.DataSource = cmd.ExecuteReader();
                    ddl1.DataTextField = "Name";
                    ddl1.DataValueField = "MainActivityID";
                    ddl1.DataBind();
                    con.Close();
                }
            }
            ddl1.Items.Insert(0, new ListItem("--Select Customer--", "0"));
        }
    }

Is there a way in which i can stop this happening?

I am grateful to any help or understanding, thank you in advance to anyone picking this up!

@ashin it's the line string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString; — etwestlake, Jul 11 '17 at 15:24
Possible duplicate of [What is a NullReferenceException, and how do I fix it?](https://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — VDWWD, Jul 11 '17 at 15:24
check your app.config/web.config to make sure the connection string named "constr" is present — ashin, Jul 11 '17 at 15:24
I hope this is just some kind of prototype, because in a long-lived product you shouldn't have SQL in your code-behind. This should be in a lower-level tire. — Tsahi Asher, Jul 11 '17 at 15:36
Hi @TsahiAsher, this is an internal web application for passing data through forms and into a Word document / Spreadsheet, therefore it will not be vulnerable. — etwestlake, Jul 11 '17 at 15:57

score 0 · Accepted Answer · answered Jul 11 '17 at 15:28

0

Check your web.config to look to constr in Configuration --> ConnectionStrings section. If it is not there, add the connectionstring.

Check https://www.connectionstrings.com/ for proper syntax.

Also, line

ddl1.DataTextField = "Name";

Will give you next error. Name field is not part of your query.

answered Jul 11 '17 at 15:28

Amit Kumar Singh

4,393
2
9
22

Thank you for this, the data was wrong within the web.cofig for some reason even though I had set this up previously. Thank you for the help – etwestlake Jul 11 '17 at 15:56

Bring data into DropDownList from SQL in ASP.NET / C#?

1 Answers1