How to insert the row into the table of SQL database?

Question

My issue can seem so trivial, but unfortunately I don't know how to solve that problem.

There's one table in one database. I have to add the data with clicking on the button to that table. When user clicks on the button, he's getting to some form where he/she can input the data. At the first time it would be good if I am able to input: id, kind of service, price. I've decided to create a new class where I would content all variables including these three variables. These variable are public.

Also I've decided to read the text from the textBoxes and to write this information to the variables of that class. In the second form there are 2 buttons. "Ok" and "Cancel". And I have decided to use ShowDialog.

I'm capable to output the table from the database to the DataGridView, but I am not well-aware how to add the data to my table and showcase that successfully in the datagridview after the inserting.

My class:

 public class AllDataDB
{ 
    public int id_serv;
    public double price;
    public string name;
}

The second form:

public partial class TypeService : Form
{
    public AllDataDB Class;
    public TypeService(AllDataDB t)
    {
        Class = t;
        InitializeComponent();
    }

    private void button2_Click(object sender, EventArgs e)
    {
        this.Close();
    }

    private void button1_Click(object sender, EventArgs e)
    {
        AllDataDB Class = new AllDataDB();

        Class.id_serv = Convert.ToInt32(textBox3.Text);
        Class.name = NameService.Text;
        Class.price = Convert.ToDouble(PriceService.Text);

        this.DialogResult = DialogResult.OK;
        this.Close();
    }
}

The work of the button calling the form and the query:

private void NewServe_Click(object sender, EventArgs e)
    { 

        AllDataDB Class = new AllDataDB();
        TypeService form = new TypeService(Class);

        if (form.ShowDialog() == DialogResult.OK)
        { // відповідно до класу створюється новий запис. INSERT.

                SqlConnection Con = new SqlConnection(connectionString);
                Con.Open();
                string Que = "INSERT INTO type_service " +
                        "VALUES(" + Class.id_serv + " ,'" + Class.name +
                        "' ," + Class.price + " );" +
                        "SELECT * FROM type_service";
                SqlCommand cmd = new SqlCommand(Que, Con);
                cmd.ExecuteNonQuery();
                Con.Close();

                SqlDataAdapter sqlDa = new SqlDataAdapter("SELECT * FROM type_service", Con);
                DataTable d = new DataTable(); 
                sqlDa.Fill(d);

                dataGridView3.AutoGenerateColumns = false;
                dataGridView3.DataSource = d;
        }
    }

https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection — mjwills, Mar 15 '21 at 08:18

Steve · Accepted Answer · 2021-03-15T08:29:34.693

There are many problems in the code shown. The most important ones are the way in which you read the values and how do you try to insert in the data. Inside the form TypeService you create a new instance of the AllDataDB class where you store the results. This instance is no more the one you have passed in input, so you need to read the values from the instance created by the TypeService form (stored in the global field Class). The second problem is the string concatenation of your values. This is a well known problem leading to parsing problems and sql injection. It is fixed by a parameterized query as shown below

private void NewServe_Click(object sender, EventArgs e)
{ 
    // Do no pass an instance of Class here, just pass null 
    // or remove it at all if you don't plan to reuse the form for updating 
    TypeService form = new TypeService(null);
    if (form.ShowDialog() == DialogResult.OK)
    { 
        // Add the using statement to ensure a proper release of resources.
        using SqlConnection Con = new SqlConnection(connectionString);
        Con.Open();
        // Parameterized query
        string Que = "INSERT INTO type_service VALUES(@id,@name,@price);";
        SqlCommand cmd = new SqlCommand(Que, Con);
        cmd.Parameters.Add("@id", SqlDbType.Int).Value = form.Class.id_serv;    
        cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = form.Class.name;
        cmd.Parameters.Add("@price", SqlDbType.Decimal).Value = form.Class.price;
        cmd.ExecuteNonQuery();

        SqlDataAdapter sqlDa = new SqlDataAdapter("SELECT * FROM type_service", Con);
        DataTable d = new DataTable(); 
        sqlDa.Fill(d);
        dataGridView3.AutoGenerateColumns = false;
        dataGridView3.DataSource = d;
    }
}

There are other minor problems. In the TypeService form and in the class AllDataDB you have used global fields instead of the more flexible way of using properties.

public class AllDataDB
{ 
    public int id_serv {get;set;}
    public double price {get;set;}
    public string name {get;set;}
}

as well

public AllDataDB Class {get;set;}

also, given the null passed to the constructor of the TypeService form you now need to ensure to not use that Class properties without cheching for its null value

Can you explain, please, where have I use "public AddDataDB Class {get; set;}"? — , Mar 15 '21 at 08:44
"_My problem is unhandled exception_" No, your problem is not only not handling exceptions, you must also fix what causes those exceptions. — Cleptus, Mar 15 '21 at 09:03

score 0 · Answer 2 · answered Mar 15 '21 at 09:06

Well, this aid was very useful. Thanks. But I've resolved this problem with creating a new 3 variables in the class TypeService. These variables are public too. And that is looking like this:

In the class TypeService:

public int i;
public string n;
public double p;

In the class MainCore:

cmd.Parameters.Add("@id", SqlDbType.Int).Value = form.i;// form.Class.id_serv;
cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = form.n;//form.Class.name;
cmd.Parameters.Add("@price", SqlDbType.Decimal).Value = form.p; //form.Class.price;

So it works. And I can remove the class AllDataDB, because that is not important and useful now. Thanks for help.

How to insert the row into the table of SQL database?

2 Answers2