Reputation: 880

insert into statement in c#

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();

Upvotes: 0

Answers (6)

Curtis

Reputation: 103388

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();

Upvotes: 5

GarethD

Reputation: 69799

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

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();

Upvotes: 2

Nikhil Agrawal

Reputation: 48580

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 @).

insert into statement in c#

Answers (6)

You cannot Use Insert Query with Where Clause

Related Questions

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