?
Solved

VB .NET DataSet Problem

Posted on 2004-09-17
8
Medium Priority
?
491 Views
Last Modified: 2012-08-14
Hy all!

I need to query somes records from a table, and after working with then, i need to make another query on another table.

I trying to use the same objects to make this, but, when i will fill the dataset by the 2nd time, it show me that there are records on it, but i can't see them.
I dont forget clearing the dataset before filling it again.

Now, if i destroy the dataset and create it again, everything works fine.

Can anybody tell me what is going wrong?

Thanks!

Private Sub Test()

'Objetos necessários
Dim DbAdapter As New OleDb.OleDbDataAdapter
Dim DbCommand As New OleDb.OleDbCommand
Dim DbDataSet As New System.Data.DataSet
Dim DbDataTable As DataTable

'FIRST QUERY...
DbCommand.Connection = DbConnection
DbCommand.CommandText = "SELECT CODCAMPANHA, C.NOME, DTINICIO, DTTERMINO, MECANICA, OBS, DTAPURACAO, CODFUNCAPURA || ' - ' || E.NOME FROM PCCAMPC C, PCEMPR E WHERE E.MATRICULA(+) = C.CODFUNCAPURA AND CODCAMPANHA = ?"
DbCommand.Parameters.Clear()
DbCommand.Parameters.Add("CodCampanha", lstCampanhas.SelectedItems(0).Text)

DbAdapter.SelectCommand = DbCommand
DbDataSet.Clear()
DbAdapter.Fill(DbDataSet)
DbDataTable = DbDataSet.Tables(0)

'WORKING WITH THE RECORDS...
'...
'END WORKING WHITH 1ST RECORDS


'2ND QUERY

DbCommand.Parameters.Clear()
DbCommand.CommandText = "SELECT * from pcusuari"

DbAdapter.SelectCommand = DbCommand

DbDataSet = New System.Data.DataSet 'IF I DON'T DO THIS, THE PROBLEM APPEAR...

DbDataSet.Clear()
DbAdapter.Fill(DbDataSet)
DbDataTable = DbDataSet.Tables(0)

'WORKING WITH THE RECORDS...
'...

End Sub
0
Comment
Question by:regisdaniel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 5

Expert Comment

by:tomasX2
ID: 12089382
Hello regis daniel

well if i understand you correctly this is what you might want to do.

points of interest are marked with a ####

But I thind your main problem was that you were doing this at the end

DbDataSet.Clear()
DbAdapter.Fill(DbDataSet)
DbDataTable = DbDataSet.Tables(0)  ' This should most likely be DbDataTable = DbDataSet.Tables(1)  0 not 1 because now the dataset has 2 table´s not 1.

'WORKING WITH THE RECORDS...
'...

End Sub


Create 2 data adapters.
You do not need to call DbDataSet.Clear()



Private Sub Test()

'Objetos necessários
Dim DbAdapter As New OleDb.OleDbDataAdapter
Dim DbAdapter2 As New OleDb.OleDbDataAdapter ' #### Create another adapter

Dim DbCommand As New OleDb.OleDbCommand
Dim DbCommand2 As New OleDb.OleDbCommand  ####  Create another command

Dim DbDataSet As New System.Data.DataSet

Dim DbDataTable As DataTable

'FIRST QUERY...
DbCommand.Connection = DbConnection
DbCommand.CommandText = "SELECT CODCAMPANHA, C.NOME, DTINICIO, DTTERMINO, MECANICA, OBS, DTAPURACAO, CODFUNCAPURA || ' - ' || E.NOME FROM PCCAMPC C, PCEMPR E WHERE E.MATRICULA(+) = C.CODFUNCAPURA AND CODCAMPANHA = ?"
' DbCommand.Parameters.Clear()' #### Don´t need to do this because the DbCommand.Parameters at this point is already cleared....
DbCommand.Parameters.Add("CodCampanha", lstCampanhas.SelectedItems(0).Text)

DbAdapter.SelectCommand = DbCommand

'DbDataSet.Clear()'####  Don´t need to do this in the first place! there are no parameters to begin with

DbAdapter.Fill(DbDataSet)

DbDataTable = DbDataSet.Tables(0)

'WORKING WITH THE RECORDS...
'2ND QUERY

'DbCommand.Parameters.Clear() ' #### Don´t need to do this because we are using another data adapter
DbCommand.CommandText = "SELECT * from pcusuari"

DbAdapter.SelectCommand = DbCommand

'DbDataSet = New System.Data.DataSet 'IF I DON'T DO THIS, THE PROBLEM APPEAR...' Now you don´t need to do this

DbAdapter2.SelectCommand = DbCommand2

DbAdapter.Fill(DbDataSet)
DbDataTable = DbDataSet.Tables(1) '

'WORKING WITH THE RECORDS...
'...

End Sub
0
 

Author Comment

by:regisdaniel
ID: 12089408
Thanks tomasX2, but, supose that i want to work only with one adapter and one dataset. How should I use?

Thanks!
0
 
LVL 5

Expert Comment

by:tomasX2
ID: 12089425
Your main problem is this...

first you do this....

'Objetos necessários
Dim DbAdapter As New OleDb.OleDbDataAdapter
Dim DbCommand As New OleDb.OleDbCommand
Dim DbDataSet As New System.Data.DataSet
Dim DbDataTable As DataTable

'FIRST QUERY...
DbCommand.Connection = DbConnection
DbCommand.CommandText = "SELECT CODCAMPANHA, C.NOME, DTINICIO, DTTERMINO, MECANICA, OBS, DTAPURACAO, CODFUNCAPURA || ' - ' || E.NOME FROM PCCAMPC C, PCEMPR E WHERE E.MATRICULA(+) = C.CODFUNCAPURA AND CODCAMPANHA = ?"
DbCommand.Parameters.Clear()
DbCommand.Parameters.Add("CodCampanha", lstCampanhas.SelectedItems(0).Text)

DbAdapter.SelectCommand = DbCommand
DbDataSet.Clear()
DbAdapter.Fill(DbDataSet)
DbDataTable = DbDataSet.Tables(0)' ***************************    


then you do this....


DbCommand.Parameters.Clear()
DbCommand.CommandText = "SELECT * from pcusuari"

DbAdapter.SelectCommand = DbCommand

DbDataSet = New System.Data.DataSet 'IF I DON'T DO THIS, THE PROBLEM APPEAR...

DbDataSet.Clear()
DbAdapter.Fill(DbDataSet)
DbDataTable = DbDataSet.Tables(0)   ' **************************   this should be DbDataTable = DbDataSet.Tables(1)   not 0
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 5

Expert Comment

by:tomasX2
ID: 12089433
when you do your second fill the dataadapter does not delete your first table... but creates another table.
0
 

Author Comment

by:regisdaniel
ID: 12089446
Thanks tomasX2, so, the clear method don't really clean all the dataset. The tables definitions remains on it, but all the rows is cleaned, corret?

If is, how can i erase one table if i need?

Thanks again!
0
 
LVL 5

Expert Comment

by:tomasX2
ID: 12089544
yes ... if you do a Clear() the rows are deleted but the table definitions remain.

to delete a tabel

ds.Tables.RemoveAt(0);
0
 
LVL 5

Accepted Solution

by:
tomasX2 earned 600 total points
ID: 12089548
so before you do

DbDataSet.Clear()
DbAdapter.Fill(DbDataSet)

do a DbDataSet.Tables.RemoveAt(0); and that will delete that table...


NB: I have not tried this, but good luck
0
 

Author Comment

by:regisdaniel
ID: 12090338
Ok! This is what I was looking for.
Thanks!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question