[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Creating a connection to MS Access with Visual Studio 2005

Posted on 2010-11-27
11
Medium Priority
?
708 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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 …
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

656 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