locked
Update data in Access database RRS feed

  • Question

  • User-1375742532 posted

    Hi,

    I am new to C# and trying to learn few things. The following is the code that i have to update an item in my database but doesn work any reason why?

    string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Jean\Desktop\Jessenger.accdb;User Id=admin;Password=;";
                string queryString = "UPDATE Member SET UserState="  + person.State + " WHERE UserID = " + person.Username + " AND  UserPassword = " + person.Password;
                using (OleDbConnection connection = new OleDbConnection(connectionString))
                {
                    OleDbCommand command = connection.CreateCommand();
                    command.CommandText = queryString;
                    try
                    {
    
                        
                        command.Parameters.AddWithValue("UserState", person.State);
                        connection.Open();
                        int rows = command.ExecuteNonQuery();
                        connection.Close();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                }
    Sunday, May 27, 2012 4:00 PM

Answers

  • User3866881 posted

    If i shouldnt use static to access the same object accross multiple what would be the best technique??

    In fact as far as I see,I think you can create another class that is for common use:

    public sealed class CommonClass
    {
      private CommonClass(){}
      public static User User{get;set;}
    }

    When you log in successfully,please directly set the User to CommonClass or fetch from it。Generally speaking,entity model class should be something like POCO……:-)

    Kindly correct me if you have a better way

    Reguards!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 29, 2012 9:25 PM

All replies

  • User-821857111 posted

    Unless you get an error that you haven't told us about, I suspect that no rows meet the criteria you are passing in to your WHERE clause. By the way, you really should use parameters to prevent a malicious user updating all rows through SQL injection:

    http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

    Sunday, May 27, 2012 4:39 PM
  • User-1375742532 posted

    Thanks, for the parameter, I will study it for sure! And yes it does work ( it seem the thread UI freeze for about 1/2 a second to do the queries, but at the end it i can carry on my work. Then i i close the application and see in the database itself if the data was changed. Of course it does not modify. My database is as follow

    <tfoot></tfoot>
    Member
    UserID UserPassword UserMood UserState FriendList
    jean@jessenger.com  password123  test  4  

    and this is the query has a result

    UPDATE Member SET UserState=2 WHERE UserID = jean@jessenger.com AND  UserPassword = password123


    Did i miss anything??

    Sunday, May 27, 2012 5:32 PM
  • User-1199946673 posted

    and this is the query has a result

    UPDATE Member SET UserState=2 WHERE UserID = jean@jessenger.com AND  UserPassword = password123


    Did i miss anything??

    Yes, you're missing delimiters! The result should be:

    UPDATE Member SET UserState=2 WHERE UserID = 'jean@jessenger.com' AND UserPassword = 'password123'

    But you really should use parameters, then you don't need to think about delimiters, but more important, you don't need to worry about SQL injections!

    http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

    Sunday, May 27, 2012 9:03 PM
  • User3866881 posted

    I am new to C# and trying to learn few things. The following is the code that i have to update an item in my database but doesn work any reason why?

    You are trying to use SqlParameter to assign values,but in fact your whole sql is a complete one;until now according to your situation,I don't think there's a must for you to use SqlParameter——

    string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Jean\Desktop\Jessenger.accdb;User Id=admin;Password=;"; 
                string queryString = "UPDATE Member SET UserState="  + person.State + " WHERE UserID = " + person.Username + " AND  UserPassword = " + person.Password; 
                using (OleDbConnection connection = new OleDbConnection(connectionString)) 
                { 
                    OleDbCommand command = connection.CreateCommand(); 
                    command.CommandText = queryString; 
                    try 
                    { 
                        connection.Open(); 
                        int rows = command.ExecuteNonQuery(); 
                        connection.Close(); 
                    } 
                    catch (Exception ex) 
                    { 
                        Console.WriteLine(ex.Message); 
                    } 
                }

    PS:If your update SQL is right,it should go well;but I don't suggest you doing so——for the sake of SQL Injection。

    Monday, May 28, 2012 9:36 PM
  • User-1199946673 posted

    PS:If your update SQL is right,it should go well

    Off course, the problem is that his SQL isn't right!

    but I don't suggest you doing so——for the sake of SQL Injection

    Isn't this exactly what I already said? I really don't understand why you keep repeating what others already explained?

    Tuesday, May 29, 2012 4:00 AM
  • User-1375742532 posted

    I understand that I must use, parameter so it doesnt allow SQL injections. I then created a function, which give user, password information and connectionString. Has follow

    private person GetUser(string user, string pass, string connectionString)
            {
                person retVal = new person(user, pass);
                using (OleDbConnection connection = new OleDbConnection(connectionString))
                {
                    OleDbCommand command = connection.CreateCommand();
                    command.CommandText = "SELECT * FROM Member WHERE UserID = @userName AND UserPassword = @passWord";
                    command.Parameters.AddWithValue("@userName",user);
                    command.Parameters.AddWithValue("@passWord",pass);
                    try
                    {    
                        connection.Open();
                        OleDbDataReader reader = command.ExecuteReader();
                        if (reader.HasRows)
                        {
                            reader.Read();
                            
                            retVal.Username = user;
                            retVal.Password = pass;
                            retVal.Mood = reader["userMood"].Value.ToString();
                            retVal.State = reader["userState"].Value.ToString();
                        }
                        reader.Close();
                    }
                    catch (Exception ex)
                    {
                        Messagebox.show(ex.Message);
                    }
                }
            return retVal;
        }

    And can is executed with the following method

    person myPerson = GetUser(txtUser.Text, txtPass.Text, Properties.Settings.Default.ConnectionString);
                if (!string.IsNullOrEmpty(myPerson.Username);
                {
                    JessengerList ChatList = new JessengerList();
                    this.Visible = false;
                    ChatList.Show();
                }

    I believe this would work, however when creating myPerson object, it doesnt recognize the method username. I might be right or I might have a total finger in the eye! I am on the right path?


    The person Class is has follow

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace Jessenger
    {
        class person
        {
            private static string username;
            private static string password;
            private static string mood;
            private static int state;
            /*
             *  State 1 = Online
             *  State 2 = Away
             *  State 3 = Busy
             *  State 4 = Offline
             */
    
            public person(String _username, String _password)
            {
                username = _username.ToLower();
                password = _password.ToLower();
            }
    
            public person(String _username, String _password, String _mood, int _state)
            {
                username = _username.ToLower();
                password = _password.ToLower();
                mood = _mood.ToLower();
                state = _state;
            }
    
            public static String Username
            {
                get
                {   
                    return username;
                }
                set
                {   
                    username = value;
                }
            }
    
            public static String Password
            {
                get
                {
                    return password;
                }
                set
                {
                    password = value;
                }
            }
    
            public static String Mood
            {
                get
                {
                    return mood;
                }
                set
                {
                    mood = value;
                }
            }
    
            public static int State
            {
                get
                {
                    return state;
                }
                set
                {
                    state = value;
                }
            }
    
            private static string toString()
            {
                return "The username is: " + username + ", the password is: " + password + " the user is in state " + state + " and the mood is: " + mood;
            }
        }
    }
    



    Tuesday, May 29, 2012 5:20 PM
  • User3866881 posted

    Hello:)

    I see that your person is a common class,but your username,password,……,ect are all static values?Why?For a common entity model class,you'd better use a non-static class property instead。

    And now,

    it doesnt recognize the method username

    Where's the method of "username"?I only see your variable name is "username"……

    Reguards!

    Tuesday, May 29, 2012 9:03 PM
  • User-1375742532 posted

    The original reason I was using a static method was to an object to be used accross many windows form. If i shouldnt use static to access the same object accross multiple what would be the best technique??

    Tuesday, May 29, 2012 9:17 PM
  • User3866881 posted

    Well……So:

    Where's the method of "username"?I only see your variable name is "username"……

    Reguards!

    Tuesday, May 29, 2012 9:22 PM
  • User-1375742532 posted
    The following is my get and set method for the username?? Only i am really missing the concept of c#
    public String Username
            {
                get
                {   
                    return username;
                }
                set
                {   
                    username = value;
                }
            }


    Tuesday, May 29, 2012 9:25 PM
  • User3866881 posted

    If i shouldnt use static to access the same object accross multiple what would be the best technique??

    In fact as far as I see,I think you can create another class that is for common use:

    public sealed class CommonClass
    {
      private CommonClass(){}
      public static User User{get;set;}
    }

    When you log in successfully,please directly set the User to CommonClass or fetch from it。Generally speaking,entity model class should be something like POCO……:-)

    Kindly correct me if you have a better way

    Reguards!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 29, 2012 9:25 PM
  • User3866881 posted

    The following is my get and set method for the username?? Only i am really missing the concept of c#
    public String Username          {              get              {                     return username;              }              set              {                     username = value;              }          }

    This looks nice except you've missed a "static" key word for UserName……So?

    Tuesday, May 29, 2012 9:28 PM
  • User-1375742532 posted

    Thanks I will study your concept. Thanks I lots. You said, it be easier to not use static, So I will try not to use it, I will try to understand more about stealed class, I never heard about this in java, or c++. So i will study it

    Tuesday, May 29, 2012 9:28 PM
  • User3866881 posted

    Thanks I will study your concept. Thanks I lots. You said, it be easier to not use static, So I will try not to use it, I will try to understand more about stealed class, I never heard about this in java, or c++. So i will study it

    Never mind!Welcome your feedback again!

    Tuesday, May 29, 2012 10:04 PM