SQL Syntax error in C# string

Question

I'm running a query from a web form to update records. Since I'm just learning about C#, I'm using a command string as opposed to a stored procedure.

My update method is as follows:

public void updateOne()
    {
        string commandText = "update INVOICE SET <Redacted> = @<Redacted>, 
                    Supplier = @Sup, SupplierName = @SupN, NetTotal = @Net, 
                              VATTotal = @VAT, InvoiceDate = @InvDt "
       <needed a line break here, which is why I split the string again> 
                              + "WHERE K_INVOICE = @K_INV";

        using (SqlConnection dbConnection = new SqlConnection
                                                    (conParams.connectionString))
        {
            SqlCommand cmd = new SqlCommand(commandText, dbConnection);
            cmd.Parameters.Add("@K_INV", SqlDbType.Int);
            cmd.Parameters["@K_INV"].Value = @K_INV;

            cmd.Parameters.AddWithValue("@<Redacted>", @<Redacted>.ToString());
            cmd.Parameters.AddWithValue("@Sup", @Sup.ToString());
            cmd.Parameters.AddWithValue("@SupN", @SupN.ToString());
            cmd.Parameters.AddWithValue("@Net", @Net.ToString());
            cmd.Parameters.AddWithValue("VAT", @VAT.ToString());
            cmd.Parameters.AddWithValue("@InvDt", @InvDt.ToString());

            try
            {
                dbConnection.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                errorString = e.Message.ToString();
            }
        }
    }

Catch stalls on an SQL error (Incorrect syntax near SET), and I have an idea that the issue occurs because I convert the parameters to strings. The first parameter is an Int, which should be OK.

If this is the case, what should I convert the parameters to? If not, what on earth is wrong?

Is your column name <Redacted> contains <> that might be the problem in your query. — Habib
– Habib, Commented Sep 3, 2014 at 14:46
you do not need the @ Literal symbol in your second param for the addwithvalue. what's < > used for in your Param..? — MethodMan
– MethodMan, Commented Sep 3, 2014 at 14:47
@Habib no, this is a placeholder since the column name may identify my organisation — Wolfish
– Wolfish, Commented Sep 3, 2014 at 14:49
@marc_s does this imply that I need to convert all the strings to a c# type that corresponds to the SQL type? — Wolfish
– Wolfish, Commented Sep 3, 2014 at 14:51
No - instead of cmd.Parameters.AddWithValue("@SupN", @SupN.ToString()); just use cmd.Parameters.Add("@SupN", SqlDbType.VarChar, 100).Value = ........; — marc_s
– marc_s, Commented Sep 3, 2014 at 14:53

Felipe Oriani · Accepted Answer · 2014-09-03 14:49:56Z

3

Try to add a @ before the string to escape the breaklines, for sample:

string commandText = @"update INVOICE SET [Redacted] = @Redacted, 
                    Supplier = @Sup, SupplierName = @SupN, NetTotal = @Net, 
                              VATTotal = @VAT, InvoiceDate = @InvDt "
                              + "WHERE K_INVOICE = @K_INV";

In parameterName argument you can add the @ but the value not, just the variable, for sample

cmd.Parameters.AddWithValue("@Redacted", redacted.ToString());

Try to execute this query in the databse with some values to check if everything is correct. You could use [brackets] in the table name and column names if you have a reserved word.

edited Sep 3, 2014 at 14:49

answered Sep 3, 2014 at 14:44

Felipe Oriani

38.7k20 gold badges141 silver badges203 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Wolfish Over a year ago

I now have no error, but the record doesn't save. Could this be something to do with session data?

Wolfish Over a year ago

By the way, I need to remove the segments that I edited, as they could potentially identify the organisation I am apprenticing with

Wolfish Over a year ago

Thankyou, appreciated

Wolfish Over a year ago

Ah, I understand now. The string itself is named @<stringname>. This is also part of a larger naming convention, and as such I'd need to re-write half of my program. I did write a quick program to try this out, but it made no difference.

Felipe Oriani Over a year ago

The @ is a char to escape strings in C#. You do not need to use it in variables like you did in parameters. The marc_s' anwser also help you.

marc_s · Accepted Answer · 2014-09-03 15:03:12Z

1

I would recommend you read this blog article on the dangers of .AddWithValue():

Can we stop using AddWithValue already?

Instead of

cmd.Parameters.AddWithValue("@Sup", @Sup.ToString());

you should use

cmd.Parameters.Add("@Sup", SqlDbType.VarChar, 50).Value = ...(provide value here)..;

(is your variable in C# really called @SupN ?? Rather unusual and confusing....)

I would recommend to always define an explicit length for any string parameters you define

answered Sep 3, 2014 at 15:03

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

1 Comment

Steve Over a year ago

On the same line: How Data Access Code Affects Database Performance

Collectives™ on Stack Overflow

SQL Syntax error in C# string

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related