Can select from but not insert into database mysql C# ASP.NET

Question

I have a connection to my db and I can retrieve data no problem but I'm unable to insert. The alert also isn't being displayed so I'm sure the query isn't being executed and it's just redirecting to the aspx page. I've tried many different solutions but I assume the problem is not where I'm looking. Here's the code, I hope someone can help.

Code behind:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Threading;
using System.Threading.Tasks;

namespace Childrens.Admin
{
public partial class WebForm1 : System.Web.UI.Page
{
    protected void btnViewStaff_ServerClick(object sender, EventArgs e)
    {
        divAddStaff.Visible = false;
        staffGridView.Visible = true;
    }

    protected void btnAddNewStaff_ServerClick(object sender, EventArgs e)
    {
        staffGridView.Visible = false;
        divAddStaff.Visible = true;
    }

    protected void btnSubmitStaff_ServerClick(object sender, EventArgs e)
    {
        if (txtPassword == txtCPassword)
        {
            using (SqlConnection addStaffConn = new SqlConnection(ConfigurationManager.ConnectionStrings["myConn"].ToString()))
            {
                try
                {
                    addStaffConn.Open();

                    string query = "INSERT INTO [Staff] (staff_fname,staff_sname,staff_email,staff_pass) VALUES ('" + txtFName + "','" + txtSName + "','" + txtEmail + "','" + txtPassword+"')"; //(@fname,@sname,@email,@pass)";
                    SqlDataAdapter staffAdapter = new SqlDataAdapter();
                    SqlCommand addStaffCommand = new SqlCommand(query, addStaffConn);

                    /*addStaffCommand.Parameters.AddWithValue("@fname", txtFName);
                    addStaffCommand.Parameters.AddWithValue("@sname", txtSName);
                    addStaffCommand.Parameters.AddWithValue("@email", txtEmail);
                    addStaffCommand.Parameters.AddWithValue("@pass", txtPassword);*/
                    staffAdapter.InsertCommand = addStaffCommand;
                    staffAdapter.InsertCommand.ExecuteNonQuery();


                    addStaffConn.Close();
                    Response.Write(String.Format("<script>alert('The entry was successful!');window.location='{0}';</script>", "URL=staff.aspx"));

                }
                catch (Exception ex)
                {
                    Response.Write(String.Format("<script>alert('The entry was successful!');window.location='{0}';</script>", "URL=staff.aspx"));
                }
                finally
                {
                    if (addStaffConn.State == System.Data.ConnectionState.Open)
                    {
                        addStaffConn.Close();
                    }
                    addStaffConn.Dispose();
                }
            }
        }
    }
}
}

web.config file:

<?xml version="1.0" encoding="utf-8"?>

<!--
For more information on how to configure your ASP.NET application, please 
visit
https://go.microsoft.com/fwlink/?LinkId=169433
-->
<configuration>
<appSettings>
    <add key="ValidationSettings:UnobtrusiveValidationMode" value="None" />
</appSettings>
<system.web>
<compilation debug="true" targetFramework="4.6.1"/>
<httpRuntime targetFramework="4.6.1"/>
</system.web>
<system.codedom>
<compilers>
  <compiler language="c#;cs;csharp" extension=".cs" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.CSharpCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=1.0.8.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
    warningLevel="4" compilerOptions="/langversion:default /nowarn:1659;1699;1701"/>
  <compiler language="vb;vbs;visualbasic;vbscript" extension=".vb"
    type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.VBCodeProvider, 
   Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=1.0.8.0, 
Culture=neutral, PublicKeyToken=31bf3856ad364e35"
    warningLevel="4" compilerOptions="/langversion:default /nowarn:41008 
/define:_MYTYPE=\&quot;Web\&quot; /optionInfer+"/>
</compilers>
</system.codedom>
<connectionStrings>
<add name="myConn" connectionString="server=localhost;user 
id=root;persistsecurityinfo=True;database=childrens" />
<add name="childrensConnectionString" 
connectionString="server=localhost;user id=root;password=password;persistsecurityinfo=True;database=childrens;allowuservariables=True"
    providerName="MySql.Data.MySqlClient" />
 </connectionStrings>

 </configuration>

Answer 1

You should use MySQL's .Net Connector, instead of a library intended for MS SQL Server. Also, data adapters tend to be used more for data tables and similar things; for your needs simply executing "command" objects should be sufficient.

The security vulnerabilities everyone is referring to is that your query (at best) breaks the second an "fname", "sname", etc... contains one or more apostrophes; you should look into parameterized queries to avoid such issues.

Edit: Also, [ and ] are field delimiters for Microsoft databases (MS SQL Server, and MS Access); the ` (on the ~ key) is used by MySQL.

Edit#2: Vulnerability example:

INSERT INTO [Staff] (staff_fname,staff_sname,staff_email,staff_pass) VALUES ('" + txtFName + "','" + txtSName + "','" + txtEmail + "','" + txtPassword+"')"

User puts in their first name as O','','','then'), ('they', 'can', 'add', 'multiple'), ('users','or','possibly','worse'), ('without','even','causing','an'), ('error