Characters found after end of SQL statement VB.NET, access

Question

0.00/5 (No votes)

See more:

How to combine multiple Select queries in VB.Net, Access? I have tried below codes. But its giving a result as Characters found after end of SQL statement

Or is there any other way to combine?

What I have tried:

Dim MyDataSet As New DataSet
RecExists = False
DataFilterCondition1 = " Where edate>=@Me.dtpAccountFrom1st.Value.Date And edate<=@Me.dtpAccountTo1st.Value.Date"

DataFilterCondition2 = " Where edate>=@Me.dtpAccountFrom2nd.Value.Date And edate<=@Me.dtpAccountTo2nd.Value.Date"

DataFilterCondition3 = " Where edate>=@Me.dtpAccountFrom3rd.Value.Date And edate<=@Me.dtpAccountTo3rd.Value.Date"

DataFilterCondition4 = " Where edate>=@Me.dtpAccountFrom4th.Value.Date And edate<=@Me.dtpAccountTo4th.Value.Date"

DataFilterCondition5 = " Where edate>=@Me.dtpAccountFrom1st.Value.Date And edate<=@Me.dtpAccountTo4th.Value.Date"

DataFilterQuery1 = "Select '1st Quarter' As TERM, 'PLEDGE' As PARTICULARS, Sum(amount) As AMOUNT, 0 As INTAMOUT, Sum(stock) As STOCK, Count(edate) As TOTALCOUNT FROM pledger " & DataFilterCondition1 & ""
        
DataFilterQuery2 = "Select '1st Quarter' As TERM, 'REDEMPTION' As PARTICULARS, Sum(pledgeramount) As AMOUNT, Sum(intamt) As INTAMOUT, Sum(stock) As STOCK, Count(edate) As TOTALCOUNT FROM redemption " & DataFilterCondition1 & ""

DataFilterQuery3 = "Select '2nd Quarter' As TERM, 'PLEDGE' As PARTICULARS, Sum(amount) As AMOUNT, 0 As INTAMOUT, Sum(stock) As STOCK, Count(edate) As TOTALCOUNT FROM pledger " & DataFilterCondition2 & ""
        
DataFilterQuery4 = "Select '2nd Quarter' As TERM, 'REDEMPTION' As PARTICULARS, Sum(pledgeramount) As AMOUNT, Sum(intamt) As INTAMOUT, Sum(stock) As STOCK, Count(edate) As TOTALCOUNT FROM redemption " & DataFilterCondition2 & ""

DataFilterQuery5 = "Select '3rd Quarter' As TERM, 'PLEDGE' As PARTICULARS, Sum(amount) As AMOUNT, 0 As INTAMOUT, Sum(stock) As STOCK, Count(edate) As TOTALCOUNT FROM pledger " & DataFilterCondition3 & ""
        
DataFilterQuery6 = "Select '3rd Quarter' As TERM, 'REDEMPTION' As PARTICULARS, Sum(pledgeramount) As AMOUNT, Sum(intamt) As INTAMOUT, Sum(stock) As STOCK, Count(edate) As TOTALCOUNT FROM redemption " & DataFilterCondition3 & ""

DataFilterQuery7 = "Select '4th Quarter' As TERM, 'PLEDGE' As PARTICULARS, Sum(amount) As AMOUNT, 0 As INTAMOUT, Sum(stock) As STOCK, Count(edate) As TOTALCOUNT FROM pledger " & DataFilterCondition4 & ""
        
DataFilterQuery8 = "Select '4th Quarter' As TERM, 'REDEMPTION' As PARTICULARS, Sum(pledgeramount) As AMOUNT, Sum(intamt) As INTAMOUT, Sum(stock) As STOCK, Count(edate) As TOTALCOUNT FROM redemption " & DataFilterCondition4 & ""

DataFilterQuery9 = "Select 'Yearly' As TERM, 'PLEDGE' As PARTICULARS, Sum(amount) As AMOUNT, 0 As INTAMOUT, Sum(stock) As STOCK, Count(edate) As TOTALCOUNT FROM pledger " & DataFilterCondition5 & ""
        
DataFilterQuery10 = "Select 'Yearly' As TERM, 'REDEMPTION' As PARTICULARS, Sum(pledgeramount) As AMOUNT, Sum(intamt) As INTAMOUT, Sum(stock) As STOCK, Count(edate) As TOTALCOUNT FROM redemption " & DataFilterCondition5 & ""

DataFilterQuery = String.Concat(DataFilterQuery1, ";", DataFilterQuery2, ";", DataFilterQuery3, DataFilterQuery4, ";", ....)

Using MyConnection As OleDb.OleDbConnection = MdlCommonCodes.GetConnection,
            MyDataAdapter As New OleDb.OleDbDataAdapter(DataFilterQuery, MyConnection)
If MyConnection.State = ConnectionState.Closed Then MyConnection.Open()
            MyDataAdapter.SelectCommand.Parameters.Add("@Me.dtpAccountFrom1st.Value.Date", OleDb.OleDbType.Date).Value = Me.dtpAccountFrom1st.Value.Date
            MyDataAdapter.SelectCommand.Parameters.Add("@Me.dtpAccountTo1st.Value.Date", OleDb.OleDbType.Date).Value = Me.dtpAccountTo1st.Value.Date
            MyDataAdapter.SelectCommand.Parameters.Add("@Me.dtpAccountFrom2nd.Value.Date", OleDb.OleDbType.Date).Value = Me.dtpAccountFrom2nd.Value.Date
            MyDataAdapter.SelectCommand.Parameters.Add("@Me.dtpAccountTo2nd.Value.Date", OleDb.OleDbType.Date).Value = Me.dtpAccountTo2nd.Value.Date
            MyDataAdapter.SelectCommand.Parameters.Add("@Me.dtpAccountFrom3rd.Value.Date", OleDb.OleDbType.Date).Value = Me.dtpAccountFrom3rd.Value.Date
            MyDataAdapter.SelectCommand.Parameters.Add("@Me.dtpAccountTo3rd.Value.Date", OleDb.OleDbType.Date).Value = Me.dtpAccountTo3rd.Value.Date
            MyDataAdapter.SelectCommand.Parameters.Add("@Me.dtpAccountFrom4th.Value.Date", OleDb.OleDbType.Date).Value = Me.dtpAccountFrom4th.Value.Date

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

MyDataAdapter.Fill(MyDataSet, 0)
MyDataSet.AcceptChanges()
Me.grdPledgerCountPreview.DataSource = MyDataSet.Tables(0)
End Using

Posted 20-Aug-20 16:58pm

Venu Gopal Mulavana Kayamkulam

Updated 20-Aug-20 18:21pm

v3

Add a Solution

2 solutions

Solution 1

You have to run each query separately. Access doesn't support multiple result sets.

Posted 20-Aug-20 17:48pm

Dave Kreskowiak

Comments

Venu Gopal Mulavana Kayamkulam 21-Aug-20 12:58pm

yes. access does't support multiple result set. Now I used Union in the query as per the Solution 2. anyway..... thanks a lot 2 help me.. seeks ur support in future also.

Dave Kreskowiak 21-Aug-20 13:00pm

UNIONS can work, but you better pay attention to the columns in the result set that is returned. You might be surprise by what you get back.

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)

Sandeep Mewara · Accepted Answer · 2020-08-20T18:22:00

Solution 2

Here, try this: Use a union query to combine multiple queries into a single result - Access[^]

Quote:
Sometimes you might want to list the records from one table or query with those from one or more other tables to form one set of records - a list with all the records from the two or more tables. This is the purpose of a union query in Access

Example:

SQL

SELECT Customers.Company, Customers.[Last Name], Customers.[First Name]
FROM Customers

UNION

SELECT Suppliers.Company, Suppliers.[Last Name], Suppliers.[First Name]
FROM Suppliers

ORDER BY [Last Name], [First Name];

Posted 20-Aug-20 18:22pm

Sandeep Mewara

Comments

Venu Gopal Mulavana Kayamkulam 21-Aug-20 13:00pm

Done as below!!!! thanks a lot from Kerala... (Y) :)

DataFilterQuery = DataFilterQuery1 + " Union " + DataFilterQuery2 + " Union " + DataFilterQuery3 + " Union " + DataFilterQuery4 _
+ " Union " + DataFilterQuery5 + " Union " + DataFilterQuery6 + " Union " + DataFilterQuery7 + " Union " + DataFilterQuery8 _
+ " Union " + DataFilterQuery9 + " Union " + DataFilterQuery10

DevCodeproject1 3-Oct-22 14:56pm

Can we use the same UNION for insert statements