Data entered in a form doesn't get insert in the table

Question

I cannot see the added data in the data table this is the code:

I'm using Visual Studio 2010 Express.

private void button1_Click(object sender, EventArgs e)
{
    string t1 = textBox1.Text;

    SqlCeConnection conn =
       new SqlCeConnection(@"Data Source=|DataDirectory|\Database1.sdf");

    conn.Open();

    SqlCeCommand cmdInsert = conn.CreateCommand();
    cmdInsert.CommandText = "INSERT TO table_name (Column1) VALUES (t1)";

    cmdInsert.ExecuteNonQuery();

    conn.Close();
}

It doesn't insert into data table after clicking on the button, it gives me an error on

cmdInsert.ExecuteNonQuery();

it debugs it, but when I click on the button, it shows me an error saying

SqlCeException was unhandled. There was an error parsing the query. [ Token line number = 1,Token line offset = 8,Token in error = TO ]

Does the insert work when you run it in SSMS? – Brian Mar 05 '13 at 19:34 — Brian, Mar 05 '13 at 19:34

score 7 · Accepted Answer · answered Mar 05 '13 at 19:37

7

Try:

cmdInsert.Parameters.AddWithValue("@t1", textBox1.Text);
cmdInsert.CommandText = "insert INTO table_name (Column1) VALUES (@t1)";

answered Mar 05 '13 at 19:37

Andomar

232,371
49
380
404

score 3 · Answer 2 · edited May 23 '17 at 12:13

There are two problems with your code:

Syntax error in SQL statement - you should write INSERT INTO instead of INSERT TO.
You cannot use t1 directly in the SQL string. Although you could concatenate strings as suggested in other comments, it's better to use parametrized command instead.

Here is the corrected version:

SqlCeCommand cmdInsert = conn.CreateCommand();
cmdInsert.CommandText = "INSERT INTO table_name (Column1) VALUES (@t1)";
cmdInsert.Parameters.AddWithValue("@t1", t1);
cmdInsert.ExecuteNonQuery();

See Why do we need SqlCeCommand.Parameters.AddWithValue() to Insert a value? for more details on command parameters.

score 1 · Answer 3 · answered Mar 05 '13 at 19:37

1

Your sql query is wrong.

Instead of

cmdInsert.CommandText = "INSERT TO table_name (Column1) VALUES (t1)";

There should be

cmdInsert.CommandText = "INSERT INTO table_name (Column1) VALUES (t1)";

answered Mar 05 '13 at 19:37

Davor Zlotrg

6,020
2
33
51

The variable `t1` still isn't reachable. It would need to be a parameterized query and a parameter would need added to the command like in the answer provided by @Andomar. – Mike Perrenoud Mar 05 '13 at 19:39

kashif · Answer 4 · 2013-03-05T19:48:13.457

1

string t1 = textBox1.Text;
            SqlCeConnection conn = new SqlCeConnection(@"Data Source=|DataDirectory|\Database1.sdf");
            conn.Open();
            SqlCeCommand cmdInsert = conn.CreateCommand();
            cmdInsert.CommandText = "INSERT into table_name (Column1) VALUES ('" + t1 + "')";
            cmdInsert.ExecuteNonQuery();
            conn.Close();

edited Mar 05 '13 at 19:48

answered Mar 05 '13 at 19:40

kashif

3,713
8
32
47

4

This is a teaching moment for someone new to queries. We **should** not be telling the OP to not use a parameterized query. See Andromar's answer below. – Brian Mar 05 '13 at 19:42
1

The approach is flawed, and it introduces a [Sql Injection Vulnerability](http://en.wikipedia.org/wiki/SQL_injection). – Michael Fredrickson Mar 05 '13 at 19:51
2

I would still use: `cmdInsert.Parameters.AddWithValue("@t1", textBox1.Text); cmdInsert.CommandText = "insert INTO table_name (Column1) VALUES (@t1)";`. As Michael just mentioned, **NOT** using a parameterized query invites a SQL Injection attack. – Brian Mar 05 '13 at 19:52
Not such a bad answer. If your database has sysadmin password "welcome", there is absolutely no point in worrying about SQL injection. – Andomar Mar 05 '13 at 20:11
@Andomar Thanks really very much. I need appreciation from guys like you. – kashif Mar 05 '13 at 20:14
@kashif - I upvoted your post once again. And am always happy to talk code with you :) – Brian Mar 05 '13 at 20:27

score 0 · Answer 5 · answered Mar 05 '13 at 19:39

You need to pass the value of t1, probably with a parameter.

private void button1_Click(object sender, EventArgs e)
{
    string t1 = textBox1.Text;
    SqlCeConnection conn =
       new SqlCeConnection(@"Data Source=|DataDirectory|\Database1.sdf");
    conn.Open();
    SqlCeCommand cmdInsert = conn.CreateCommand();
    cmdInsert.CommandText = "INSERT INTO table_name (Column1) VALUES (@t1)";
    var parameter = cmdInsert.CreateParameter();
    parameter.Value = t1;
    parameter.ParameterName = "@t1";

    cmdInsert.Parameters.Add(parameter);

    cmdInsert.ExecuteNonQuery();
    conn.Close();
}

Data entered in a form doesn't get insert in the table

5 Answers5