VB, using database... Inner join help!

[Kielo]

Right now I have a listbox,

this listbox displays surnames... when clicked it displays their address.

I have a 'confirm' button... to confirm the right person..

And now I need more information to come up when that's clicked... giving data from another table.... I'm sure I need to use an inner join but I don't quite understand how to do it, any help is much appreciated....

Here's what I have so far....

Dim ConnectionString As String
Dim SQLString As String
Dim cmd As System.Data.OleDb.OleDbCommand
Dim conn As System.Data.OleDb.OleDbConnection
Dim dr As System.Data.OleDb.OleDbDataReader
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data "
ConnectionString += "Source=" & "Opticians.accdb "
conn = New System.Data.OleDb.OleDbConnection(ConnectionString)
'AddressTextBox.Text = ListBoxOutput.Text.Substring(0, 5)
AddressTextBox.Text = ListBoxOutput.Text
SQLString = "SELECT * FROM CustomerTable "
SQLString += "Where '" & AddressTextBox.Text & "'"
SQLString += "= Surname"
Try
conn.Open()
If ConnectionState.Open.ToString = "Open" Then
cmd = New System.Data.OleDb.OleDbCommand(SQLString, conn)
dr = cmd.ExecuteReader()
If dr.HasRows Then
dr.Read()
If Not IsDBNull(dr.Item("Street")) Then
AddressTextBox.Text = dr.Item("Street").ToString
End If
If Not IsDBNull(dr.Item("Town")) Then
Address2TextBox.Text = dr.Item("Town").ToString
End If
If Not IsDBNull(dr.Item("County")) Then
Address3TextBox.Text = dr.Item("County").ToString
End If
End If
End If
Catch ex As Exception
End Try

[keystone_paul]

hard to give you specifics without knowing your database structure, but this should help you out with the syntax. Aside from your SQL you shouldn't need to change anything.

Note though - you should be using parameterised queries rather than concatenating strings like that. If you search the board you'll find examples of how (and more importantly why) to do this.

[Shaggy Hiker]

If the other data is in a second table, then a join does sound right. However, will this join return multiple rows? Will a reader suffice, since it is forward only, or will you want to put the data into a datatable.

As for the join, we'd need to know what the other table is, and what fields to join on.

[Kielo]

Ideally I need 3 tables joined in, the first displays info as shown above... then when I click a button I need new information to be displayed...

Then I need to write data to another table... is all that possible?

[GaryMazzone]

Of course it is posible. But a lot more information is needed before any more valid information can be supplied to you.

What about the second table? How do you decided what to get with the where clause of the SQL statement?

Can this data all be brought back at on time using Joins between table1 and table2 (INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN)?

What is supposed to written to the third table? What is the structure of that table? Where will the information be coming from to create that insert statement? Or will this be an Update statement to change a row that already exists in the third table?

[Kielo]

Of course it is posible. But a lot more information is needed before any more valid information can be supplied to you.

What about the second table? How do you decided what to get with the where clause of the SQL statement?

Can this data all be brought back at on time using Joins between table1 and table2 (INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN)?

What is supposed to written to the third table? What is the structure of that table? Where will the information be coming from to create that insert statement? Or will this be an Update statement to change a row that already exists in the third table?

unfortunately i don't understand how the joins work right now, very new to this... i'll try to play around and see how it works..

As for the tables..

Customer Table: Surname, forename, address, telephone no.
Eye Test Table: Eye measurements, date of test
Order table: Order ID, stock number, date

I need to write to the Order table... one of them being an auto number.

I need to display date from the customer table (works)

when I click confirm (confirm the address) I need information from the eye test table to load, and then allow me write to the order table...

Completely lost on how to go about it though, and sorry If the information I've supplied is unhelpful

[GaryMazzone]

There is nothing in what you have show that links the different tables together. How does EyeTest relate to a customer? How does an Order relate to a customer or Eyr test?

[Kielo]

The customer table has Customer ID
Eye test table has Customer ID
Eye test table has that customers eye measurements and date of test

Order table is different to those, but I just need to post to it...

[Kielo]

I need to call the Order table in the same window... when a button is clicked... basically

[Shaggy Hiker]

The general form of the SQL statement for the first two tables would be this:

SELECT <all the fields you want from both tables> FROM CustomerTable INNER JOIN EyeTestTable ON CustomerTable.CustomerID = EyeTestTable.CustomerID WHERE <The where clause you have already written goes here>

That would give you access to all the fields based on the selection criteria in the WHERE clause. Speaking of Access, one option would be to build a query in Access with the visual query designer tools, then, when it is displaying the columns that you want, you can go to the SQL tab to see the SQL statement that was generated. Access tends to add in loads of spurious parentheses, but otherwise it is an easy way to make a fairly complicated query.

[GaryMazzone]

Opening the Order table on the same form the only thing you can do is show all orders since there is nothing you have shown that links an order to a customer of an ExeText

[Kielo]

Thanks for the help guys!

Could I then, have a button saying "Place Order"

Which would load a new form whereby I could input data to the order table?

Thanks again!