Multiple select statement to a dataset is not working properly

Question

0.00/5 (No votes)

See more:

The specified code is not working perfectly. However,

VB

stock As PAYMENT

in

DataFilterQuery2

not showing in the result, if

PAYMENT

is lesser than 1

How it will be fix?

What I have tried:

VB

Dim MyDataSet As New DataSet

Dim StockBalance As Double = StockOpeningBalance

DataFilterCondition = " Where edate>=@Me.dtpAccountFrom.Value.Date And edate<=@Me.dtpAccountTo.Value.Date"

DataFilterQuery1 = "Select edate As EDATE, pledgerno As REFNO, stock As RECEIPT, 0 As PAYMENT, balance As BALANCE From pledger " & DataFilterCondition & " Order By edate, pledgerno"

DataFilterQuery2 = "Select edate As EDATE, pledgerno As REFNO, 0 As RECEIPT, stock As PAYMENT, balance As BALANCE FROM redemption " & DataFilterCondition & " Order By edate, pledgerno"

Using MyConnection As OleDb.OleDbConnection = MdlCommonCodes.GetConnection, MyDataAdapter As New OleDb.OleDbDataAdapter(DataFilterQuery1, MyConnection)

     MyDataAdapter.SelectCommand.Parameters.Add("@Me.dtpAccountFrom.Value.Date", OleDb.OleDbType.Date).Value = Me.dtpAccountFrom.Value.Date

     MyDataAdapter.SelectCommand.Parameters.Add("@Me.dtpAccountTo.Value.Date", OleDb.OleDbType.Date).Value = Me.dtpAccountTo.Value.Date

     MyDataAdapter.Fill(MyDataSet, 0)
     MyDataAdapter.SelectCommand.CommandText = DataFilterQuery2
     MyDataAdapter.Fill(MyDataSet, 0)
     Dim DataView As DataView = MyDataSet.Tables(0).DefaultView
     DataView.Sort = "edate"
     MyDataSet.AcceptChanges()
     Me.grdStockRegisterPreview.DataSource = MyDataSet.Tables(0)
End Using

Posted 6-May-20 22:16pm

Venu Gopal Mulavana Kayamkulam

Updated 7-May-20 2:57am

Add a Solution

Comments

Richard MacCutchan 7-May-20 5:51am

What do you mean "is not working properly"?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

phil.o · Accepted Answer · 2020-05-07T02:58:00

Solution 1

~~The Fill() method simply fills the whole table with data, it does not add to the table. Thus, every subsequent call replaces formerly added data.~~
You may need to combine both queries into a single one. The UNION statement would be perfect for that.
UNION operation (Microsoft Access SQL) | Microsoft Docs[^]
Use a union query to combine multiple queries into a single result - Access[^]

Posted 7-May-20 2:58am

phil.o

Updated 7-May-20 4:50am

v3

Comments

Richard Deeming 7-May-20 10:33am

Are you sure about that? :)

DataAdapter.Fill Method (System.Data.Common) | Microsoft Docs[^]
"You can use the Fill method multiple times on the same DataTable. If a primary key exists, incoming rows are merged with matching rows that already exist. If no primary key exists, incoming rows are appended to the DataTable."

Unless the DataAdapter is creating a primary key, the second call should simply append the new rows to the table.

phil.o 7-May-20 10:48am

It dates back a long time ago, I remember I had to combine my queries into a single one while using a DataAdapter because Fill method did not append data like I thought it would. But it seems problem must have been elsewhere. My bad.

Venu Gopal Mulavana Kayamkulam 7-May-20 13:10pm

Namasthe Richard.. as per ur answer, I have done it by UNION.
Thank you so much. :)