Difficulty Inserting Data from C# form into a Database

Question

I've just started learning C# using Visual Studio 2015, and my task is to create a lottery program that saves the generated numbers into a database. I've tried various methods and none of them seem to make any additions to my table. Can anyone help me understand what I need to do take an Integer that has been generated and converted into a string/ textbox and then insert that value into my table.

Heres my current code below, button 2 being the button I am trying to use to save the data from the textboxes with.

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 System.Configuration;
using System.Data.SqlClient;

namespace WindowsFormsApplication2
{
    public partial class Form1 : Form
    {
        //Database details
        string connectionString;
        SqlConnection connection;
        public Form1()
        {
            InitializeComponent();
            connectionString = ConfigurationManager.ConnectionStrings["WindowsFormsApplication2.Properties.Settings.LottoConnectionString"].ConnectionString;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Random rnd = new Random();
            int[] slot = new int[6];
            int counter = 0;

            for (int i = 0; i < slot.Length; i++)
            {
                slot[i] = rnd.Next(0, 100);
            }

            //Converting generated ints to Strings for display
            textBox1.Text = (slot[0].ToString());
            textBox2.Text = (slot[1].ToString());
            textBox3.Text = (slot[2].ToString());
            textBox4.Text = (slot[3].ToString());
            textBox5.Text = (slot[4].ToString());
            textBox6.Text = (slot[5].ToString());

            //Incrementing Counter checks matches
            if (numericUpDown1.Value == slot[0])
            {
                counter += 1;
            }
            if (numericUpDown2.Value == slot[1])
            {
                counter += 1;
            }
            if (numericUpDown3.Value == slot[2])
            {
                counter += 1;
            }
            if (numericUpDown4.Value == slot[3])
            {
                counter += 1;
            }
            if (numericUpDown5.Value == slot[4])
            {
                counter += 1;
            }
            if (numericUpDown6.Value == slot[5])
            {
                counter += 1;
            }

            //display total matches
            textBox7.Text = ("You got" + counter + "/6 matches!");

           LottoDataSetTableAdapters.ResultsTableAdapter resultsTableAdapter =
            new LottoDataSetTableAdapters.ResultsTableAdapter();

            resultsTableAdapter.Insert((slot[0].ToString()), (slot[1].ToString()), (slot[2].ToString()), (slot[3].ToString()), (slot[4].ToString()), (slot[5].ToString()));
        }

        private void button2_Click(object sender, EventArgs e)
        {
            // Adding Data to Database
            string query = "INSERT INTO Results VALUES (@First)";
            using (connection = new SqlConnection(connectionString))
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                connection.Open();
                command.Parameters.AddWithValue("@First", textBox1.Text);
                command.Parameters.AddWithValue("@Second", textBox2.Text);
                command.Parameters.AddWithValue("@Third", textBox3.Text);
                command.Parameters.AddWithValue("@Fourth", textBox4.Text);
                command.Parameters.AddWithValue("@Fifth", textBox5.Text);
                command.Parameters.AddWithValue("@Sixth", textBox6.Text);
            }
        }
    }
}

All help will be greatly appreciated.

Please include the definition of your Results table. Is the results table a single column? — db_brad, Jan 22 '16 at 15:30

db_brad · Answer 1 · 2016-01-22T15:48:00.613

0

Your INSERT statement is missing the other parameters in the VALUES portion. You also need to execute the command and you were missing brackets for the using of the connection.

private void button2_Click(object sender, EventArgs e)
{
    // Adding Data to Database
    string query = "INSERT INTO Results (First, Second, Third, Fourth, Fifth, Sixth) VALUES (@First, @Second, @Third, @Fourth, @Fifth, @Sixth)";
    using (var connection = new SqlConnection(connectionString))
    {
        using (SqlCommand command = new SqlCommand(query, connection))
        {
            connection.Open();
            command.Parameters.AddWithValue("@First", textBox1.Text);
            command.Parameters.AddWithValue("@Second", textBox2.Text);
            command.Parameters.AddWithValue("@Third", textBox3.Text);
            command.Parameters.AddWithValue("@Fourth", textBox4.Text);
            command.Parameters.AddWithValue("@Fifth", textBox5.Text);
            command.Parameters.AddWithValue("@Sixth", textBox6.Text);

            command.ExecuteNonQuery();

        }
    }
}

edited Jan 22 '16 at 15:48

answered Jan 22 '16 at 15:35

db_brad

903
6
22

Thanks that makes a lot of sense. The only issue I have now is that when it reaches the command.ExecuteNonQuery(); It gives me an unhandled error caused by Column name or number of supplied values does not match table definition. As far as I am aware my Coloumns are "First" "Second" etc. Thanks for your time. – SteveBeck Jan 22 '16 at 15:42
Can you please get the table definition from sql server? If the column names truly are first, second, third... then I need to change my answer. – db_brad Jan 22 '16 at 15:44
I edited my answer to reflect a table with six columns. You may need to change this or the table definition based on your needs. – db_brad Jan 22 '16 at 15:48
The names reflected in the properties in the SQL server are "First" "Second" etc. – SteveBeck Jan 22 '16 at 15:50
Okay the Code now runs, however the table in my server explorer does not reflect any new additions. Is there some other location I should be looking to confirm that it is inserting the values into the database? – SteveBeck Jan 22 '16 at 15:56
What is the connection string in your config file? – db_brad Jan 22 '16 at 16:01

monkeylumps · Answer 2 · 2016-01-22T15:34:49.423

-1

It looks like this question as been asked and answered before - have a look here - How to insert data into SQL Server

edit - my first impression was wrong, I can't see where you are executing your query against the db. its been a while since it have written ado code manual without using an orm so forgive me if i am wrong.

edited Jan 22 '16 at 15:34

answered Jan 22 '16 at 15:31

monkeylumps

747
4
10
23

Difficulty Inserting Data from C# form into a Database

2 Answers2