Solved

VB .NET DataSet Problem

Posted on 2004-09-17
8
488 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
  • 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VS.net 2010 11 48
SqlServer no dupes 25 38
MailAddress in vb 4 29
Code enhancement 4 22
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

830 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