dcsimg
Results 1 to 5 of 5

Thread: VB.NET search MS Access and return results to gridview

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2018
    Posts
    9

    VB.NET search MS Access and return results to gridview

    Hi, I am looking for a solution to fill in a textbox in a textbox and click a button in turn to search my ms database, and return all the data in the rows that contain the text inputted in the textbox.

    There may be more than one row for each textbox entry I would require all rows to be returned.

    I have vb knowledge from excel, however this is my forst attempt at running with it from visual studio so please dont judge me.

    Thanks izzy

  2. #2

    Thread Starter
    New Member
    Join Date
    Feb 2018
    Posts
    9

    Re: VB.NET search MS Access and return results to gridview

    My current code...

    Imports System.Data.OleDb
    Public Class Form1

    Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\IzzyM\Desktop\Engie\KKS\KKS Maintenance Tool.accdb"
    Dim MyConn As OleDbConnection
    Dim da As OleDbDataAdapter
    Dim ds As DataSet
    Dim tables As DataTableCollection
    Dim source1 As New BindingSource
    Dim provider As String
    Dim dataFile As String
    Public myConnection As OleDbConnection = New OleDbConnection
    Public dr As OleDbDataReader

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim connstring As String
    provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="
    dataFile = "C:\Users\IzzyM\Desktop\Engie\KKS\KKS Maintenance Tool.accdb" ' Change it to your Access Database location
    connstring = provider & dataFile
    myConnection.ConnectionString = connstring
    End Sub
    Private Sub Button1_click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    myConnection.Open()
    Dim str As String
    Dim i As Integer
    str = "SELECT * FROM EWH INSTRUMENT KKS WHERE (Code = '" & TextBox1.Text & "')"
    Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
    dr = cmd.ExecuteReader
    While dr.Read()
    Me.DataGridView1.Rows(i).Cells("KKS Number") = dr("KKS Number")
    Me.DataGridView1.Rows(i).Cells("Type") = dr("Type")

    End While
    myConnection.Close()
    End Sub
    End Class

  3. #3

    Thread Starter
    New Member
    Join Date
    Feb 2018
    Posts
    9

    Re: VB.NET search MS Access and return results to gridview

    I'm now getting the following warning:

    System.Data.OleDb.OleDbException: 'Syntax error (missing operator) in query expression '(KKS Number = ('HLA20 CF001'))'.'

    Code:
    Imports System.Data.OleDb
    Public Class Form1
    
        Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\IzzyM\Desktop\Engie\KKS\KKS Maintenance Tool.accdb"
        Dim MyConn As OleDbConnection
        Dim da As OleDbDataAdapter
        Dim ds As DataSet
        Dim tables As DataTableCollection
        Dim source1 As New BindingSource
        Dim provider As String
        Dim dataFile As String
        Public myConnection As OleDbConnection = New OleDbConnection
        Public dr As OleDbDataReader
    
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Dim connstring As String
            provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="
            dataFile = "C:\Users\IzzyM\Desktop\Engie\KKS\KKS Maintenance Tool.accdb" ' Change it to your Access Database location
            connstring = provider & dataFile
            myConnection.ConnectionString = connstring
        End Sub
        Private Sub Button1_click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            myConnection.Open()
            Dim str As String
            Dim i As Integer
            str = "SELECT * FROM [EWH INSTRUMENT KKS] WHERE (KKS Number = ('" & TextBox1.Text & "'))"
            Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
            dr = cmd.ExecuteReader
            While dr.Read()
                Me.DataGridView1.Rows(i).Cells("KKS Number") = dr("KKS Number")
                Me.DataGridView1.Rows(i).Cells("Type") = dr("Type")
    
            End While
            myConnection.Close()
        End Sub
    End Class

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,109

    Re: VB.NET search MS Access and return results to gridview

    The problem there is that you have a space in your field name, which is not a good idea for a variety of reasons... if possible, change the field name so that there is no space (eg: KKSNumber or KKS_Number).

    For more information (including lists of Reserved words), see the article What names should I NOT use for tables/fields/views/stored procedures/...? from our Database Development FAQs/Tutorials (at the top of the Database Development forum)


    If you can't rename the field, a temporary solution that might work here (but not necessarily everywhere in your program) is to wrap the field name in square brackets, eg: WHERE ([KKS Number] =

  5. #5
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,760

    Re: VB.NET search MS Access and return results to gridview

    I've seen another thread of yours where you implement the solution suggested here, so your issue is obviously resolved. Please use the Thread Tools menu to mark your thread Resolved so everyone can see that you no longer need help without opening the thread and reading the each post.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width