Solved

VB .NET DataSet Problem

Posted on 2004-09-17
8
490 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
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!

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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

688 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