Tjazz Tjazz - 2 months ago 17
SQL Question

Update database field SQL in C#

I have tried many things, with no result. My question is, as the title says, how do I update a field in a database using SQL? Since I am a beginner with coding and SQL, I will copy my whole code below, not knowing what information you may need:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using HPJFRMS;

namespace HPJFRMS
{
public partial class HomeFRM : Form
{
private string conn;
MySqlConnection connect;
string _naam = "";
Form _Loginfrm;

public HomeFRM(Form logFrom, string _name)
{
_Loginfrm = logFrom;
InitializeComponent();
lbWelkom.Text = "welkom " + _name;
_naam = _name;
}

private void HomeFRM_Load(object sender, EventArgs e)
{
tmCheck.Enabled = true;
}

private bool Todo_ophalen()
{
db_connection();
MySqlCommand cmdRead = new MySqlCommand();
cmdRead.CommandText = "SELECT `todo` FROM `user` WHERE `username` LIKE '" + _naam + "'";
cmdRead.Connection = connect;
MySqlDataReader tdOphalen = cmdRead.ExecuteReader();
if (tdOphalen.Read())
{
tbTodo.Text = tdOphalen.GetString(0);
connect.Close();
return true;
}
else
{
connect.Close();
return false;
}
}

private void db_connection()
{
try
{
conn = "Server=127.0.0.1;Database=users;Uid=root;Pwd=;";
connect = new MySqlConnection(conn);
connect.Open();
}
catch (MySqlException e)
{
throw;
}
finally
{
lbStatus.ForeColor = Color.Red;
}
}

private void btBewerk_Click(object sender, EventArgs e)
{
if (btBewerk.Text == "Bewerken")
{
tbTodo.ReadOnly = false;
btBewerk.Text = "Opslaan";
tmCheck.Enabled = false;
}
else
{
/* HERE COMES THE "UPDATE" CODE */
}
}

private void tmCheck_Tick(object sender, EventArgs e)
{
try
{
bool T = Todo_ophalen();
if (T)
{
lbStatus.ForeColor = Color.Green;
lbStatus.Text = "Online";
}
}
catch
{
lbStatus.ForeColor = Color.Red;
lbStatus.Text = "Offline";
}
}
}
}

Answer

There are different methods, you can use any of these.

Method 1: using simple SQL query

public void Update(Int UserId,String UserName  )
{
    SqlConnection con = new SqlConnection("Your Connection String");  
    con.Open();  
    string str = " UPDATE [dbo].[User] SET [UserName] = "+UserName  +" WHERE   [UserId] ="+ UserId+"";
    SqlCommand cmd = new SqlCommand(str , con);
    cmd.ExecuteNonQuery();
    con.Close();
}

Method 2: using a stored procedure

First execute your stored procedure in database.

Example

CREATE PROCEDURE [dbo].[UpdateUser]   
    @UserId int,   
    @UserName varchar(25)   
AS   
BEGIN   
    UPDATE [dbo].[User] 
    SET [UserName] = @UserName 
    WHERE [UserId] = @UserId   
END   

public void Update(Int UserId,String UserName  )
{ 
    SqlConnection con = new SqlConnection("Your Connection String");  
    con.Open();

    SqlCommand cmd = new SqlCommand("UpdateUser", con); //UpdateUser is the name of stored procedure you created
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.AddWithValue("UserName ", UserName );
    cmd.Parameters.AddWithValue("UserId", UserId);

    cmd.ExecuteNonQuery();

    con.Close();
 }