Write line to Service-Based Database with textbox

Question

I'm trying to create and write line by line to the Service-Based Database with textbox, without additional columns just Index and phrase. So as beginner need help or useful example for my case to figure out, what I'm doing wrong here.

So what I did, my database:

CREATE TABLE [dbo].[User]
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY, 
    [Phrase] TEXT NOT NULL
)

In Id column Properties Identity Specification I’ve changed (is identity) to True, then Update dbo.User [Design], then refresh to my SampleDatabase.mdf , then Show Table Data my tab User, and then I’ve added new item LINQ to SQL Classes to my project as DataClass and I’ve added to my Program.cs directory AppDomain.CurrentDomain.SetData("DataDirectory", System.Environment.CurrentDirectory.Replace("\\bin\\Debug", "")); this way:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace XX_7_DATABASE_LOCAL
{
    static class Program
    {
        [STAThread]
        static void Main()
        {   
            AppDomain.CurrentDomain.SetData("DataDirectory", System.Environment.CurrentDirectory.Replace("\\bin\\Debug", ""));  
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Form1());
        }
    }
}

And here my Form1:

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 XX_7_DATABASE_LOCAL
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            User newUser = new User();
            newUser.Phrase = (textBox1.Text);

            DataClassDataContext dbCtx = new DataClassDataContext();
            dbCtx.Users.InsertOnSubmit(newUser);

            try
            {
                dbCtx.SubmitChanges();
                MessageBox.Show("Successful!");
            }
            catch(Exception ex)
            {
                MessageBox.Show("Fail!");
            }        
        }

        private void button2_Click(object sender, EventArgs e) 
        {
            DataClassDataContext dbContext = new DataClassDataContext();
            var getData = (
                from x in dbContext.Users
                select x
                );

            dataGridView1.DataSource = getData;
        }
    }
}

So I’ve replaced message to MessageBox.Show(ex.Message + ":" + ex.StackTrace);, but not sure what it means and what I have to do:

if Identity Specification is False, writes only one line to database, but with second one, I got this message:

Try adding the user leaving its id out of it. I mean, if you set the id column of table User to Identity, then your database should do the job for you: it will automatically increment the id ... that is the reason for your first error: when IDENTITY_INSERT is off, your database automatically handles it — Henrique Forlani, Aug 25 '16 at 19:06

score 0 · Accepted Answer · edited May 23 '17 at 12:33

0

I posted as a comment, but I think it is actually an answer. So I will add some more explanation:

If you are not explicitly saying to the database that you can insert a value to an Identity column by setting INSERT_IDENTITY to On, then the database is its owner, not you. You can either stop trying to insert an user id and let the database handle it for you or you can set INSERT_IDENTITY to On and do the job. If you don't set the id to be an Identity column, then make sure that there will be no duplicated ids, otherwise it will crash, as described in the second error.

I would go for the first option and let the database do that for you.

Credits : Problems with IDENTITY_INSERT Set to OFF? :-/

edited May 23 '17 at 12:33

Community

1
1

answered Aug 25 '16 at 19:16

Henrique Forlani

1,325
9
22

I'm new with C# and database especially, as I said if Identity Specification is True my file is empty, does not writes nothing. Or I don't understand you right? – Aug 25 '16 at 19:59
It is empty, because you are getting the errors you posted. How is your User class? Does it have an id? – Henrique Forlani Aug 25 '16 at 20:04
newUser.Id is probably null right? Try to delete the Id parameter of your user class first... – Henrique Forlani Aug 25 '16 at 20:15
yes I'm just forget add it again after changing to True :) – Aug 25 '16 at 20:16

Write line to Service-Based Database with textbox

1 Answers1

Linked