2 Replies - 1078 Views - Last Post: 21 June 2013 - 03:06 PM Rate Topic: -----

#1 zorak  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 8
  • Joined: 05-February 12

Data not being saved when button is pressed

Posted 21 June 2013 - 02:23 PM

Hey guys, I'm new to asp.net and I'm having a little bit of trouble.

I have added a page to my project where I can add new users by entering a user name, password, and a security level from a dropdown list. When the page first loads the users already in the db show right below in a datagrid. But after I enter a new user information and click on the Add User button the page just refreshes and the new user is not added. Session is enabled. Thanks for any help

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)
    {
        //validate input data
        if (txtUserName.Text == "")
        {
            txtUserName.BackColor = System.Drawing.Color.Yellow;
            lblError.Text = "You must enter a name";
        }

        else if (txtPassword.Text == "")
        {
            txtPassword.BackColor = System.Drawing.Color.Yellow;
            lblError.Text = "You must enter a password";
        }

        else
        {

            // information is passed to SaveUser function for saving
            clsDataLayer.SaveUser(Server.MapPath("PayrollSystem_DB.mdb"),
                                       txtUserName.Text,
                                       txtPassword.Text,   
                                       txtSecurityLevel.Text);

            Response.Redirect("frmManageUsers.aspx");
            grdViewUsers.DataBind();
            lblError.Text = "The user was successfully added!";
        }

    }
    protected void txtSecurityLevel_SelectedIndexChanged(object sender, EventArgs e)
    {

    }
}   





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

        bool userSaved;

        try
        {
            // Establish connection to data source using connection string
            OleDbConnection conn = new OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;" +
                                                       "Data Source=" + Database);
            conn.Open();
            OleDbCommand command = conn.CreateCommand();
            string strSQL;

            // Define insert parameters that will be passed to the database
            strSQL = "Insert into tblUserLogin " +
                     "(UserName, UserPassword, SecurityLevel) values ('" +
                     UserName + "', '" + UserPassword + "', " + SecurityLevel + "')";

            // Create SQL command objects to indicate how the command object interprets the value of the command text property
            command.CommandType = CommandType.Text;
            command.CommandText = strSQL;

            // Returns a value that indicates how many rows were affected by the insert command
            command.ExecuteNonQuery();

            // close connection to database	
            conn.Close();
            userSaved = true;
        }
        catch (Exception ex)
        {
            userSaved = false;

        }

        return userSaved;
    }//end SaveUser function



Is This A Good Question/Topic? 0
  • +

Replies To: Data not being saved when button is pressed

#2 jace75  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 31
  • Joined: 11-June 13

Re: Data not being saved when button is pressed

Posted 21 June 2013 - 02:54 PM

For starters, you are missing a single quote before "SecurityLevel". I would also recommend that you create a stored procedure to call from your DAL instead of using SQL injection.

CREATE PROCEDURE SaveUser(@Username nvarchar(50), @Password nvarchar(50), @SecurityLevel nvarchar(50))
AS
insert into tblUserLogin values(@Username, @Password, @SecurityLevel)
RETURN



to use a procedure in your CS file, you would populate the Command object like this...

command.CommandText = "SaveUser";

//Create Parameter
DbParameter param = CreateParam(command, "@UserName", UserName, DbType.String;
command.Parameters.Add(param);

//Create Parameter
DbParameter param2 = CreateParam(command, "@UserPassword", UserPassword, DbType.String;
command.Parameters.Add(param2);

//Create Parameter
DbParameter param3 = CreateParam(command, "@SecurityLevel", SecurityLevel, DbType.String;
command.Parameters.Add(param3);

//Execute an update, insert, or delete statement
    public static int ExecuteNonQuery(DbCommand command) {
        int affectedRows = -1;
        try {
            command.Connection.Open();
            affectedRows = command.ExecuteNonQuery();
        }
        catch (Exception ex) {
            Utilities.LogError(ex);
            throw;
        }
        finally {
            command.Connection.Close();
        }
        return affectedRows;
    }

 //Create DbCommand object linked to database
    public static DbCommand CreateCommand() {
        DbConnection connection = DbProviderFactories.GetFactory(Configuration.DbProviderName).CreateConnection();
        connection.ConnectionString = Configuration.DbConnectionString;
        DbCommand command = connection.CreateCommand();
        command.CommandType = CommandType.StoredProcedure;
        return command;
    }

    public static DbParameter CreateParam(DbCommand command, string paramName, object paramValue, DbType type) {
        DbParameter param = command.CreateParameter();
        param.ParameterName = paramName;
        param.Value = paramValue;
        param.DbType = type;
        return param;
    }



I know it requires a little more code upfront, but believe when I say that it's definately worth avoiding SQL injections!

Happy coding B)
Was This Post Helpful? 1
  • +
  • -

#3 zorak  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 8
  • Joined: 05-February 12

Re: Data not being saved when button is pressed

Posted 21 June 2013 - 03:06 PM

Thanks a lot!!! Had been stuck for quite a while. I wasn't getting any errors which made that single quote hard to see. We haven't covered stored procedures yet but I will definitely look into your suggestion.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1