• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 660
  • Last Modified:

DAO Query in VB.NET error

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
undyshelts
Asked:
undyshelts
  • 3
  • 3
  • 3
  • +1
1 Solution
 
Ryan ChongCommented:
Try:

dim dDB As New Database

or

dim dDB As New DAO.Database

?
0
 
undysheltsAuthor Commented:
nah sorry ryancys.. it didn't work...

Anyone know anything else?
0
 
Arthur_WoodCommented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
DabasCommented:
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
 
Arthur_WoodCommented:
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
 
DabasCommented:
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
 
Arthur_WoodCommented:
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
 
DabasCommented:
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
 
undysheltsAuthor Commented:
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
 
undysheltsAuthor Commented:
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
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.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 3
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now