vb.NET "{0}" concatenation doesn't work in MySQL commands but "&" does

Question

I've been trying to send a long MySQL command in vb.NET. When I want to use variables as part of the MySQL command string I've been concatenating the string using "&".

This works, but makes my code look messy, so I tried using "{0}, {1}" etc.

This however does not work and I get the error:

Overload resolution failed because no accessible 'New' accepts this number of arguments.

Examples:

connector.Open()
commander = New MySqlCommand("UPDATE 'pupil_data' SET '" & Question & "'='" & Answer & "' WHERE 'username'=" & Environment.UserName & ";", connector)
dataAdapter = New MySqlDataAdapter(commander)
connector.Close()

This works, however:

connector.Open()
commander = New MySqlCommand(("UPDATE 'pupil_data' SET '{0}'='{1}' WHERE 'username'='{2}'", Question, Answer, Environment.Username), connector)
dataAdapter = New MySqlDataAdapter(commander)
connector.Close()

Doesn't work. It could just be a problem with my bracketing but I've tried several combinations and it's logically sound (I think).

string do not automatically use the format mask. you should use `String.Format("UPDATE pupil_data...", arg1, arg2...)` with the mask and args to get the result. and place that inside New SQLCommand or create the string to a temp var and pass it — Ňɏssa Pøngjǣrdenlarp, Jan 23 '15 at 21:06
This is not a 90's party. Today *(and yesterday)* we use prepared/parameterized statements: [How do I create a parameterized SQL query? Why Should I?](http://stackoverflow.com/questions/542510/how-do-i-create-a-parameterized-sql-query-why-should-i/542542#comment44610013_542542). — Bjørn-Roger Kringsjå, Jan 23 '15 at 22:05

Ňɏssa Pøngjǣrdenlarp · Accepted Answer · 2016-02-17T22:08:43.007

The "{n}" designations do not represent automatic concatenation, but are a formatting placeholder used as part of String.Format. A string with a placeholder wont use automatically know what to do with it. Your MySQLCommand object certainly doesn't know how to use it, so you get the error. Eg:

connector.Open()
Dim SQL As String = String.Format("UPDATE pupil_data SET '{0}'='{1}' WHERE 'username'='{2}'", 
       Question, Answer, Environment.Username)
commander = New MySqlCommand(SQL, connector)
dataAdapter = New MySqlDataAdapter(commander)
connector.Close()

I am not sure you need all those ticks in the SQL either. Note that if something like Question is a numeric, you need to convert to string:

Dim SQL As String = String.Format("Update ...", 
       Question.ToString, Answer.ToString, Environment.Username)

There are a few things like Console.WriteLine, StringBuilder.AppendFormat which automatically implement this as well.

See String.Format

Note that this is absolutely the wrong way to create SQL. If user name for instance is D'Artagnan the query will choke. String.Format does not protect you from SQL Injection, its just neater code than concatenating bits of string together.

vb.NET "{0}" concatenation doesn't work in MySQL commands but "&" does

1 Answers1