View Single Post
 
Old February 22nd, 2007, 06:16 AM
AussieRay AussieRay is offline
Registered User
 
Join Date: Feb 2007
Location: Wamboin, NSW, Australia.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to AussieRay
Default

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