C# insert data into database windows form app

Question

I need to build a application where people can make a reservation but before doing that they need to fill in some information. I get this error code at the moment when i try to save the data: An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

This is my code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace BonTemps
{
    public partial class Home : Form
    {
        public Home()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            var Form1 = new Form1();
            Form1.Show();
        }

        private void tabPage1_Click(object sender, EventArgs e)
        {

        }

        private void label2_Click(object sender, EventArgs e)
        {

        }

        private void Home_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'bonTempsDBDataSet.Tafel' table. You can move, or remove it, as needed.
            this.tafelTableAdapter.Fill(this.bonTempsDBDataSet.Tafel);

        }

        private void btnOpslaan_Click(object sender, EventArgs e)
        {
            SqlConnection sc = new SqlConnection();
            SqlCommand com = new SqlCommand();
            sc.ConnectionString = ("Data Source=ACER;Initial Catalog=BonTempsDB;Integrated Security=True");
            sc.Open();

            com.Connection = sc;
            com.CommandText = (@"INSERT INTO Klant (Naam, Adres, Woonplaats, Telefoonnummer, EmailAdres), VALUES ('" + txtNaam.Text + "','" + txtAdres.Text + "','" + txtWoon.Text + "','" + txtTel.Text + "','" + txtMail.Text + "'");
            com.ExecuteNonQuery();

            sc.Close();

        }

    }
}

Where in the code did the error occur? You could wrap it in `Try-Catch` to get some more info on the exception, and please post the details of this. — Barry O'Kane, Jul 01 '16 at 12:40
why do you have a comma in Insert command before the VALUES? — Krishnanunni Jeevan, Jul 01 '16 at 12:42
For best practice you should be using com.Parameters to add the values to your command — Jared Stroebele, Jul 01 '16 at 12:47
This should be a mandatory reading for anyone that wants to write any kind of sql related code. [How does the SQL injection from the “Bobby Tables” XKCD comic work?](http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work) — Steve, Jul 01 '16 at 12:54

score 1 · Accepted Answer · answered Jul 01 '16 at 12:52

1

Remove the comma Before VALUES.

If that is not enough, you can debug and copy the generated string from Command Text and try running it directly in SQL Server Mangement Studio or similar

answered Jul 01 '16 at 12:52

Jonny

1,037
7
15

score 1 · Answer 2 · answered Jul 01 '16 at 13:00

1

A typographical error remove the COMMA before the word VALUES.

answered Jul 01 '16 at 13:00

eyeshield21

186
8

Alex · Answer 3 · 2016-07-01T12:57:02.620

You have to pass an open SqlConnection to your SqlCommand to make it work:

com.Connection = sc;

Also, consider using named parameters to pass data to your query to make your query more error-proof:

SqlConnection sc = new SqlConnection();
SqlCommand com = new SqlCommand();
sc.ConnectionString = ("Data Source=ACER;Initial Catalog=BonTempsDB;Integrated Security=True");
sc.Open();

com.Connection = sc;
com.CommandText = @"INSERT INTO Klant (Naam, Adres, Woonplaats, Telefoonnummer, EmailAdres) VALUES (@naam, @adres, @woon, @tel, @mail)";
com.Parameters.AddWithValue("@naam", txtNaam.Text);
com.Parameters.AddWithValue("@adres", txtAdres.Text);
com.Parameters.AddWithValue("@woon", txtWoon.Text);
com.Parameters.AddWithValue("@tel", txtTel.Text);
com.Parameters.AddWithValue("@mail", txtMail.Text);

com.ExecuteNonQuery();
sc.Close();

Oh, I guess I missed that. Anyway using named parameters is a good suggestion. — Alex, Jul 01 '16 at 13:06

score 0 · Answer 4 · answered Jul 01 '16 at 12:57

using (var sc = new SqlConnection("Data Source=ACER;Initial Catalog=BonTempsDB;Integrated Security=True"))
{
    using (var com = new SqlCommand("sql cmd text", sc))
    {
        try
        {
            sc.Open();
            com.ExecuteNonQuery();
        }
        catch
        {

        }
    }
}

C# insert data into database windows form app

4 Answers4