0

i have two tables in same database:

  • table1 contains order_no(primery key) and cust_name
  • table2 contains order_no(foreign key) (which also have duplicates entry per food item ordered in single order), table_no, items, date, cust_name and so on...

so my question is how can i show the records in listbox and datagrid or in textbox using table1's primery key order_no so i can get all the records which have assigned the foreign key with same order number in table2

i am using visual basic 6.0

shahaf
  • 4,750
  • 2
  • 29
  • 32
Rushabh Patil
  • 39
  • 7
  • Sounds like an `INNER JOIN`, you can find lots of examples on the internet. The `INNER JOIN` keyword selects records that have matching values in both tables (`order_no`). – Étienne Laneville Oct 04 '19 at 16:46

1 Answers1

3

Try something like this

Private Sub mLoadData(lOrder_no As Long)
    ' add a reference to Microsoft ActiveX Data Objects 2.8 Library
    ' add a MSHFLXGD (Microsoft Hierarchical FlexGrid) control named grData to form
    Dim rc As ADODB.Recordset
    Dim db As New ADODB.Connection
    Dim sConnString As String, sSQL As String

    'sConnString = create a connection string according to your database - https://www.connectionstrings.com/
    db.Open sConnString

    sSQL = "SELECT * FROM table2 WHERE order_no =" & lOrder_no
    Set rc = db.Execute(sSQL)
    Set grData.DataSource = rc

End Sub
Smith
  • 710
  • 1
  • 7
  • 11
  • This is only retriving data from table2...i want access data from table1 using primery key – Rushabh Patil Oct 04 '19 at 15:02
  • 1
    He's given you all the VB6 code you need, you can go a little further and modify the SELECT statement. – Étienne Laneville Oct 04 '19 at 16:37
  • sSQL = "SELECT * FROM " & table_name & " WHERE order_no =" & lOrder_no – Jimmy Smith Oct 06 '19 at 13:10
  • And I recommend a parameterized query if the user can input the number there (although it doensn't look this way - [see this](https://stackoverflow.com/questions/23014834/how-to-prevent-sql-injection-on-following-code-in-visual-basic-2012)). – Jimmy Smith Oct 06 '19 at 13:13