Data changes based on combo box selection

[Qbert]

i have a form and on that form there is a combo box that has a list of clients that you can select from. what i am trying to do is, on that form i would like a list of previous job numbers to display. but i would like to have the list of job numbers relate to the client that has been selected. and if someone selects another client i would like the list of job numbers to change. but i want the job numbers to be displayed as text.

is something like this possible?

i have done it with a web application but now i have to do it with vb and i am very new to it.

thanks

 

[Levu]

Im not realy sure of what u mean, but I think i understood it

You mean something like this?:

Doubleclick the combobox.
ComboBox1_SelectedIndexChanged <-- you get a sub named this, or whatever name you got at your combobox..
Just add under there what you want to happen after you someone has changed the current target.
If you want to check what he selected, then you do:
Combobox1.selecteditem.

Like: if combobox1.selecteditem = "Job1" then

end if..

Etc etc..
Is that what you want?

 

[Qbert]

thanks i will give it a try and see what happens.

so i could do something like this?

--------------
if combobox1.selecteditem = "Job1" then

--select stuff from the database here--

end if
--------------

i would have to put something in the spot where i want to display the previous jobs for each client. right now i have a list box that i would like the jobs to appear in.

 

[Levu]

How I understand you want it:

1 Listbox, or a control with clients..??
When someone selelcts one of the clients?, then the user gets up all previous job numbers that is relative to that selected client..

Do you draw clients out from a database?
Your table names? Your colum names?
I'll take a look at it if you clearify to me what I just asked for..
Write the database like this:

ClientID(Prim), ClientName, <-- or how you have done it.. Maybe I can place up a little code for you

 

[Qbert]

Levu

that is exactly what i am looking to do. yes the clients are being pulled from the database.

here is what i have going. the clients get pulled from this table:

CREATE TABLE  "CLIENTS" 
   (	"CLIENT_ID" NUMBER, 
	"CLIENT_NAME" VARCHAR2(4000), 
	"CLIENT_CODE" VARCHAR2(4000), 
	"CLIENT_ADDRESS1" VARCHAR2(4000), 
	"CLIENT_ADDRESS2" VARCHAR2(4000), 
	"CLIENT_CITY" VARCHAR2(4000), 
	"CLIENT_STATE" VARCHAR2(4000), 
	"CLIENT_ZIP" NUMBER, 
	"CLIENT_CONTACT_FIRST_NAME" VARCHAR2(4000), 
	"CLIENT_CONTACT_LAST_NAME" VARCHAR2(4000), 
	"CLIENT_CONTACT_PHONE" VARCHAR2(4000), 
	"CLIENT_CONTACT_EMAIL" VARCHAR2(4000), 
	 CONSTRAINT "CLIENTS_PK" PRIMARY KEY ("CLIENT_ID") ENABLE)

and i need to pull the job numbers from this table based on a particular client.

CREATE TABLE  "JOBS" 
   (	"JOB_ID" NUMBER, 
	"JOB_NUMBER" VARCHAR2(20), 
	"JOB_CLIENT" VARCHAR2(500), 
	"JOB_NAME" VARCHAR2(4000), 
	"JOB_NOTES" VARCHAR2(4000), 
	"JOB_STATUS" VARCHAR2(10), 
	"JOB_WHO_OPEN" VARCHAR2(10), 
	"JOB_OPEN_DATE" DATE
CONSTRAINT "JOBS_ID_PK" PRIMARY KEY ("JOB_ID") ENABLE, 
	 CONSTRAINT "JOBS_UK1" UNIQUE ("JOB_NUMBER") ENABLE
   )

and it is the client_name and the job_client that relate. here is the code taht i have for the form now. the combobox is being populated with the clients already i just dont know how to get the jobs for the client.

thanks for you help

Public Class NewProject
    Dim ClientIDArray() As Integer
    Dim ClientNameArray() As String
    Private Sub CancelToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles CancelToolStripMenuItem.Click
        Me.Close()
    End Sub

    Private Sub ProjectNameTextBox_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles ProjectNameTextBox.TextChanged

    End Sub

    Private Sub ClientComboBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles ClientComboBox.SelectedIndexChanged

    End Sub

    Private Sub NewProject_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        IanConnect.Open()

        Dim ClientList As New System.Data.OracleClient.OracleCommand

        ClientList.Connection = IanConnect
        ClientList.CommandText = "select * from clients order by client_name"

        Dim dr As System.Data.OracleClient.OracleDataReader = ClientList.ExecuteReader()
        Dim counter As Integer = 0

        While dr.Read()
            ClientComboBox.Items.Add(dr.Item(1))
            ReDim Preserve ClientIDArray(counter + 1)
            ReDim Preserve ClientNameArray(counter + 1)
            ClientIDArray(counter) = dr.Item(0)
            ClientNameArray(counter) = dr.Item(1)
            counter += 1
        End While

        IanConnect.Close()
    End Sub
End Class

 

[Qbert]

does anyone know how i would do this? i now have a list box that displays the previos jobs but it shows all the jobs. i need to get it to show just the jobs for a specific client. the one that the user chooses from the combobox.

is this even possible?

thanks

 

[cjard]

I'll do you an example project... Actually, i think I have one somewhere

 

[cjard]

Nits.. i cant do it right now because I have recently changed monitor layout on my laptop and theres a weird bug in the graphics drivers that mean any DataGridView in any app on my system doesnt render properly. .THe problem extends to the DataSer designer too, in that it opens, but I see nothing other than the cursor change and tooltips appear depicting the datatables.. I cant restart this machine right now because of other work in progress. I'll get back to this when these long running jobs are done.. be about 13 hours

 

[Qbert]

cool thanks

i appreciate it

 

[cjard]

Could you prepare an access database (open access, link it to Oracle via an external link, do a SELECT * INTO whatever FROM oracle_whatever) so that I have some meaningful sample data to work with when showing you? I dont want the teaching point to get lost in the confusion of having to look at a database full of different names of tables etc...

 

[Qbert]

here you go. i have recreated the two tables that i have in access.

thanks.

 

[cjard]

Here is the sample. I include 2 ways of doing what you seek. The first uses relations, the second, fills the "child" table ad hoc, based on whichever parent is currently selected.

Do note that having CLIENT_ID as an integer, but PROJECT_CLIENT_ID as a VARCHAR makes things a bit of a pain in the arse. If a column is to be a foreign key, it should take the datatype of its parent column in the primary key

 

[Qbert]

thanks for your example. i now see what you ment about accessing the data. i have now changed all my data access to the way that you showed me.

one problem i am having now with the example that you made for me is i am using an oracle database and at the end of your query it had a ? at the end. when i added it to my application that uses the oracle db it gave me an error about the ?.

and visual studio changed the ? to ARAM1. now i dont get any errors but the query does not work. i have tried to assign a value to the variable PARAM1 but have had now luck. if i put in a value of an id like 21 it works fine but dosent change based on the client you select from the list.

do i need to add or change something here?

Me.ProjectTableAdapter.FillByProjectClientID(Me.MinnowDataSet.PROJECT, CurrentClientsRow.CLIENT_ID.ToString())

 

[cjard]

thanks for your example. i now see what you ment about accessing the data. i have now changed all my data access to the way that you showed me.

one problem i am having now with the example that you made for me is i am using an oracle database and at the end of your query it had a ? at the end. when i added it to my application that uses the oracle db it gave me an error about the ?.

and visual studio changed the ? to ARAM1. now i dont get any errors but the query does not work. i have tried to assign a value to the variable PARAM1 but have had now luck. if i put in a value of an id like 21 it works fine but dosent change based on the client you select from the list.

do i need to add or change something here?

Me.ProjectTableAdapter.FillByProjectClientID(Me.MinnowDataSet.PROJECT, CurrentClientsRow.CLIENT_ID.ToString())

Well, youre right in that Oracle doesnt use ? for parameters, it uses names like :CLIEND_ID or :MY_PARAM - anything that starts with a single colon is a parameter

JUst zip up the project youre having problems with and post it up here again. I'll check the query and parameter formation and test it against my oracle if necessary. I'm sure it will be something simple, but I'll need to look, rather than give you a big list of things to check