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