fritz_the_blank
asked on
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\De sktop\Test \dbTest"
objConnection.ConnectionSt ring = 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("@strUsern ame", OleDb.OleDbType.VarChar, 25).Value = Me.txtUserName.Text
.Parameters.Add("@strPassw ord", 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.OleDbExc eption was unhandled
ErrorCode=-2147217843
Message="Invalid authorization specification"
Source="Microsoft OLE DB Provider for SQL Server"
StackTrace:
at System.Data.OleDb.OleDbCon nectionInt ernal..cto r(OleDbCon nectionStr ing constr, OleDbConnection connection)
at System.Data.OleDb.OleDbCon nectionFac tory.Creat eConnectio n(DbConnec tionOption s options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.D bConnectio nFactory.C reateNonPo oledConnec tion(DbCon nection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.D bConnectio nFactory.G etConnecti on(DbConne ction owningConnection)
at System.Data.ProviderBase.D bConnectio nClosed.Op enConnecti on(DbConne ction outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbCon nection.Op en()
at ESAMailer.frmLogOn.Button1 _Click(Obj ect sender, EventArgs e) in C:\Users\Patrick\Desktop\E SA\ESAMail er\ESAMail er\Form1.v b:line 27
at System.Windows.Forms.Contr ol.OnClick (EventArgs e)
at System.Windows.Forms.Butto n.OnMouseU p(MouseEve ntArgs mevent)
at System.Windows.Forms.Contr ol.WmMouse Up(Message & m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Contr ol.WndProc (Message& m)
at System.Windows.Forms.Butto nBase.WndP roc(Messag e& m)
at System.Windows.Forms.Butto n.WndProc( Message& m)
at System.Windows.Forms.Contr ol.Control NativeWind ow.WndProc (Message& m)
at System.Windows.Forms.Nativ eWindow.De buggableCa llback(Int Ptr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.Unsaf eNativeMet hods.Dispa tchMessage W(MSG& msg)
at System.Windows.Forms.Appli cation.Com ponentMana ger.System .Windows.F orms.Unsaf eNativeMet hods.IMsoC omponentMa nager.FPus hMessageLo op(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Appli cation.Thr eadContext .RunMessag eLoopInner (Int32 reason, ApplicationContext context)
at System.Windows.Forms.Appli cation.Thr eadContext .RunMessag eLoop(Int3 2 reason, ApplicationContext context)
at Microsoft.VisualBasic.Appl icationSer vices.Wind owsFormsAp plicationB ase.OnRun( )
at Microsoft.VisualBasic.Appl icationSer vices.Wind owsFormsAp plicationB ase.DoAppl icationMod el()
at Microsoft.VisualBasic.Appl icationSer vices.Wind owsFormsAp plicationB ase.Run(St ring[] commandLine)
at ESAMailer.My.MyApplication .Main(Stri ng[] Args) in 17d14f5c-a337-4978-8281-53 493378c107 1.vb:line 81
at System.AppDomain._nExecute Assembly(A ssembly assembly, String[] args)
at Microsoft.VisualStudio.Hos tingProces s.HostProc .RunUsersA ssembly()
at System.Threading.Execution Context.Ru n(Executio nContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHel per.Thread Start()
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\De
objConnection.ConnectionSt
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("@strUsern
.Parameters.Add("@strPassw
End With
'Once I fix the above, I'll do something here.
objConnection.Close()
End Sub
System.Data.OleDb.OleDbExc
ErrorCode=-2147217843
Message="Invalid authorization specification"
Source="Microsoft OLE DB Provider for SQL Server"
StackTrace:
at System.Data.OleDb.OleDbCon
at System.Data.OleDb.OleDbCon
at System.Data.ProviderBase.D
at System.Data.ProviderBase.D
at System.Data.ProviderBase.D
at System.Data.OleDb.OleDbCon
at ESAMailer.frmLogOn.Button1
at System.Windows.Forms.Contr
at System.Windows.Forms.Butto
at System.Windows.Forms.Contr
at System.Windows.Forms.Contr
at System.Windows.Forms.Butto
at System.Windows.Forms.Butto
at System.Windows.Forms.Contr
at System.Windows.Forms.Nativ
at System.Windows.Forms.Unsaf
at System.Windows.Forms.Appli
at System.Windows.Forms.Appli
at System.Windows.Forms.Appli
at Microsoft.VisualBasic.Appl
at Microsoft.VisualBasic.Appl
at Microsoft.VisualBasic.Appl
at ESAMailer.My.MyApplication
at System.AppDomain._nExecute
at Microsoft.VisualStudio.Hos
at System.Threading.Execution
at System.Threading.ThreadHel
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Which version of Access are you using?
ASKER
I have Access 2010 installed, but I believe that the file was created in Access 2003.
My current connection string:
strConnection = "Provider=Microsoft.Jet.OL EDB.4.0;Da ta Source=C:\Users\Patrick\De sktop\ESA\ dbESA.mdb"
Thanks again for reading the question.
My current connection string:
strConnection = "Provider=Microsoft.Jet.OL
Thanks again for reading the question.
Well, the provider from Access 2007 onwards has changed. If you're not sure which version it is try changing your connection string to:
Failing that, if the provider is still missing, you may need to install MDAC in order to get the 4.0 drivers.
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Patrick\Desktop\ESA\dbESA.mdb;"
Failing that, if the provider is still missing, you may need to install MDAC in order to get the 4.0 drivers.
ASKER
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.InvalidOperationExc eption 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.OleDbSer vicesWrapp er.GetData Source(Ole DbConnecti onString constr, DataSourceWrapper& datasrcWrapper)
at System.Data.OleDb.OleDbCon nectionInt ernal..cto r(OleDbCon nectionStr ing constr, OleDbConnection connection)
at System.Data.OleDb.OleDbCon nectionFac tory.Creat eConnectio n(DbConnec tionOption s options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.D bConnectio nFactory.C reateNonPo oledConnec tion(DbCon nection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.D bConnectio nFactory.G etConnecti on(DbConne ction owningConnection)
at System.Data.ProviderBase.D bConnectio nClosed.Op enConnecti on(DbConne ction outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbCon nection.Op en()
at ESAMailer.frmLogOn.Button1 _Click(Obj ect sender, EventArgs e) in C:\Users\Patrick\Desktop\E SA\ESAMail er\ESAMail er\Form1.v b:line 31
at System.Windows.Forms.Contr ol.OnClick (EventArgs e)
at System.Windows.Forms.Butto n.OnMouseU p(MouseEve ntArgs mevent)
at System.Windows.Forms.Contr ol.WmMouse Up(Message & m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Contr ol.WndProc (Message& m)
at System.Windows.Forms.Butto nBase.WndP roc(Messag e& m)
at System.Windows.Forms.Butto n.WndProc( Message& m)
at System.Windows.Forms.Contr ol.Control NativeWind ow.WndProc (Message& m)
at System.Windows.Forms.Nativ eWindow.De buggableCa llback(Int Ptr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.Unsaf eNativeMet hods.Dispa tchMessage W(MSG& msg)
at System.Windows.Forms.Appli cation.Com ponentMana ger.System .Windows.F orms.Unsaf eNativeMet hods.IMsoC omponentMa nager.FPus hMessageLo op(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Appli cation.Thr eadContext .RunMessag eLoopInner (Int32 reason, ApplicationContext context)
at System.Windows.Forms.Appli cation.Thr eadContext .RunMessag eLoop(Int3 2 reason, ApplicationContext context)
at Microsoft.VisualBasic.Appl icationSer vices.Wind owsFormsAp plicationB ase.OnRun( )
at Microsoft.VisualBasic.Appl icationSer vices.Wind owsFormsAp plicationB ase.DoAppl icationMod el()
at Microsoft.VisualBasic.Appl icationSer vices.Wind owsFormsAp plicationB ase.Run(St ring[] commandLine)
at ESAMailer.My.MyApplication .Main(Stri ng[] Args) in 17d14f5c-a337-4978-8281-53 493378c107 1.vb:line 81
at System.AppDomain._nExecute Assembly(A ssembly assembly, String[] args)
at Microsoft.VisualStudio.Hos tingProces s.HostProc .RunUsersA ssembly()
at System.Threading.Execution Context.Ru n(Executio nContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHel per.Thread Start()
I wonder if this would be different if I were on XP rather than on Windows 7.
System.InvalidOperationExc
Message="The 'Microsoft.ACE.OLEDB.12.0'
Source="System.Data"
StackTrace:
at System.Data.OleDb.OleDbSer
at System.Data.OleDb.OleDbCon
at System.Data.OleDb.OleDbCon
at System.Data.ProviderBase.D
at System.Data.ProviderBase.D
at System.Data.ProviderBase.D
at System.Data.OleDb.OleDbCon
at ESAMailer.frmLogOn.Button1
at System.Windows.Forms.Contr
at System.Windows.Forms.Butto
at System.Windows.Forms.Contr
at System.Windows.Forms.Contr
at System.Windows.Forms.Butto
at System.Windows.Forms.Butto
at System.Windows.Forms.Contr
at System.Windows.Forms.Nativ
at System.Windows.Forms.Unsaf
at System.Windows.Forms.Appli
at System.Windows.Forms.Appli
at System.Windows.Forms.Appli
at Microsoft.VisualBasic.Appl
at Microsoft.VisualBasic.Appl
at Microsoft.VisualBasic.Appl
at ESAMailer.My.MyApplication
at System.AppDomain._nExecute
at Microsoft.VisualStudio.Hos
at System.Threading.Execution
at System.Threading.ThreadHel
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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).
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).
Up to you really. But I think a split would be a fair conclusion.
ASKER
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.OL EDB.4.0;Da ta Source=C:\Users\Patrick\De sktop\ESA\ dbESAMaile r.mdb"
objConnection.ConnectionSt ring = 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("@strUsern ame", OleDb.OleDbType.VarChar, 25).Value = Me.txtUserName.Text
.Parameters.Add("@strPassw ord", 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
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.OL
objConnection.ConnectionSt
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("@strUsern
.Parameters.Add("@strPassw
End With
'figure out how to get at the data here.
objConnection.Close()
End Sub
End Class
ASKER
System.InvalidOperationExc
Message="The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine."
Source="System.Data"
StackTrace:
at System.Data.OleDb.OleDbSer
at System.Data.OleDb.OleDbCon
at System.Data.OleDb.OleDbCon
at System.Data.ProviderBase.D
at System.Data.ProviderBase.D
at System.Data.ProviderBase.D
at System.Data.OleDb.OleDbCon
at ESAMailer.frmLogOn.Button1
at System.Windows.Forms.Contr
at System.Windows.Forms.Butto
at System.Windows.Forms.Contr
at System.Windows.Forms.Contr
at System.Windows.Forms.Butto
at System.Windows.Forms.Butto
at System.Windows.Forms.Contr
at System.Windows.Forms.Nativ
at System.Windows.Forms.Unsaf
at System.Windows.Forms.Appli
at System.Windows.Forms.Appli
at System.Windows.Forms.Appli
at Microsoft.VisualBasic.Appl
at Microsoft.VisualBasic.Appl
at Microsoft.VisualBasic.Appl
at ESAMailer.My.MyApplication
at System.AppDomain._nExecute
at Microsoft.VisualStudio.Hos
at System.Threading.Execution
at System.Threading.ThreadHel