Filling a Secondary Combobox based on information from a primary combobox 
Author Message
 Filling a Secondary Combobox based on information from a primary combobox

Hi,

I have a form with two combo boxes.

The first box is wired up to a table in an access database to display
a name and contain a value in the valuemember property.

The second box is wired up to a different table in the same access
database.  The SQL query I run is based on the value of the selected
item in the primary combo box.

Unfortunately I keep getting errors when I try to fill the dataset for
the second oledbdataadapter.

This is the error I receive from VB.NET

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

I assume that the reason I get this error is because my dataset is not
populating at the right time.

Any help is greatly appreciated.

Thanks,

Goldfinger



Sat, 13 Aug 2005 04:26:22 GMT  
 Filling a Secondary Combobox based on information from a primary combobox
Interesting handle, by the way.

Well, I banged and I banged and I mumbled and groaned over this one and yes,
you are right, this error will occur almost regiliously.
Plain and simple, with out spending any more time on it, here's code which
works.

Imports ADODB
Public Class Form1
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.
    'Do not modify it using the code editor.
    Friend WithEvents ComboBox1 As System.Windows.Forms.ComboBox
    Friend WithEvents ComboBox2 As System.Windows.Forms.ComboBox
    Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
    Friend WithEvents DataGrid2 As System.Windows.Forms.DataGrid
    <System.Diagnostics.De{*filter*}StepThrough()> Private Sub
InitializeComponent()
        Me.ComboBox1 = New System.Windows.Forms.ComboBox()
        Me.ComboBox2 = New System.Windows.Forms.ComboBox()
        Me.DataGrid1 = New System.Windows.Forms.DataGrid()
        Me.DataGrid2 = New System.Windows.Forms.DataGrid()
        CType(Me.DataGrid1,
System.ComponentModel.ISupportInitialize).BeginInit()
        CType(Me.DataGrid2,
System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'ComboBox1
        '
        Me.ComboBox1.Location = New System.Drawing.Point(664, 24)
        Me.ComboBox1.Name = "ComboBox1"
        Me.ComboBox1.Size = New System.Drawing.Size(176, 21)
        Me.ComboBox1.TabIndex = 0
        Me.ComboBox1.Text = "ComboBox1"
        '
        'ComboBox2
        '
        Me.ComboBox2.Location = New System.Drawing.Point(664, 64)
        Me.ComboBox2.Name = "ComboBox2"
        Me.ComboBox2.Size = New System.Drawing.Size(176, 21)
        Me.ComboBox2.TabIndex = 1
        Me.ComboBox2.Text = "ComboBox2"
        '
        'DataGrid1
        '
        Me.DataGrid1.DataMember = ""
        Me.DataGrid1.HeaderForeColor =
System.Drawing.SystemColors.ControlText
        Me.DataGrid1.Name = "DataGrid1"
        Me.DataGrid1.Size = New System.Drawing.Size(656, 208)
        Me.DataGrid1.TabIndex = 2
        '
        'DataGrid2
        '
        Me.DataGrid2.DataMember = ""
        Me.DataGrid2.HeaderForeColor =
System.Drawing.SystemColors.ControlText
        Me.DataGrid2.Location = New System.Drawing.Point(0, 232)
        Me.DataGrid2.Name = "DataGrid2"
        Me.DataGrid2.Size = New System.Drawing.Size(656, 216)
        Me.DataGrid2.TabIndex = 3
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(856, 501)
        Me.Controls.AddRange(New System.Windows.Forms.Control()
{Me.DataGrid2, Me.DataGrid1, Me.ComboBox2, Me.ComboBox1})
        Me.Name = "Form1"
        Me.Text = "Form1"
        CType(Me.DataGrid1,
System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.DataGrid2,
System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region
    Dim rs As Object
    Dim FirstTime As Boolean
    Dim SecondTime As Boolean

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
        FirstTime = True
        Dim cn As OleDb.OleDbConnection = New
OleDb.OleDbConnection("Provider=Microsoft.jet.oledb.4.0;Data
Source=C:\Program Files\microsoft visual studio\vb98\nwind.mdb")
        cn.Open()
        Dim da As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter("SELECT * FROM Suppliers", cn)
        Dim ds As New Data.DataSet()

        da.Fill(ds)

        DataGrid1.DataSource = ds.Tables(0)

        rs = CreateObject("ADODB.Recordset")

        Dim cnstr As String
        cnstr = "Provider=Microsoft.jet.oledb.4.0;Data Source=C:\Program
Files\microsoft visual studio\vb98\nwind.mdb"
        rs.ActiveConnection = cnstr
        rs.CursorLocation = CursorLocationEnum.adUseClient
        rs.LockType = LockTypeEnum.adLockOptimistic
        rs.Source = "Select * From Suppliers"
        rs.Open()

        Dim X

        rs.MoveFirst()
        Do While Not rs.EOF

            ComboBox1.Items.Add(rs.Fields(1).Value)
            rs.MoveNext()
        Loop

        ComboBox1.Text = ComboBox1.Items(0)

        FirstTime = False

    End Sub

    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
ComboBox1.SelectedIndexChanged

        If FirstTime = True Then Exit Sub
        SecondTime = False
        ComboBox2.Items.Clear()

        Dim rs1 As Object

        Dim X As Integer

        rs.MoveFirst()

        For X = 0 To ComboBox1.SelectedIndex - 1
            rs.movenext()
        Next

        rs1 = CreateObject("ADODB.Recordset")

        Dim cnstr As String
        cnstr = "Provider=Microsoft.jet.oledb.4.0;Data Source=C:\Program
Files\microsoft visual studio\vb98\nwind.mdb"
        rs1.ActiveConnection = cnstr
        rs1.CursorLocation = CursorLocationEnum.adUseClient
        rs1.LockType = LockTypeEnum.adLockOptimistic
        Dim MySQL As String
        MySQL = "Select * From Products WHERE SupplierID=" &
rs.Fields(0).Value
        rs1.Source = MySQL
        rs1.Open()

        rs1.MoveFirst()
        Do While Not rs1.EOF
            ComboBox2.Items.Add(rs1.Fields(1).Value)
            rs1.MoveNext()
        Loop

        ComboBox2.Text = ComboBox2.Items(0)
        SecondTime = True

    End Sub

    Private Sub ComboBox2_SelectedIndexChanged(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
ComboBox2.SelectedIndexChanged

        If SecondTime = True Then

            Dim sql1 As String

            'Note while I am used to running square brackets around spaced
fields, both single quotes and brackets failed. Double quotes worked.

            sql1 = "SELECT * FROM Products Where ProductName =""" &
ComboBox2.Text & """"

            Dim rs2 As Object

            rs2 = CreateObject("ADODB.Recordset")

            Dim cnstr As String
            cnstr = "Provider=Microsoft.jet.oledb.4.0;Data Source=C:\Program
Files\microsoft visual studio\vb98\nwind.mdb"
            rs2.ActiveConnection = cnstr
            rs2.CursorLocation = CursorLocationEnum.adUseClient
            rs2.LockType = LockTypeEnum.adLockOptimistic
            rs2.Source = sql1
            rs2.Open()

            Dim da2 As New OleDb.OleDbDataAdapter()
            Dim ds2 As New Data.DataSet()

            da2.Fill(ds2, rs2, "Products")

            DataGrid2.DataSource = ds2.Tables(0)
        End If

    End Sub

hth

r.t.edwards
http://www.*-*-*.com/


Quote:
> Hi,

> I have a form with two combo boxes.

> The first box is wired up to a table in an access database to display
> a name and contain a value in the valuemember property.

> The second box is wired up to a different table in the same access
> database.  The SQL query I run is based on the value of the selected
> item in the primary combo box.

> Unfortunately I keep getting errors when I try to fill the dataset for
> the second oledbdataadapter.

> This is the error I receive from VB.NET

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

> I assume that the reason I get this error is because my dataset is not
> populating at the right time.

> Any help is greatly appreciated.

> Thanks,

> Goldfinger



Sat, 13 Aug 2005 07:50:24 GMT  
 Filling a Secondary Combobox based on information from a primary combobox
Thank You Richard,

I appreciate the code snippet.  I don't understand why this is
difficult to do in VB.NET while in ASP.NET using vb code, it's very
easy to handle.  See my ASP.NET version of my code below.  For some
reason, VB.NET doesn't like to enumerate the SelectedValue Property.

Protected Function createConn(strSQL) as dataset
            dim dbaseType as string = selectdata
            Dim strConnection as String
            'Dim strSQL as String = SQLQueryChoice
            Dim objConnection as OleDbConnection
            Dim objAdapter as OleDbDataAdapter
                strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" &
_
                "Data
Source=\\srv_dist\netshare\wellbld\serials\serials.mdb"

            objConnection = New oledbConnection(strCOnnection)
            objAdapter = new oledbdataadapter(strsql, objConnection)
            Dim dataset as new dataset()

            Try

               objAdapter.Fill(dataset, dbaseType)

               Catch e As Exception

               dataSet = Nothing
            End Try

            Return dataSet

end Function

    Sub createGroupList
            dim strSQL as String

            strSQL="SELECT DISTINCT * From Groups ORDER BY GRP_NAME;"
            dim dataset as dataset = createconn(strSQL)
                groupName.datasource = dataset
                groupName.datatextfield="GRP_NAME"
                groupName.datavaluefield="GRP_ABREV"
                groupName.databind()
    End Sub

    Sub createProfileList
            dim strSQL as String

            strSQL="SELECT DISTINCT * From Profiles WHERE GRP_ABREV ="
            strSQL=strSQL & "'" & groupName.selecteditem.value & "' "
            strSQL=strSQL & "ORDER BY GRP_PROFILE_ID;"
            dim dataset as dataset = createconn(strSQL)
                profileName.datasource = dataset
                profileName.datatextfield="PROFILE_NAME"
                profileName.datavaluefield="GRP_PROFILE_ID"
                profileName.databind()
    End Sub

See, it's easy to do in ASP, why doesn't it just translate over to VB?
 GRRR
Thanks for your help.

Goldfinger



Sat, 13 Aug 2005 21:39:07 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Hoe to use 2 Primary Tables in a Primary/Secondary Data Form

2. 2 Primary Tables in a Primary/Secondary Data Form

3. Hoe to use 2 Primary Tables in a Primary/Secondary Data Form

4. Programatically fill a combobox A2K ??

5. Using the pipe symbol to fill a listbox or combobox

6. ComboBox Custom Fill Functions

7. fill combobox with contents directory

8. Reference ComboBox item to fill ListBox

9. Fill a combobox

10. How to clear a filled combobox

11. Filling a comboBox with ODBC-data

12. Filling a ComboBox with data from DB

 

 
Powered by phpBB® Forum Software