vb.net Query error binding dataset to combobox with autonumber primary key

I am trying to setup a valuemeber for a combobox so I can use it for other queries.

My table (simplified) is Account
IDAccount = (PK) Autonumber Long Integer
bName = text 50

it is an Access database.

I have the following code

dim sql as string = "SELECT bName, IDAccount " _
& "From Account " _
& "ORDER BY bName"


dim ds as New dataset

cmboAccounts.DisplayMember = "bName"
cmboAccounts.valueMember = "IDAccount"
cmboAccounts.DataSource = ds.Tables("Account")

On cmboAccounts.DataSource = ds.Tables("Account") I get the following error "Syntax error (missing operator) in query expression 'Account.IDAccount ='."

Please help.  I'd really like an answer today so i'm making it high points.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Can you post the exact code you are using to create the SQL?
Éric MoreauSenior .Net ConsultantCommented:
Hi Brothernod,

Account may be a reserved word. Either rename it or put it between square brackets:
& "From [Account] " _

I don't think Account is a reserved word in Access.  See here




And that error message looks strange for that line.  Assuming the datatable is being filled correctly (you could check that by


immediately after the .Fill) my guess would be some code in the combobox's .SelectedIndexChanged or .SelectedValueChanged sub or, perhaps, some other binding to the combobox's .SelectedValue.

Do any of those ideas ring any bells with you?

OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Éric MoreauSenior .Net ConsultantCommented:
Sancler, why an event would cause SQL error?

Not the event itself, but some code - e.g. "SELECT * FROM Account WHERE Account.IDAccount =" & cmboAccounts.SelectedValue - in the event's sub after it was passed to the SQL parser.

But equally, why would setting the datasource produce a parser error?  If the datasource doesn't exist the error is "can't find table x".  If the datasource does exist but the data member doesn't, what I get (and I've just rechecked this) is no error but equally no active binding for the control's property.  Even if the table is named within the dataset by a reserved word (and I've just checked that, too), it doesn't matter.

My point was simply that if the reported error related to the reported line it looked odd.  Which suggested that it really related to something else.  It may indeed be the .Fill - on which I suggested a check - but if that has been successfuly navigated then, I reckon, something else is going elsewhere.

BrothernodAuthor Commented:
Using the debuger, just after the fill line I've checked each value in the dataset, for testing purposes I only have 4 values (2 rows, 2 columns).  IDAccount is returned as Integer, bName as string.
BrothernodAuthor Commented:
I have a selectedindexchanged event for the combobox but it uses cmboAccount.Text temporarily until I fix this (so I can continue working) and uses bName not IDAccount.
BrothernodAuthor Commented:

Just what I figured, it has to do with binding to the combobox and the autonumber.  I actually tried changing the autonumber field to a string in the dataset as a last ditch effort before going to bed but it didn't do anything.

So now I need suggestions.  Could I maybe clone or copy the dataset after I fill it and then use the new dataset which has no actual association with the database? I don't really have a complete understanding of how datasets work.
BrothernodAuthor Commented:
ehh, after reading it again it's not exactly what I thought, but I still think it's related.
I think you'll find code in the SelectedIndexChanged is the culprit.  I think you will find that the code was trying to fire while the combobox's data was being loaded by the new databindings and the value your code was trying to use did not exist at that stage.  To overcome that specific problem I suggest you declare a Boolean - say Loading - at form level.  Set it to True before you do the bindings and to False afterwards and make

  If Loading Then Exit Sub

the first line of your sub.

If I am right, that will remove one problem but you might then face another.  If you explicitly use "Account.IDAccount = " in your code, you might find the parser saying it cannot find the column [Account.IDAccount].  That is the usual syntax for typed datasets, which yours doesn't appear to be.  You might need to change it just to "IDAccount =".


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BrothernodAuthor Commented:
I changed it to Account.IDAccount last night because that was the error message I was getting so I assumed it wanted the full name for some reason.

I solved the problem using another method.  One of the other knowledgebase articles I found was about frontpage running into problems with autonumber querries because of null values.

I have NO IDEA why or how my primary key / autonumber field could have a null value, but I humored the program and changed my sql to this

"            Dim sql As String = "SELECT bName, IIF(IsNull(IDAccount),0,IDAccount) AS ID " _
                & "FROM Account " _
                & "ORDER BY bName"

I of course changed my valuemember to "ID" instead of "IDAccount"

it works now.  Of interest, which might lend to some of what you are saying is that when it runs the function to populate the combobox it leaves it's index set to 0 which fires the selectedindexchanged code.  I changed this as it wasn't what I wanted, but I thought it was interesting.
BrothernodAuthor Commented:
Ok More testing :)

I changed my selectedindexchanged function back to using the Tag from cmboAccounts.

It's giving me the same error.

I pulled up the debugger at the start of the function and noticed something odd.

cmboAccounts.Text has a value of "GBCC" which is correct
cmboAccounts.Tag = Nothing and that's what I find odd.
BrothernodAuthor Commented:
cmboAccounts.DataSource = ds.Tables("Account") fires the selectedindexchanged for cmboAccounts and indeed seems to do it before it completes binding and sets the tag values.

and in other news I don't know what I changed but now it's not setting the tag property... It's not firing an error, the 2nd columns in the ds has values, and the valuemember column name is the same as the ds column name..... but no values are being assigned to cmboAccounts.Tag
BrothernodAuthor Commented:
Today's Lesson... don't ask stupid questions before playing with it more.

cmboAccounts.Tag should be cmboAccounts.SelectedValue

Thank you for your help.  I removed my Null parsing and used your loading boolean :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.