'How to get rid of this error: An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'

The purpose of my application is when a user types in a customer, their username and a message into the respective textboxes, it's supposed to be recorded into a table on SQL Server after the user presses the send button.

Here's my code: 

Public Class Form1

Dim Con As OleDbConnection
Dim cmd As New OleDbCommand

Dim conString As String = "Data Source=176.111.555.24;Initial Catalog=MyDatabase;User ID=Username;Password=Password"

Public Sub MessageSent()

    Try

        Dim con As New OleDbConnection
        con.ConnectionString = conString
        con.Open()

        cmd.Connection = con

        cmd.CommandText = "insert into tblmessage(Customer, UserName, Message) values('" & txtCustomer.Text & "', '" & txtUserName.Text & "', '" & rtfMessage.Text & "')"


        cmd.ExecuteNonQuery()

        con.Close()
        MsgBox("Message Sent")

        con.Close()
        con.Dispose()

    Catch ex As Exception
        MsgBox(ex.Message)

    End Try

End Sub

   Private Sub btnSend_Click(sender As Object, e As EventArgs) Handles btnSend.Click
        MessageSent()
    End Sub

I'm getting the following error:

An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'.

What is it that I'm doing wrong and how can this be resolved.

sql-servervb.net


Solution 1:[1]

It looks like you are trying to give a SQL Server connection string to an OleDbConnection. You need to either use a SqlConnection to open with that connection string or else you need to create a valid OLEDB connection string to point to that database. For reference, see this site.

Solution 2:[2]

When you want to connect to an MS SQL database you should use SQL connection instead of OleDbConnection.

The same is true for sqlCommand over the OldDbommand.

This SO question as some answers explaining the difference between Sql and OldDb Client.

Public Sub MessageSent()

  Try
    Using con As SqlConnection = New SqlConnection


      con.ConnectionString = "Data Source=176.111.555.24;Initial Catalog=MyDatabase;User ID=Username;Password=Password"
      con.Open()

      Using cmd As New SqlCommand
        cmd.Connection = con
        cmd.CommandText = "insert into tblmessage(Customer, UserName, Message) values('" & txtCustomer.Text & "', '" & txtUserName.Text & "', '" & rtfMessage.Text & "')"
        cmd.ExecuteNonQuery()
      End Using

      MsgBox("Message Sent")
      con.Close()           


    End Using


  Catch ex As Exception
    MsgBox(ex.Message)
  End Try

End Sub

Solution 3:[3]

I think you are just missing this bit: Provider=SQLOLEDB;

For example:

Dim conString As String = "Provider=SQLOLEDB;Data Source=176.111.555.24;Initial Catalog=MyDatabase;User ID=Username;Password=Password"

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Steven Doggart
Solution 2 Community
Solution 3

Related Questions