Sierra Sierra - 3 months ago 18
ASP.NET (C#) Question

Cannot Save User Login Information

I've run into a bit of a wall and I can't seem to get the results I'm looking for. The desired results are to have data get saved when all three form fields are populated. Instead, I get a lblerror that the input is not being saved. I'm assuming it's an error from the clsdatalayer document, and not the page load file, but I'll include both just in case I'm wrong.

clsDataLayer Code:

// This function saves the User data
public static bool SaveUser(string Database, string Username, string Password,
string SecurityLevel)
{
bool recordSaved;
// Transaction for SQL
OleDbTransaction myTransaction = null;
try
{
// Opens OleDBConnection
OleDbConnection conn = new OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=" + Database);
conn.Open();
OleDbCommand command = conn.CreateCommand();

//Setting value to myTransaction
myTransaction = conn.BeginTransaction();
command.Transaction = myTransaction;
string strSQL;

// Creates strSQL and sets the value.
strSQL = "Insert into tblUserLogin " +
"(UserName, Password, SecurityLevel) values ('" +
Username + "', '" + Password + "','" + SecurityLevel + "')";

//Receives Input
command.CommandType = CommandType.Text;
command.CommandText = strSQL;

// Executes the Query
command.ExecuteNonQuery();

//Commit Changes to myTransaction
myTransaction.Commit();

// Closes Connection and saves record
conn.Close();
recordSaved = true;
} //end try
catch (Exception ex)//This produces warning, but not error.
{
//Rollback changes
myTransaction.Rollback();
recordSaved = false;
}//end of catch
return recordSaved;//returns values
}//ends function


Page load code

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)
{
btnAddUser.Click += new EventHandler(this.btnAddUser_Click);//event for button
}
protected void btnAddUser_Click(object sender, EventArgs e)
{
if (clsDataLayer.SaveUser(Server.MapPath("PayrollSystem_DB.accdb"), txtUserName.Text, txtPassword.Text, ddlSecurityLevel.SelectedValue))
{
lblDisplay.Text = "The user was successfully added";
grdviewUsers.DataBind();
}
else
{
lblDisplay.Text = "The user was not added.";
}
}//end protected void
}//end class


Actual Page Code

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

<asp:ImageButton ID="ImageButton1" runat="server" ImageUrl="~/Images/CIS407A_iLab_ACITLogo.jpg" />

</div>
<asp:Panel ID="Panel1" runat="server">
&nbsp;<asp:Label ID="Label1" runat="server" Text="Username"></asp:Label>
&nbsp;&nbsp;&nbsp;
<asp:TextBox ID="txtUserName" runat="server"></asp:TextBox>
<br />
<asp:Label ID="Label2" runat="server" Text="Password"></asp:Label>
&nbsp;&nbsp; &nbsp;
<asp:TextBox ID="txtPassword" runat="server"></asp:TextBox>
<br />
<asp:Label ID="Label3" runat="server" Text="Security Level"></asp:Label>
&nbsp;&nbsp;&nbsp;
<asp:DropDownList ID="ddlSecurityLevel" runat="server">
<asp:ListItem>A</asp:ListItem>
<asp:ListItem Selected="True">U</asp:ListItem>
</asp:DropDownList>
<br />
<br />
<asp:Button ID="btnAddUser" runat="server" Text="Add User" />
<br />
<br />
<br />
<asp:Label ID="lblDisplay" runat="server" Text="[lblDisplay]"></asp:Label>
<br />
<asp:Label ID="Label4" runat="server" Text="Users:"></asp:Label>
<br />
<asp:GridView ID="grdviewUsers" runat="server" AutoGenerateColumns="False" CellPadding="4" DataKeyNames="UserID" DataSourceID="ManageUsers" ForeColor="#333333" GridLines="None">
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:BoundField DataField="UserID" HeaderText="UserID" InsertVisible="False" ReadOnly="True" 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" />
<asp:CommandField ShowEditButton="True" />
<asp:CommandField ShowDeleteButton="True" />
</Columns>
<EditRowStyle BackColor="#2461BF" />
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#EFF3FB" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#F5F7FB" />
<SortedAscendingHeaderStyle BackColor="#6D95E1" />
<SortedDescendingCellStyle BackColor="#E9EBEF" />
<SortedDescendingHeaderStyle BackColor="#4870BE" />
</asp:GridView>
<asp:SqlDataSource ID="ManageUsers" runat="server" ConnectionString="<%$ ConnectionStrings:PayrollSystem_DBConnectionString %>" ProviderName="<%$ ConnectionStrings:PayrollSystem_DBConnectionString.ProviderName %>" SelectCommand="SELECT * FROM [tblUserLogin]"></asp:SqlDataSource>
<br />
<br />
<br />
</asp:Panel>
</form>
</body>

Answer

You are using MS.Access as your database and for this engine, the word PASSWORD is reserved. If you really have a field with this name then you need to enclose the field in square bracket in every query.

Said that, your code is very weak and could be exploited with an Sql Injection attack, you should use a parameterized query

So, I would rewrite your SaveUser in this way

public static bool SaveUser(string Database, string Username, string Password,
string SecurityLevel)
{
    bool recordSaved;
    // Transaction for SQL
    OleDbTransaction myTransaction = null;
    try
    {
        // Opens OleDBConnection
        using(OleDbConnection conn = new OleDbConnection(....))
        using(OleDbCommand command = conn.CreateCommand())
        {
             conn.Open();   
             using(myTransaction = conn.BeginTransaction())
             {
                  command.Transaction = myTransaction;
                  string strSQL = @"Insert into tblUserLogin 
                       (UserName, [Password], SecurityLevel) 
                        values (@uname, @pwd, @level)";
                  command.CommandType = CommandType.Text;
                  command.CommandText = strSQL;
                  command.Parameters.Add("@uname", OleDbType.VarWChar).Value = Username;
                  command.Parameters.Add("@pwd", OleDbType.VarWChar).Value = Password;
                  command.Parameters.Add("@level", OleDbType.VarWChar).Value = SecurityLevel;

                  // Executes the Query
                  command.ExecuteNonQuery();
                  myTransaction.Commit();
             }
        }    
        recordSaved = true;
    } //end try
    catch (Exception ex)
    {
        // I suggest to log somewhere the exception message here....
        myTransaction.Rollback();
        recordSaved = false;
    }
}
Comments