Solved

vb.net OleDbDataAdapter or SqlDataAdapter

Posted on 2006-07-11
12
1,476 Views
Last Modified: 2012-08-14
I want to put all the code to communicate with my db in a module.

In a form, if I need to fill a combo box with a table from my db, I currently doing something like this:
        Dim dataadapter As New SqlDataAdapter
        Dim dataAdapter As New OleDb.OleDbDataAdapter(strSelect, myConnection)
        Dim table As New DataTable()
        dataAdapter.Fill(table)
        cmbBox.DataSource = table
        cmbBox.DisplayMember = "description"
        cmbBox.ValueMember = "ID"

If I have a function return a dataadapter, it would help me put the sql stuff in my new module.
The reason I am doing this is because I don't know what kind of db I am going to be using.
How can I call a function if I don't know if I am going to use a OleDbDataAdapter or SqlDataAdapter.

like
        Dim dataAdapter As New OleDb.OleDbDataAdapter
        dataAdapter = mynewfunction(strSelect)

or
        Dim dataAdapter As New SqlDataAdapter
        dataAdapter = mynewfunction(strSelect)


I don't mind changing code in my db module, but don't want to have to change code every time I call a function from this module.

Is there any way to have a more generic dataadapter?

If there is another solution, please let me know.
0
Comment
Question by:jackjohnson44
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 total points
ID: 17083572
Yes. You can delcare an object using the interface implemented by each of the DataAdapters:

    Dim dataAdapter As IDataAdapter
   
Any if you are returning a DataAdapter from a method declare the return type as IDataAdapter.
0
 

Author Comment

by:jackjohnson44
ID: 17083585
thanks, I am new to this, what does the "I" stand for?

Is this common in .net?
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 17083603
Its a prefix that indicates that it is an interface rather than a class. Its a common convention throughout most OO languages.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jackjohnson44
ID: 17083690
I tried below, getDataAdapter returns IDataAdapter
It says "table" DataTable can't be converted to DataSet.
What am I doing wrong?

        Dim dataAdapter As IDataAdapter

        dataadapter = getDataAdapter("SELECT ID, description FROM Tests")

        Dim table As New DataTable()

        dataAdapter.Fill(table)

        cmbBox.DataSource = table
        cmbBox.DisplayMember = "description"
        cmbBox.ValueMember = "ID"
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 17083722
What does getDataAdapter do ? It sounds like its returning a DataTable rather than a DataAdapter. If thats the case the you can just return DataTable as that is a generic type across all the data providers.
0
 

Author Comment

by:jackjohnson44
ID: 17083739
Thanks a lot for your help.

It should be returning a IDataAdapter

    Public Function getDataAdapter(ByVal selectString As String) As IDataAdapter
        Dim myData As New DataSet()
        Dim myConnection As New OleDb.OleDbConnection("mystring, this works")

        myConnection.Open()

        Dim dataAdapter As New OleDb.OleDbDataAdapter(selectString, myConnection)

        getDataAdapter = dataAdapter

    End Function

do I have to convert something?
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 17083786
Ah, didn't read the post properly. This line:

    dataAdapter.Fill(table)

Is expecting a DataSet rather than a DataTable:

    Dim ds As New DataSet()
    dataAdapter.Fill(ds)
0
 

Author Comment

by:jackjohnson44
ID: 17083859
Thanks, but I am confused now.
I thought I was supposed to use a IDataAdapter

        Dim dataAdapter As IDataAdapter
        dataadapter = getDataAdapter("SELECT ID, description FROM Tests")
        Dim table As New DataTable()
        dataAdapter.Fill(table)
        cmbBox.DataSource = table
        cmbBox.DisplayMember = "description"
        cmbBox.ValueMember = "ID"
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 17084109
dataAdapter and the return value from getDataAdapter() should be IDataAdapter. The Fill() method of that DataAdapter takes a DataSet as a parameter. It is that that the compiler was complaining about.
0
 

Author Comment

by:jackjohnson44
ID: 17084367
Then how do you fill a combo box with a dataset?

I was using a datatable before.

This works
        Dim table As New DataTable
        dataAdapter.Fill(table)
        cmbTestTesting.DataSource = table
        cmbTestTesting.DisplayMember = "description"
        cmbTestTesting.ValueMember = "ID"

This does not

        Dim table As New DataSet
        dataAdapter.Fill(table)
        cmbTestTesting.DataSource = table
        cmbTestTesting.DisplayMember = "description"
        cmbTestTesting.ValueMember = "ID"
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 17084391
The DataSet is a collcetion of DataTables, so you would use:

        Dim table As New DataSet
        dataAdapter.Fill(table)
        cmbTestTesting.DataSource = table.Tables(0)
        cmbTestTesting.DisplayMember = "description"
        cmbTestTesting.ValueMember = "ID"
0
 

Author Comment

by:jackjohnson44
ID: 17084443
Thanks a lot
0

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question