Returning a concatenated value from a data table using a select SQL satement

Posted on 2011-10-10
Last Modified: 2012-05-12
I am wondering why this code in VB.NET doesnt work and it works fine in access. I want to return user surname and other names as one string usernames

SELECT StrConv([surname],3) & " " & StrConv([othernames],3) AS usernames, users.userId,
FROM users;

Question by:Anthony Matovu
    LVL 83

    Expert Comment

    Use + instead of &

    SELECT StrConv([surname],3) + " " + StrConv([othernames],3) AS usernames, users.userId,
    FROM users;
    LVL 1

    Author Comment

    by:Anthony Matovu
    Thank you
    i get the error msg

    Syntax error (missing operator) in query expression 'StrConv([surname],3) StrConv([othernames],3)'.

    this is the code

     dadp = New OleDb.OleDbDataAdapter("SELECT StrConv([surname],3)" + " " + "StrConv([othernames],3) AS usernames, users.userId, FROM users;", db)
            dT = New DataTable : dadp.Fill(dT) : dadp.Dispose()
            CType(frm.Controls("compBy"), ComboBox).DataSource = dT
            CType(frm.Controls("compBy"), ComboBox).DisplayMember = "usname" : CType(frm.Controls("compBy"), ComboBox).ValueMember = "userId"
            CType(frm.Controls("compBy"), ComboBox).BindingContext = New BindingContext : CType(frm.Controls("compBy"), ComboBox).SelectedIndex = -1

    LVL 83

    Accepted Solution

    The plus needs to be within the quotes

     dadp = New OleDb.OleDbDataAdapter("SELECT StrConv([surname],3)" + " " + "StrConv([othernames],3)


     dadp = New OleDb.OleDbDataAdapter("SELECT StrConv([surname],3) + ' ' + StrConv([othernames],3) AS usernames, users.userId, FROM users;", db)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    This video discusses moving either the default database or any database to a new volume.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    6 Experts available now in Live!

    Get 1:1 Help Now