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" %>





    


    
    

        
            
            Cool
            Biz
             Productions, 
            Inc.

D Stanley · Accepted Answer

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

Cannot write to database

Answers (1)

Related Questions