?
Solved

Creating a connection to MS Access with Visual Studio 2005

Posted on 2010-11-27
11
Medium Priority
?
715 Views
Last Modified: 2013-11-26
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
Comment
Question by:fritz_the_blank
  • 6
  • 4
11 Comments
 
LVL 20

Accepted Solution

by:
ElrondCT earned 1000 total points
ID: 34223904
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
 
LVL 46

Author Comment

by:fritz_the_blank
ID: 34224005
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
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 34225337
Which version of Access are you using?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 46

Author Comment

by:fritz_the_blank
ID: 34226063
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
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 34226202
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
 
LVL 46

Author Comment

by:fritz_the_blank
ID: 34226213
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
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 1000 total points
ID: 34226221
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
 
LVL 46

Author Comment

by:fritz_the_blank
ID: 34226262
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
 
LVL 46

Author Comment

by:fritz_the_blank
ID: 34226289
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
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 34226302
Up to you really. But I think a split would be a fair conclusion.
0
 
LVL 46

Author Comment

by:fritz_the_blank
ID: 34226347
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Screencast - Getting to Know the Pipeline
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question