Using CONCAT in a MySQL query from .NET

Question

I am writing a C# WPF application with controls bound to a MySQL 5.5 database. I'm populating a DataTable using a MySqlDataAdapter, passing in a MySQL SELECT query.

When the query is something trivial, like SELECT * FROM People or SELECT LastName, FirstName, PersonID FROM People, all is well and my ListBox control ends up populated with the expected names.

When the query contains a fairly simple CONCAT operator, though, the query silently fails and the ListBox stays empty. In particular neither of the following have worked, even though both work fine from within the MySQL command line.

SELECT CONCAT(LastName, FirstName) as Name FROM People
SELECT CAST(CONCAT(LastName, FirstName) AS CHAR) as Name FROM People

Both LastName and FirstName are defined as VARCHAR. So, I wouldn't expect this to be an instance of CONCAT returning a binary string in any case. I mention that because it appears to have been the problem for similar issues that other folks have mentioned.

akatakritos · Accepted Answer · 2012-12-07 03:36:50Z

1

I'd imagine the DataAdapter attempts to parse your query into an internal format so that it can do things like generate Update and Insert commands.

Can you fill your datatable using a MySqlDataReader instead? This is probably only helpful if you are not planning to do database updates with the DataTable

Going from memory...

MySqlCommand cmd = new MySqlCommand(query, connection);
MySqlDataReader reader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(reader);

edited Dec 7, 2012 at 3:36

answered Dec 7, 2012 at 3:30

akatakritos

9,8781 gold badge25 silver badges30 bronze badges

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

1 Comment

Mike Woolf Over a year ago

Yep, that worked. Just a note that the connection must be explicitly opened before calling ExecuteReader though (unlike the data adapter).

Collectives™ on Stack Overflow

Using CONCAT in a MySQL query from .NET

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related