Reading MSAccess Tables/Records from VB.NET

Ok - This is a project that I have inherited. I'm using bits of routines already in place in the program to add additional features.

I'm having an issue when I'm reading data from an Access DB
---------------------------------------------------------------------------
Dim dtPlanPortfolioComponents As dsICCPortfolioComponents.PortfolioComponentsDataTable = New dsICCPortfolioComponents.PortfolioComponentsDataTable
ACCESSCommand = mICCConnection.CreateCommand()
ACCESSCommand.CommandType = CommandType.Text
Dim ACCESSDataAdapter As OleDb.OleDbDataAdapter
ACCESSCommand.CommandText = "SELECT * FROM ACCOUNT_COMBINED WHERE (COMB_ACCOUNT = '/" + drPlanPortfolio.ICCAccountNumber + "')"
ACCESSDataAdapter = New OleDb.OleDbDataAdapter(ACCESSCommand)
Try
      ACCESSDataAdapter.Fill(dtPlanPortfolioComponents)
Catch ex As Exception
      ShowException(ex, ACCESSCommand.CommandText)
End Try
For Each drPlanPortfolioComponent As dsICCPortfolioComponents.PortfolioComponentsRow In dtPlanPortfolioComponents.Rows
      'MsgBox(drPlanPortfolioComponent.COMB_ACCOUNT_NUM)
      'MsgBox(drPlanPortfolioComponent.ACCOUNT_NUM)
Next
---------------------------------------------------------------------------
I get the following error.
No value given for one or more rquired parameters.
SELECT * FROM ACCOUNT_COMBINED WHERE COMB_ACCOUNT = '/E271110'

if I remove the WHERE clause it works fine.

The thing that confuses me is there is other places were the WHERE works fine.

Any thoughts?



tlatackiAsked:
Who is Participating?
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.

PoeticAudioCommented:
I might be wrong, But I think the parenthesis are screwing you up...

ACCESSCommand.CommandText = "SELECT * FROM ACCOUNT_COMBINED WHERE COMB_ACCOUNT = '/" + drPlanPortfolio.ICCAccountNumber + "'"
0
tlatackiAuthor Commented:
Sorry about that - I didn't originally have them - I added to see if that would help, access uses them in its queries. It didn't make any difference. Still gives me the error.
0
PoeticAudioCommented:
alright so ACCOUNT_COMBINED is your table right? (doesn't quite sound like a table name to me, but of coarse I could be wrong)

I don't really see anything wrong with that query, unless it has something to do with

drPlanPortfolio.ICCAcountNumber
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

LacutahCommented:
Is the COMB_ACCOUNT field a numeric field instead of a text field?
0
tlatackiAuthor Commented:
I don't see anything wrong either - yes, they do have some strange table names.

Thanks - maybe the problem is with my connection - but since it works without the Where clause I'm not sure about that either.

0
tlatackiAuthor Commented:
Nope - its text.

The interesting thing is  if I take the Select statement as it appears in the error message and run it in Access I get basically the same error - the window prompting me for a parameter appears.

Now I know the sql syntax in SQL and ACCESS SQL are different - but I thought that the Data Provider was supposed to handle issues like that.

0
grayeCommented:
I'm thinking its the slash "/" that's causing the problem....

Try substituting the following as a test:

SELECT * FROM ACCOUNT_COMBINED WHERE COMB_ACCOUNT like '?E271110'
  or
SELECT * FROM ACCOUNT_COMBINED WHERE COMB_ACCOUNT like '[/]E271110'
0

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
tlatackiAuthor Commented:
Ok - I finally looked back into this issue and it actually boiled down to not reading the table correctly and following someone else's table layout.

the COMB_ACCOUNT field should have been COMB_ACCOUNT_NUM - it was just a bad error message - it wasn't telling me the real issue like field not found.
0
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.

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.