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

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

Don't need:

Dim row As DataRow

I declared it further along as:

Dim oRow As DataRow

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?

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

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.

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.