[RESOLVED] [02/03] Combobox/Textbox help please

[frankwhite]

Hi people, was hoping someone could offer me some help please. On my form I have two comboboxes and two textboxes, the comboxes and textboxes are linked to a database.

The first combobox and textbox is linked to a customers table, the second combobox and textbox is linked to a caller table. Each customer in the customer table has a CustomerID, this field is also used in the Caller table as 1 customer can have many callers. Now for the complicated part, when the form loads each customers name is loaded in combobox 1, and when a customer is selected from combobox1 in textbox1 the CustomerID is shown. So far I have this working perfectly, however the next part is where I’m struggling.

What I want to do is take the customerID from textbox1, and show all the callers in combobox2 with that same CustomerID, also each caller has their own telephone number so when a caller is selected I want to display the telephone number in textbox 2.

Is this possible? I’ve been struggling a lot on the coding and any help I would appreciate it.

So far I have the following coding which shows the customers in combobox1 and the customer ID in textbox1.

    Dim connection As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & Application.StartupPath & "\MS.mdb;Jet OLEDB:Database Password=king;")
        Dim adapter As New OleDbDataAdapter("SELECT ID, CusName, CusID FROM Customer ORDER BY CusName", connection)
        Dim table As New DataTable
        adapter.Fill(table)
        Me.cmbcus.DisplayMember = "Name"
        Me.cmbcus.ValueMember = "CusID"
        Me.cmbcus.DataSource = table
        Me.txtcus.DataBindings.Add("Text", table, "CusID")

Please help me as I am stuggling.

[dolot]

my guess is it would be very similar to the code you have above, except for the select statement. In the select statement you would have to have a WHERE clause to filter by customer id

So, and I'm just guessing here...

"SELECT CallerID, CallerName, PhoneNumber
FROM Caller
WHERE CustomerID = " & textbox1.text

Then you would set the combobox properties for combobox2 and textbox2 accordingly.

Does this help?

[koskilla]

"SELECT CallerID, CallerName, PhoneNumber
FROM Caller
WHERE CustomerID = " & textbox1.text

I'm not 100% sure, but I seem to remember you need extra quotes in there like this:

"SELECT CallerID, CallerName, PhoneNumber
FROM Caller
WHERE CustomerID = '" & textbox1.text & "'"

That is a single ' and a double " followed by a double " single ' double "

so you end up with ...WHERE CustomerID = 'textbox1'

[riteshjain1982]

regarding

 Dim connection As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & Application.StartupPath & "\MS.mdb;Jet OLEDB:Database Password=king;")
        Dim adapter As New OleDbDataAdapter("SELECT ID, CusName, CusID FROM Customer ORDER BY CusName", connection)
        Dim table As New DataTable
        adapter.Fill(table)
        Me.cmbcus.DisplayMember = "Name"
        Me.cmbcus.ValueMember = "CusID"
        Me.cmbcus.DataSource = table
        Me.txtcus.DataBindings.Add("Text", table, "CusID")

If data is showing up in cmbcus than it should so related cud_id automatically.... just make sure your txtcus's Enable Property is True
also try with

Me.txtcus.DataBindings.Clear()
Me.txtcus.DataBindings.Add("Text", table, "CusID")

[dolot]

I'm not 100% sure, but I seem to remember you need extra quotes in there like this:

"SELECT CallerID, CallerName, PhoneNumber
FROM Caller
WHERE CustomerID = '" & textbox1.text & "'"

That is a single ' and a double " followed by a double " single ' double "

so you end up with ...WHERE CustomerID = 'textbox1'

That's true if the CustomerID is a text field of some sort. Most ID fields are some form of numeric, so I left the single quotes off. Good point, though.

[onlyGirl]

SELECT CallerID, CallerName, PhoneNumber
FROM Caller
WHERE CustomerID = @CustomerID

cmd.Parameters.AddWithValue ("@CustomerId", textbox1.Text)

Cmd will be the name of your command

[dolot]

SELECT CallerID, CallerName, PhoneNumber
FROM Caller
WHERE CustomerID = @CustomerID

cmd.Parameters.AddWithValue ("@CustomerId", textbox1.Text)

Cmd will be the name of your command

Yes, this is another way of doing it. You set the Cmd.CommandText = the SQL string and then add the parameters.

OnlyGirl: Does this automatically put in single quote around the parameter value if they're needed?

[koskilla]

That's true if the CustomerID is a text field of some sort. Most ID fields are some form of numeric, so I left the single quotes off. Good point, though.

Ya, thats usually true. I've seen customer IDs that use part of the name or something so it easier to identify, so the quotes would be necessary in that case, and I think it works the same with the quotes, even if the data type is numeric. But ya, you're right, its probably not needed

[onlyGirl]

I believe so.

[frankwhite]

I'm sorry but I'm confused with what everyone is talking about

My coding above I have set as a sub and call it in the form load event, shall I copy and paste the same coding and change the Sql statement? Onlygirl I'm not sure what you mean with cmd. Sorry to be a pain people.

[dolot]

shall I copy and paste the same coding and change the Sql statement?

What I would do is copy the code into a new sub procedure, change the SQL string, and then change the references to those of combobox2 and textbox2. Then you would call this new sub procedure from the combobox2_SelectedItemChanged event.

I'll let OnlyGirl explain to you about the cmd.

[frankwhite]

Hey Dolot so far I've done this following (currently I dont have the customer database so I'm testing it out on another database.) The database consists of pupils in the different years.

The following is under my sub

    Private Sub Loadpup()
        Dim connection As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & Application.StartupPath & "\MS.mdb;Jet OLEDB:Database Password=liambradywasking;")
        Dim adapter As New OleDbDataAdapter("SELECT ID, Name, PupilNumber FROM Year7 ORDER BY Name", connection)
        Dim table As New DataTable
        adapter.Fill(table)
        Me.cmbbyname.DisplayMember = "Name"
        Me.cmbbyname.ValueMember = "ID"
        Me.cmbbyname.DataSource = table
        Me.txtbyname.DataBindings.Add("Text", table, "PupilNumber")
    End Sub

    Private Sub loadtest()
        Dim connection As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & Application.StartupPath & "\MS.mdb;Jet OLEDB:Database Password=liambradywasking;")
        Dim adapter As New OleDbDataAdapter("SELECT ID, Name, PupilNumber FROM(Year8) WHERE PupilNumber = '" & txtbyname.Text & "'", connection)
        Dim table As New DataTable
        adapter.Fill(table)
        Me.cmbbyname2.DisplayMember = "Name"
        Me.cmbbyname2.ValueMember = "ID"
        Me.cmbbyname2.DataSource = table
        Me.txtbyname2.DataBindings.Add("Text", table, "Merits")

    End Sub

'Form Load Event

 Loadpup()

 Private Sub cmbbyname2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbbyname2.SelectedIndexChanged
        loadtest()
    End Sub

The above fills the first combobox and textbox but doesn't do anything with the 2nd combobox and textbox. Any ideas on where I may hvae gone wrong?

Thanks

[onlyGirl]

I'm sorry but I'm confused with what everyone is talking about

My coding above I have set as a sub and call it in the form load event, shall I copy and paste the same coding and change the Sql statement? Onlygirl I'm not sure what you mean with cmd. Sorry to be a pain people.

Instead of supplying the adapter with the delete command, you create a command by itself then you can add parameters to it. For example:

 Dim deleteProd As New OleDbCommand("Delete FROM Param_Prod WHERE API = @API AND StartDate = @StartDate AND EndDate = @EndDate AND Reservoir=@Reservoir AND InitialRate = @InitialRate")

                    deleteProd.Parameters.Add("@API", OleDbType.VarChar, 30, "API")
                    deleteProd.Parameters.Add("@StartDate", OleDbType.Date, 12, "StartDate")
                    deleteProd.Parameters.Add("@EndDate", OleDbType.Date, 12, "EndDate")
                    deleteProd.Parameters.Add("@Reservoir", OleDbType.VarChar, 30, "Reservoir")
                    deleteProd.Parameters.Add("@InitialRate", OleDbType.Double, 30, "InitialRate")

                    deleteProd.Connection = gConn
                    daWellData.DeleteCommand = deleteProd

[dolot]

Did you mean to put "Merits" in instead of "PupilNumber" in loadtest()? I'm seeing "PupilNumber" in the sql string and "Merits" in the textbox databindings.

[frankwhite]

Thanks Onlygirl but that sounds confusing.

Dolot sorry my mistake, I want to display the merits from Year 8 in textbox 2 (so some student in year7 will have the same pupilnumber as some student in year8). I have changed it to:

    Dim connection As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & Application.StartupPath & "\MS.mdb;Jet OLEDB:Database Password=liambradywasking;")
        Dim adapter As New OleDbDataAdapter("SELECT ID, Name, PupilNumber, Merit FROM(Year8) WHERE PupilNumber = '" & txtbyname.Text & "'", connection)
        Dim table As New DataTable
        adapter.Fill(table)
        Me.cmbbyname2.DisplayMember = "Name"
        Me.cmbbyname2.ValueMember = "ID"
        Me.cmbbyname2.DataSource = table
        Me.txtbyname2.DataBindings.Add("Text", table, "Merit")

However again only combobox1 and textbox 1 show the data, this is driving me crazy now

[dolot]

Opps! Sorry. combobox1_SelectedIndexChanged event is where you should call loadtest() from.

[frankwhite]

No problem, I tried that but it breaks on the adapter.fill line of loadtest.

[dolot]

What's the error that you get when it breaks?

[frankwhite]

well just before it breaks i can see it work for a split second, the error says

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

hth

[frankwhite]

Hey,

I did the following:

        Try
            Dim connection As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & Application.StartupPath & "\MS.mdb;Jet OLEDB:Database Password=liambradywasking;")
            Dim adapter As New OleDbDataAdapter("SELECT ID, Name, PupilNumber, Merit FROM(Year8) WHERE PupilNumber = '" & txtbyname.Text & "'", connection)
            Dim table As New DataTable
            adapter.Fill(table)
            Me.cmbbyname2.DisplayMember = "Name"
            Me.cmbbyname2.ValueMember = "ID"
            Me.cmbbyname2.DataSource = table
            Me.txtbyname2.DataBindings.Add("Text", table, "Merit")
        Catch ex As OleDbException ' 
            MessageBox.Show(ex.Message)
        End Try

And the error it threw was 'Data type mismatch in criteria expression'

Hope that means something to you.

[frankwhite]

Anyone please? I'm desperate to get this working.

[frankwhite]

I figured out as the PupilNumber was a number it did not require the "", however now it breaks on this line:

 Me.txtbyname2.DataBindings.Add("Text", table, "Merit")

The error it shows is:

Additional information: This would cause two bindings in the collection to bind to the same property.

Anyone know maybe why it is saying this? Much appreciated

[Nerd44]

Hi!

I would recommend to start from new.
All Databinding stuff can be easily done in several assistants and designers of the IDE.
U can learn to do so by whatching the Forms over data video-series.
An interesting experiment to me would be, if u try to understand my tutorial DatenBank in 10 Minuten.zip
Sorry, its german, but its full animated (u always have to click the green zones), and can lead u through the configuration process.
And there is a small runnable Sample-App, which can especially teach, how to load and save Datasets with linked Tables.

[dolot]

Sorry, I took the weekend off. Could you re-post your current code so we can take a look at it and see if we can see what's up?