PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
Searching Access DB-VBForums
Results 1 to 6 of 6

Thread: Searching Access DB

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    3

    Searching Access DB

    hi there Sorry if the question has been asked before but i am banging my head against a wall at the moment.
    i have play with programing before but not for a long time.

    im doing a dB from my garage that i own, you know customer ,car ,year etc

    i have set up a DB in access with 2 tables one has customer info the other has car info like reg,work done price etc.
    that's the history lesson know here is the question.

    i am able to enter information into both tables of the DB but what i would like to know is how can i
    say do a search on a Registration of a car and get up all work done on the car thought VB

    here is the code i use to input the data for the car info
    VB.Net Code:
    1. provider = "provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\AUTOFIX DB.accdb"
    2.  
    3.         myConnection.ConnectionString = provider
    4.  
    5.         myConnection.Open()
    6.  
    7.         Dim str As String
    8.  
    9.         str = "insert into Car([Reg],[Work],[Date],[Next],[Price],[Cost]) values(?,?,?,?,?,?)"  
    10.  
    11.          'reg-registration
    12.          'work-work done
    13.          'date- next MOT (government car check)
    14.          ' cost and price -----
    15.  
    16.         Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
    17.  
    18.         cmd.Parameters.Add(New OleDbParameter("[Reg]", CType(TextBox1.Text, String)))
    19.         cmd.Parameters.Add(New OleDbParameter("[Work]", CType(TextBox2.Text, String)))
    20.         cmd.Parameters.Add(New OleDbParameter("[Date]", CType(TextBox3.Text, String)))
    21.         cmd.Parameters.Add(New OleDbParameter("[Next]", CType(TextBox6.Text, String)))
    22.         cmd.Parameters.Add(New OleDbParameter("[Cost]", CType(TextBox5.Text, String)))
    23.         cmd.Parameters.Add(New OleDbParameter("[Price]", CType(TextBox4.Text, String)))
    24.  
    25.         Try
    26.             cmd.ExecuteNonQuery()
    27.             cmd.Dispose()
    28.             myConnection.Close()
    29.  
    30.         Catch ex As Exception
    31.  
    32.             MsgBox(ex.Message)
    33.  
    34.         End Try
    35.  
    36.         myConnection.Close()
    hope someone can help
    Last edited by FunkyDexter; May 31st, 2018 at 09:22 AM.

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

    Re: Searching Access DB

    First things first, your database design is not really ideal. You should probably have at least three tables: Customer, Vehicle and Work. The primary key for Customer would be CustomerId and Vehicle would have a CustomerID column as a foreign key. Likewise, VehicleId would be the primary key for Vehicle and Work would have VehicleId column as a foreign key. You'd then get the VehicleId for the Vehicle record you were interested in and then query the Work table with that VehicleId in the WHERE clause.

    As your database design stands, you'd query the Car table with the desired Reg value in the WHERE clause. If you don't know how to write a query with a WHERE clause, I'd suggest that you follow the SQL tutorial link in my signature below and learn the basics.
    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

  3. #3
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,926

    Re: Searching Access DB

    Welcome to the forum.

    I added highlighting to your post to make the code more readable. If you want to do this yourself you can just press the button marked VB in the formatting bar above the post window. When it asks you for a style enter "VB".

    As JM says, your design isn't really right. I'd suggest you take a look at that before you start worrying about querying it or you'll find yourself going back and forward a couple of times. JM has talked about primary and foreign keys. Do you know what they are and how to use them?
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

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

    Re: Searching Access DB

    Quote Originally Posted by FunkyDexter View Post
    When it asks you for a style enter "VB".
    Personally, I would suggest using VB.NET for the option rather than VB. In my opinion, the syntax highlighting is better.
    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

  5. #5
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,926

    Re: Searching Access DB

    Actually, yeah. The colours make more sense.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  6. #6

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    3

    Re: Searching Access DB

    thanks every body i think i will go down the SQL root.


    will do somw searching and get back soon

    thanks again everybody

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