?
Solved

Reading MSAccess Tables/Records from VB.NET

Posted on 2005-02-25
8
Medium Priority
?
188 Views
Last Modified: 2010-04-23
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?



0
Comment
Question by:tlatacki
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 6

Assisted Solution

by:PoeticAudio
PoeticAudio earned 80 total points
ID: 13406434
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
 

Author Comment

by:tlatacki
ID: 13406474
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
 
LVL 6

Expert Comment

by:PoeticAudio
ID: 13406590
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Assisted Solution

by:Lacutah
Lacutah earned 80 total points
ID: 13406610
Is the COMB_ACCOUNT field a numeric field instead of a text field?
0
 

Author Comment

by:tlatacki
ID: 13406631
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
 

Author Comment

by:tlatacki
ID: 13406681
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
 
LVL 41

Accepted Solution

by:
graye earned 80 total points
ID: 13412826
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
 

Author Comment

by:tlatacki
ID: 13511187
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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 …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses

762 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