Solved

vb.net OleDbDataAdapter or SqlDataAdapter

Posted on 2006-07-11
12
1,465 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
  • 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
Technology Partners: 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

749 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