Inserting a nullable Int64 value from C# into a nullable column in SQL server, when user leaves the TextBox empty

Question

I have a Table as below: (Time-saving tip: Just focus on the "Price" column)

public class Expense
{
    public Expense()
    {
        Price = null; //Setting default value for Price
    }
    public string Type { get; set; }
    public Int64? Price { get; set; }
    public string Date { get; set; }
    public string Comment { get; set; }
    public string UserName { get; set; }
    public Int16 ChangeCount { get; set; }
    public string ErrorMessage { get; set; }
}

I want to INSERT INTO this table as below:

public Boolean Insert(Expense exp)
    {
        String query = "INSERT INTO Expense VALUES(N'{0}', {1}, '{2}', N'{3}', '{4}', {5})";
        query = String.Format(query,
               exp.Type,
               exp.Price == null ? (Int64?)null : exp.Price, //This line works perfectly
               exp.Date,
               exp.Comment,
               exp.UserName,
               exp.ChangeCount);
    }

After Executing, Everything works great, JUST one thing is not right; the resulting INSERT Command is:

INSERT INTO Expense VALUES(N'anyTitle', , '1393/09/07 22:37', N'anthing', 'hb', 0)

I expect to see null instead of blank in the Price column! Error is: Incorrect syntax near ','

Thank you in advance.

Use `"null"` instead of `null`. This is exactly why you should use parameterized queries! — Patrick Hofman, Nov 28 '14 at 19:24
[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should ***never*** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection — marc_s, Nov 28 '14 at 19:25
You're right :-) parametrized query solves it, but what's done is done! I tried your solution: exp.Price == null ? "null" : exp.Price but it gives this error: " Type of conditional expression cannot be determined because there is no implicit conversion between 'string' and 'long?' " — user4266447, Nov 28 '14 at 19:27

score 0 · Answer 1 · answered Nov 28 '14 at 20:25

0

The answer from @Nugsson solved my problem.

However I've decided to use parametrized queries to overcome this problem and threats like SQL injection. Thanks to all. @Patrick Hofman @marc_s @Nugsson

answered Nov 28 '14 at 20:25

user4266447

151
1
1
7

Nugsson · Accepted Answer · 2014-11-28T19:33:49.890

-1

I think what you're looking for is this:

public Boolean Insert(Expense exp)
{
    String query = "INSERT INTO Expense VALUES(N'{0}', {1}, '{2}', N'{3}', '{4}', {5})";
    query = String.Format(query,
           exp.Type,
           exp.Price == null ? "NULL" : exp.Price.Value.ToString(),
           exp.Date,
           exp.Comment,
           exp.UserName,
           exp.ChangeCount);
}

However, as marc_s absolutely rightly points out, you should be doing this with parameters - there's a danger of a SQL injection attack otherwise.

edited Nov 28 '14 at 19:33

answered Nov 28 '14 at 19:27

Nugsson

196
2
12

Thank you so much for your time :-) but It gives this error: but it gives this error: " Type of conditional expression cannot be determined because there is no implicit conversion between 'string' and 'long?' " – user4266447 Nov 28 '14 at 19:30
Apologies. The .ToString() I've just added will have resolved this. – Nugsson Nov 28 '14 at 19:34
Thank u, It worked! you're the best :-) But I've decided to go with the Parametrized version!! You guys scared me off :-))) Thank u anyways :-) – user4266447 Nov 28 '14 at 19:39
Good man, it's the right thing to do! – Nugsson Nov 28 '14 at 19:46

Inserting a nullable Int64 value from C# into a nullable column in SQL server, when user leaves the TextBox empty

2 Answers2