using a concat statement with a dataadapter

[pfpc]

I am using mysql with corelabs dataadapter
When I try to configure a dataadpter with a concat statement


works

Code: Select all

SELECT  (firstname, surname, userid) 
FROM employees

not working

Code: Select all

SELECT CONCAT (firstname,  ', ',  surname)  AS name, userid
FROM employees

not working

Code: Select all

SELECT { fn CONCAT (firstname,  ', ',  surname)} AS name, userid
FROM employees

also not working
SELECT { fn CONCAT (firstname, '\, ', surname)} AS name, userid
FROM employees
SELECT CONCAT (firstname & surname) AS name, userid
FROM employee


When I configure the dataadapter I can sometimes preview the data.
After I have created and filled the dataset when I try to preview the data the dataset is empty. Although the schema is correct.

Would this be becuase I only have the standard edition or is it a problem with my code.

If you need anymore information just let me know

[Serious]

Column 'CONCAT (firstname, ', ', surname) AS name' cannot be edited.
For more information see schema table in the DataAdapter editor. You'll see that this column is a read-only expression, so MySqlCommandBuilder that uses this metadata cannot generate update commands.

To edit such complex queries with DataAdapters you have to write Insert, Update, Delete commands manually.

[pfpc]

Column 'CONCAT (firstname, ', ', surname) AS name' cannot be edited.

........

To edit such complex queries with DataAdapters you have to write Insert, Update, Delete commands manually.

In my code I have the following

Code: Select all

 'Fill the Combo boxes with the generated Dataset of Registered Users
    Private Sub fill_RegisteredUsersDisplay()

        Dim strLocation As String = Session("Location").ToString
        'Dim DataSetRegistered As New DataSet
        Try

            ' Fill the Portfolio combo box
            mySqlSelectCommandRegistered.CommandText = "SELECT CONCAT(Surname, ', ', FirstName) AS Name, FFNumber " & _
                                                        "FROM EMPLOYEES_Registered " & _
                                                        "WHERE (EmpStatus = 'A') " & _
                                                        "AND (Location LIKE '" & strLocation & "') " & _
                                                        "ORDER BY Surname"
            DataSetRegistered.Clear()
            ' Fill the dataset
            MySqlDataAdapterRegistered.Fill(DataSetRegistered)
            ListBoxUserNames.DataSource = DataSetRegistered
            ' Fill the text with the name and the value with the ffNumber
            ListBoxUserNames.DataTextField = "name"
            ListBoxUserNames.DataValueField = "ffnumber"
            ' Bind the data to the object
            ListBoxUserNames.DataBind()

        Catch ex As Exception
        End Try

    End Sub

But this doesn't fill the dataadapter
I was able to do this with the ODBC settings and I need to be able to have a list box with user name (first and surnam) with the id as a value field.
I am not sure how to achive this using the mysqldataadapters

[Serious]

I've tested code that is similar to yours and found no problems:

Code: Select all

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim connection As MySqlConnection = New MySqlConnection("host=server;port=3307;database=test;user id=root;password=root;")
    Dim command As MySqlCommand = New MySqlCommand("select concat(dname,',',loc) as aa, deptno from dept", connection)
    Dim dataAdapter As MySqlDataAdapter = New MySqlDataAdapter(command)
    Dim commandBuilder As MySqlCommandBuilder = New MySqlCommandBuilder(dataAdapter)
    Dim dataSet As DataSet = New DataSet()
    connection.Open()
    Try
      dataAdapter.Fill(dataSet, "dept_table")
      DataGrid1.DataSource = dataSet.Tables("dept_table")
    Finally
      connection.Close()
    End Try
  End Sub

(table 'dept' defined in \Samples\tables.sql)

Check if you have no errors with data binding, and you have required data in your tables.

If this does not help, send us your send us information about your MySQLDirect .NET version, MySQL version and send us small test project if possible to reproduce the problem; it is desirable to use 'test' schema objects, otherwise include definition of your own database objects. Do not use third party components.

[pfpc]

Hi serious,

Thanks once again for helping me fix the problem

Taking on board what you said about putting the concat into the code as opposed to configuring the dataadapter I have now got this to work

I have configured the dataadapter with a general Select Statement

Code: Select all

SELECT firstname, surname, userid FROM tablename

and am doing the concat in my code

Code: Select all

  Dim strLocation As String = Session("Location").ToString
        'Dim DataSetRegistered As New DataSet
        Try

            ' Fill the Portfolio combo box
            mySqlSelectCommandRegistered.CommandText = "SELECT CONCAT(Surname, ', ', FirstName) AS Name, userid " & _
                                                        "FROM EMPLOYEES" 

            DataSetRegistered.Clear()

            ' Fill the dataset
            MySqlDataAdapterRegistered.Fill(DataSetRegistered)
            ListBoxUserNames.DataSource = DataSetRegistered


            ' Fill the text with the name and the value with the userid
            ListBoxUserNames.DataTextField = "Name"
            ListBoxUserNames.DataValueField = "userid"

            ' Bind the data to the object
            ListBoxUserNames.DataBind()

        Catch ex As Exception
        End Try

Also this problem is described as a mysql bug

http://bugs.mysql.com/bug.php?id=11947