how do I insert the values into the sqlserver database in visual studio 2008

Question

0.00/5 (No votes)

See more:

VS2008

VS2010

, +

Hello everybody, Every time I got the error when I am trying to insert the values into the SQLServer database in visual studio 2008 and I got the Unhandled Sqlexception and the error is "An explicit value for the identity column in table 'Emp' can only be specified when a column list is used and IDENTITY_INSERT is ON.".So please help to solve my this problem.Here's below is code

private void button1_Click(object sender, EventArgs e)
       {
           SqlConnection con = new SqlConnection("Data Source=PS201\\SQLEXPRESS;Initial Catalog=EmployeeInfo;Integrated Security=True;Pooling=False");
           con.Open();
           SqlCommand sc = new SqlCommand("insert into Emp values(" + textBox1.Text + ",'" + textBox2.Text + "','" + textBox3.Text + "'," + textBox4.Text + ",'" + textBox5.Text + "');",con);
           int o = sc.ExecuteNonQuery();
           MessageBox.Show(o+"Insert value successfully..");
           con.Close();


       }

       public static void main(string[] arg)
       {
         Application.Run(new Form1());
       }

       private void button2_Click(object sender, EventArgs e)
       {
           Application.Exit();
       }

Posted 5-Sep-14 22:34pm

AnoopGharu

Updated 5-Sep-14 22:40pm

v2

Add a Solution

3 solutions

Solution 2

sir, please see the below link it might help you :-

http://stackoverflow.com/questions/2005437/an-explicit-value-for-the-identity-column-in-table-can-only-be-specified-when-a/2005449#2005449[^]

Posted 5-Sep-14 22:49pm

agent_kruger

Updated 5-Sep-14 22:51pm

v3

Solution 3

C#

Actually you have one Identity Column so You have to specify rest of  Column Name for Inserting Value into the table.

For Example :

INSERT INTO EMP_TBL (NAME,ADDRESS) VALUES ('"+TextBox1.Text+"','"+TextBox2.Text+"')

Posted 6-Sep-14 0:13am

aarif moh shaikh

Comments

OriginalGriff 6-Sep-14 6:28am

Reason for my vote of one: Never, ever, encourage people to concatenate textbox contents into SQL strings! It is seriously dangerous: any user can delete your database just by typing in the text box!

Google for "Bobby Tables" and don't assume it's just a joke...

aarif moh shaikh 6-Sep-14 7:58am

If we take that TextBoxes value into a String Variable or any Parameterized Variables .. but finally these varible also will take values from that textboxes , than how you saying like this.

OriginalGriff 6-Sep-14 8:08am

There is a difference between a concatenated query:
string sql = "INSERT INTO MyTable (MyColumn) VALUES('" + "x');DROP TABLES MyTable" + "')";
using (SqlCommand cmd = new SqlCommand(slq, con))
{
...

and a parametrized one:

string sql = "INSERT INTO MyTable (MyColumn) VALUES(@VAL)";
using (SqlCommand cmd = new SqlCommand(slq, con))
{
cmd.Parameters.AddWithValue("@VAL", "x');DROP TABLES MyTable");
...

The former sends two commands to SQL: an INSERT to put "x" into the column, and a second one which deletes the table. SQL executes both!
The send sends one command, which inserts the whole string into the column and can't let the user corrupt your DB.

This is called an SQL Injection attack, and it lets users bypass your login security, alter or delete your database, and so forth. And it's is unbelievably simple for a user to do...

And if you think I'm joking: try it on your code. Make sure you backup your database first though... :laugh:

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2014-09-05T22:47:00

Always list the columns you want to insert or alter: if you don't then SQL assumes you mean in it's current column order starting from the "Left" - and the problem you are meeting is one of the results of that (The other is much worse - your DB gets irretrievably corrupted after a "small change" to the DB which appears to work fine until data problems are seen six months later).

Basically, you can't write to an identity column.
So specify your SQL INSRT like this:

SQL

INSERT INTO Emp (MyColumnName1, MyColumnName2) VALUES (...)

And that problem will go away.

But...don't do it the way you are! That is spectacularly dangerous. Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

how do I insert the values into the sqlserver database in visual studio 2008

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

how do I insert the values into the sqlserver database in visual studio 2008

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us