0

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.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Dave
  • 41
  • 2
  • 9

3 Answers3

4

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.

Steven Doggart
  • 43,358
  • 8
  • 68
  • 105
  • Dim conString As String = "Data Source=176.111.555.24;Initial Catalog=MyDatabase;User ID=Username;Password=Password" – Dave Jun 19 '15 at 14:55
  • I want to use something like this: Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("conStr").ConnectionString) – Dave Jun 19 '15 at 14:55
  • Probably best to ask that as another separate question, but you'll need to explain what, specifically, about doing it that way isn't working for you. On the face of it, that doesn't look wrong. – Steven Doggart Jun 19 '15 at 14:57
2

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"
Donal
  • 31,121
  • 10
  • 63
  • 72
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
Community
  • 1
  • 1
The_Black_Smurf
  • 5,178
  • 14
  • 52
  • 78
  • Instead of declaring these globally: Dim Con As SqlConnection Dim cmd As New SqlCommand Can I put them into my subroutine? – Dave Jun 19 '15 at 14:52
  • 1
    I didn't change it in the example but yes, you should always try to avoid using global instance if you don't have to. – The_Black_Smurf Jun 19 '15 at 14:59
  • 1
    It's also a good practice to wrap the sqlConnection / sqlCommand inside a [using statement](https://msdn.microsoft.com/en-us/library/htd05whh.aspx) to make you they are disposed correctly. – The_Black_Smurf Jun 19 '15 at 15:01
  • I updated the code to show you how to work with the using statement (and without a global instance). – The_Black_Smurf Jun 19 '15 at 15:11
  • Good, but slightly better would be `MsgBox(ex.ToString())`. – John Saunders Jun 19 '15 at 15:44