C# .net winforms data insert error

Question

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

namespace crudwithlogin
{
    public partial class student_registration : Form
    {
        SqlConnection conn = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Android\Documents\Data.mdf;Integrated Security=True;Connect Timeout=30");

        public student_registration()
        {
            InitializeComponent();
        }

        private void student_registration_Load(object sender, EventArgs e)
        {
            label1.Text = "You are logged in as "+((Form)this.MdiParent).Controls["label1"].Text;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "insert into student values ('" 
                                                   + textBox1.Text 
                                                   + "','" 
                                                   + textBox2.Text 
                                                   + "''" 
                                                   + dateTimePicker2.Value.ToString() 
                                                   + "','" 
                                                   + textBox4.Text 
                                                   + "','" 
                                                   + comboBox2.Text 
                                                   + "','" 
                                                   + textBox6.Text 
                                                   + "','" 
                                                   + dateTimePicker1.Value.ToString() 
                                                   + "')";
            cmd.ExecuteNonQuery();
            conn.Close();
            MessageBox.Show("Data Added Successfully. ");
        }
    }
}

Hello Experts I am creating a Windows forms app using C#, I am trying to insert data into a database but I am getting an error of values and columns do not match even though I am entering the right value for right attribute.

I have 8 attributes (id with auto increment), and for the remaining 7 columns I am entering 7 values but I get error of value not match.

Please help me solve this problem. I have attached the code and screenshots of problem

Error I get is shown here:

enter image description here

Database schema screenshot is here

enter image description here

Possible duplicate of [sql server: Column name or number of supplied values does not match table definition](http://stackoverflow.com/questions/20372361/sql-server-column-name-or-number-of-supplied-values-does-not-match-table-defini) — Shell, Nov 05 '16 at 17:17
[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection — marc_s, Nov 05 '16 at 22:07

score 1 · Accepted Answer · answered Nov 05 '16 at 17:00

('" + textBox1.Text + "','" + textBox2.Text + "''" + dateTimePicker2.Value.ToString() + "','" 
//missed , ------------------------------------^

Also use Command.Parameters in future and you will avoid problems like this ! Also you will be protected from SqlInjection. Here example how to define your params.

 SqlCommand cmd = conn.CreateCommand();
 cmd.CommandType = CommandType.Text;
 cmd.CommandText = @"insert into TableNameFromDB values (@ID, @Title)";
 cmd.Parameters.AddWithValue("@ID", yourIDValue);
 cmd.Parameters.AddWithValue("@Title", yourTitleValue);
 cmd.ExecuteNonQuery();
 conn.Close();

Also don't share one connection in your app, use multiple connections. Connection pool is your friend. Check what is using block and use it for auto disposing of your IDisposable objects( example: SqlConnection).

what will i write place of 'yourIDValue' ? textbox1.text ? – Precious Pari Nov 05 '16 at 17:27 — Precious Pari, Nov 05 '16 at 17:27

score 0 · Answer 2 · answered Nov 05 '16 at 16:59

0

Carefully look your insert query there is a , (coma)missing. Could you please correct your query as following:

"insert into student values ('" + textBox1.Text + "','" + textBox2.Text + "','" + dateTimePicker2.Value.ToString() + "','" + textBox4.Text + "','" + comboBox2.Text + "','" + textBox6.Text + "','" + dateTimePicker1.Value.ToString() + "')";

If yet not works then may be problem with database structure and corresponding values.

answered Nov 05 '16 at 16:59

Plycoder

721
1
6
18

Welcome@Precious Pari – Plycoder Nov 05 '16 at 17:10
can you tell me which database should i use for stand alone application which i could install on any other pc without database installation – Precious Pari Nov 05 '16 at 17:16
and if i want to create point of sale app then should i go with window form or wpf ? – Precious Pari Nov 05 '16 at 17:17

C# .net winforms data insert error

2 Answers2