none
Help Updating Listbox data from database query of Access .accdb data based upon Textbox value

    Question

  • Using Visual Studio 2017 IDE and Visual Basic as language. I have an external Access database (accdb format) that receives data from and feeds data to various controls on a Windows Form.

    Fundamentally, I want to run a query whenever the value in a text box is changed, and fill an associated listbox with the results of the query.  I have been trying everything I can find that describes how to interface with a database.

    The textbox (TboFind) will receive a string that causes a search for a match for TboFind.text the in Call_Sign field of the Access database table 'ECARS_db' in the external Access Database 'ECARS Mod Logger.accdb' database.  I have configured Visual Studio per article "Connect to data in an Access database (Windows Forms)." 

    I have tried many things, with the two following examples seeming to get closest.  The first example never seems to search the database and simply returns each letter inside the quotes of findQuery

    Dim findQuery = "SELECT ECARS_db.Call_Sign FROM ECARS_db WHERE (ECARS_db.Call_Sign =" & TboFind.Text & ")"
            LboFind_List2.Items.Clear()
            For Each Call_Sign In findQuery
                LboFind_List2.Items.Add(Call_Sign)
            Next

    I have tried, the following but cannot seem to find valid terms for the findQuery 'From' clause.

    Dim findQuery = From cs In ECARSModLoggerDataDataSetBindingSource
                            Where cs = TboFind.text
                            Select cs
            LboFind_List2.Items.Clear()
            For Each Call_Sign In findQuery
                LboFind_List2.Items.Add(Call_Sign)
            Next

    Any help to make this work would be much appreciated. 

    Bob





    • Moved by qing__ Thursday, March 16, 2017 2:40 AM related to VB
    Tuesday, March 14, 2017 11:56 PM

Answers

  • I downloaded your project, several times Visual Studio reported an issue with the database lock file and upon request allowed VS to delete the lock file.

    Modified version is here

    https://1drv.ms/u/s!AtGAgKKpqdWjiDEpG7WTf4q3sVVD

    Any ways I modified the xsd with 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by BobGn Sunday, March 19, 2017 9:51 PM
    Sunday, March 19, 2017 3:10 PM
    Moderator

All replies

  • Hi Bob,

    Welcome to MSDN forum.

    This forum is discussing Visual Studio WPF/SL Designer, Visual Studio Guidance Automation Toolkit, Developer Documentation and Help System, and Visual Studio Editor, your issue is more related to visual basic, I will move your thread to Visual Studio Languages , .NET Framework > Visual Basic for a professional support.

    Best regards,

    Joyce


    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 16, 2017 2:39 AM
  • Hi Bod,

    Based on your description, I put ListBox and TextBox control on Form. I do a simple sample that you can refer to.

    Code sample:

    Private Sub LoadStart()
            Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database\Database1.accdb; Persist Security Info=False;"
            Dim selectSql = "select ColumnName from table1 where ColumnName='" + textBox1.Text + "';"
            Using conn As New OleDbConnection(strConn)
                conn.Open()
                Using cmd As New OleDbCommand(selectSql, conn)
                    cmd.CommandType = CommandType.Text
                    Using reader As OleDbDataReader = cmd.ExecuteReader()
                        While reader.Read()
                            listBox1.Items.Add(reader("ColumnName").ToString())
                        End While
                    End Using
                End Using
                conn.Close()
            End Using
    
        End Sub
        Private Sub Cleardata()
            listBox1.Items.Clear()
        End Sub
        Private Sub textBox1_TextChanged(sender As Object, e As EventArgs)
            Cleardata()
            LoadStart()
        End Sub
    
    

    Best Regards,

    Cherry Bu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 16, 2017 5:50 AM
    Moderator
  • Hello,

    If you creates your objects e.g. BindingSource, TableAdapter etc via data wizards in Visual Studio then you would have a .xsd file like this.

    If so in the xsd file, then right click on the area with Fill

    Build the query e.g.

    Give it a name

    Set up the ListBox data source and data member in the ListBox property window

    Add code to have the search work, in this case pressing Button1. If there is text and it's located data is shown in the ListBox, if not found or no text the ListBox will be empty.

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Me.ProductsTableAdapter.FillByCategoryName(
                Me.NorthDataSet.Products,
                TextBox1.Text)
        End Sub
    End Class
    
    To do incremental search you need to use Like with the appropriate wild cards e.g. WHERE SomeFile = '%somevalue% for instance.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, March 16, 2017 9:52 AM
    Moderator
  • Ms Bu,

        Thank you for your response.  I adapted your suggested code by using my file and field names and tested it.  The Visual Studio 2017 compiler does not recognize the functions OleDbConnection, OleDbCommand, or OleDbDataReader methods.  I am beginning to think that I am missing something in my Visual Studio load that is critical to working with databases.  The image shown below shows your code as adapted, the Data Source listing, and the Compiler error codes.

        If anything about this suggests to you how to get Visual Studio to recognize those three functions, please let me know.

    Bob Goodwin

    bobgn1@cox.net


    Bob Goodwin

    Thursday, March 16, 2017 4:30 PM
  • Karen,

    Thanks for your response.

    By way of preface, I created the dataset by following the instructions of https://msdn.microsoft.com/en-us/library/ms171893.aspx. There was an anomaly in following these instructions as there was not step 11 option presented to me, and the result of step 10 presented me the following option.  I selected 'No' and proceeded.

    The following dataset was created.

     

    After completing the steps of your guidance, I got the following error message from Visual Studio 2017 during the typing of the code to be executed on the textbox.text change event.

    If you can see the problem from this, please let me know.  However, I'm beginning to wonder if I am missing some database critical module in my Visual Studio 2017 load.  If that thought suggests anything that I might check to ensure it is loaded, please advise.

    I have several other images to forward to you but I will provide by second reply to you since this reply crashed once already while uploading an image.

    Thanks for your assistance.

    Bob Goodwin

    bobgn1@cox.net


    Bob Goodwin

    Thursday, March 16, 2017 6:00 PM
  • This reply supplements the earlier reply.  The following extra images are provided in case they are indicative of the solution


    Bob Goodwin

    Thursday, March 16, 2017 6:09 PM
  • Hi Bob,

    What I can make if the image is the Fill method is incorrect, "Too many parameters" indicates you did not write the query correctly. Unfortunately I don't have the tools to provide a working example for a couple of hours but once home (in three hours) I can upload my code sample to Microsoft OneDrive where you can open the project and see what I did.

    In short the FillByMatchToTboFindText needs to have the DataSet as parmeter 1 and the parameter value from the Textbox in parameter 2.

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, March 16, 2017 6:49 PM
    Moderator
  • Hello,

    Here is a code sample

    https://1drv.ms/u/s!AtGAgKKpqdWjiC3JcMIpYM7xJJzz


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, March 16, 2017 10:44 PM
    Moderator
  • Bob, 

    First of all look to what columns you selected. A Listbox can maximal hold 2 columns. A display member and a value member. 

    Therefore don't do it rude but softly and select only the columns you need. 

    If you're testing then include first the connectionstring in your project by the way, if it runs you can always do it with that outside the project. 


    Success
    Cor

    Thursday, March 16, 2017 11:11 PM
  • Hi Bob,

    Based on your screenshots, I can see that OleDbConnection is not defined, have you added Imports System, Imports System.Data, Imports System.Data.OleDb in your project? If not, please add these and try it again.

    Best Regards,
    Cherry Bu

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 17, 2017 2:27 AM
    Moderator
  • Thanks Cherry,

        I have looked all through the Visual Studio 2017 for someplace to import those methods but could not find any such capability.  So, to continue my display of ignorance, are they simply configuration options, and if so, what is the statement that would invoke them.

    Thanks,

    Bob,

    bobgn1@cox.net


    Bob Goodwin

    Friday, March 17, 2017 2:11 PM
  • Thanks Karen.  I downloaded your Project and it runs nicely on my setup so there does not appear to be an underlying configuration problem on my end.  Last night I listed and compared the names and structure of your database, tables, and fields to the same in mine.  Then listed and compared the query statement, list box binding, list box code, table adapter name construction.  Outwardly, they appear to be constructed in the same way.  The biggest immediately observable difference is that your query statement includes the table name before each field name.  When my query was created, the VS 2017 defaulted to creating the query with just the field names.

    Today, I'll remove the current database connections, queries, etc and start again.  If the query is presented to me with just the field names, I'll try to force use of the table names . field names and see if that helps.

    In some cases, my database naming scheme allowed blanks in the names in lieu of using underscores, certainly in the Access project file name.  Could that cause a problem?


    Bob Goodwin

    Friday, March 17, 2017 2:24 PM
  • Karen,

    I have been playing with your project from the link you provided and comparing and contrasting with my project.  I cannot find a error on my side.  Your project runs nicely on my system, so I

    a.  created a new query on your project and linked that query to the list box --> Ran fine

    b.  deleted your .xsd file and recreated it, created query, and linked the query to the list box --> Had error

    c.  noticed that you had a copy of the NorthWind database in the .xsd file and I did not.  That appears to be the result of answering 'No' when I receive the following message while creating the .xsd per https://msdn.microsoft.com/en-us/library/ms171893.aspx The image is the choice presented to me whenever building the .xsd file.

    d.  repeated b. above but answered 'Yes' when the above popup question was asked.  Finished building query, linking to listbox, and writing code for text box text changed.  --> Worked fine.  I concluded that this had been my problem all along (failing to answer 'yes' to above local file question)

    e.  Completely deleted my Logger project and remade that part of it which is the same as your example project sent to me.  This time, I answered 'yes' to the above local file question. -->  Did not work.  Continued to get the 'Too many parameters error message shown just below.

    I am out of ideas.  I have uploaded a zipped copy of my project to DropBox.  It can be retrieved from

    https://www.dropbox.com/s/5o5gle9rzozlv78/Logger.zip?dl=0

    Please look this project over and see if you can find out where it errs and creates the problem shown above.   Thanks for any help you can continue to provide.

    Secondary question:  is there a comprehensive guide to using the various features of Visual Studio?  I'm big on reading manuals but all I've found are narrowly focused tutorials and not a Visual Studio Users Guide.

    Thanks again!

    Bob,

    bobgn1@cox.net


    Bob Goodwin

    Saturday, March 18, 2017 4:42 PM
  • This may be useful if you get time to look at my project.

    Compare databases as follows:

    Yours database file:  NorthWind.accdb   My database file:  Logger.accdb

    Your database tables:  Products and Categories   My database table:  ECARS_db

    Your field to be displayed in the listbox: ProductName    My field to be displayed in the listbox:  Call_Sign

    Your query:  an inner join between Products and Categories with a strange WHERE statement "Where (Categories.CategoryName=?)" 

    My query:  a single table where the Call_Sign value in ECARS_db equals the text entry in the text box - "Where Call_Sign=textbox1.text"


    Bob Goodwin

    Saturday, March 18, 2017 4:53 PM
  • Bob,

    Although the way Karen shows is nicer, you can do it for a simple listbox as well easier.. 

    Public Class Form1
        Private Sub loadf(sender As Object, e As EventArgs) Handles MyBase.Load
            Using da As New OleDb.OleDbDataAdapter("Select CustomerID, CompanyName from Customers", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Test\nwind.mdb")
                Dim dt As New DataTable
                da.Fill(dt)
                ListBox1.DataSource = dt
                ListBox1.ValueMember = "CustomerID"
                ListBox1.DisplayMember = "CompanyName"
            End Using
        End Sub
    End Class

    Know that Microsoft is not yet able to make it possible to use an Accdb file in a 64bits envioronment with .Net

    (You can use the 86bit provider but when you then want to change Office you have to undo that again, in my perception unusable). 


    Success
    Cor

    Saturday, March 18, 2017 5:19 PM
  • Thanks Cor,

          I am very interested in your comment about not being able to use an ACCDB file in a 64 bit environment with .net.  I am running Win7 64 bit and the Visual Studio is a 64 bit application.  When Karen sent me a demo application, which I imported into Visual Studio and it successfully ran on my 64 bit setup.  But when I tried to recreate her demo application from scratch, using her ACCDB database, it did not work. 


    Bob Goodwin

    Saturday, March 18, 2017 8:22 PM
  • Sorry, I hit a bad key combo and closed out the reply.  Are you saying that I should uninstall the Visual Studio and reinstall a 32 bit version running on my 64 bit computer?

    Bob Goodwin


    Bob Goodwin

    Saturday, March 18, 2017 8:24 PM
  • Sorry, I hit a bad key combo and closed out the reply.  Are you saying that I should uninstall the Visual Studio and reinstall a 32 bit version running on my 64 bit computer?

    Bob Goodwin


    Bob Goodwin

    No I only try to tell that ACCdb with .Net is a bad combination. Try another database, even the old Access files are better. 

    However, changing Visual Studio does not change anything . It is just the let say a little bit strange policy of the Office department of Microsoft.

    https://support.office.com/en-us/article/Choose-between-the-64-bit-or-32-bit-version-of-Office-2dee7807-8f95-4d0c-b5fe-6c6f49b8d261

    To translate it to non marketing language: "We the Office team did a lousy job, to gain back Microsoft on the place it had in past. We are simply protecting what we did in the way Ford did that you could only buy a T ford with the color black." 

    Don't let this make your judgement of Microsoft, there are other departments who do great jobs.

     


    Success
    Cor







    Saturday, March 18, 2017 8:55 PM
  • Check under project properties, compile tab, what do you have for the following.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, March 18, 2017 9:56 PM
    Moderator
  • The 'Prefer 32-bit is checked on my machine, including for the project you sent me but which is running on my machine.  Your project runs in this environment.  Was it originally compiled with this checked or not checked?

    Bob


    Bob Goodwin

    Saturday, March 18, 2017 10:36 PM
  • The 'Prefer 32-bit is checked on my machine, including for the project you sent me but which is running on my machine.  Your project runs in this environment.  Was it originally compiled with this checked or not checked?

    Bob


    Bob Goodwin

    I don't remember what it was. On an average day I might create, modify up to 20 projects in a day for either assisting here or writing MSDN code samples. If you open the project I provided the setting is what it was either as the default or I set it to that and would not change without you changing it.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, March 18, 2017 11:14 PM
    Moderator
  • Thanks.  Amazing.  It runs on your machine but would not compile on mine.  Your program, would compile on my computer and run on it.

    Like you, I don't remember what the setting was for the 'Prefer 32 bit' checkbox on any of my prior efforts, but it was probably checked.  As a test, I created a new Form project and then looked and found that it was checked for preferring 32 bit with no action on my part.

    I made a set of images of all my configuration screens for Application through Code Analysis and they can be viewed at https://www.dropbox.com/sh/g684lza9pjasldu/AABu3ngMMbJiQglkBElI7Hk5a?dl=0.  If you see anything of concern, please let me know.

    A Ms Cherry Bu also responded to my original problem posting and she provided some code that can be seen in her first posting on this topic.  I also tried her solution and it did not recognize some attributes.  Her response when I noted that to her was:

    "Based on your screenshots, I can see that OleDbConnection is not defined, have you added Imports System, Imports System.Data, Imports System.Data.OleDb in your project? If not, please add these and try it again."

    I was unable to figure out what 'Imports System', 'Imports System.data', and 'Imports System.Data.OleDb' were or how to 'add them to my project'.  She has not responded to a question on how to do that.  Does this experience add any useful information to what might be causing this?

    Further background:  Computer:  64 bit HP with Intel I7 quad processor;  O/S:  Win 7 Pro;   RAM: 8 GB;  Virus Protection:  Norton 360 but it was deactivated for the download of Visual Studio 2017;   Boot Environment:  I boot from an SSD that has Windows on it but I loaded Visual Studio to the E: drive, a rotating HD.  The installation routine allowed this so I did it to minimize consumption of the much smaller SSD space.

    I expect that I'll uninstall the Visual Studio and reinstall it after a reboot, to see if that resolves the issue.


    Bob Goodwin

    Sunday, March 19, 2017 2:29 PM
  • I downloaded your project, several times Visual Studio reported an issue with the database lock file and upon request allowed VS to delete the lock file.

    Modified version is here

    https://1drv.ms/u/s!AtGAgKKpqdWjiDEpG7WTf4q3sVVD

    Any ways I modified the xsd with 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by BobGn Sunday, March 19, 2017 9:51 PM
    Sunday, March 19, 2017 3:10 PM
    Moderator
  • Thank you very much!  Your patience and perseverance are remarkable.

    After all is said and done, the 'overloading' error was a syntax question. 

    You went ahead and did the next step for me, which was to implement the wildcards for a partial string match, so thanks again.  I am still confused on the WHERE syntax.  I don't understand how the '?' plays into this.  Clearly I'm trying to find matches for a string in the text box to entries in the Call_Sign field of the ECARS_db.  The SELECT and FROM clauses make perfect sense but the WHERE clause seems very incomplete.  In the Access database forms that were the original incarnation of this project, the Where was an explicit statement of Call_Sign Like *&TboFind&*.  In the Visual Studio 2017 syntax of (Call_Sign Like '* + ? + *') what does the '?' represent.

    That last question is mostly rhetorical.  I'll see if I can figure that out. 

    Thanks again.  I'll see if I can mark this a closed and completed.


    Bob Goodwin

    • Marked as answer by BobGn Sunday, March 19, 2017 8:21 PM
    • Unmarked as answer by BobGn Sunday, March 19, 2017 9:51 PM
    Sunday, March 19, 2017 8:20 PM
  • Thank you very much!  Your patience and perseverance are remarkable.

    After all is said and done, the 'overloading' error was a syntax question. 

    You went ahead and did the next step for me, which was to implement the wildcards for a partial string match, so thanks again.  I am still confused on the WHERE syntax.  I don't understand how the '?' plays into this.  Clearly I'm trying to find matches for a string in the text box to entries in the Call_Sign field of the ECARS_db.  The SELECT and FROM clauses make perfect sense but the WHERE clause seems very incomplete.  In the Access database forms that were the original incarnation of this project, the Where was an explicit statement of Call_Sign Like *&TboFind&*.  In the Visual Studio 2017 syntax of (Call_Sign Like '* + ? + *') what does the '?' represent.

    That last question is mostly rhetorical.  I'll see if I can figure that out. 

    Thanks again.  I'll see if I can mark this a closed and completed.


    Bob Goodwin

    First off in regards to marking your reply as the answer, I did the work yet you marked yourself as the answer, do you really feel comfortable with that?

    The syntax for the LIKE condition is OleDb (the actual data provider) way of doing the LIKE.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, March 19, 2017 8:39 PM
    Moderator
  • Nice catch.  Fixed (I think) the marking as to you providing the solution.  Sorry

    Bob Goodwin

    Sunday, March 19, 2017 9:52 PM