Cannot write to database

Question

I have a webform with a username, password and user identifier that classifies a user as an "A" for admin or "U" for standard user.

When you submit the form it should write to the database I have setup in visual studio, which currently already has other users.

When I test the web form I get an error "NullReferenceException was unhandled by user code", "Object reference not set to an instance of an object." and it points to this line of code on my web form page.

clsDataLayer.SaveUser(Server.MapPath("PayrollSystem_DB.mdb"), Session["txtUserName"].ToString(), Session["txtPassword"].ToString(), Session["drpdwnlstSecurityLevel"].ToString());

Do you see anything wrong with this line of code?

I have a textbox labeled "txtPassword", a textbox labeled "txtPassword" and a dropdown list with the option of U or A labeled "drpdwnlstSecurityLevel".

When you submit the information it is supposed to send it to my clsDataLayer.cs SaveUser method which is:

public static bool SaveUser(string Database, string UserName, string UserPassword, string SecurityLevel)
    {

        bool userSaved;

        try
        {
            // Define SQLConnClass
            OleDbConnection conn = new OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;" +
                                                       "Data Source=" + Database);
            conn.Open();
            OleDbCommand command = conn.CreateCommand();
            string strSQL;

            // this insert data to user table
            strSQL = "Insert into tblUserLogin " +
                     "(UserName, UserPassword, SecurityLevel) values ('" +
                     UserName + "', '" + UserPassword + "', " + SecurityLevel + "')";

            // this gives a command to get or set values
            command.CommandType = CommandType.Text;
            command.CommandText = strSQL;

            // This sql statements brings out the affacted rows
            command.ExecuteNonQuery();

            // closes the connection
            conn.Close();
            userSaved = true;
        }

        catch (Exception ex)
        {
            userSaved = false;
        }

        return userSaved;
    }

When you try to create a new user with my webform it does not create any record, it only issues the error I mentioned.

Here is all of my code that is relative to this question:

FILE clsDataLayer.cs:

// This function saves the user data public static bool SaveUser(string Database, string UserName, string UserPassword, string SecurityLevel) {

bool userSaved;

try
{
    // Define SQLConnClass
    OleDbConnection conn = new OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;" +
                                               "Data Source=" + Database);
    conn.Open();
    OleDbCommand command = conn.CreateCommand();
    string strSQL;

    // this insert data to user table
    strSQL = "Insert into tblUserLogin " +
             "(UserName, UserPassword, SecurityLevel) values ('" +
             UserName + "', '" + UserPassword + "', " + SecurityLevel + "')";

    // this gives a command to get or set values
    command.CommandType = CommandType.Text;
    command.CommandText = strSQL;

    // This sql statements brings out the affacted rows
    command.ExecuteNonQuery();

    // closes the connection
    conn.Close();
    userSaved = true;
}

catch (Exception ex)
{
    userSaved = false;
}

return userSaved;

}

FILE frmManageUsers.aspx.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class frmManageUsers : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnAddUser_Click(object sender, EventArgs e)
    {
        string userName, userPassword;


        if (txtUserName.Text == "" || txtUserName.Text == null)
        {
            lblUserError.Text = ("User Name may not be empty");
            lblUserError.ForeColor = System.Drawing.Color.Red;
            return;
        }
        else

            userName = (txtUserName.Text);


        if (txtPassword.Text == "" || txtPassword.Text == null)
        {
            lblUserError.Text = ("Password may not be empty");
            lblUserError.ForeColor = System.Drawing.Color.Red;
            return;
        }
        else
        {
            userPassword = (txtPassword.Text);
        }

        // clsDataLayer.SaveUser(Server.MapPath("PayrollSystem_DB.mdb"), Session["txtUserName"].ToString(), Session["txtPassword"].ToString(), Session["drpdwnlstSecurityLevel"].ToString());
        clsDataLayer.SaveUser(
    Server.MapPath("PayrollSystem_DB.mdb"),
    txtUserName.Text,
    txtPassword.Text,
    drpdwnlstSecurityLevel.SelectedValue
    );
        Server.Transfer("frmManageUsers.aspx");        
    }
}

FILE frmManageUsers.aspx:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="frmManageUsers.aspx.cs" Inherits="frmManageUsers" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div align="center">

        <a href="frmMain.aspx">
            <font color="black" size="2" style="text-align: center"><strong>
            <font color="blue" face="Comic Sans MS" size="4">Cool</font>
            <font color="#ff6600" face="Comic Sans MS" size="4">Biz</font>
            <font face="Comic Sans MS" size="4"> <font color="#993366">Productions</font>, 
            Inc.</font></strong></font>
        </a>
        <br />
        <br />
        <asp:Label ID="Label1" runat="server" Text="Manage Users"></asp:Label>
        <br />
        <asp:Label ID="Label2" runat="server" Text="User Name: "></asp:Label>
        <asp:TextBox ID="txtUserName" runat="server"></asp:TextBox>
        <br />
        <asp:Label ID="Label3" runat="server" Text="Password: "></asp:Label>
        <asp:TextBox ID="txtPassword" runat="server"></asp:TextBox>
        <br />
        <asp:Label ID="lblUserError" runat="server"></asp:Label>
        <br />
        <asp:Label ID="Label4" runat="server" Text="Security Level: "></asp:Label>
        <asp:DropDownList ID="drpdwnlstSecurityLevel" runat="server" 
            DataSourceID="SqlDataSource2" DataTextField="SecurityLevel" 
            DataValueField="SecurityLevel">
            <asp:ListItem></asp:ListItem>
            <asp:ListItem></asp:ListItem>
        </asp:DropDownList>
        <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
            ConnectionString="<%$ ConnectionStrings:PayrollSystem_DBConnectionString %>" 
            ProviderName="<%$ ConnectionStrings:PayrollSystem_DBConnectionString.ProviderName %>" 
            SelectCommand="SELECT [SecurityLevel] FROM [tblUserLogin]">
        </asp:SqlDataSource>
        <br />
        <br />

        <asp:Button ID="btnAddUser" runat="server" onclick="btnAddUser_Click" 
            Text="Add User" />

        <br />
        <br />
        <asp:GridView ID="grdUserLogin" runat="server" AutoGenerateColumns="False" 
            DataSourceID="SqlDataSource1">
            <Columns>
                <asp:BoundField DataField="UserID" HeaderText="UserID" InsertVisible="False" 
                    SortExpression="UserID" />
                <asp:BoundField DataField="UserName" HeaderText="UserName" 
                    SortExpression="UserName" />
                <asp:BoundField DataField="UserPassword" HeaderText="UserPassword" 
                    SortExpression="UserPassword" />
                <asp:BoundField DataField="SecurityLevel" HeaderText="SecurityLevel" 
                    SortExpression="SecurityLevel" />
            </Columns>
        </asp:GridView>
        <br />
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:PayrollSystem_DBConnectionString %>" 
            InsertCommand="INSERT INTO [tblUserLogin] ([UserID], [UserName], [UserPassword], [SecurityLevel]) VALUES (?, ?, ?, ?)" 
            ProviderName="<%$ ConnectionStrings:PayrollSystem_DBConnectionString.ProviderName %>" 
            SelectCommand="SELECT * FROM [tblUserLogin]">
            <InsertParameters>
                <asp:Parameter Name="UserID" Type="Int32" />
                <asp:Parameter Name="UserName" Type="String" />
                <asp:Parameter Name="UserPassword" Type="String" />
                <asp:Parameter Name="SecurityLevel" Type="String" />
            </InsertParameters>
        </asp:SqlDataSource>

    </div>
    </form>
</body>
</html>

you've got 5 different object references in there. could be any of those throwing the null ref error. — Marc B
– Marc B, Commented Dec 6, 2012 at 20:25
Show where you're putting the data into Session, because that's the problem - the data isn't there. — zimdanen
– zimdanen, Commented Dec 6, 2012 at 20:26
Set a break point and step through to see which item is a null reference. — user27414
– user27414, Commented Dec 6, 2012 at 20:29
Please, for the love of memory, wrap those Disposable objects in using() {} statements. Also, while you're still developing, you should throw the exception so you can get to the Stack Trace and zero in on the specific line that's causing you trouble. — Maurice Reeves
– Maurice Reeves, Commented Dec 6, 2012 at 20:29

D Stanley · Accepted Answer · 2012-12-06 20:32:09Z

4

Are you storing those values in Session? If the code you posted is in the code-behind you should be able to access the values directly:

clsDataLayer.SaveUser(
    Server.MapPath("PayrollSystem_DB.mdb"), 
    txtUserName.Text, 
    txtPassword.Text, 
    drpdwnlstSecurityLevel.SelectedValue
    );

Also you should strongly consider using SQL with parameters rather than concatenation to avoid SQL Injection:

strSQL = "Insert into tblUserLogin " +
         "(UserName, UserPassword, SecurityLevel) " + 
         "values (@UserName, @UserPassword, @SecurityLevel)";

While you're at it, do the following things as well:

Wrap your OleDbConnection and OleDbCommand in using blocks
display the exception message somehow in your catch block, don't just return false

answered Dec 6, 2012 at 20:32

D Stanley

153k12 gold badges190 silver badges257 bronze badges

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

4 Comments

n8wrl Over a year ago

I shouldn't be snarky but D.Stanley is exactly right: I wish there was a way to detect SQL injection and overlay a "please hack me" on every website that does it.

Chaddly Over a year ago

Ok, i've updated my question to include all of the code that would be referenced. Also, i tried your clsDataLayer version and now it submits, doesn't throw and error but doesn't write to the database. Thanks for helping.

Chaddly Over a year ago

Can i just put Using oleDbConnection; Using OleDbCommand; at the top of the page? Is that what your suggesting @D Stanley

D Stanley Over a year ago

No, that's for namespaces. Read MSDN's documenttaion on the using statement

Collectives™ on Stack Overflow

Cannot write to database

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related