locked
VB 2005/8 sql connection string RRS feed

  • Question

  •  


    Can you help me  please?

    I  have found this excellent article


           

    http://www.homeandlearn.co.uk/NET/nets12p6.ht

    ml

    which  explains programaticle  access to  'ms

     access' databases. I  would like to  do  the

    same with 'sql  server  express' databases
    my  access connectiom  string is:

         Dim con As New OleDb.OleDbConnection


            con.ConnectionString =

    "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source

    = C:\Documents and Settings\steve\My

    Documents\vb\2005\tutorials\home and learn

    database tutorial\AddressBook.mdb"

     

    I have tried:

         Dim con As New OleDb.OleDbConnection

            con.ConnectionString =

    "Provider=System.Data.SqlClient;Data

    Source=.\SQLEXPRESSsttachDbFilename=C:\Docume

    nts and Settings\steve\My

    Documents\vb\2005\examples\database\sql\nov

    28 sql.mdf;Integrated Security=True;Connect

    Timeout=30;User Instance=True"


    To connect to an  'sql  server  express'

    database But get the message;

    "The 'System.Data.SqlClient' provider is not

    registered on the local machine."

    when it reaches the "con.open"line.

    system.data is checked in my project

    properties, references settings tab.

    Is  this indeed a  connection string problem

    and  if  do  can anyone help me  please.

     

    Thanks Steve

    Wednesday, November 28, 2007 4:22 PM

Answers

  • Steve, SQL has its own library of objects.

     

    Access does not, and uses OLEDB connections.

     

    So you should be using classes from the system.data.sqlclient namespace instead of system.data.oledb namespace when connecting to SQL, as these classes are optimized for SQL Server.

     

    I don't know the exact specifics of your database, but your connection should look SOMETHING like this

    Code Block

     

    Dim MySQLConnection As New System.Data.SqlClient.SqlConnection("Server=.\SQLExpress;AttachDbFilename=C:\Documents and Settings\steve\My Documents\vb\2005\examples\database\sql\nov 28 sql.mdf;Database=DBNAMEHERE;Trusted_Connection=Yes;")

     

     

     

     

    Wednesday, November 28, 2007 4:44 PM
    Moderator
  • For examples of valid connection strings, go here:   www.connectionstrings.com

     

    Chris

     

    Thursday, November 29, 2007 6:42 PM

All replies

  • Steve, SQL has its own library of objects.

     

    Access does not, and uses OLEDB connections.

     

    So you should be using classes from the system.data.sqlclient namespace instead of system.data.oledb namespace when connecting to SQL, as these classes are optimized for SQL Server.

     

    I don't know the exact specifics of your database, but your connection should look SOMETHING like this

    Code Block

     

    Dim MySQLConnection As New System.Data.SqlClient.SqlConnection("Server=.\SQLExpress;AttachDbFilename=C:\Documents and Settings\steve\My Documents\vb\2005\examples\database\sql\nov 28 sql.mdf;Database=DBNAMEHERE;Trusted_Connection=Yes;")

     

     

     

     

    Wednesday, November 28, 2007 4:44 PM
    Moderator
  • For examples of valid connection strings, go here:   www.connectionstrings.com

     

    Chris

     

    Thursday, November 29, 2007 6:42 PM