none
OleDbDataAdapter Syntax error when field names have spaces RRS feed

  • Question

  • I have the following code to read the data from an MS Access table:

    Dim da As OleDb.OleDbDataAdapter, str_sql As String, con As New OleDb.OleDbConnection, str_File as String
    Dim str_Table as String
    con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = " & str_File
    con.Open()
    str_sql = "SELECT * FROM " & str_Table
    da = New OleDb.OleDbDataAdapter(str_sql, con)
    da.Fill(ds_WholeTable, "Agencies")
    con.Close()

    where str_File is the full path to the database I am reading from and str_Table is the name of the table in the database.

    I get 'Syntax error (missing operator) in query expression 'Loan No'.' error in the last line because the field name has a space in it. If I change the SQL to

    SELECT [Loan No] AS LoanNo, etc...

    then it works fine. However, my application needs to work with any table connected to it where I do not have any control over the field names, how many there are etc. so I have to use SELECT * and thus cannot enclose the field names in square brackets.

    How can I stop this error from occurring?
    Monday, January 21, 2008 3:00 AM

All replies

  • On what line of code does the error occur? I can't reproduce it when using "SELECT *".

     

     

    Tuesday, January 22, 2008 3:56 PM
  • Sorry... I added the con.Close line after I had written the initial post.

    The error gets thrown in the da.Fill(ds_WholeTable, "Agencies") line.
    Tuesday, January 22, 2008 10:02 PM
  • I believe if you use * then it will work fine as long as you do not have spaces in a table name. In this case you need to add square brackets around table name only.

    Wednesday, January 23, 2008 12:50 AM
    Moderator
  • Any chance it has nothing to do with the column you've identified? Perhaps one of your other column names or the table name is a reserved word or contains a invalid character?

     

    Wednesday, January 23, 2008 3:08 PM
  • No, I experimented changing different field names by adding spaces to each one in turn and it always bombed out on the first one that had a space. This included LoanNo versus Loan No, PrincipalBal versus Principal Bal and ProductGroup versus Product Group.
    Wednesday, January 23, 2008 7:04 PM
  • then just put square brackets around every field name...
    Wednesday, August 28, 2013 12:44 AM
  • Since this was resurrected I thought it might be prudent to suggest the following that discounts objects being the problem rather than (as seen here) spaces in the field name. Work with just a connection and command object, use an alias on the field with spaces.  So I am simply providing food for thought for those who might stumble onto this thread.

    Figure 1 shows the table data (Identifier auto-inc, primary key, Loan No string field).

    Figure 2 is the result of code down below.

    Figure 3, declare a DataTable, get the DataTable from reading a table in MS-Access. When hitting the break-point hover over 'dt' above, click on the magnifier to examine the returned data.

    Code

    Public Function GetNorthWindData() As DataTable Dim dt As New DataTable Dim Builder As New OleDb.OleDbConnectionStringBuilder With { .Provider = "Microsoft.Jet.OLEDB.4.0", .DataSource = "C:\Data\NorthWind.mdb" } Using cn As New OleDb.OleDbConnection With {.ConnectionString = Builder.ConnectionString} Using cmd As New OleDb.OleDbCommand With { .Connection = cn, .CommandText = "SELECT Identifier, [Loan No] As LoanNumber FROM Table1" } cn.Open() dt.Load(cmd.ExecuteReader) End Using End Using Return dt End Function

    The above keeps required objects to a minimum and thus allows a developer to debug easier


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Tuesday, September 3, 2013 9:12 PM