Visual Basic 2010 and Microsoft Access


  • Hello everyone, I am new to programming databases and I am having some trouble.


    I am writing a program that stores information about users such as "Username", "First Name", "Last Name", "Points", and "Last Visited".  I created the database with the appropriate columns in access.  I then imported this database into visual basic and I now have a form that can save, delete, add, modify, and navigate through the users with arrows.  Note that this form has text boxes, labels, option buttons, etc. and is NOT a table view.  

    Here is what I need help with:

    First, I would like to add a text box search field to this form (because navigating through several thousand users with arrows isn't going to work).  When the search button is pressed, I want the username to be looked for.  If found, the form (and each textbox, option button, etc.) is updated with that user.  I can then modify the user's information and press save.

    Second, how do I access and change information in a database with only code? For example, if I periodically want to check every user in the database to see if they have over "100 points" and if they do, I subtract 10 points from that user.



    Tuesday, June 15, 2010 4:32 AM


All replies

  • Doogh,

    Make it the first time easy for you.

    Start with a new VB project, you can always use your old project later, but you did it already so it takes a few minutes to redo the old one.

    Go to the Data tab -> Add new DataBase ->  DataBase -> DataSet -> new connection and select your database.

    Select what you need from that and select the columns. When you are ready do Configurate, all your update statements are made (check if you have a primary key otherwise it goes wrong)

    Now select from the toolbox a BindingNavigator and a BindingSource

    In the properties of the Bindingsource at the property DataSource you select the datasource you have created with its table.

    In the properties of the BindingNavigator you set the Bindingsource.

    Now you drag again the textboxes on your form and set in DataBinding (that is in top of the properties) the Text column to the correct BindingSource field.

    F5 and run.

    What you have done is created all kind of code, nothing is behind the scene. You can see that in when set in top of solution explorer the button show all files to show all files.

    The created code is now in YourProgramName.Designer.vb

     Be aware this is typed in new text, not a copy of something.

    So I can have made typos and other mistakes (but those you should see while busy) :-)

    Tuesday, June 15, 2010 6:16 AM
  • Thank you for the response, however, I am not sure this answers my question...

    I already have working fields with an add, delete, navigate buttons, save, etc.

    I want to figure out how to create a search box.  When the username is found, all the controls are updated with that user's information.  I suppose I am asking, how do I loop through every contact, check to see if the username match, and then, if it does, update all the controls with that information.

    For the second part, I want to check to see who has over 100 points, and if they do, subtract 10.  Here I need to know how to loop over every contact, check to see if they have at least 100 points, and if they do, how do I subtract 10?

    If you are claiming this can be done with only the user interface (and not code), please explain how?

    Tuesday, June 15, 2010 3:58 PM
  • I could really use some help.  I can't find videos or tutorials on this anywhere.
    Tuesday, June 15, 2010 5:43 PM
  • So, people have told me that I need an OleDB connection.  Is this correct?  I have tried the following:

    Dim cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Douglas\Documents\Visual Studio 2008\Projects\GameBoxRPG\GameBoxRPG\Contacts.accdb;Persist Security Info=False")
        Dim cmd As OleDbCommand = New OleDbCommand("Select * from Contacts where Username= " & txtSearch.Text & " ", cn)
        Dim rdr As OleDbDataReader
        rdr = cmd.ExecuteReader(CommandBehavior.SingleRow)
        Dim NoAcc As String
        If rdr.HasRows Then
          NoAcc = rdr("Username")
          If (txtSearch.Text = NoAcc) Then UsernameTextBox.Text = rdr("Username")
          If (txtSearch.Text = NoAcc) Then First_NameTextBox.Text = rdr("First Name")
          If (txtSearch.Text = NoAcc) Then First_NameTextBox.Text = rdr("Last Name")
        End If
      End Sub
    And I get an error trying to utilize "execute reader".  In addition, someone told me that while I may be able to update the fields, it will not update the active contact that is being viewed.  Is this correct?  I really need help.

    Tuesday, June 15, 2010 7:21 PM
  • Tuesday, June 15, 2010 8:27 PM
  • That was incredibly helpful!  My database is now mostly functional!

    I have a field named "First Name" and a field named "Last Name".  However, when I use filter, these fields do not filter properly.  I believe this is an issue with the space between the words.  I tried "FirstName", "First_Name" and "First Name" as parameters, none of which worked.  However, my "Username" filter worked appropriately.

    Now, I need help searching for people with over 100 points and then decreasing their current points by 10.

    Tuesday, June 15, 2010 10:33 PM
  • The way you use now is the way as was used when every computer had not more then 640Kb memory.

    But it can be done like that, however probably are you better of with a full populated DataTable and a rowfilter over that.

    But then you have to change some things, which I am definitaly sure of seeing this code, you will do anyway.

    It would take me less time to try what I wrote then the time it took me to write, but that seems even to much work for you.

    Wednesday, June 16, 2010 5:17 AM