Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 25th, 2003, 05:21 AM
Registered User
 
Join Date: Sep 2003
Location: Karlstad, , Sweden.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Connecting to Access DB using Visual Basic .NET

Hi I've got a problem when following the book "Beginning Visual Basic .NET"

In chapter 15, which I was looking for, the auther goes through how to work with databases and goes through how to create one in access (I already knew how so...) afterwards he writes on how to connect this one to a new empty windows application but somehow I can't seem to get it to work.

I'll outline what I've done below so you maybe can tell me whats wrong

* Created a new windows application
* Added a OleDbDataAdapter and in the wizard that followed did the following:
  - New connection, Provider, Microsoft JET 4.0 OLE DB Provider
  - Looked up my database
  - Pressed ok
  - Could only select Use SQL statements so picked that one
  - In the Advanced Options unchecked everything (only the first two were checked)
  - Generated a simple SQL statement using the Query Builder
  - Pressed next (No errors: Generated SELECT statement, Generated table mappings)

* Entered menu Data and selected Generate DataSet...
* Accepted everything as it was (New: DataSet1, checked for my created table using OleDbAdapter, checked the bottom checkbox)
* Pressed Ok (ERROR: "There were prolems generating Binding_Data.DataSet1. To resolve this problem, build the project, fix any errors, and then generate the dataset again.")
* Tried to build the project, no problems
* Generated the dataset again but same thing happened

Is there anybody who might be able to help me out here? I really want to be able to start using SQL databases but until I figure out whats wrong I can't
 
Old September 25th, 2003, 03:11 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Lieth,

I stepped through your instructions and couldn't reproduce your error. The dataset was generated OK. The VS.NET wizards can be a little funny that way I've found. The following, however, codes your steps for you. Just create a new Windows Forms App, and place a button control on the form. Open the folder that contains your new app. You'll see a directory named 'bin'. This is where your exe gets compiled when you build your application. Place a copy of the Northwind.mdb in the 'bin' directory. The code that follows looks for it there. Also place the following line of code at the very top of your form module, before the Public Class Form1 statement:

Imports Sytem.Data.OleDb

Here is your command buttons click event:

Private Sub btnLoadDataSet_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
        Handles btnLoadDataSet.Click

        Dim strConn As String = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & _
            Application.StartupPath & "\Northwind.mdb;"

        Dim cn As New OleDbConnection(strConn)
        Dim da As OleDbDataAdapter = New OleDbDataAdapter()
        Dim ds As DataSet
        Dim row As DataRow

        da.TableMappings.Add("Table", "Customers")
        cn.Open()
        Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Customers", cn)
        cmd.CommandType = CommandType.Text
        da.SelectCommand = cmd
        ds = New DataSet("Customers")
        da.Fill(ds)

        ' Dump dataset contents to Output window
        Dim oRow As DataRow
        Dim strRecord As String

        For Each oRow In ds.Tables("Customers").Rows
            strRecord = "Customer Id: " & oRow("CustomerId").ToString()
            strRecord = strRecord & " Company Name: "
            strRecord = strRecord & oRow("CompanyName").ToString()
            Console.WriteLine(strRecord)
        Next
        cn.Close()
    End Sub

This creates your OleDB connection, dataadapter, command and dataset object in code, opens the connection, executes a SQL statment against the Access db, fills a dataset object with the results, and writes the results to the Output window. The same code will work for SQL Server if you change the connection string and use the SQLClient namespace instead of the OleDB namespace and change your ADO.NET objects accordingly (i.e., SqlConnection, SqlCommand, etc.).

HTH,

Bob

 
Old September 25th, 2003, 04:26 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Don't need:

Dim row As DataRow

I declared it further along as:

Dim oRow As DataRow

 
Old September 25th, 2003, 04:54 PM
Registered User
 
Join Date: Sep 2003
Location: Karlstad, , Sweden.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hmmm I'm starting to wonder if my installation of VS.NET is faulty in some aspects. I tried to run your code but I got the following error


An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll


when the line "da.Fill(ds)" is executed. What do you think?
 
Old September 25th, 2003, 07:07 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hmm...

First add these additional import statments and run it again (long shot):

Imports System
Imports System.Data
Imports System.Data.OleDb

System.Data is the namespace where ADO.NET classes live including your DataSet class, which doesn't seem to want to cooperate.

Now try this: instead of using a DataAdapter to fill a DataSet, use a OleDbCommand object to execute a DataReader. The DataReader gives you a forward-only, read-only data stream. Here's the whole Form1 class. Just curious to see if you can get data on the connection:

Imports System.Data.OleDb

Public Class Form1
    Inherits System.Windows.Forms.Form

    ' Windows Form Designer generated code

    Private Sub btnLoadDataSet_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
        Handles btnLoadDataSet.Click

        Dim strConn As String = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & _
            Application.StartupPath & "\Northwind.mdb;"

        Dim cn As New OleDbConnection(strConn)
        Dim cmd As OleDbCommand = _
          New OleDbCommand("SELECT CustomerID, CompanyName FROM Customers", cn)
        cn.Open()

        Dim reader As OleDbDataReader = cmd.ExecuteReader()

        Do While reader.Read()
            Console.WriteLine(vbTab & reader.GetString(0) & vbTab & reader.GetString(1))
        Loop

        cn.Close()
    End Sub
End Class


 
Old September 26th, 2003, 03:20 AM
Registered User
 
Join Date: Sep 2003
Location: Karlstad, , Sweden.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Same error occured at another row.

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

in row "Dim reader As OleDbDataReader = cmd.ExecuteReader()"

I did however get one example to work yesterday, the follwing code (I remove some in in since that is just initialations and placements of controls:


Imports System.Data

Public Class Form1
 Inherits System.Windows.Forms.Form
 Public ds As DataSet

Windows Form Designer generated code
 Friend WithEvents sc As OleDbCommand
 Friend WithEvents da As OleDbDataAdapter
 Friend WithEvents dc As OleDbConnection
 Friend WithEvents dg As DataGrid
     '
     'dg
     '
     Me.dg.DataSource = Me.ds
 End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
                       System.EventArgs) Handles MyBase.Load
     Me.da.Fill(ds)
     Me.dg.DataMember = "Customer"
End Sub


Hmm maybe this was a dumg thing to remove some code. If you want to I could send you a copy of the code if needed. I used the wizard to create the OleDbDataAdapter and thereby also the OleDbConnection as well. I only added some lines in the code above to make it work. I placed the Public ds as DataSet at the top, added the line "Me.dg.DataSource = Me.ds" in the generated code section, might be foolish to place it there but I was just trying. At last I wrote the button click handler. This do work I'm just curios as to why other code aren't working.
 
Old September 26th, 2003, 06:17 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Wish a had some wise words for ya', but all I can think to say is beats me. Yor system.data.oledb namespace seems to be the culprit somehow.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Connecting to Access 20007 DB with vb.net JohnBoy VB.NET 1 May 21st, 2008 01:26 PM
connecting the Equipment through Visual basic 2005 poja Visual Basic 2005 Basics 5 October 15th, 2006 06:02 AM
Connecting to Access Db mattastic Classic ASP Databases 3 October 14th, 2004 02:32 AM
How to access foxpro Database using Visual Basic Ling Fong Access 1 August 23rd, 2004 07:42 AM
Programmatic access to Visual Basic Project is not aspadda Excel VBA 2 March 19th, 2004 04:06 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.