dataset with multiple dataadapters

[zozzie]

Here I'm again.

I'm constantly trying to code VB.NET, but it is pretty hard for a newby.

I was trying to write some code to create a dataset without using the wizard of VB.NET.

I wanted to create one dataset which is filled with two dataadapters (DAMainGroup and DAGroup), but it doesn't work.

My code is the following:


Imports System.Data.OleDb

Public Class Form1
Inherits System.Windows.Forms.Form

Dim dataset As dataset = New dataset

Dim connection As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\softlist\softlist_database.mdb;")
#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 ListBox1 As System.Windows.Forms.ListBox
Friend WithEvents ListBox2 As System.Windows.Forms.ListBox
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.ListBox1 = New System.Windows.Forms.ListBox
Me.ListBox2 = New System.Windows.Forms.ListBox
Me.SuspendLayout()
'
'ListBox1
'
Me.ListBox1.Location = New System.Drawing.Point(24, 48)
Me.ListBox1.Name = "ListBox1"
Me.ListBox1.Size = New System.Drawing.Size(200, 69)
Me.ListBox1.TabIndex = 0
'
'ListBox2
'
Me.ListBox2.Location = New System.Drawing.Point(248, 48)
Me.ListBox2.Name = "ListBox2"
Me.ListBox2.Size = New System.Drawing.Size(200, 69)
Me.ListBox2.TabIndex = 1
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(752, 365)
Me.Controls.Add(Me.ListBox2)
Me.Controls.Add(Me.ListBox1)
Me.Name = "Form1"
Me.Text = "Form1"
Me.ResumeLayout(False)

End Sub

#End Region

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim DAMainGroup As OleDbDataAdapter = New OleDbDataAdapter("select * from tblMainGroup", connection)
connection.Open()

DAMainGroup.Fill(dataset, "tblMainGroup")

connection.Close()

Me.ListBox1.DataSource = dataset.Tables("tblMainGroup")
Me.ListBox1.DisplayMember = "MaingroupDescription"
Me.ListBox1.ValueMember = "MaingroupID"


End Sub

Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
Dim strSQLGroup As String
strSQLGroup = "SELECT GroupDescription FROM tblGroup where tblGroup.MainGroupID=" & Me.ListBox1.SelectedValue.ToString
Dim DAGroup As OleDbDataAdapter = New OleDbDataAdapter(strSQLGroup, connection)

DAGroup.Fill(dataset, "tblGroup")

Me.ListBox2.DataSource = dataset.Tables("tblGroup")
Me.ListBox2.DisplayMember = "GroupDescription"

End Sub
End Class


The error is at line 'DAGroup.Fill(dataset, "tblGroup")'. Is this because I want to fill the same dataset with two different dataadapters or has it something to do with the fact that the sqlstring for the DAGroup is based on what the user clicks in het first listbox?

Thanks,

Tom

[nswan]

what is the error you get?

try putting a

 
try
......your code here
catch ex as exception
    msgbox ex.tostring
end try

around the code that is erroring

Nick

[zozzie]

The error is the following;

System.Data.OleDb.OleDbException: No value given for one or more required parameters.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
at TypedDatasets.Form1.ListBox1_SelectedIndexChanged(Object sender, EventArgs e) in C:\Mijn documenten\Visual Studio Projects\TypedDatasets\Form1.vb:line 159


I don't understand it? Can you help me?

Once I clicked ok to the message box, the program runs but listbox 2 is filled with the values from tblGroup as stated but the values are placed after each other when I click on another value in listbox 1, so the list just becomes longer in listbox2, while it should only be updated.

Any ideas?

Thanks Tom

[nswan]

does it work if you try using 2 different datasets?

[zozzie]

The error message is the same when I use a second dataset but the list in listbox2 is filled up correctly?

Any ideas?

Greetz, Tom

[nswan]

try

daGroup.fillschema(dataset, schematype.mapped, "tblGroup")

before daGroup.fill

[zozzie]

Tried your suggestion, but the error remains the same.

I attached the code. I would appreciate if you could take a look.

PS: don't look after the crappy code, I'm a newbie

Thanks,

Tom

The database is in the next post.

[zozzie]

the database

[nswan]

Imports System.Data.OleDb

Public Class Form1
    Inherits System.Windows.Forms.Form

    Dim dataset As DataSet = New DataSet()

    Dim connection As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                                       "Data Source=c:\softlist_database.mdb;")

    Dim blnLoadListBox1 As Boolean
#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 ListBox1 As System.Windows.Forms.ListBox
    Friend WithEvents ListBox2 As System.Windows.Forms.ListBox
    Friend WithEvents TextBox1 As System.Windows.Forms.TextBox
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.ListBox1 = New System.Windows.Forms.ListBox()
        Me.ListBox2 = New System.Windows.Forms.ListBox()
        Me.TextBox1 = New System.Windows.Forms.TextBox()
        Me.SuspendLayout()
        '
        'ListBox1
        '
        Me.ListBox1.Location = New System.Drawing.Point(24, 48)
        Me.ListBox1.Name = "ListBox1"
        Me.ListBox1.Size = New System.Drawing.Size(200, 69)
        Me.ListBox1.TabIndex = 0
        '
        'ListBox2
        '
        Me.ListBox2.Location = New System.Drawing.Point(248, 48)
        Me.ListBox2.Name = "ListBox2"
        Me.ListBox2.Size = New System.Drawing.Size(200, 69)
        Me.ListBox2.TabIndex = 1
        '
        'TextBox1
        '
        Me.TextBox1.Location = New System.Drawing.Point(32, 168)
        Me.TextBox1.Name = "TextBox1"
        Me.TextBox1.Size = New System.Drawing.Size(640, 20)
        Me.TextBox1.TabIndex = 2
        Me.TextBox1.Text = "TextBox1"
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(752, 365)
        Me.Controls.Add(Me.TextBox1)
        Me.Controls.Add(Me.ListBox2)
        Me.Controls.Add(Me.ListBox1)
        Me.Name = "Form1"
        Me.Text = "Form1"
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        blnLoadListBox1 = False

        Dim DAMainGroup As OleDbDataAdapter = New OleDbDataAdapter("select * from tblMainGroup", connection)
        connection.Open()

        DAMainGroup.Fill(dataset, "tblMainGroup")

        connection.Close()

        Me.ListBox1.DataSource = dataset.Tables("tblMainGroup")
        Me.ListBox1.DisplayMember = "MaingroupDescription"
        Me.ListBox1.ValueMember = "MaingroupID"

        blnLoadListBox1 = True

    End Sub


    Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
        If blnLoadListBox1 = True Then

            Dim strSQLGroup As String
            Dim fout As String
            Dim dataset2 As DataSet = New DataSet()
            Try
                strSQLGroup = "SELECT GroupDescription FROM tblGroup where tblGroup.MainGroupID=" & Me.ListBox1.SelectedValue.ToString
                Dim DAGroup As OleDbDataAdapter = New OleDbDataAdapter(strSQLGroup, connection)
                DAGroup.Fill(dataset2, "tblGroup")
            Catch ex As Exception
                MsgBox(ex.ToString)
                fout = ex.ToString
            End Try
            Me.TextBox1.Text = fout

            Me.ListBox2.DataSource = dataset2.Tables("tblGroup")
            Me.ListBox2.DisplayMember = "GroupDescription"
        End If
    End Sub
End Class

you'll need to change your connection string. Is this what you want?

Hope it helps
Nick

[zozzie]

Thank you very much,

it really works!!!!!!!!!!!!!

I also tried to use one dataset; this works also but the values in listbox are adding up while the users selects a value in listbox1.

I think I have to clear the datatable "tblGroup" in the dataset, but the following code:

Me.dataset.Tables("tblGroup").Clear()

doesn't do the job.

Should I use something else??

Thanks,

Tom

[nswan]

try

Me.dataset.Tables("tblGroup").rows.clear

(i haven't tried it tho)

[zozzie]

WHen I try your suggestion, I get the following error message:

An unhandled exception of type 'System.NullReferenceException' occurred in TypedDatasets.exe

Additional information: Object reference not set to an instance of an object.

Hasn't it something to do with the fact that I use only one dataset???

Tom

PS: i placed your suggestion just before the try...catch block

[nswan]

Try
    dataset.Tables("tblGroup").Rows.Clear()
Catch
End Try

Try
    strSQLGroup = "SELECT GroupDescription FROM tblGroup where tblGroup.MainGroupID=" &  Me.ListBox1.SelectedValue.ToString
    Dim DAGroup As OleDbDataAdapter = New OleDbDataAdapter(strSQLGroup, connection)
    DAGroup.Fill(dataset, "tblGroup")
Catch ex As Exception
    MsgBox(ex.ToString)
    fout = ex.ToString
End Try
Me.TextBox1.Text = fout

Me.ListBox2.DataSource = dataset.Tables("tblGroup")
Me.ListBox2.DisplayMember = "GroupDescription"

not pretty but it works!!!

[zozzie]

Thanks for all your help, I really appreciate it.

Tom