insert into statement in c#

Question

I want to create an SQL statement that inserts a value taken from a textBox into a column where any value in that column is NULL

I'm doing it in C# and I was wondering if anyone could help me out... I wrote a pseudo code version of the command:

string newPhoneNumber = textBox.Text;
SqlCommand cmd = new SqlCommand(
"INSERT INTO table ([Tag ID]) 
 VALUES ('" + newPhoneNumber + "')"; 
 WHERE columnName = NULL" 
cmd.ExecuteNonQuery();

The answers given are correct, but I would always use a stored procedure instead of SQL, for several reasons including security and performance. — CompanyDroneFromSector7G, May 16 '12 at 13:35
I'm not going to post an answer because the others are *close* to being right, but ***CONTENCATING STRINGS INTO AN SQL STATEMENT IT BAD!*** It's the leadfing cause of the #1 cause of database breaches. Use parameterized queries instead. http://www.aspnet101.com/2010/10/protect-your-asp-net-app-from-sql-parameter-injection/ — David, May 16 '12 at 13:38

score 5 · Answer 1 · answered May 16 '12 at 13:33

5

Use is NULL instead of equal to NULL :

WHERE columnName IS NULL

answered May 16 '12 at 13:33

aleroot

71,077
30
176
213

Perfect answer simple and to the Point. – General Grey May 16 '12 at 13:50
@K'Leg I wouldn't say perfect. Changing `=` to `IS` in the OP's code will throw an exception. – Curtis May 16 '12 at 13:50
1

@Curt Care to explain? Don't get me wrong Curt your answer is great too. – General Grey May 16 '12 at 13:55
@K'Leg SO's syntax highlighting makes it quite clear. The string has ended on that line, `WHERE..` isn't a string object, its just text within the code. It would not parse. – Curtis May 16 '12 at 13:58
1

@Curt Fair enough, but that would never compile, therefore it is likely just a typo. It doesn't matter to me either way. If you wish to do everything for everyone that is your prerogative. I believe that when the OP shows a little skill you can leave a little for them to figure out on their own. – General Grey May 16 '12 at 14:01

Curtis · Answer 2 · 2012-05-16T14:34:53.930

5

First off, use SQL Parameters. This will prevent SQL Injection. Can't believe no one else has mentioned this yet. 5 answers and so far I'm the only one to mention this.

Structure your command like so:

SqlCommand cmd = new SqlCommand(
"INSERT INTO table ([Tag ID]) SELECT TOP 1 @newPhoneNumber FROM [table] WHERE columnName IS NULL");
cmd.Parameters.Add("@newPhoneNumber", Sqldbtype.nvarchar).Value = textBox.Text;
cmd.ExecuteNonQuery();

edited May 16 '12 at 14:34

answered May 16 '12 at 13:37

Curtis

101,612
66
270
352

2

+1 I was also amazed. It also goes beyond SQL Injection, it makes code more readable by separating SQL from values, and stops data loss through string conversion. – GarethD May 16 '12 at 13:41
1

Even better, don't use SQL - USE A STORED PROCEDURE like I said in my comment. – CompanyDroneFromSector7G May 16 '12 at 13:45
@bukko and how do we get stored procedures under version control?! – May 16 '12 at 13:52
You script them. Add the script to your version control system like you would any file. – CompanyDroneFromSector7G May 16 '12 at 13:56
I was so blinded by rage about parameterised queries I missed the obvious: `Incorrect syntax near the keyword 'WHERE'.` You cannot use `WHERE` with `INSERT...VALUES`. – GarethD May 16 '12 at 14:18

Nikhil Agrawal · Accepted Answer · 2012-05-16T14:07:50.273

3

You cannot Use `Insert Query` with `Where` Clause

Since your values are already there, even if you write ColumnName IS NUll as other answers have said, it will not work.

You have to use Update query

"Update tablename set first_field_name = first_value, 
  second_field_name = second_field_value WHERE columnName IS NULL";

Moreover NULL is not a value. Its a state. Which means (in front-end) that memory has not been assigned.

For e.g. strings in C# - if memory is not assigned they are null. They do not have null.

Note/Warning related to question, not related to answer:

Always be aware of SQL Injection. Use Parameterised Query (the one with @).

More on SQL Injection

edited May 16 '12 at 14:07

answered May 16 '12 at 13:34

Nikhil Agrawal

47,018
22
121
208

1

It is not our responsibilty to correct every problem that the OP has, this answer is more than satisfactory. If the Answerer(can you say that) decides to go above and beyond than that is bonus points. – General Grey May 16 '12 at 13:48
4

@K'Leg - That's arguable. Considering SQL injections in his apps can be used to plant malicious JavaScript, which can be used to provide cross-site request forgery attacks on other people's apps, making the web a safer place is arguably ALL of our responsibility. If the experts here aren't helping to train newbies onthe basics, who will? Note that I took hits on my pont value to downvote this, not *only* because I'm a jerk (although I am) but because its that important. -1 revoked, by the way. Thank you. – David May 16 '12 at 13:52
@DavidStratton Glad you admit to being a Jerk. I suppose what I am suggesting is that it's a good idea to mention it, but it should not be a requirement. And I don't think downvoting someone trying to help is the Answer, it will simply discourage people from helping. Add it to the comments the way you did, or link to a previous post dedicated to sql injection. – General Grey May 16 '12 at 13:58
1

I appreciate David's passion for the issue. It is a big deal. Also glad the -1 was revoked. – Jon Crowell May 16 '12 at 14:19
Thank You Update Was the main problem I overlooked – kev670 May 22 '12 at 10:50

LaGrandMere · Answer 4 · 2012-05-16T13:48:24.683

3

Use this query, with IS NULL.

"INSERT INTO table ([Tag ID]) VALUES (@PhoneNumber) WHERE columnName IS NULL"

Use ParametrizedCommand, you have examples in the other answers.

edited May 16 '12 at 13:48

answered May 16 '12 at 13:34

LaGrandMere

10,265
1
33
41

@DavidStratton : you're totally right :) I'm going to correct it right now – LaGrandMere May 16 '12 at 13:47

GarethD · Answer 5 · 2012-05-16T14:23:42.070

There are 2 problems. In SQL NULL does not equal anything, not even NULL. i.e. NULL = NULL is false. So the first problem is WHERE ColumnName = NULL

Secondly, the SQL is not valid syntax. You cannot specify a where clause when using INSERT .. VALUES, You have to use SELECT .. WHERE or IF.. ELSE. e.g.

INSERT INTO table ([Tag ID])
SELECT  TOP 1 @PhoneNumber
FROM    YourTable 
WHERE   YourColumn IS NULL

OR

IF EXISTS (SELECT 1 FROM YourTable WHERE YourColumn IS NULL)
    BEGIN
        INSERT Table ([tAG id]) VALUES(@PhoneNumber)
    END

In both of these examples YourTable is the table that YourColumn Exists in.

Finally avoid string concatenation for building SQL statements where possible.

string sql = // One of the queries above
cmd.CommandText = sql
cmd.Parameters.Add("@PhoneNumber", SqlDbType.Varchar).Value = textBox.Text;
cmd.ExecuteNonQuery();

score 1 · Answer 6 · edited May 23 '17 at 10:34

1

Use LINQ to SQL, which automatically uses parameters, eliminating the danger of SQL Injection.

It is also much easier to work with than concatenating SQL Statements.

Will using LINQ to SQL help prevent SQL injection

How to: Insert Rows Into the Database (LINQ to SQL)

edited May 23 '17 at 10:34

Community

1
1

answered May 16 '12 at 13:58

Jon Crowell

21,695
14
89
110

insert into statement in c#

6 Answers6

You cannot Use Insert Query with Where Clause

You cannot Use `Insert Query` with `Where` Clause