Change JET Database connection to SQL Server Connection

Posted on 2011-05-08
Last Modified: 2012-05-11
Hello All;

Here is the JET Connection
Private Function GetConnectionString() As String
Return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(MDBFILE) & ";"
End Function

Open in new window

This is my SQL Server Connection

Dim cn As System.Data.SqlClient.SqlConnection
cn = New System.Data.SqlClient.SqlConnection("Data Source=SERVER\2005;Database=cs;User ID=username;Password=******;")

Open in new window

The original code can be found at the top of the page here

Question by:Wayne Barron
    LVL 83

    Expert Comment

    Not sure what the question is.

    One thing is that with Jet, you use OLEDB provider where as with Sql Server, you would use the SQLClient provider which is in System.Data namespace.
    LVL 30

    Accepted Solution

    Here is the code that I went with.
    I took it from another project, and ripped out all the insert/update code that was originally in this project, and made it work with my own.

    Dim objConnection As OleDbConnection = Nothing
    Dim objCmd As OleDbCommand = Nothing
    Dim strConnection As [String], strSQL As [String]
    Dim cn As System.Data.SqlClient.SqlConnection
    strConnection = "Provider=SQLOLEDB;Data Source=2005;Database=cs;User ID=username;Password=********;"
                    objConnection = New OleDbConnection(strConnection)
                    objConnection.ConnectionString = strConnection
                    strSQL = "INSERT INTO csFiles(Filename, FileSize, csid)VALUES(?,?,1)"
                    objCmd = New OleDbCommand(strSQL, objConnection)
                    objCmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("@Filename", strName))
                    objCmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("@FileSize", Buffer.Length))
                    'objCmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("@csid", 1)

    Open in new window

    Thanks codecruiser for taking a look.
    This is completed.

    LVL 30

    Author Closing Comment

    by:Wayne Barron
    Remembered code that I had used before, and implemented it into the project.
    Works like a charm.
    Code Supplied.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
    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…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    737 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

    19 Experts available now in Live!

    Get 1:1 Help Now