how to update data stored in a sqlite table within c# with a user input

Question

I want to update a user name and password stored in a SQLite database with a new username and password provided by a user so that they can update their user details.

I have already tried to look up solutions to my problem and have found similar users with a similar requirement but when I try to implement their solutions with my code it doesn't seem to work.

SQLiteConnection con = new SQLiteConnection("Data Source=Users.sqlite;Version=3;");

SQLiteCommand cmd = new SQLiteCommand("select * from UserInfo where username like @username and password = @password;", con);
cmd.Parameters.AddWithValue("@username", oldusername);
cmd.Parameters.AddWithValue("@password", oldpassword);
con.Open();

SQLiteDataReader sdr = cmd.ExecuteReader();

if ((sdr.Read() == true))
{
    MessageBox.Show("Username and Password Updated Successfully!",
                    "Task Completed");

    string update ="UPDATE UserInfo SET UserName='" + newusername + "', Password='" + newpassword + "'  WHERE (Username='" + oldusername + "' AND Password ='" + oldusername + "');";
    con.Close();
}
else
{
    MessageBox.Show("Invalid username or password",
                    "Incorrect details entered");
}

The problem is that my code checks if the old username and password is stored in the UserInfo table but it doesn't update the table with the new username and password. I don't know what I am doing wrong so it would be great if someone could correct me and maybe improve the code. Thanks in advance.

First, gluing data into strings to make SQL like that has not been the correct way to build SQL for a very long time. Use SQL Parameters for the INSERT as you did for the SELECT (though why you are using LIKE is odd). Then never store passwords as plain text - hash and salt them. Finally, you should use `Add(string, dbtype)` rather than `AddWithValue` — Ňɏssa Pøngjǣrdenlarp, Jan 05 '19 at 23:39
I don't see any code in which you actually execute the `UPDATE` query... — Nick, Jan 06 '19 at 02:10

Mohamed Elrashid · Answer 1 · 2019-01-05T23:58:42.460

You need to read more about c# and sql basics

Here the skills used here read them :

Here the working Code

using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp4
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var con = new SQLiteConnection("Data Source=Users.sqlite;Version=3;"))
            using (var cmd = new SQLiteCommand())
            {

                cmd.Connection = con;
                con.Open();


                var cmd1 = @"
                    DROP TABLE IF EXISTS UserInfo;
                    CREATE TABLE IF NOT EXISTS UserInfo(username varchar(255),password 
                                  varchar(255),CONSTRAINT u_username UNIQUE (username));
                    INSERT INTO UserInfo(username,password) VALUES ('mohamed', '12345');
                    ";
                var cmd2 = @"select count(*) from UserInfo where username = @curent_username
                                and password = @curent_password;";
                var cmd3 = @"UPDATE UserInfo SET UserName = @new_username , Password= @new_password
                            where username = @curent_username and password = @curent_password;";


                cmd.CommandText = cmd1;
                cmd.ExecuteNonQuery();

                cmd.CommandText = cmd2;
                cmd.Parameters.AddWithValue("@curent_username", "mohamed");
                cmd.Parameters.AddWithValue("@curent_password", "12345");
                var userCount = (long)cmd.ExecuteScalar();
                if (userCount == 1)
                {
                    cmd.CommandText = cmd3;
                    cmd.Parameters.AddWithValue("@new_username", "adam");
                    cmd.Parameters.AddWithValue("@new_password", "6789");
                    var result = (Int32)cmd.ExecuteNonQuery();
                    if (result == 1)
                    {
                        Console.WriteLine("Username and Password Updated Successfully! | Task Completed");
                    }
                }
                else
                {
                    Console.WriteLine("Invalid username or password |Incorrect details entered");
                }
            }

            Console.ReadLine();
        }

    }
}

do you need more help ? – Mohamed Elrashid Jan 07 '19 at 05:33 — Mohamed Elrashid, Jan 07 '19 at 05:33

how to update data stored in a sqlite table within c# with a user input

1 Answers1

Here the working Code