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.

Answer 1

You need to read more about c# and sql basics

Here the skills used here read them :

• SQL CREATE TABLE Statement

• SQL Operators

• Multiline String Literal in C# - Stack Overflow

• SQLite Replace Statement - Tutlane

• SQLite: Unique Constraints

• SQL INSERT INTO Statement

• sql - Drop existing table in SQLite, when IF EXISTS operator is not supported - Stack Overflow

• SqlCommand.ExecuteScalar Method (System.Data.SqlClient) | Microsoft Docs

• SQL SELECT Statement

• What are the uses of "using" in C# - Stack Overflow

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();
        }

    }
}