Solved

VB .NET DataSet Problem

Posted on 2004-09-17
8
478 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
 
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now