[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2006-03-30
14
Medium Priority
?
374 Views
Last Modified: 2009-12-16
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.
0
Comment
Question by:Brothernod
  • 8
  • 3
  • 2
  • +1
14 Comments
 
LVL 34

Expert Comment

by:flavo
ID: 16330958
Can you post the exact code you are using to create the SQL?
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 16331101
Hi Brothernod,

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

Cheers!
0
 
LVL 34

Expert Comment

by:Sancler
ID: 16331480
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 70

Expert Comment

by:Éric Moreau
ID: 16331499
Sancler, why an event would cause SQL error?
0
 
LVL 34

Expert Comment

by:Sancler
ID: 16331914
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
 
LVL 1

Author Comment

by:Brothernod
ID: 16334063
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
 
LVL 1

Author Comment

by:Brothernod
ID: 16334189
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
 
LVL 1

Author Comment

by:Brothernod
ID: 16334236
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
 
LVL 1

Author Comment

by:Brothernod
ID: 16334246
ehh, after reading it again it's not exactly what I thought, but I still think it's related.
0
 
LVL 34

Accepted Solution

by:
Sancler earned 1000 total points
ID: 16334568
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
 
LVL 1

Author Comment

by:Brothernod
ID: 16334781
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
 
LVL 1

Author Comment

by:Brothernod
ID: 16334879
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
 
LVL 1

Author Comment

by:Brothernod
ID: 16335101
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
 
LVL 1

Author Comment

by:Brothernod
ID: 16335233
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

868 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