Chapter 16 the OLEdb Way?

solva · January 14th, 2004, 09:17 PM

How does the code on page638 of 'Beginning VB.Net 2nd edition' need to be adapted for use with OLE.

I keep getting an error message at

Code:

myDataAdapter.Fill(myDataSet, "customers")

this si the error message

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

here is my whole code I adapted for my use with an Access database called concord.

Code:

' IMport Data and Oledb namespaces
Imports System.Data
Imports System.Data.OleDb

Public Class Form1
    Inherits System.Windows.Forms.Form
    Dim myConnection As OleDbConnection = New _
        OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source = c:\concord.mdb")
    Dim myDataAdapter As New OleDbDataAdapter()
    Dim myDataSet As DataSet = New DataSet()

 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ' Set the SelectCommand properties
        myDataAdapter.SelectCommand = New OleDbCommand()
        myDataAdapter.SelectCommand.Connection = myConnection
        myDataAdapter.SelectCommand.CommandText = _
            "SELECT CustNum, WorkPhone, Alt Phone, Firstname, LastName, Address, Address1, City, StateOrProvince " & _
            "FROM customer " & _
            "ORDER BY LastName, LastName"
        myDataAdapter.SelectCommand.CommandType = CommandType.Text

        ' OPen the database connection
        myConnection.Open()
        'Now execute the command
        myDataAdapter.SelectCommand.ExecuteNonQuery()
        ' Fill DataSet object with data
        myDataAdapter.Fill(myDataSet, "customers")

        'Close the database connection
        myConnection.Close()
        ' Set the dataGrid properties to bind it to our data
        grdCustomerInfo.DataSource = myDataSet
        grdCustomerInfo.DataMember = "customers"
    End Sub
End Class

"I'm Still Learning" Neely Fuller Jr.

Imar · January 15th, 2004, 03:35 PM

Hi Solva,

I tried to answer your personal message, but your anti-spam application doesn't let me through.

Anyway, I can't believe this is so hard to figure out. Try this Google query:

which will take you here:

http://authors.aspalliance.com/aspxt...ClassFill.aspx

or here:

http://www.visual-basic-data-mining....spx?PostID=745

or here:

http://www.fawcette.com/vsm/2003_10/...efault_pf.aspx

or. No wait, I am sure you get the idea.

These sites have some nice examples of how to fill a dataset. I don't think you should use the ExecuteNonQuery as it executes a query without results. Since you need results in your dataset, you can't use that method. The Fill method will handle all this for you.

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Imar · January 15th, 2004, 04:10 PM

Would you mind posting to p2p.wrox.com instead of sending messages to me directly?

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

solva · January 15th, 2004, 04:29 PM

Imar,

Do you have the vb.net book or are you familiar with vb.net. Cause the question I have specifcally deals with chapter 16 and how to change the code in such a way that it will work with an access database?

Thabks

"I'm Still Learning" Neely Fuller Jr.

Imar · January 15th, 2004, 05:00 PM

I don't have the book, so how about you post some code so people can take a look at? If I were you, I'd post both the old and the new code, explain what you have done so far to make it work and where you're running into problems....

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

solva · January 15th, 2004, 05:28 PM

Here is the original code form the book. I posted my code in my first post.

this is the error message i get

Code:

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

I get it here

Code:

myDataAdapter.SelectCommand.ExecuteNonQuery()

Code:

Imports System.Data
Imports System.Data.SqlClient

Public Class Form1
    Inherits System.Windows.Forms.Form
    ' Declare objects...
    Dim myConnection As SqlConnection = New _
        SqlConnection("server=(local);database=pubs;uid=sa;pwd=")

    Dim myDataAdapter As New SqlDataAdapter()
    Dim myDataSet As DataSet = New DataSet()

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ' Set the SelectCommand properties...
        myDataAdapter.SelectCommand = New SqlCommand()
        myDataAdapter.SelectCommand.Connection = myConnection
        myDataAdapter.SelectCommand.CommandText = _
            "SELECT au_lname, au_fname, title, price " & _
            "FROM authors " & _
            "JOIN titleauthor ON authors.au_id = titleauthor.au_id " & _
            "JOIN titles ON titleauthor.title_id = titles.title_id " & _
            "ORDER BY au_lname, au_fname"
        myDataAdapter.SelectCommand.CommandType = CommandType.Text

        ' Open the database connection...
        myConnection.Open()
        ' Now execute the command...
        myDataAdapter.SelectCommand.ExecuteNonQuery()

        ' Fill the DataSet object with data...
        myDataAdapter.Fill(myDataSet, "authors")

        ' Close the database connection...
        myConnection.Close()

        ' Set the DataGrid properties to bind it to our data...
        grdAuthorTitles.DataSource = myDataSet
        grdAuthorTitles.DataMember = "authors"

"I'm Still Learning" Neely Fuller Jr.

jlick · January 15th, 2004, 07:13 PM

As an FYI. There are forums for indivudual books. If you go to this post (on the site), you can get to the Beginning VB.NET BOOK forum by doing the following...

Click on the "All Forums" link at the top of the page. Click on the "+ Books" link (NOT "WROX BOOKS"). This will display a list of all the books that have forums. Click on the one called "Beginning VB.NET, 2nd Edition". This is a forum on the book.

John R Lick
[email protected]

Imar · January 16th, 2004, 05:49 AM

Well, I can't see any problems in your code. It might be the SQL statement. Try to enclose the column names in [] when they contain a space (or better yet, modify the Access database so the table and column names no longer contain spaces)

myDataAdapter.SelectCommand.CommandText = _
            "SELECT CustNum, WorkPhone, [Alt Phone], Firstname, LastName, Address, Address1, City, StateOrProvince " & _
            "FROM customer " & _
            "ORDER BY LastName, LastName"

I am sure you already checked it, but does the concord.mdb contain the Customers table? Doe that table contain all the fields you're using in your SQL statement? You may want to try the SQL statement in the database first.

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

peptide · January 29th, 2004, 05:27 PM

I noted in your code you had the table as both "customers" and "customer" this is the likely problem.

Cheers,
Josh

peptide · January 29th, 2004, 05:29 PM

Quote:

quote:Originally posted by peptide
I noted in your code you had the table as both "customers" and "customer" this is the likely problem.

Cheers,
Josh

See the your select command specifically.