Incorrect syntax near 'Bairstow'. I am getting error on the name of the player which is stored in the sql server

Question

protected void btn_Save_Click(object sender, EventArgs e)
{
    foreach (GridViewRow row in GridView1.Rows)
    {
        CheckBox status = row.Cells[1].FindControl("cb_Cap") as CheckBox;
        //int Credits = Convert.ToInt32(row.Cells[0].Text);
        string Name = Convert.ToString(row.Cells[0].Text);
        if (status.Checked)
        {
            updaterow(Name, "Captain");
        }
        else
        {   
            updaterow(Name, "None");
        }
    }
}

    private void updaterow(string Name, string markstatus)
    {
        string mycon = @"Data Source=DESKTOP-7IGRD5V\SQLEXPRESS; Initial Catalog =ULogin; Integrated Security = True";
        string updateData = "Update teamf set role='" + markstatus + "' where Name=" + Name;
        SqlConnection con = new SqlConnection(mycon);
        con.Open();
        SqlCommand cmd = new SqlCommand(updateData);
        cmd.Connection = con;
        cmd.ExecuteNonQuery();
        lbl_Cap.Text = "Captain Added";
        con.Close();
    }

Answer 1

It's worse than you know. In the current code, the con.Close(); line won't run if you have an exception. If this happens often enough you can completely run Sql Server out of connections and effectively lock yourself out of the database.

Even worse still, I can use the Name value to run any arbitrary code I want on your server, simply by starting my name with '';. Imagine if I decided to tell you my name was '';Drop Table teamf;. Think carefully about what would happen.

This should fix both those issues, as well as resolve your question:

private void updaterow(string Name, string markstatus)
{
    string mycon = @"Data Source=DESKTOP-7IGRD5V\SQLEXPRESS; Initial Catalog =ULogin; Integrated Security = True";
    string updateData = "UPDATE teamf SET role= @Role WHERE Name = @Name";

    using (var conn = new SqlConnection(mycon))
    using (var cmd = new SqlCommand(updateData, conn))
    { 
        // Use actual column types and lengths from the database here
        cmd.Parameters.Add("@Role", SqlDbType.NVarChar, 25).Value = markstatus;
        cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 25).Value = Name;

        con.Open();
        cmd.ExecuteNonQuery();
    } //using block will guarantee the connection is closed, *even if an exception is thrown*
    lbl_Cap.Text = "Captain Added";
}

Always always ALWAYS use parameters like this to put data into a query! Anything less is practically begging to wake up a year from now to find out you were hacked six months ago. If there's any any other code at all in the application using string concatenation like this to build SQL, fixing that (because it really is broken) is job #1.

Answer 2

The issue is on your query

string updateData = "Update teamf set role='" + markstatus + "' where Name=" + Name;

The Name should have quotes. Update your query with single quotes

string updateData = $"UPDATE teamf SET role='{markstatus}' WHERE Name='{Name}'";