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
DataAdapter.Fill(ds,"Account")

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.
LVL 1
BrothernodAsked:
Who is Participating?
 
SanclerCommented:
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 =".

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

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

Cheers!
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

   http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;Q286335

and

   http://support.microsoft.com/kb/209187/

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

   MsgBox(ds.Tables("Account").Rows.Count)

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?

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

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.

Roger
0
 
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.
0
 
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.
0
 
BrothernodAuthor Commented:
http://support.microsoft.com/kb/95450/en-us

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.
0
 
BrothernodAuthor Commented:
ehh, after reading it again it's not exactly what I thought, but I still think it's related.
0
 
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.
0
 
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.
0
 
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
0
 
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 :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.