I can't insert data in table

Question

2.00/5 (1 vote)

See more:

C#

protected void Submit_Click(object sender, EventArgs e)
{
    SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS; database= Registration; trusted_connection=yes");
    conn.Open();
    
    SqlCommand com = new SqlCommand("insert into register(Username, password, email id, first name) values(" + TextBoxUS.Text + "," + TextBoxPass.Text + "," + TextBoxEA.Text + "," + TextBoxFN.Text + ") ", conn);

    com.ExecuteNonQuery();
}

I am sorry for before post
The error its showing as "SqlException was unhandled "
The error is in this line= com.ExecuteNonQuery(); it showing "Incorrect syntax near 'id'."

Posted 23-May-12 7:52am

selflearning

Updated 23-May-12 10:40am

CPallini

v4

Add a Solution

Comments

Ed Nutting 23-May-12 13:55pm

Edit: Added code formatting

So what is the error message? What is going wrong? We can't really help you if all you say is "this isn't working" - we need more detail than that. Please use the Improve Question link above to improve this so we can help.

Thanks,
Ed

selflearning 23-May-12 14:18pm

Thanks for your valuable reply please can you check now as I updated the question

Maciej Los 23-May-12 13:57pm

What's the problem, error...?

Sandeep Mewara 23-May-12 13:58pm

This is not a well framed question! We cannot work out what you are trying to do/ask from the post. Please elaborate and be specific.
Use the "Improve question" link to edit your question and provide better information.

selflearning 23-May-12 14:19pm

Thanks for your valuable reply I had improved the question and updated it please check it and give some solution...

selflearning 23-May-12 14:08pm

Thanks for your valuable reply I am sorry for that
The error its showing as "SqlException was unhandled "
The error is in this line com.ExecuteNonQuery(); it showing "Incorrect syntax near 'id'."

6 solutions

Solution 4

don`t use space between column name. Use column name in bracket Like [email id] instead of email id.

if space in column name then SQL read as different column name. email and another one is id.

Posted 23-May-12 9:43am

Sushil Kumar Purohit

Solution 3

i think you can try like this

C#

SqlCommand com = new SqlCommand("insert into register(Username, password, email_id, first_name) values(" + TextBoxUS.Text + "," + TextBoxPass.Text + "," + TextBoxEA.Text + "," + TextBoxFN.Text + ") ", conn);

Because in the insert statement it doesn't accept spaces for the column name (write in the insert statement what the column names are present in the table)

Posted 23-May-12 8:48am

anilkumar.6714

Updated 23-May-12 8:49am

v2

Comments

selflearning 23-May-12 15:03pm

protected void Submit_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS; database= Registration; trusted_connection=yes");
conn.Open();
SqlCommand com = new SqlCommand("insert into register(Username, password, [email id], [first name]) values(@Username, @password, @emailid, @firstname) ", conn);
com.Parameters.AddWithValue("@Username", TextBoxUS.Text);
com.Parameters.AddWithValue("@password", TextBoxPass.Text);
com.Parameters.AddWithValue("@emailid", TextBoxEA.Text);
com.Parameters.AddWithValue("@firstname", TextBoxFN.Text);
com.ExecuteNonQuery();

}
I got error like =Cannot insert the value NULL into column 'Id', table 'Registration.dbo.Register'; column does not allow nulls. INSERT fails.
The statement has been terminated.

anilkumar.6714 23-May-12 21:42pm

I think when you are creating the table you declared 'Id' datatype as "int", int doesn't take null values, so your getting error like that.

Solution 5

we can not give space in database field name.
change the name like email_id or emailid.....

Posted 23-May-12 20:58pm

nachiket03

Solution 6

Try to ignore all the below :

1) There is an identity field which can not be null inserted either you can do identity=true or send from front end.
2) It not a good habbit to use space in fields or use keywords that are already used in sql server.
3) use procedure to insert data into database rather using inline queries for ignoring sql injection.

Thanks,

rate if you like

Posted 23-May-12 22:45pm

AshishChaudha

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Mehdi Gholam · Accepted Answer · 2012-05-23T08:01:00

Solution 1

Column names cannot have spaces in them if they do you must put brackets around them.

For example : email id -> [email id]

Posted 23-May-12 8:01am

Mehdi Gholam

Comments

Maciej Los 23-May-12 14:03pm

Good answer, my 5!

Mehdi Gholam 23-May-12 14:07pm

Thanks losmac!

selflearning 23-May-12 14:20pm

Thanks

Wendelius 23-May-12 14:25pm

Exactly :)

Mehdi Gholam 23-May-12 14:27pm

Thanks Mika!

Ed Nutting 23-May-12 14:28pm

5+ :)

Mehdi Gholam 23-May-12 14:29pm

Thanks Ed!

VJ Reddy 23-May-12 20:59pm

Succinct and to the point. 5!

Mehdi Gholam 23-May-12 22:52pm

Thanks VJ!

Wendelius · Accepted Answer · 2012-05-23T08:18:00

Solution 2

And another thing. Never concatenate values directly to your SQL statements. This leaves you vulnerable to SQL injections, data type conversion problems and so on. Instead, always use SqlParameter[^]

So your query could look something like

C#

...
SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS; database= Registration; trusted_connection=yes");
conn.Open();
    
SqlCommand com = new SqlCommand("insert into register(Username, password, [email id], [first name]) values(@username, @password, @emailid, @firstname) ", conn);

com.Parameters.AddWithValue("@username", TextBoxUS.Text);
com.Parameters.AddWithValue("@password", TextBoxPass.Text);
com.Parameters.AddWithValue("@emailid", TextBoxEA.Text);
com.Parameters.AddWithValue("@firstname", TextBoxFN.Text);

com.ExecuteNonQuery();
...

Posted 23-May-12 8:18am

Wendelius

Updated 23-May-12 8:34am

v2

Comments

selflearning 23-May-12 14:27pm

Thanks for your reply but it showing the same error
The error its showing as "SqlException was unhandled "
The error is in this line com.ExecuteNonQuery(); it showing "Incorrect syntax near 'id'."

Wendelius 23-May-12 14:35pm

Sorry, my bad :) Forgot to add the brackets. Answer is updated

Mehdi Gholam 23-May-12 14:27pm

5'ed, (you forgot the brackets :))

Wendelius 23-May-12 14:36pm

Yeah I noticed, but then again you already mentioned that :)

Ed Nutting 23-May-12 14:28pm

A very good point, my 5+ :)

Wendelius 23-May-12 14:36pm

Thank you Ed :)

selflearning 23-May-12 14:30pm

I am sorry where i unable to find it I had written as you written above :( please help

Ed Nutting 23-May-12 14:33pm

Combine both answers and the code will work properly ;)

selflearning 23-May-12 14:34pm

very funny please help....

Wendelius 23-May-12 14:37pm

Did you get the new version working?

selflearning 23-May-12 14:43pm

No its not working it showing the same error...please

selflearning 23-May-12 14:38pm

No its not working it showing the same error :(

Wendelius 23-May-12 14:49pm

Could you post the current statement you use

selflearning 23-May-12 14:57pm

protected void Submit_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS; database= Registration; trusted_connection=yes");
conn.Open();
SqlCommand com = new SqlCommand("insert into register(Username, password, [email id], [first name]) values(@Username, @password, @emailid, @firstname) ", conn);
com.Parameters.AddWithValue("@Username", TextBoxUS.Text);
com.Parameters.AddWithValue("@password", TextBoxPass.Text);
com.Parameters.AddWithValue("@emailid", TextBoxEA.Text);
com.Parameters.AddWithValue("@firstname", TextBoxFN.Text);
com.ExecuteNonQuery();

}
Cannot insert the value NULL into column 'Id', table 'Registration.dbo.Register'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Wendelius 23-May-12 15:16pm

Aah, that's a whole different story. Now your SQL syntax is correct, but you have a column named Id in your table and you're not specifying a value for it. Either define the column in your statement and provide a value or use an auto-incremented definition (IDENTITY) for the column

selflearning 23-May-12 15:30pm

I done with that but its showing same error agian..

Wendelius 23-May-12 15:41pm

You mean error
Cannot insert the value NULL into column 'Id', table 'Registration.dbo.Register'
If that's the error you receive, the Id column doesn't get a value. Did you put it in the SQL statement? If you did, could you post the new code. If you modified the table column, how did you do it?

selflearning 23-May-12 16:02pm

I had created an textbox and initiated that for ID now its working and when I had changed in Identity its again showing the same error
Thank you very much for you precious time and information

Wendelius 23-May-12 16:09pm

Ok, so it's working now?

VJ Reddy 23-May-12 21:01pm

A very good suggestion. 5!

Wendelius 24-May-12 15:15pm

Thanks VJ :)