Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > VB Databases Basics
| Search | Today's Posts | Mark Forums Read
VB Databases Basics Beginning-level VB coding questions specific to using VB with databases. Issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB Databases Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old February 17th, 2007, 10:10 PM
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 Parameters / Filters in a datatable

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
  #2 (permalink)  
Old February 19th, 2007, 08:55 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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
================================================== =========
  #3 (permalink)  
Old February 22nd, 2007, 06:16 AM
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
  #4 (permalink)  
Old February 22nd, 2007, 10:26 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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
================================================== =========


Similar Threads
Thread Thread Starter Forum Replies Last Post
MDX filters an Parameters GabrielP BOOK: Professional SQL Server 2005 Reporting Services ISBN: 0-7645-8497-9 2 May 16th, 2006 08:31 AM
Parameters / Filters s15199d Reporting Services 0 March 23rd, 2006 12:08 PM
About filters alf.guzman Servlets 0 December 16th, 2004 06:07 PM
logins/filters desireemm SQL Language 1 August 17th, 2004 05:53 PM
Filters filters and again .... GregoryHu Access VBA 4 July 28th, 2004 10:59 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.