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
Solved

vb.net OleDbDataAdapter or SqlDataAdapter

Posted on 2006-07-11
12
1,458 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

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…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

856 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