I am new to VB coding and attempting to query SQL Server database using MS Visual Studio Community 2017. I have installed SQL Server 2017 and Data Tools for Visual Studio. I am able to query the tables in my database using SSMS. Basically I have a textbox where I enter the First Name and a command button for searching the database. My code can be found below -
Imports System.Data.OleDb
Public Class Main
Private Sub Main_Load(sender As Object, e As EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'Physmed_Home_HealthDataSet.Employee' table. You can move, or remove it, as needed.
Me.EmployeeTableAdapter.Fill(Me.Physmed_Home_HealthDataSet.Employee)
End Sub
Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
Search_Record()
End Sub
Private Sub Search_Record()
Dim conn As New OleDbConnection
Dim cmd As New OleDbCommand
Dim da As New OleDbDataAdapter
Dim dt As New DataTable
Dim sSQL As String = String.Empty
Try
'get connection string declared in the Module1.vb and assing it to conn variable
conn.ConnectionString = "Provider=.NET Framework Data Provider for OLE DB;Data Source=(localdb)\mssqllocaldb;Initial Catalog='Physmed Home Health';Integrated Security=True"
conn.Open()
cmd.Connection = conn
cmd.CommandType = CommandType.Text
sSQL = "SELECT * FROM Employee"
sSQL = sSQL & " Where First_Name like '%" & Me.txtSearchFirstName.Text & "%'"
cmd.CommandText = sSQL
da.SelectCommand = cmd
da.Fill(dt)
If dt.Rows.Count = 0 Then
MsgBox("No record found!")
Else
MsgBox("Record found!")
'dt.Rows.ToString()
End If
'Catch ex As Exception
'MsgBox("Error found!")
Finally
conn.Close()
End Try
End Sub
End Class
I get the error below when I execute and enter a first name inside my text box and click on the command button to search the database.
System.InvalidOperationException
HResult=0x80131509
Message=The '.NET Framework Data Provider for OLE DB' 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, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at Physmed_Inc_Home_Health_Care.Main.Search_Record() in C:\Users\JBanaag\Documents\Visual Studio 2017\Projects\Physmed Inc Home Health Care\Physmed Inc Home Health Care\Main.vb:line 26
at Physmed_Inc_Home_Health_Care.Main.btnSearch_Click(Object sender, EventArgs e) in C:\Users\JBanaag\Documents\Visual Studio 2017\Projects\Physmed Inc Home Health Care\Physmed Inc Home Health Care\Main.vb:line 12
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.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.OnMessage(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(IntPtr 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 Physmed_Inc_Home_Health_Care.My.MyApplication.Main(String[] Args) in :line 81
Any help will be greatly appreciated
Thanks
User contributions licensed under CC BY-SA 3.0