DataAdapter vs Coding

[Guest]

Need help understanding the following please:

When I am creating a project and code my connection using Dim connectString
As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=C:\Temp\NW-test.MDB", everything works correctly when I run the
project.

However, if I set up the exact same connection using the DataAdapter wizard
and try to run the project, I get a "Microsoft Jet database engine cannot
open the file..." error.

I don't understand why the project runs correctly using the "coded"
connection but always fails when using the DataAdapter wizard.

Any assistance would be greatly appreciated.

Thanks,

TJ

 

[Scott M.]

With a DataAdapter, you still set up your connection as normal (connection
string and all).

Dim con as New OleDb.OleDbConnection("Provider=...")
Dim da as New OleDb.OleDbDataAdapter("SELECT .... FROM ...", con)
Dim ds as New DataSet
da.Fill(ds,srcTableName)

At this point you will have a DataSet containing a table that contains
whatever data your SELECT specified.

 

[Jeff Dillon]

He asked why the Wizard is failing

With a DataAdapter, you still set up your connection as normal (connection
string and all).

Dim con as New OleDb.OleDbConnection("Provider=...")
Dim da as New OleDb.OleDbDataAdapter("SELECT .... FROM ...", con)
Dim ds as New DataSet
da.Fill(ds,srcTableName)

At this point you will have a DataSet containing a table that contains
whatever data your SELECT specified.

 

[Scott M.]

Actually, he said that he set up a DataAdapter using the wizard and that
when the project runs, it fails. He did not say that the wizard fails. So,
by using my code below (or by using the wizard to generate the code below),
he can see if he has a VS problem or just a coding problem. (I suspect that
he didn't use the da.fill which you need to still write even if you use the
wizard).

 

[Guest]

Scott -

As Jeff suggests, it's what the wizard creates that's failing to connect and
I don't understand why. Obviously, I can get a good connection if I code it,
but what I don't understand is why it doesn't work using the code the wizard
generates. FWIW, I did use the da.fill and the binding process following the
wizard setup. My problem is not in getting it to work using the proper code
but rather it's not working using wizard generated code and I don't
understand why.

Regards,

TJ Doherty

 

[Scott M.]

We would need to see the code that the wizard created for you.

 

[Jeff Dillon]

Oh, he WAS asking why the wizard was failing

I had a similar problem with the wizard, but coding works. I didn't want to
spend the time to determine why the wizard was failing, since I had a work
around.

Jeff

 

[Scott M.]

Actually, I wouldn't say that. He's asking why the code generated by the
wizard fails. The wizard itself doesn't fail and this was my point.

 

[Jeff Dillon]

No, the wizard WAS failing..it was failing to create the proper code.

Jeff

 

[Scott M.]

Semantics here Jeff. The OP was able to move through and use the features
in all of the screens of the wizard. In that sense, the wizard did not
fail.

It actually has yet to be seen if the code generated by the wizard is at
fault as well, since we haven't seen the OP give us that code yet.

 

[Guest]

Scott -

Sorry about not replying, been out of town until today.

Here's the code from the project's dll - not sure where the wizard's
generated code is stored:

"D a t a S o u r c e = " C : \ T e m p \ N W - t e s t . m d b " ; J e t
O L E D B : E n g i n e T y p e = 5 ; P r o v i d e r = " M i c r o s o f t
.. J e t . O L E D B . 4 . 0 " ; J e t O L E D B : S y s t e m d a t a b a
s e = ; J e t O L E D B : S F P = F a l s e ; p e r s i s t s e c u r i t
y i n f o = F a l s e ; E x t e n d e d P r o p e r t i e s = ; M o d e =
S h a r e D e n y N o n e ; J e t O L E D B : E n c r y p t D a t a b
a s e = F a l s e ; J e t O L E D B : C r e a t e S y s t e m D a t a b
a s e = F a l s e ; J e t O L E D B : D o n ' t C o p y L o c a l e o
n C o m p a c t = F a l s e ; J e t O L E D B : C o m p a c t W i t h o
u t R e p l i c a R e p a i r = F a l s e ; U s e r I D = A d m i n ; J
e t O L E D B : G l o b a l B u l k T r a n s a c t i o n s = 1"

As I stated, if I run the project using "Debug - Run without debugging", I
get the message below about the Jet Engine failing to open the DB due to
either a bad connection or the database being opened exclusively.

Whatever explanation you can give me would be appreciated.

Regards,

TJ

 

[Jeff Dillon]

Set Everyone Full Control on the C:\TEMP directory (you are saving your mdb
in a TEMP directory??) and also on TEMP and TMP directories

Jeff

 

[Scott M.]

If making sure that the folder and the database file have correct user
permissions doesn't do the trick (which it probably would), you should also
show us the rest of the code the wizard generated for you.

 

[Guest]

Jeff -

Only while I'm running this test. Will try what you suggest but don't
understand why I need to do that when it works perfect with the "coded
version" and I didn't set any directory permissions - that is really what I
don't understand.

TJ

 

[Guest]

Scott -

Where do I find the wizard generated code? What directory and file? So far
the only code that looks like connection information is what I copied below
from the .dll when I opened it in notepad.

TJ

 

[Jeff Dillon]

Try it and see!! It's part of the troubleshooting process. Because the
wizard "May" use a different user context. Also, the code it writes is
under a collapsed outline. Look for the + sign next to Web Form Designer
Generated Code in your code-behind file, and expand it.

Jeff

 

[Guest]

Jeff -

Here's the connection string: "Me.cnCustomers.ConnectionString = "Jet
OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDBatabase
L" & _
"ocking Mode=1;Data Source=""C:\website\NW-test.mdb"";Mode=Share
Deny None;Jet OLED" & _
"B:Engine Type=5;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet
OLEDB:System database=;Je" & _
"t OLEDB:SFP=False;persist security info=False;Extended
Properties=;Jet OLEDB:Com" & _
"pact Without Replica Repair=False;Jet OLEDB:Encrypt
Database=False;Jet OLEDB:Cre" & _
"ate System Database=False;Jet OLEDBon't Copy Locale on
Compact=False;User ID=A" & _
"dmin;Jet OLEDB:Global Bulk Transactions=1"

Changed the db directory to C:\website. Tried to set user controls but
c:\website properties only has 4 tabs. On General I cleared the read-only
check. Set web sharing tab to read, write, execute. Did nothing to the
sharing tab (using Windows XP SP2 OS). Never did find anything called "User
controls". Both Temp and Tmp default to c:\windows\temp. Won't let me clear
the read-only nor set a web sharing alias of Temp. At a loss now. Tried to
run but got the same error.

TJ

 

[Guest]

Scott -

Jeff told me where to get it. Here it is: "Me.cnCustomers.ConnectionString
= "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet
OLEDBatabase L" & _
"ocking Mode=1;Data Source=""C:\website\NW-test.mdb"";Mode=Share
Deny None;Jet OLED" & _
"B:Engine Type=5;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet
OLEDB:System database=;Je" & _
"t OLEDB:SFP=False;persist security info=False;Extended
Properties=;Jet OLEDB:Com" & _
"pact Without Replica Repair=False;Jet OLEDB:Encrypt
Database=False;Jet OLEDB:Cre" & _
"ate System Database=False;Jet OLEDBon't Copy Locale on
Compact=False;User ID=A" & _
"dmin;Jet OLEDB:Global Bulk Transactions=1"

TJ

 

[Scott M.]

We want ALL the code the wizard generated, not just the connection string.
There is quite a bit more in there than just that. When you right-click the
folder and file, there should be a "Security" tab where you can set which
account has what permissions.

 

[Guest]

Jeff & Scott -

Scott's last message to me finally got through to my cranium. Changed the
sharing on the folder in which the DB was residing and things began to work
normally. Still don't understand why you don't need to do that when using a
coded connection but have to make the change when using the data adapter
wizard information. Oh well, one of those quirks, I guess. FWIW, it worked
fine without having to reset my TEMP and TMP variables (of course I was just
reading the data, not doing updates - we'll see if it still works in the next
phase).

Want to thank both of you for hanging in there with me while I worked
through the problem - much appreciated.

TJ