Solved

vb.net OleDbDataAdapter or SqlDataAdapter

Posted on 2006-07-11
12
1,440 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
 

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now