Search code examples
sql.netvb.netoledb

Cannot Remove Last Character From String

I have an Access database and I'm querying the employee table. The last names of new employees have an asterisk appended to the end (ex: Bob Smith*). In my query I am trying to remove the asterisk and load the employee names into a ComboBox. However, the ComboBox remains blank. If I run the query within Access itself, it produces the expected results.

Database Structure:

Database Structure

Here's the relevant code:

Public Class frmMain

Dim data As DataSet

Private Sub frmMain_Load(sender As Object, e As EventArgs) Handles MyBase.Load


    'load data from master database
    data = New DataSet
    Dim a As New OleDbDataAdapter("SELECT LEFT(Last_Name_String, LEN(Last_Name_String)-1), First_Name_String FROM Basic_Employee_Information_Table", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" &
        fileReader.ToString())
    a.Fill(data, "Employees")

    For Each item In data.Tables("Employees").Rows
        cboEmployee.Items.Add(item("Last_Name_String") & ", " & item("First_Name_String"))
    Next

End Sub
Solution

  • Your query is not returning anything called "Last_Name_string". Use an alias:

    SELECT LEFT(Last_Name_String, LEN(Last_Name_String)-1) as Last_Name_String,
       First_Name_String