This is probably pretty basic but ... I have a datatable with the stakeholders for each of many projects in the database. I have a Query in MS ACcess (2003) that requires a parameter call @Project_ID. This needs to be selected according to an instance of a "Project" Class with the variable value "Project.Proj_ID" that shows the ID of the currently active project.

Now to populate a datatable ("Stakeholders") and from that a combobox that has only the stakeholders (Primary Key - Stakeholder_ID - the field I want to show is "JoinedName") from my active project I have tried passing a parameter to the Query ("usp_SelectStakeholder") but it keeps bombing. I thought one way of doing this is to select the entire stakeholders table and then filter it.

Any suggestions for code to approach this either way? Thanks mates

hi there..

if you are using access, why don't forget about parameters and build the query yourself???



HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========

Thanks Gonzalo

I think I've solved the problem. I must have been doing something really basic very badly (I just retyped it all from the start).

I guess in answer to your question - because I want to set this up over both SQL Server and Oracle later. MSAccess is just my sand pit so I'm trying to use code that will be fairly robust for all database apps. I have the parameter query set up in Access. I could have just used VBA but that won't stand up against SQL and Oracle.

The code I ended up using is as follows (if you can think of any improvements feel free to whack me around the head with it!!). It uses the WDABaseClass out of the book

Private Sub LoadCombos()
        'Sub to load scope elements and stakeholders
        'declare dataset - don't think I need variables at this level
        objScopeDS = New DataSet

        'initialise a new instance of the data access base class

        Using objData As New WDABaseClass
            Try
                'clear any previous data bindings - just in case
                cmbxStageDesc.DataSource = Nothing
                cmbxStageDesc.DisplayMember = String.Empty
                cmbxStageDesc.ValueMember = String.Empty
                cmbxChangeAuthBy.DataSource = Nothing
                cmbxChangeAuthBy.DisplayMember = String.Empty
                cmbxChangeAuthBy.ValueMember = String.Empty

                'get all stakeholders and stages in a dataset object (two datatables)
                objData.OpenConnection()
                objData.SQL = "usp_Stakeholders"
                objData.InitializeCommand()
                objData.FillDataSet(objScopeDS, "Stakeholders")

                objData.SQL = "usp_Stages"
                objData.InitializeCommand()
                objData.AddParameter("@Proj_ID", Data.OleDb.OleDbType.Integer, 16, Project.Proj_ID)


                objData.FillDataSet(objScopeDS, "Stages")
                'objData.AddParameter("@Proj_ID", OleDb.OleDbType.Integer, 5, CInt(Project.Proj_ID))
                'Rebind the combobox control
                cmbxStageDesc.DataSource = objScopeDS.Tables("Stages")
                cmbxStageDesc.DisplayMember = "StageDesc"
                cmbxStageDesc.ValueMember = "StageSequence"
                cmbxChangeAuthBy.DataSource = objScopeDS.Tables("Stakeholders")
                cmbxChangeAuthBy.DisplayMember = "JointName"
                cmbxChangeAuthBy.ValueMember = "Stakeholder_ID"

                cmbxStageDesc.SelectedIndex = 0
                cmbxChangeAuthBy.SelectedIndex = 0

            Catch ExceptionErr As Exception
                MessageBox.Show(ExceptionErr.Message, strAppTitle)

            End Try
        End Using
        'Clean up
        objData = Nothing
        'keep the dataset alive? Think so. Clean it up at the end of the form load

    End Sub

Thanks

Hey when do the Wallabies play the Pumas next?!?!

cheers

Ray

glad you worked it out...

I don't know when they play.. is there a test match before the world cup???

i can't find any info around here...

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========