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

Creating a connection to MS Access with Visual Studio 2005

Hello and thank you for reading my question. I'm just getting started with .Net, and I need to relearn a great many things. The code below relates to a log on form. When I enter values for the user name and password and then press the button, I get an error with the details below. Would someone be kind enough to point me in the right direction?

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If Me.txtUserName.Text = "" Then
            MsgBox("Please Enter User Name")
            Me.txtUserName.Focus()
            Return
        End If
        If Me.txtPassWord.Text = "" Then
            MsgBox("Please Enter a Password")
            Me.txtPassWord.Focus()
            Return
        End If

        Dim strSQL As String
        Dim strConnection As String
        Dim objConnection As New OleDb.OleDbConnection()
        strConnection = "Provider=sqloledb;Data Source=C:\Users\Patrick\Desktop\Test\dbTest"
        objConnection.ConnectionString = strConnection
        objConnection.Open()

        Dim objCommand As New OleDb.OleDbCommand

        strSQL = "SELECT intUserID, strUserName, strPassword " _
                & "FROM tblUsers " _
                & "WHERE strUserName=@strUserName AND strPassword = @strPassWord;"

        With objCommand
            .Connection = objConnection
            .CommandType = CommandType.Text
            .CommandText = strSQL
            .Parameters.Add("@strUsername", OleDb.OleDbType.VarChar, 25).Value = Me.txtUserName.Text
            .Parameters.Add("@strPassword", OleDb.OleDbType.VarChar, 25).Value = Me.txtPassWord.Text
        End With

        'Once I fix the above, I'll do something here.
        objConnection.Close()

    End Sub


System.Data.OleDb.OleDbException was unhandled
  ErrorCode=-2147217843
  Message="Invalid authorization specification"
  Source="Microsoft OLE DB Provider for SQL Server"
  StackTrace:
       at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
       at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.OleDb.OleDbConnection.Open()
       at ESAMailer.frmLogOn.Button1_Click(Object sender, EventArgs e) in C:\Users\Patrick\Desktop\ESA\ESAMailer\ESAMailer\Form1.vb:line 27
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
       at ESAMailer.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
0
fritz_the_blank
Asked:
fritz_the_blank
  • 6
  • 4
2 Solutions
 
ElrondCTCommented:
Is your database Microsoft Access (a .mdb file) or SQL Server? Your question title says Access, but the code's connection string says "Provider=sqloledb". If you're trying to connect to Access, you use the Jet engine: "Provider=Microsoft.Jet.OLEDB.4.0".
0
 
fritz_the_blankAuthor Commented:
Thank you. I changed the connection string to use the correct provider. The same line of code (objConnection.cpen) is producing an error:


System.InvalidOperationException was unhandled
  Message="The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine."
  Source="System.Data"
  StackTrace:
       at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper)
       at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
       at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.OleDb.OleDbConnection.Open()
       at ESAMailer.frmLogOn.Button1_Click(Object sender, EventArgs e) in C:\Users\Patrick\Desktop\ESA\ESAMailer\ESAMailer\Form1.vb:line 27
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
       at ESAMailer.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
0
 
Carl TawnSystems and Integration DeveloperCommented:
Which version of Access are you using?
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
fritz_the_blankAuthor Commented:
I have Access 2010 installed, but I believe that the file was created in Access 2003.

My current connection string:


        strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Patrick\Desktop\ESA\dbESA.mdb"


Thanks again for reading the question.
0
 
Carl TawnSystems and Integration DeveloperCommented:
Well, the provider from Access 2007 onwards has changed. If you're not sure which version it is try changing your connection string to:
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Patrick\Desktop\ESA\dbESA.mdb;"

Open in new window


Failing that, if the provider is still missing, you may need to install MDAC in order to get the 4.0 drivers.
0
 
fritz_the_blankAuthor Commented:
Thank you once again. The new error that occurs on objConnection.Open()

I wonder if this would be different if I were on XP rather than on Windows 7.


System.InvalidOperationException was unhandled
  Message="The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine."
  Source="System.Data"
  StackTrace:
       at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper)
       at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
       at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.OleDb.OleDbConnection.Open()
       at ESAMailer.frmLogOn.Button1_Click(Object sender, EventArgs e) in C:\Users\Patrick\Desktop\ESA\ESAMailer\ESAMailer\Form1.vb:line 31
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
       at ESAMailer.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
0
 
Carl TawnSystems and Integration DeveloperCommented:
Possibly, since Windows 7 is 64-bit you will need to force your app to build in x86 mode, and may also need to configure IIS for 32-bit mode.

Maybe this thread will help:

    http://social.msdn.microsoft.com/Forums/en/adodotnetdataproviders/thread/45aa44dd-0e6f-42e5-a9d6-9be5c5c8fcd1
0
 
fritz_the_blankAuthor Commented:
Thank you Carl. I just found the same link, and I am indeed running a 64bit system. Unfortunately, the option to change the compiling to 32 bit is greyed out. I'll keep on poking at it.
0
 
fritz_the_blankAuthor Commented:
Carl--

My mistake--I was still running the code, and that is why I couldn't change the parameters. All works fine now. ElrondCT sent me in the right direction, but you provided the final answer. What is the best way to close the question? (I've been away from EE for quite a while now).
0
 
Carl TawnSystems and Integration DeveloperCommented:
Up to you really. But I think a split would be a fair conclusion.
0
 
fritz_the_blankAuthor Commented:
Fair enough. I am grateful for all of the help and don't wish to offend anyone. Here is what I have that finally works:

Public Class frmLogOn
    Public intUserID As Integer 'stores user id from tblUsers

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If Me.txtUserName.Text = "" Then
            MsgBox("Please Enter User Name")
            Me.txtUserName.Focus()
            Return
        End If
        If Me.txtPassWord.Text = "" Then
            MsgBox("Please Enter a Password")
            Me.txtPassWord.Focus()
            Return
        End If

        Dim strSQL As String
        Dim strConnection As String
        Dim objConnection As New OleDb.OleDbConnection()
        Dim objCommand As New OleDb.OleDbCommand
        Dim objDA As New OleDb.OleDbDataAdapter

        strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Patrick\Desktop\ESA\dbESAMailer.mdb"
        objConnection.ConnectionString = strConnection
        objConnection.Open()

        strSQL = "SELECT intUserID, strUserName, strPassword " _
                & "FROM tblUsers " _
                & "WHERE strUserName=@strUserName AND strPassword = @strPassWord;"

        With objCommand
            .Connection = objConnection
            .CommandType = CommandType.Text
            .CommandText = strSQL
            .Parameters.Add("@strUsername", OleDb.OleDbType.VarChar, 25).Value = Me.txtUserName.Text
            .Parameters.Add("@strPassword", OleDb.OleDbType.VarChar, 25).Value = Me.txtPassWord.Text
        End With
        'figure out how to get at the data here.
        objConnection.Close()

    End Sub

 
End Class
0
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

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

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