SQL string problem... take a value, take the ID, search for the ID

[KingJ]

Hi,

I would like to write allot of text here, but i know that I would just be waffling!

What I have is a combobox that displays all of the Location Names in a form. (this works perfectly) What i want is to take the Location Name that the user selected and look in the Location Table Table for the Location ID.Then to get all of the records in the Stock Table with the Location ID as a Foreign key and put the the Name of the Item of stock (that was found) into a separate Combobox.

For more info: see my post below.

There must be a simple way to do this, I have had a look on google and tried the JOIN method,but that doesn't work! IT doesn't do anything at all.... its bugging me.

I'm using Access 2010 and Vb.net 2008 with OLEDB

Any help would be appreciated.


Thanks

John.

[Shaggy Hiker]

Show us the SQL using the JOIN, because that is what you need to do, most likely. It would look something like this:

SELECT <fields from Stock table> FROM StockTable INNER JOIN LocationTable ON StockTable.LocationID = StockTable.LocationID WHERE LocationTable.LocationName = '" & comboboxitem & "'"

The exact details are missing from that, though (and it concatenates in a string rather than using parameters, but that could be easily changed).

[KingJ]

Thanks.
Will Test that out now and let you know

[KingJ]

Right I can't get it to work

I have 3 tables:

Table "DepartmentTable" contains: "DepartmentID", "ClientID", "DepartmentName"
Table "LocationTable" contains: "LocationID", "LocationID"
Table "ClientTable" contains: "ClientID", "ClientName"

I have the following code that I am completely Stuck with, trying to follow the advice of @Shaggy Hiker .

I am getting NO reading at all from this. which seems VERY strange. I think that im doing it correctly although I am not certain. Infact, 100&#37; sure its not correct.

Sql = "SELECT * FROM DepartmentTable " & _
    " INNER JOIN ClientTable" & _
    " ON DepartmentTable.ClientID = ClientTable.ClientID" & _
    " WHERE ClientTable.ClientName = '" & _
        " " & CLBClient.Items(x) & " ') "
 
 
        Comando = New OleDbCommand(Sql, DBConn)
        Comando.CommandType = CommandType.Text
        RDR = Comando.ExecuteReader()
 
        While RDR.Read()
            MessageBox.Show(RDR("DepartmentName"))
 
        End While

EDIT:
When I add a breakPoint into the table, I get the following SQL string.

SELECT * FROM DepartmentTable  INNER JOIN ClientTable ON DepartmentTable.ClientID = ClientTable.ClientID WHERE ClientTable.ClientName = ' MAH ')

MAH has a ClientID 2 and appears 4 times in the DepartmentTable with CLIENTID as 2.

[CoachBarker]

Why not load your combo box with the LocationID and the Location, display only the Location and when you select the Location you will also have the LocationID, requery the Stock Table with the LocationID and fill the other combo box?

[KingJ]

Why not load your combo box with the LocationID and the Location, display only the Location and when you select the Location you will also have the LocationID, requery the Stock Table with the LocationID and fill the other combo box?

Its because Its not a Combobox i'm adding it to .. its a ComboListbox. I will try the suggestion and see it helps. Thanks

[Ritzky]

I'm also having a similar problem.

Dim SQLQuery = "SELECT * FROM BTABLE WHERE BCOLUMN = WC.DownloadString("http://mywebsite.com/create.txt")

Is there anyway I can get this to work? I know I need more quotes,apostrophes,colons, etc; But I have no clue where to put them.

thanks

[CoachBarker]

What I have is a combobox that displays all of the Location Names in a form. (this works perfectly) What i want is to take the Location Name that the user selected and look in the Location Table Table for the Location ID.Then to get all of the records in the Stock Table with the Location ID as a Foreign key and put the the Name of the Item of stock (that was found) into a separate Combobox.

Same principle different control.

[KingJ]

I really Don't understand what your saying CoachBarker. Sorry

[CoachBarker]

will try to put something together later, am going out for a while and will be back later.

[KingJ]

Thanks CoachBarker. I may well go to bed when you return. As i live in the UK.

If you could post a comment later then that would be brilliant! thanks

[Evil_Giraffe]

Here's a simple example of what CoachBarker is suggesting. Define an object that holds both the name of the location (that you want to display to the user) and the id of the location (that you want the computer to work with):

Public Class Location
    Private ReadOnly _locationId As Integer
    Private ReadOnly _locationName As String
 
    Public Sub new (ByVal locationId As Integer, ByVal locationName As string)
        _locationId = locationId
        _locationName = locationName
    End Sub
 
    Public ReadOnly Property Id As Integer
        Get
            Return _locationId
        End Get
    End Property
 
    Public ReadOnly Property Name As String
        Get
            Return _locationName
        End Get
    End Property
End Class

Now when you fill your combo box of locations, don't fill it with strings holding the location name, fill it with Location objects holding both the name and the id. Use the ComboBox's DisplayMember path to make the ComboBox display the Name property of the item.
When retrieving the user's selection, use the SelectedItem property to get the whole Location object, from which you can retrieve the Id directly:

Public Class Form1
 
    Private Sub Form1_Load( ByVal sender As System.Object,  ByVal e As System.EventArgs) Handles MyBase.Load
        ' Populate the combo box from your database, obviously, but for a sample...
        locationsComboBox.Items.AddRange(
            {
                New Location(1, "Location 1"),
                New Location(2, "Location 2"),
                New Location(3, "Location 3")
            })
 
        ' Make the combo box display the Name property of the item.
        ' This can be done from the designer as well, but I can't show that in a code sample
        locationsComboBox.DisplayMember = "Name"
    End Sub
 
    Private Sub getStockButton_Click( ByVal sender As System.Object,  ByVal e As System.EventArgs) Handles getStockButton.Click
        Dim selectedLocation As Location = locationsComboBox.SelectedItem
 
        ' If no location selected, exit early
        If selectedLocation is Nothing
            Return
        End If
 
        ' Otherwise, do your query directly with the location id of the location
        MessageBox.Show(String.Format("Location Id = {0}", selectedLocation.Id))
    End Sub
End Class

Passing the location name as a parameter to your subsequent query isn't a great idea. Not only does it mean you need to join two tables, but the database will be optimised for looking up the relevant records based on their ids. Also consider what happens if you have two locations with the same name?

It's also just more complex to understand what you're doing, whereas a query that takes a location id and queries a table to get all the stock for that location (or clients and departments as your subsequent posts talk about) is nice and clear in what its doing. Compare:

SELECT <fields from Stock table>
FROM StockTable
INNER JOIN LocationTable ON StockTable.LocationID = StockTable.LocationID
WHERE LocationTable.LocationName = ???

SELECT <fields from Stock table>
FROM StockTable
WHERE StockTable.LocationID = ???

[Shaggy Hiker]

I totally agree. That's the way to go. The JOIN will be inefficient unless you had set an index on the name field, which you shouldn't be doing, because you already have an index on the ID field (as it should be).

[KingJ]

Ok - so how would i use the index on the field name and put that into something that I can actually use. I have tried the Code that Evil suggested no luck

[CoachBarker]

Something doesn't add up, you posted

Then to get all of the records in the Stock Table with the Location ID as a Foreign key and put the the Name of the Item of stock (that was found) into a separate Combobox.

then you posted:

I have 3 tables:

Table "DepartmentTable" contains: "DepartmentID", "ClientID", "DepartmentName"
Table "LocationTable" contains: "LocationID", "LocationID"
Table "ClientTable" contains: "ClientID", "ClientName"

I assume in LocationTable you meant "LocationID", "LocationName". So how many tables are you using and what are their relationships? Where does Stock fit in here?

Post a screen shot of your relationship diagram from your Access DB.

[KingJ]

Yes, your correct to assume that. LocationID was location Name. I didn't mean to write that!

I don't have any relationships in Access..i'm trying to create them all in the application. Is that wrong?!?

OK so what the mishap, and it is totally my fault. I'M going to use the same code several times but adapt it. Originally i was going to start with STOCK and Location... (its a many to many relationship and therefore we need another table to make it 3rd normal form). On that basis i asked for help. It was then that i realized that If i started with the Client and also the Department then I could knock out a larger chunk. If that makes sense.

Sorry my written English is TERRIBLE! you can see that i failed my English GCSE

[Evil_Giraffe]

Ok - so how would i use the index on the field name and put that into something that I can actually use. I have tried the Code that Evil suggested no luck

What didn't work?

[CoachBarker]

I was taught to always set the relationships in access when creating a db. If you don't set the relationships how is the db functional, a foreign key in a table doesn't mean anything if you don't create a relationship between the tables.

[Shaggy Hiker]

I'm not as strict about the relationships in the DB, though I think it is often a good idea. In this case, it isn't really the problem.

What I would do would be this:

On form load, fill a datatable with the LocationID and LocationName. This will be used for the display of the combobox, so that datatable can just sit there unchanged (unless items are added to it). I would then bind the datatable to the combobox, setting the ValueMember as the LocationID and the DisplayMember as the Location Name. This gives you either the LocationID or the LocationName when somebody selects something (depending on whether you use SelectedItem (though this will have to be cast to a datarowview, I think), SelectedValue, or whatever the other one is).

Once you have access to the ID, which is probably the Key field, I'd use that in a JOIN if a JOIN is necessary, or use that in the WHERE clause if the JOIN was not necessary.