strange behavior of combobox ValueMember

RussGreen
RussGreen used Ask the Experts™
on
I'm having a real hard time trying to get this funtion to work correctly

Public Function PopulateListContol(ByVal ListControl As Object, ByVal ValMem
As String, ByVal DisMem As String, ByVal Database As String, ByVal DataTable
As String)
Try

'connection stuff
Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM " & DataTable, _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Database)

Dim ds As New DataSet()
da.Fill(ds)

ListControl.DataSource = ds.Tables(0)
ListControl.DisplayMember = DisMem
ListControl.ValueMember = ValMem

Catch ex As Exception
MsgBox(ex.Message)
End Try
End Function

I'm calling the function during a form load event in a block of code like
this:

'fill combo boxes from database
PopulateListContol(Me.cboClientPvt, "ClientPvtID", "Name", PathtoDB & ProjectDB, "ClientPvt")
PopulateListContol(Me.cboClientCorp, "ClientCorpID", "CompanyName", PathtoDB & ProjectDB, "ClientCorp")
PopulateListContol(Me.cboArchitect, "ArchitectID", "CompanyName", PathtoDB & ProjectDB, "Architect")
PopulateListContol(Me.cboLandArch, "LandArchID", "CompanyName", PathtoDB & ProjectDB, "LandArch")
PopulateListContol(Me.cboStrucEng, "StrucEngID", "CompanyName", PathtoDB & ProjectDB, "StrucEng")
PopulateListContol(Me.cboServEng, "ServEngID", "CompanyName", PathtoDB & ProjectDB, "ServEng")
PopulateListContol(Me.cboQuantSurv, "QuantSurvID", "CompanyName", PathtoDB & ProjectDB, "QuantSurv")
PopulateListContol(Me.cboContractor, "ContractorID", "CompanyName", PathtoDB & ProjectDB, "Contractor")
PopulateListContol(Me.cboProjMan, "ProjManID", "CompanyName", PathtoDB & ProjectDB, "ProjMan")

So far so good, evberything seems to work. all the combos populate with the CompanyName or Name
fields from the correct tables. The thing is when I try to write to another table, the ValueMember
isn't writing out correctly. The wrong number is being inserted everytime. For example of just done
a test where the cboClientCorp.ValueMember should have been 12, but when the ValueMember for this
record was written to another table it was written as 10.

Please help, I'm totally confused on this one.

Many thanks

Russ
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
where, when and what are you writing to another table?

Author

Commented:
Ok sorry I wasn't clear. I have an access database that consists of 10 tables (Projects, ClientPvt, ClientCorp, Architect, Contractor, LandArch, ProjMan, QuantSurv, ServEng, StrucEng) Basically they are all address books except the Projects table which stores information related to jobs (this is a tool for managing jobs in an Architectural practice).

To setup a new project there is a form with various text boxes to complete and a range of combos. Some of the combos are populated wy text files whilst others are getting populated by the other tables in the database. For the sake of this form for creating new projects the only two fields required from each of the address-book tables are the "CompanyName" and "ID" fields. I wan't to display the CompanyName in each of the Combos but when it comes to writing to the Projects table I want to insert the ID field and not the CompanyName field. Easy right?

In the case of the Architect table (just as an example) I have a combo called cboArchitect on my new project form. cboArchitect is being populated with ArchitectID and CompanyName fields from the Architect table. CompanyName is being displayed in the combo whilst ArchitectID is being assigned to the ValueMember (I hope). ArchitectID is the primary key in the table and is an autonumber that does not allow duplicates. When the new project form is complete and saved the ValueMember (ArchitectID) is written to the Projects table into a field called ArchitectID which is also assigned to be a number field (Long Integer).

The save happens with code like this...

        Dim daProjects As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & PathtoDB & ProjectDB
        Dim strSQL As String

        Try
            ' Define a query string
            strSQL = "INSERT INTO Projects ([Number], [Name], [Type], <<MORE FIELDS IN HERE>>, [ArchitectID]) VALUES ('"
            strSQL = strSQL & Me.txtJobNumber.Text & "', '" & Me.txtJobName.Text & "', '" & Me.cboProjectType.Text & "', <<MORE VALUES IN HERE>>,'" & Me.cboArchitect.SelectedValue & "')"

            'Connection Stuff
            Dim ocon As OleDbConnection = New OleDbConnection(daProjects)
            ocon.Open()

            'Command Object to Execute the SQL
            Dim ocmd As OleDbCommand = New OleDbCommand(strSQL, ocon)
            ocmd.ExecuteNonQuery()

            'Cleanup
            ocmd = Nothing
            ocon.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

PathtoDB & ProjectDB are set in the applications preferences section but basically combine to form the full path and filename of the database.

Hopefully that a little better explained now..

Russ

Author

Commented:
Ok sorry I wasn't clear. I have an access database that consists of 10 tables (Projects, ClientPvt, ClientCorp, Architect, Contractor, LandArch, ProjMan, QuantSurv, ServEng, StrucEng) Basically they are all address books except the Projects table which stores information related to jobs (this is a tool for managing jobs in an Architectural practice).

To setup a new project there is a form with various text boxes to complete and a range of combos. Some of the combos are populated wy text files whilst others are getting populated by the other tables in the database. For the sake of this form for creating new projects the only two fields required from each of the address-book tables are the "CompanyName" and "ID" fields. I wan't to display the CompanyName in each of the Combos but when it comes to writing to the Projects table I want to insert the ID field and not the CompanyName field. Easy right?

In the case of the Architect table (just as an example) I have a combo called cboArchitect on my new project form. cboArchitect is being populated with ArchitectID and CompanyName fields from the Architect table. CompanyName is being displayed in the combo whilst ArchitectID is being assigned to the ValueMember (I hope). ArchitectID is the primary key in the table and is an autonumber that does not allow duplicates. When the new project form is complete and saved the ValueMember (ArchitectID) is written to the Projects table into a field called ArchitectID which is also assigned to be a number field (Long Integer).

The save happens with code like this...

        Dim daProjects As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & PathtoDB & ProjectDB
        Dim strSQL As String

        Try
            ' Define a query string
            strSQL = "INSERT INTO Projects ([Number], [Name], [Type], <<MORE FIELDS IN HERE>>, [ArchitectID]) VALUES ('"
            strSQL = strSQL & Me.txtJobNumber.Text & "', '" & Me.txtJobName.Text & "', '" & Me.cboProjectType.Text & "', <<MORE VALUES IN HERE>>,'" & Me.cboArchitect.SelectedValue & "')"

            'Connection Stuff
            Dim ocon As OleDbConnection = New OleDbConnection(daProjects)
            ocon.Open()

            'Command Object to Execute the SQL
            Dim ocmd As OleDbCommand = New OleDbCommand(strSQL, ocon)
            ocmd.ExecuteNonQuery()

            'Cleanup
            ocmd = Nothing
            ocon.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

PathtoDB & ProjectDB are set in the applications preferences section but basically combine to form the full path and filename of the database.

Hopefully that a little better explained now..

Russ
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
dunno what happened there. I refreshed my page and wound up with two loads of comment. Sorry about that

Commented:
I'm not sure, but as I understand, your datasete is being destroyed everytime you leave your procedure. So If I am right, you should declare separete dataset for every table and keep them alive while you need the access to their data. hope it helps, but i'm not sure. Good luck anyway :)

Author

Commented:
so i thats a result of this function then?

Author

Commented:
I need to add the ability for the function to use unique name for the dataset. I've added "ByVal DatSet As String" so that the name of the dataset can be specified when the funtion is called but how do I turn DatSet into ds in this funtion??

Public Function PopulateListContol(ByVal ListControl As Object, ByVal ValMem As String, _
ByVal DisMem As String, ByVal Database As String, ByVal DataTable As String, ByVal DatSet As String)
       
 Try
            'connection stuff
            Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM " & DataTable, _
            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Database)

            Dim ds As New DataSet()
            da.Fill(ds)

            ListControl.DataSource = ds.Tables(0)
            ListControl.DisplayMember = DisMem
            ListControl.ValueMember = ValMem

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
End Function

Commented:
no no. i'm telling not to initialize it in the procedure

Public Function PopulateListContol(ByVal ListControl As Object, ByVal ValMem As String, _
ByVal DisMem As String, ByVal Database As String, ByVal DataTable As String, ByRef ds As DataSet)
       
Try
           'connection stuff
           Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM " & DataTable, _
           "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Database)

           da.Fill(ds)

           ListControl.DataSource = ds.Tables(0)
           ListControl.DisplayMember = DisMem
           ListControl.ValueMember = ValMem

       Catch ex As Exception
           MsgBox(ex.Message)
       End Try
End Function

and in your body

Dim dsClientPvt As New DataSet()
PopulateListContol(Me.cboClientPvt, "ClientPvtID", "Name", PathtoDB & ProjectDB, "ClientPvt",dsClientPvt)
Dim dsClientCorp As New DataSet()
PopulateListContol
(Me.cboClientCorp, "ClientCorpID", "CompanyName", PathtoDB & ProjectDB, "ClientCorp",ClientCorp)
etc...

At least it sounds more logical for me, but as i said, im not sure. Good luck.

Author

Commented:
we'll i agree it seems more logical and it does work but unfortunately it works in the same way as before. There is a discrepancy in the numbers of the Primary ID fields of the address book tables and the number that is written into the projects table.

What is really bizarre is that there is a table for editing the project details. That table is alsmost a complete clone except for the databinding and the navigation. When that table loads it firstly populates the combos with the same function, it then binds the data to the projects table so when you navigate up and down through the records the the combos display the saved name (e.g. cboClientCorp displays the CompanyName relating to the saved SelectedValue) and they actually display the correct names. But the numbers in the database do not match up (this is something I don't understand and I need to as there is an intranet app which displays the database on a day to day basis and that simply isn't funtioning because the database has the wrong values stored.

Am I making some kind of error in the ValueMember > SelectedValue area??

Russ

Russ

Commented:
Look, dont you run these
Dim dsClientPvt As New DataSet() etc in a body of any procedure? If you do it is same mistake, Main thing is that they, Datasets, should stay alive all the time when you access them. Those datasets must be sort of public, declared in the class declaration or whereever you declare public virable. I just dont see the whole code, but I'm sure you can find a place.

Author

Commented:
I get that. I am declaring them in the class declaration. It must be another problem causing this. I have tested this in a small test app with only one combo and a 2 table db and its ok. This thing isn't throwing up any errors

Author

Commented:
this all seems to be a result of the combo having sort set to true. i've now got it set to false and using order by in the sql query.

everything is working
Points refunded and placed in PAQ

Computer101
E-E Admin

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial