• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 187
  • Last Modified:

Change JET Database connection to SQL Server Connection

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

Wayne Barron
Wayne Barron
  • 2
1 Solution
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.
Wayne BarronAuthor, Web DeveloperAuthor Commented:
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.

Wayne BarronAuthor, Web DeveloperAuthor Commented:
Remembered code that I had used before, and implemented it into the project.
Works like a charm.
Code Supplied.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now