Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Reading MSAccess Tables/Records from VB.NET

Posted on 2005-02-25
8
Medium Priority
?
210 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
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
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!

 
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

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.

Question has a verified solution.

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

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

580 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