Using WDABase Class With DataGridView

sirmilt · April 3rd, 2010, 01:36 PM

I am using the WSABase class extensively in a program I'm working on. I have no problem with most operations but filling a DataGridView has me stumped. Here's the situation.

I am using an Access 2000 database and Visual Basic 2008 Professional. Since i can't get the WDABase class to work for this specific procedure, my code looks like this:

Code:

PrivateSub FillGridContent()
GetAppDataPath()
Cn = New OleDbConnection(strCnString)
Cmd = New OleDbCommand
Cmd.CommandText = strSQL
Cmd.CommandType = CommandType.StoredProcedure
Cmd.Connection = Cn
Da = New OleDbDataAdapter
Ds = New DataSet
Da.SelectCommand = Cmd
Try
Da.Fill(Ds, strTable)
dgvCollections.DataSource = Ds
dgvCollections.DataMember = strTable
With dgvCollections
.Columns(0).Width = 35
.Columns(1).Width = 200
.Columns(2).Width = 250
.Columns(3).Width = 250
EndWith
Catch ex As Exception
MsgBox(ex.Message)
EndTry
EndSub

All of the other database functions use the WDABase class and work perfectly, but aftera half dozen different attempts I can't get this code converted to use WDABase also.

Can anyone help?

Thearon · April 5th, 2010, 06:07 AM

Hi,

Is the code that you posted contained in the WSABase class? Is the WSABase class inherited by one of the other data access classes?

I suspect that this may be the case and that these classes do not have access to the DataGridView object in your form. You should have the WSABase class pass back a populated DataSet back to your form that you can use.

Thearon

sirmilt · April 5th, 2010, 06:12 PM

Thanks Thearon, for the quick reply.

I think my problem stems from the fact that I use the DataAdapter and the DataSet in poulating the Grid, I have added the changed the variable definitions and added the DataSet andDataAdapter. The variables in the WDABase code now look like this:

Public cn As OleDbConnection
Public cmd As OleDbCommand
Public da As OleDbDataAdapter
Public dr As OleDbDataReader
Public ds As DataSet
Public cm As CurrencyManager

Since tha DataGridView uses both the DataAdapter and the DataView, I'm at a loss as to how to code them in my program. The program works with the coding as shown in my first post, but for uniformities sake I would like all of my code to use the WDABase class.

Any suggestions would really be appreciated.
Thanks

Thearon · April 6th, 2010, 06:38 AM

I'm kind of at a loss since I do not know what your project looks like. What kind of error are you getting?

Thearon

sirmilt · April 6th, 2010, 10:46 AM

Thanks, Thearon for the reply.

Let me try to do a litle better job expaining my problem.

I am working on a personal home entertainment collections program. The program allows the user to collect and save Book, DVD and CD information.

The user sees a DataGridView of one of the collections and can select a specific item t review, change or delete by clicking the line on the grid

All of the database activities,including filling the details, saving the record, deleting a record an adding a new recordare programed using the WDABase class that you decribe and they work fine.

Since I haven't been able to get the DataGridView to work using the WDABase classs I programmed it using the Imprts OLEDB and that works fine, but the incocistany of method bothers me. I have tried (unsuccessfully) to program the DataGridView usingthe WDABas class..

Here's what I tried to do:
I set up a new very simple project with one form and a DataGrid view. I added the WDABase class with the following changes:

Code:


PublicClass wdaBase
Implements IDisposable
'Class level variables that are available to classes that instantiate me
Public SQL AsString
Public cn As OleDbConnection
Public cmd As OleDbCommand
Public da As OleDbDataAdapter
Public dr As OleDbDataReader
Public ds As DataSet
Public cm As CurrencyManager
Private disposed AsBoolean = False

In the form Load event of my little test I added the following:

Code:

PrivateSub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) HandlesMyBase.Load
GetAppDataPath()
Using oData AsNew wdaBase
oData.SQL = "qryBooksGridSelect"
oData.InitializeCommand()
oData.OpenConnection()
oData.da("qryBooksGridSelect", oData.cn)
EndUsing

The reference to "GetAppPath" is a module where I build the connection string. It works fine elsewhere and the reference is removed from the WDABase class.
The line "
oData.da("qryBooksGridSelect", oData.cn)" produces an error "Expression is not a method"

I just don't know where to go from here. Can you help?

Milt

Thearon · April 7th, 2010, 02:14 PM

Hi,

You need to set the SelectCommand property of the DataAdpater something like this:

oData.da.SelectCommand = oData.cmd

I think that may be the part you are missing.

After the SelectCommand property has been set you can then call the Fill method to populate your DataSet.

Thearon

sirmilt · April 8th, 2010, 09:41 AM

Thanks, Thearon, for spending so much time with an "old" novice.

Following your suggestion, my code now looks like this:

Code:

GetAppDataPath()
Dim strTable AsString = "tblBooks"
Using oData AsNew WDABase
oData.SQL = "qryBooksGridSelect"
oData.InitializeCommand()
oData.OpenConnection()
oData.da.SelectCommand = oData.cmd
Ds = New DataSet
Da.Fill(Ds, strTable)
dgv.DataSource = Ds
dgv.DataMember = strTable
dgv.Columns(0).Width = 35
dgv.Columns(1).Width = 200
dgv.Columns(2).Width = 250
dgv.Columns(3).Width = 250
EndUsing

When I trt to run I get the error "Object reference not set to an instance of an object." at the line that is bold and underlined.

Milt

Thearon · April 8th, 2010, 10:23 AM

It appears that perhaps you have not instantiated the DataAdapter object before trying to use it. Before your line in bold or in your data access class do the following:

oData.da = New OleDbDataAdapter(oData.cmd, oData.connection)

Thearon

sirmilt · April 8th, 2010, 10:59 AM

Thearon:

I made the change you suggested, thiscode:
oData.da = New OleDbDataAdapter(oData.cmd, oData.cn)

produced this error:
Type 'OleDbDataAdapter' is not defined.

When I added:
Imports System.Data
Imports System.Data.OleDb

above the Class I get this error
Overload resolution failed because no accessible 'New' can be called with these arguments:

Do you think that I would be better off not using the WDA Base class for yhe DataGridView?

Thanks again for trying to help.

Milt

Thearon · April 8th, 2010, 11:30 AM

Sorry, my mistake. Try this:

oData.da = New OleDbDataAdapter(oData.cmd)

The cmd object should already be initialized with the query you want to execute and the connection object to the database.

Thearon