ADODB to OLEDB Conversion in VB.NET

Hi, i am sure this is a fairly simple question, but I simply do not know the keywords / terminology that I need. I currently have an application, that is using ADODB connections to an ACCESS Database. I am now going to connect to a SQL database and want to use OLEDB.

Therefore the code I was previously using would look something like -----

        Dim conDB As New ADODB.Connection
        Dim cmdDB As New ADODB.Command
        Dim rsDB As New ADODB.Recordset

        conDB.ConnectionString = Access =Microsoft.Jet.OLEDB.4.0;Data Source = \Datastore\DB1.mdb
        conDB.Open()
        cmdDB.ActiveConnection = conDB

        cmdDB.CommandText = "SELECT * FROM Appointment_Templates Order By Name"
        rsDB = cmdDB.Execute

        conDB.Close()

- could somebody please show me how to convert this into OLEDB, along with the new connectionstring and any references that I need to add.

Many thanks
    Steve

LVL 1
swtileyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

entrapnetCommented:
if you planning to go for SQL server... you should use SQLconnection, SQLdataadapter

example
Public Function SelectSqlSrvRows(dataSet As DataSet, connection As String, query As String) As DataSet
    Dim conn As New SqlConnection(connection)
    Dim adapter As New SqlDataAdapter()
    adapter.SelectCommand = new SqlCommand(query, conn)
    adapter.Fill(dataset)
    Return dataset
End Function

entrapnetCommented:
as for OLEDB... it is identical as how it works...

SQLconnection = OLEDBconnection
SQLdataadapter = OLEDBdataadapter

below would be the msdn version of the tutorial

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqldataadapterclasstopic.asp
swtileyAuthor Commented:
Thanks for the quick response 'entrapnet' - I will use the SQLconnection then, but how would I run the sql statement, I am currently using

       cmdDB.ActiveConnection = conDB
         cmdDB.CommandText = "SELECT * FROM Appointment_Templates Order By Name"
        rsDB = cmdDB.Execute

thanks
   Steve
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

SanclerCommented:
Steve

A good way of finding your way around SQL connection strings and CommandText for SQLDataAdapters is to use the Data tab on the Toolbox.  If you click on that, and drag an SQLDataAdapter onto a form, wizard/s will guide you through the configuration.  You can then switch to the code window for that form, open up the "Windows Form Designer generated code" Region, and inspect the strings built by the wizard.  You don't have to use these if you don't want.  But it gives some insight as what is expected.

Roger
entrapnetCommented:
drag a dataset into your asp.net form

drag a button into your asp.net form

copy and paste that function into your form

double click on your button

type SelectSqlSrvRows(dataset1 <-- the dataset name that u just drag in, "sql connection string", "SELECT * FROM Appointment_Templates Order By Name")

done...

p/s: Sancler ways works too.
entrapnetCommented:
http://www.vbdotnetheaven.com/Code/May2004/DataGridSampMCB1.asp

sample for oledb connection to access database
RJeyaPrakashCommented:
using Sql Connection and sql command for SQL server makes you achieve great compatibility
try the following

dim SqlConnection1 as SqlConnection
dim sqlcommand as sqlcommand


 Me.SqlConnection1.ConnectionString = "<yr connection string>"

sqlcommand .commandtext =  "SELECT * FROM Appointment_Templates Order By Name"
sqlcommand.connection = sqlconnection1
sqlconnection1.open()

step1: if you going to access the data as readonly, use reader

Dim dr As SqlDataReader
dr = sqlcommand.executereader()

while dr.read()
<your code to access the values>
<your code to access the values>
<your code to access the values>
end while

After reading e'thing, close the reader

dr.Close()

rgds
jp

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RJeyaPrakashCommented:
use New for creating the command and connection

dim SqlConnection1 as new SqlConnection
dim sqlcommand as new sqlcommand
Bob LearnedCommented:
Was this question answered fully?

Bob
swtileyAuthor Commented:
Thanks guys i think i have been able to piece together a solution from a couple of the answers.

Cheers
   Steve
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.