none
how to update and insert to database sql when clicking 1 button

    Question

  • Hello 

    im new to sql and C# trying to make a login system with friend request option

    trying to find somekinda code to both update and insert data into sql server from clicking 1 button

    i got 2 textboxes 1 button. 

    textbox1 is for username in database 

    textbox2 is for friendusername in database

    so i want to check if username A and his friendusername B already exist in database 

    if it does messagebox ("request already sent") 

    and so on.. 

    i got this code to insert and update into the database ... but dont know how to combine it. 

     for update i got this code but it dont ofc makes new columns

    cmd.CommandText = "update Request set username='" + this.textbox1.Text + "',friendusername='" + this.textBox2.Text +  "'";

    for insert i got this code:   

    cmd.CommandText = "insert into Request (username,friendusername) values ('" + this.label3.Text + "','" + this.textBox1.Text +  "')";

    and this i can keep adding million columns with ... 

    would be awesome if some1 could help a beginner continueing his path to become better. 

    Thursday, April 13, 2017 8:49 AM

Answers

  • Mr Champagne --- FYI, you shouldn't be concatenating user inputs into SQL commands, because of the danger of what is called "SQL injection". Here's a link that describes what that means: https://www.techopedia.com/definition/4126/sql-injection  and one of my favorites:  https://xkcd.com/327/

    The way to avoid this is to use Parameters, as both Sabah and Karen showed in their examples.


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, April 15, 2017 4:54 AM
    Moderator
  • Hello,

    The following uses parameters which is best practice (and recommended by Bonnie) which not only makes data safer but also if there were  apostrophes in the value sent to the query, without parameters this would raise an exception for malformed query.

    Next, always best to separate user interface and data operations so I recommend as shown below using a class for the data operations.

    Next, using a DataTable is overkill, all we need is ExecuteScalar.

    In the code below I have two bool variables which could had been one but wanted to make the code paths clear.

    using System.Data.SqlClient;
    
    
    public class Operations
    {
        /// <summary>
        /// Replace with your SQL Server name
        /// </summary>
        private string Server = "KARENS-PC";
        /// <summary>
        /// Database in which data resides, see SQL_Script.sql
        /// </summary>
        private string Catalog = "ForumExamples";
        /// <summary>
        /// Connection string for connecting to the database
        /// </summary>
        private string ConnectionString = "";
        /// <summary>
        /// Setup the connection string
        /// </summary>
        public Operations()
        {
            ConnectionString = $"Data Source={Server};Initial Catalog={Catalog};Integrated Security=True";
        }
        public bool MakeRequest(string userName, string friendusername)
        {
            var success = false;
            var result = false;
    
            using (SqlConnection cn = new SqlConnection { ConnectionString = ConnectionString })
            {
                using (SqlCommand cmd = new SqlCommand { Connection = cn })
                {
    
                    cmd.CommandText = @"SELECT COUNT(1)  
                                        FROM Request 
                                        WHERE username = @username AND friendusername = @friendusername";
    
                    cmd.Parameters.AddWithValue("@username", userName);
                    cmd.Parameters.AddWithValue("@friendusername", friendusername);
    
                    cn.Open();
    
                    result = ((int)cmd.ExecuteScalar() == 1);
                    if (result)
                    {
                        success = false;
                    }
                    else
                    {
    
                        cmd.CommandText = @"INSERT INTO dbo.Request (username,friendusername) 
                                            VALUES (@username,@friendusername)";
    
                        result = cmd.ExecuteNonQuery() == 1;
    
                        success = result;
    
                    }
                }
            }
    
            return success;
    
        }
    }
    
    

    Form code

    using System;
    using System.Windows.Forms;
    
    namespace WindowsFormsApplication2
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                if (!string.IsNullOrWhiteSpace(textBox1.Text))
                {
                    var ops = new Operations();
                    if (ops.MakeRequest(label3.Text, textBox1.Text))
                    {
                        MessageBox.Show("Request sent!");
                    }
                    else
                    {
                        MessageBox.Show("Request already sent!");
                    }
                }
                else
                {
                    MessageBox.Show("Missing friendly name");
                }
    
            }
        }
    }
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, April 15, 2017 10:17 AM
    Moderator

All replies

  • Hi __Mr__Champagne,

    You can try following concept but modify it as per your requirement.

                if (txtUserName.Text != "" & txtBoxPassword.Text != "")
                {
                    string queryText = "SELECT Count(*) FROM registration " +
                                       "WHERE user_name = @Username AND password = @Password";
                    using (SqlConnection cn = new SqlConnection("Server = (local); DataBase=login; Integrated Security=SSPI"))
                    using (SqlCommand cmd = new SqlCommand(queryText, cn))
                    {
                        cn.Open();
                        cmd.Parameters.AddWithValue("@Username", txtUserName.Text);  // cmd is SqlCommand 
                        cmd.Parameters.AddWithValue("@Password", txtBoxPassword.Text);
                        int result = (int)cmd.ExecuteScalar();
                        if (result > 0)
                        {
                            //MessageBox.Show("Logged In.");
                            
    
                        }
    
                        else
                            MessageBox.Show("Please try again.");
                    }
                }


    Thanks,
    Sabah Shariq

    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered"Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


    Thursday, April 13, 2017 9:04 AM
    Moderator
  • Hello,

    If using MS-Access for the login, take a look at my MSDN code sample that shows how to login but does not show how to insert or update.

    To provide simple examples of insert and update, the following code would do the trick with using the database from the code sample above.

    /// <summary>
    /// Add a user to the database
    /// </summary>
    /// <param name="UserName"></param>
    /// <param name="UserPassword"></param>
    public void AddUser(string UserName, string UserPassword)
    {
        using (OleDbConnection cn = new OleDbConnection { ConnectionString = Builder.ConnectionString })
        {
            using (OleDbCommand cmd = new OleDbCommand { Connection = cn })
            {
                cmd.CommandText = "INSERT INTO Users (UserName, UserPassword) VALUES (@UserName,@UserPassword) ";
                cmd.Parameters.AddWithValue("@UserName", UserName);
                cmd.Parameters.AddWithValue("@UserPassword", UserPassword);
    
                cn.Open();
    
                cmd.ExecuteNonQuery();
            }
        }
    }
    /// <summary>
    /// Update user password by user name.
    /// Generally speaking when a user log in we would possible remember their
    /// primary key in the database table so we can find their record better then
    /// by user name.
    /// </summary>
    /// <param name="UserName"></param>
    /// <param name="UserPassword"></param>
    public void UpdatePassword(string UserName, string UserPassword)
    {
        using (OleDbConnection cn = new OleDbConnection { ConnectionString = Builder.ConnectionString })
        {
            using (OleDbCommand cmd = new OleDbCommand { Connection = cn })
            {
                cmd.CommandText = "UPDATE Users SET UserPassword = @UserPassword WHERE UserName = @UserPassword";
                cmd.Parameters.AddWithValue("@UserPassword", UserPassword);
                cmd.Parameters.AddWithValue("@UserName", UserName);
    
                cn.Open();
    
                cmd.ExecuteNonQuery();
            }
        }
    }
    

    To a tad more security the database in the code sample is password protected.

    So that is the basics which you can learn from and adapt to your project.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, April 13, 2017 9:31 AM
    Moderator
  •  

    Hey Sabah and karen Thx for for commenting my question ... both helped me getting to my end result

    this is the code i ended up with... 

    for user to send a friend request ... after user have logged in. 

    i got to understanding its simply just like making an new registre 

    dident need to update table.. 

    private void button1_Click(object sender, EventArgs e)

            {
                SqlConnection con = new SqlConnection(@"Data Source=database name;Initial Catalog= table name;Integrated Security=True");
      string str1 = "select username,friendusername from Request where Username='" + label3.Text + "' and friendusername='" + textBox1.Text + "'";
                SqlCommand cmd = new SqlCommand(str1, con);
                SqlDataAdapter da = new SqlDataAdapter(str1, con);
                SqlDataAdapter dk = new SqlDataAdapter("Select * from Request Where username='" + label3.Text + "' and friendusername='" +             textBox1.Text + "' ", con);

                DataTable dt = new DataTable();
                dk.Fill(dt);
                if (dt.Rows.Count == 1)
                {
                    if (textBox1.Text != null)
                        MessageBox.Show("Request already sent");
                }
                else
                {
                    con.Open();
                    cmd.CommandText = "insert into Request (username,friendusername) values ('" + this.label3.Text + "','" + this.textBox1.Text + "')";
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("Request have been sent");
                    cn.Close();
                }
            } 

           
    Saturday, April 15, 2017 4:14 AM
  • Mr Champagne --- FYI, you shouldn't be concatenating user inputs into SQL commands, because of the danger of what is called "SQL injection". Here's a link that describes what that means: https://www.techopedia.com/definition/4126/sql-injection  and one of my favorites:  https://xkcd.com/327/

    The way to avoid this is to use Parameters, as both Sabah and Karen showed in their examples.


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, April 15, 2017 4:54 AM
    Moderator
  • Hello,

    The following uses parameters which is best practice (and recommended by Bonnie) which not only makes data safer but also if there were  apostrophes in the value sent to the query, without parameters this would raise an exception for malformed query.

    Next, always best to separate user interface and data operations so I recommend as shown below using a class for the data operations.

    Next, using a DataTable is overkill, all we need is ExecuteScalar.

    In the code below I have two bool variables which could had been one but wanted to make the code paths clear.

    using System.Data.SqlClient;
    
    
    public class Operations
    {
        /// <summary>
        /// Replace with your SQL Server name
        /// </summary>
        private string Server = "KARENS-PC";
        /// <summary>
        /// Database in which data resides, see SQL_Script.sql
        /// </summary>
        private string Catalog = "ForumExamples";
        /// <summary>
        /// Connection string for connecting to the database
        /// </summary>
        private string ConnectionString = "";
        /// <summary>
        /// Setup the connection string
        /// </summary>
        public Operations()
        {
            ConnectionString = $"Data Source={Server};Initial Catalog={Catalog};Integrated Security=True";
        }
        public bool MakeRequest(string userName, string friendusername)
        {
            var success = false;
            var result = false;
    
            using (SqlConnection cn = new SqlConnection { ConnectionString = ConnectionString })
            {
                using (SqlCommand cmd = new SqlCommand { Connection = cn })
                {
    
                    cmd.CommandText = @"SELECT COUNT(1)  
                                        FROM Request 
                                        WHERE username = @username AND friendusername = @friendusername";
    
                    cmd.Parameters.AddWithValue("@username", userName);
                    cmd.Parameters.AddWithValue("@friendusername", friendusername);
    
                    cn.Open();
    
                    result = ((int)cmd.ExecuteScalar() == 1);
                    if (result)
                    {
                        success = false;
                    }
                    else
                    {
    
                        cmd.CommandText = @"INSERT INTO dbo.Request (username,friendusername) 
                                            VALUES (@username,@friendusername)";
    
                        result = cmd.ExecuteNonQuery() == 1;
    
                        success = result;
    
                    }
                }
            }
    
            return success;
    
        }
    }
    
    

    Form code

    using System;
    using System.Windows.Forms;
    
    namespace WindowsFormsApplication2
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                if (!string.IsNullOrWhiteSpace(textBox1.Text))
                {
                    var ops = new Operations();
                    if (ops.MakeRequest(label3.Text, textBox1.Text))
                    {
                        MessageBox.Show("Request sent!");
                    }
                    else
                    {
                        MessageBox.Show("Request already sent!");
                    }
                }
                else
                {
                    MessageBox.Show("Missing friendly name");
                }
    
            }
        }
    }
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, April 15, 2017 10:17 AM
    Moderator
  • Hi __Mr__Champagne,

    If your issue is solved please Mark as answer or Vote as helpful post to the appropriate answer so that it will help other members to find solution if they faces similar issue.

    Your understanding and cooperation will be grateful.

    Thanks,
    Sabah Shariq

    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered"Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


    Friday, May 12, 2017 1:01 PM
    Moderator