CODEWITHSUNDEEP
vb.net
luke.irvin21
luke.irvin21

Reputation: 75

Vb.Net Windows Form Search Access Database

Ok, hopefully I explain this correctly. I have had about 3 months professional experience working with Access databases, and none with Vb.net, but I am working on a project to get my degree. The goal of this project was to create a Windows Form for my parent's company, All Keyed Up Lock & Safe.

This form was going to link to an Access database with 4 tables that was a listing of some of the company's customers. The form would then allow you to search that database for a specific customer and then return all the information on that specific customer.

This would be useful for billing and also would let us view any special notes we have on the customer before leaving. The four tables are [McDonald's-Corporate Stores], [McDonald's-Independently Owned], [Sonic-Corporate Stores], [Sonic-Independently Owned].

Basically the problem I am having is with the search function. I cannot for the life of me figure out a way to get this to work. I have tried 4 or 5 different solutions and still nothing. I am using Visual Studio 2013. Here is my code I have at the moment, for the search button:

  Private Sub SearchButton_Click(sender As Object, e As EventArgs) Handles SearchButton.Click
        Dim Connection As New SqlClient.SqlConnectionStringBuilder
        Connection.DataSource = "file:///C:\Users\thelukester145\Documents\All%20Keyed%20Up\All%20Keyed%20Up,%20Lock%20&%20Safe,%20LLC.accdb"
        Dim objSQLConnection = New SqlClient.SqlConnection(Connection.ConnectionString)
        Dim cmd As New SqlCommand()
        Dim myTable As New DataTable()
        Dim SearchFor = SearchBox.Text
        If AddressButton.Checked Then
            cmd.Connection = objSQLConnection
            cmd.CommandText = "SELECT * FROM [McDonald's-Corporate Stores] WHERE [Address] LIKE '%" & SearchFor & "%'"
            Dim myAdapter As New SqlDataAdapter(cmd)
            myAdapter.Fill(myTable)
            DataGrid.DataGridView1.DataSource = myTable
        ElseIf NumberButton.Checked Then
            cmd.Connection = objSQLConnection
            cmd.CommandText = "SELECT * FROM [McDonald's-Corporate Stores] WHERE [McOpCo#] LIKE '%" & SearchFor & "%'"
            Dim myAdapter As New SqlDataAdapter(cmd)
            myAdapter.Fill(myTable)
            DataGrid.DataGridView1.DataSource = myTable
        End If
        DataGrid.Show()


    End Sub

As you can see I have just been trying to get this to work for just one table and can't even do that. I would prefer not to use the datagrid method to display the information but rather display it in textboxes that are positioned on the form.

Anyways any help would be MUCH appreciated and sorry for such a long question.

Upvotes: 2

Views: 3677

Answers (1)

Ankit
Ankit

Reputation: 63

Where are you opening the connection. Can you try the following code to establish a connection:

Dim cnnOLEDB As New OleDbConnection
Dim cmdOLEDB As New OleDbCommand
Dim strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Environment.CurrentDirectory & "\URDataBaseName.mdb"
cnnOLEDB.ConnectionString = strConnectionString
cnnOLEDB.Open()

' your code goes here

cnnOLEDB.Close()

Upvotes: 1

Related Questions