Solved

vb.net OleDbDataAdapter or SqlDataAdapter

Posted on 2006-07-11
12
1,454 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

776 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