• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 499
  • Last Modified:

VB .NET DataSet Problem

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
regisdaniel
Asked:
regisdaniel
  • 5
  • 3
1 Solution
 
tomasX2Commented:
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
 
regisdanielAuthor Commented:
Thanks tomasX2, but, supose that i want to work only with one adapter and one dataset. How should I use?

Thanks!
0
 
tomasX2Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
tomasX2Commented:
when you do your second fill the dataadapter does not delete your first table... but creates another table.
0
 
regisdanielAuthor Commented:
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
 
tomasX2Commented:
yes ... if you do a Clear() the rows are deleted but the table definitions remain.

to delete a tabel

ds.Tables.RemoveAt(0);
0
 
tomasX2Commented:
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
 
regisdanielAuthor Commented:
Ok! This is what I was looking for.
Thanks!
0

Featured Post

Technology Partners: 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!

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now