Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DAO Query in VB.NET error

Posted on 2004-04-24
10
Medium Priority
?
647 Views
Last Modified: 2010-04-24
G'day everyone

I'm using DAO in vb.NET (yes, i know ADO.NET exists i just don't know how to use it YET)...

Anyway... i have the following code...

dim dDB As Database
selectString="SELECT t1.[file path] & t1.[file name] AS [FileName & Path], t1.[file size], t2.[file size]
FROM FileSys1 AS t1 INNER JOIN FileSys2 AS t2 ON (t1.[file name]=t2.[file name]) AND (t1.[file path]=t2.[file path])AND ((t1.[file size]<>t2.[file size]));"

dDB.Execute(selectString)

When i run this code, when it reaches the "dDB.Execute(selectString)" line it shows me the following error:

An unhandled exception of type 'System.NullReferenceException' occurred in simpledb.exe
Additional information: Object reference not set to an instance of an object.

Although when i copy and paste the SQL statement into an access query it works fine!
In vb.net i can successfully execute queries such as dDB.Execute("DELETE [Table1].*, * FROM Table1 ;")

How can i make my code work!

Cheers, Andrew
0
Comment
Question by:undyshelts
[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
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 10908424
Try:

dim dDB As New Database

or

dim dDB As New DAO.Database

?
0
 
LVL 1

Author Comment

by:undyshelts
ID: 10910625
nah sorry ryancys.. it didn't work...

Anyone know anything else?
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 10913238
try in this way, then:

dim dDB As Database
dDB =  DBEngine.OpenDatabase(<path to your MDB>)

selectString="SELECT t1.[file path] & t1.[file name] AS [FileName & Path], t1.[file size], t2.[file size]
FROM FileSys1 AS t1 INNER JOIN FileSys2 AS t2 ON (t1.[file name]=t2.[file name]) AND (t1.[file path]=t2.[file path])AND ((t1.[file size]<>t2.[file size]));"

dDB.Execute(selectString)

you must still create open the spcific database which you wish to interact with, just as you did with VB 6.


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 27

Accepted Solution

by:
Dabas earned 500 total points
ID: 10913733
Hi undyshelts:
> (yes, i know ADO.NET exists i just don't know how to use it YET
I seriously suggest you do spend a little time to learn it!
As many others here, it requires a learning curve, but once you have done so, you will not want to return to DAO any more!

AS to your statement dDB.Execute(SelectString)... it is not clear. What exactly do you want to do?
Even in VB6 DAO this would not make sense. Your query returns a recordset. Where are you storing it?

Your dDB.Execute("DELETE...) does not have this problem because it is an ACTION query which does not necessarily return a value.

Dabas
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 10914591
Dabas, in either case, the Database Obejct that is being created does NOT have any related PHYSICAL database fiel associated with it.

undyshelts, have you ever done *any* DAO programming, as the code you show seems to suggest that you do not understand DAO either.

AW
0
 
LVL 27

Expert Comment

by:Dabas
ID: 10914614
Arthur:
Agreed concerning the DB Object. As far as I knew until now, DAO does not work with .NET.
Maybe undyshelts is confusing DAO with ADO?
The DELETE statement provided also does not look 100% correct in syntax to me (Why does the * appear twice?)
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 10914642
yes it does, according to MSDN.

and yse, this

In vb.net i can successfully execute queries such as dDB.Execute("DELETE [Table1].*, * FROM Table1 ;")
  is VERY suspect, and in fact will produce a ruin-time error (SYNTAX error)

perhaps he means 'can I successfully...', not 'i can successfully..."

The Delete statement should read

dDB.Execute("DELETE * FROM Table1")


AW

0
 
LVL 27

Expert Comment

by:Dabas
ID: 10914667
AW:

He uses the correct syntax for the delete in: (Similar question)

http://www.experts-exchange.com/Programming/Q_20843286.html "DAO Query Troubles in VB.NET"
0
 
LVL 1

Author Comment

by:undyshelts
ID: 10916752
Hey everyone...

nothing works in DAO with that query i had.. although i did teach myself ADO.NET.. and it works like a charm... cheers Dabas for making me get off my backside and finally learning it...

as for all the DAO code people provided, i tried it all and nothing seemed to work, maybe DAO isn't advanced enough to support such queries..

Cheers :-)
0
 
LVL 1

Author Comment

by:undyshelts
ID: 10918421
G'day everyone..

I have posted another problem i'm having... this time in ADO.NET (impressive huh!!)...

it's at:  http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB.NET/Q_20967653.html

Cheers :-)
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

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 …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

722 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