Interogare din interogare

Incerc sa mut o aplicatie din Access in VB 2010...  in Access am o interogare care selecteaza datele dintr-o alta interogare, nu direct dintr-un tabel. N-am nici cea mai vaga idee ce trebuie sa fac. Orice sfat este binevenit.

In VB trimiti interogarea (simpla/complexa) si primesti rezultatul. Da detalii ce faci tu acolo !
Faptul ca ai o "interogare din interogare" nu are relevanta in disctuia cu VB. Aceasta interogare o trimiti SGBD-ului si primesti rezultatul.

Edited by neagu_laurentiu, 06 April 2013 - 08:56.

In Access am o interogare care selecteaza datele dintr-o subinterogare:

  Prima interogare = Sum Products (selecteaza si insumeaza date din Tab1):

SELECT Sum(Tab1.Inputs) AS SumOfInputs, Sum(Tab1.ValInp) AS SumOfValInp, Sum(Tab1.Outputs) AS SumOfOutputs, Sum(Tab1.ValOut) AS SumOfValOut, Products.Product, Products.VAT, Products.UM
FROM Tab1 INNER JOIN Produse ON Tab1.ProductID = Products.ProductID
GROUP BY Products.Product, Products.VAT, Product.UM, Tab1.ProductID;

A doua inteorogare (selecteaza si calculeaza date din prima interogare):

SELECT Nz([SumOfInputs],0)-Nz([SumOfOutputs],0) AS Stoc, Nz([SumOfValInp],0)-Nz([SumOfValOut],0) AS ValStoc, IIf([Stoc]=0,0,([ValStoc]/[Stoc])) AS CMP, [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM
FROM [Sum Products]
GROUP BY [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM
HAVING (((Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))>0.09 Or (Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))<-0.09));

Am incercat sa le combin astfel incit sa obtin o singura interogare, dar nu am reusit.

Ma gindesc ca in VB 2010 ar trebui sa existe vreo posibilitate sa selectez datele dintr-o alta interogare, dar nu am nici cea mai vaga idee.

Ce baza de date folosesti acum in VB ?
Toata problema ta tine de "databases" si nu de VB ! Probabil si in Acces amesteci notiunea de front-end cu SGBD-ul.

Ca idee generica de baze de date:
SELECT * FROM (SELECT * FROM tbl1 WHERE conditie [alte clauze]) AS tbl2 WHERE conditie [alte clauze]

Sau poti avea tabele temporare daca nu merge direct.

Mai nou ai LINQ ce poti face prelucrare peste un DataTable din VB (dar nu e cazul pt. ce vrei tu).

Si nu in ultimul rand citeste cat mai mult despre instrumentul ce-l folosesti, sunt concepte ce nu se descriu pe formum din lipsa de timp/spatiu, sunt carti intregi despre asa ceva. Aici punctul o problema, nu teoria de la zero.

Edited by neagu_laurentiu, 06 April 2013 - 10:40.

In Access, FrontEnd-ul este reprezentat de From-uri si Rapoarte, SGBT-ul de Tabele. Folosesc aceeasi baza de date.

Am incercat sa combin cele doua interogari astfel : SELECT * FROM (SELECT * FROM tbl1 WHERE conditie [alte clauze]) AS tbl2 WHERE conditie [alte clauze] , dar nu am reusit...
E prima data cind aud de tabele temporare...

"[...]sunt carti intregi despre asa ceva"

Recomanda si tu o carte d-asta

Edited by Sargon, 06 April 2013 - 15:40.

CREATE [TEMPORARY] TABLE table ...
http://msdn.microsof...office.12).aspx

Posteaza exact ce ai incercat, tipul de cerere de care am vorbit e suportat in Access.

Amazon-ul e plin de carti.

Carti si tutoriale video de VB 2008-2010 si Access am destule, dar unde sa caut? :-)


SELECT
Nz([SumOfInputs],0)-Nz([SumOfOutputs],0) AS Stoc, Nz([SumOfValInp],0)-Nz([SumOfValOut],0) AS ValStoc, IIf([Stoc]=0,0,([ValStoc]/[Stoc])) AS CMP, [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM
FROM

(
SELECT Sum(Tab1.Inputs) AS SumOfInputs, Sum(Tab1.ValInp) AS SumOfValInp, Sum(Tab1.Outputs) AS SumOfOutputs, Sum(Tab1.ValOut) AS SumOfValOut, Products.Product, Products.VAT, Products.UM
FROM Tab1 INNER JOIN Produse ON Tab1.ProductID = Products.ProductID
GROUP BY Products.Product, Products.VAT, Product.UM, Tab1.ProductID;
) AS Sum Products
GROUP BY [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM
HAVING (((Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))>0.09 Or (Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))<-0.09));

Syntax Error in FROM Clause

Edited by Sargon, 06 April 2013 - 15:59.

...) AS [Sum Products]

Cu paranteze patrate.

Edited by neagu_laurentiu, 06 April 2013 - 16:10.

Gata, a mers.! Am pus parantezele drepte si am sters ";" din fata parantezei:

GROUP BY Products.Product, Products.VAT, Product.UM, Tab1.ProductID;
)

Multumesc mult! Sincer, fara ajutorul tau, nu as fi reusit. Iti sint recunoscator.

Edited by Sargon, 06 April 2013 - 17:14.

Laurentiu, am testat interogarea in Access si a mers, dar cind am incercat sa o introduc in VB/StocDataSet/table adapter/query/command text  nu a vrut sa o accepte.

Practic, vreau sa creez un report care sa afiseze datele selectate si calculate in de aceasta interogare. In Access am reusit, e foarte usor...

Care-i codul in VB cu pricina ? VB nu are ce sa nu accepte, el trimite comanda la "server" si Access-ul (dll-urile sale/driverul/Microsoft Jet Database Engine, aici nu avem server sql in adevaratul sens al cuvantului) o executa si returneaza datele iar VB primeste rezultatul. VB e "prost" la afacerea asta, adica intermediar.

Edited by neagu_laurentiu, 07 April 2013 - 00:20.

Private Sub frmStoc_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    Dim oCmd As SqlClient.SqlCommand
    Dim oDR As SqlClient.SqlDataReader
    Dim strSQL As String
    Dim strConn As String
    strConn = "Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Sargon\Desktop\Database1.accdb"
    strSQL = "SELECT Nz([SumOfInputs],0)-Nz([SumOfOutputs],0) AS Stoc, Nz([SumOfValInp],0)-Nz([SumOfValOut],0) AS ValStoc, IIf([Stoc]=0,0,([ValStoc]/[Stoc])) AS CMP, [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM"
    strSQL = "FROM (SELECT Sum(Tab1.Inputs) AS SumOfInputs, Sum(Tab1.ValInp) AS SumOfValInp, Sum(Tab1.Outputs) AS SumOfOutputs, Sum(Tab1.ValOut) AS SumOfValOut, Products.Product, Products.VAT, Products.UM
FROM Tab1 INNER JOIN Produse ON Tab1.ProductID = Products.ProductID
GROUP BY Products.Product, Products.VAT, Product.UM, Tab1.ProductID)  AS [Sum Products]"
    strSQL = "GROUP BY [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM"
    strSQL = "HAVING (((Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))>0.09 Or (Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))<-0.09));"
    Try
    oCmd = New SqlClient.SqlCommand()
    With oCmd
    .Connection = New SqlClient.SqlConnection(strConn)
    .Connection.Open()
    .CommandText = strSQL
    oDR = .ExecuteReader()
    End With
    If oDR.Read() Then
    With oDR
    txtProductID.Text = .Item("ProductID").ToString()
    txtProduct.Text = .Item("Product").ToString()
    txtStoc.Text = .Item("Stoc").ToString()
    txtCMP.Text = .Item("CMP").ToString()
    txtValStoc.Text = .Item("ValStoc").ToString()
    End With
    End If
    oDR.Close()
    oCmd.Connection.Close()
    Catch oException As Exception
    MessageBox.Show(oException.Message)
    End Try
    End Sub

Pai si ce mai ai in strSQL daca tot ii dai in cap prin umplere cu alte valori !

Si vezi ca SqlClient.* sunt clase specifice conexiunilor la SQL Server nu Access !

Edited by neagu_laurentiu, 07 April 2013 - 11:22.

OK, deci, cum fac? strSQL e bine acum?

Private Sub frmStoc_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
  
    Dim strSQL As String
    Dim strConn As String
    strConn = "Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Sargon\Desktop\Database1.accdb"
    strSQL = "SELECT Nz([SumOfInputs],0)-Nz([SumOfOutputs],0) AS Stoc, Nz([SumOfValInp],0)-Nz([SumOfValOut],0) AS ValStoc, IIf([Stoc]=0,0,([ValStoc]/[Stoc])) AS CMP, [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM
FROM (SELECT Sum(Tab1.Inputs) AS SumOfInputs, Sum(Tab1.ValInp) AS SumOfValInp, Sum(Tab1.Outputs) AS SumOfOutputs, Sum(Tab1.ValOut) AS SumOfValOut, Products.Product, Products.VAT, Products.UM
FROM Tab1 INNER JOIN Produse ON Tab1.ProductID = Products.ProductID
GROUP BY Products.Product, Products.VAT, Product.UM, Tab1.ProductID)  AS [Sum Products]
   GROUP BY [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM"
    HAVING (((Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))>0.09 Or (Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))<-0.09));"
  
    End Sub

Sargon, on 07 aprilie 2013 - 12:02, said:

Intre cele doua linii nu are ce cauta ".

Bun, dar cum populez un datagridview cu informatiile obtinute de aceasta interogare?

Private Sub frmStoc_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

Dim strSQL As String
Dim strConn As String

Dim rs As Object

Dim objDataSet As New DataSet()

strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Sargon\Desktop\Database1.accdb"
strSQL = "SELECT Nz([SumOfInputs],0)-Nz([SumOfOutputs],0) AS Stoc, Nz([SumOfValInp],0)-Nz([SumOfValOut],0) AS ValStoc, IIf([Stoc]=0,0,([ValStoc]/[Stoc])) AS CMP, [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM
FROM (SELECT Sum(Tab1.Inputs) AS SumOfInputs, Sum(Tab1.ValInp) AS SumOfValInp, Sum(Tab1.Outputs) AS SumOfOutputs, Sum(Tab1.ValOut) AS SumOfValOut, Products.Product, Products.VAT, Products.UM
FROM Tab1 INNER JOIN Produse ON Tab1.ProductID = Products.ProductID
GROUP BY Products.Product, Products.VAT, Product.UM, Tab1.ProductID)  AS [Sum Products]
   GROUP BY [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM
HAVING (((Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))>0.09 Or (Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))<-0.09));"

DataGridView1.DataSource = strSQL
   ' DataGridView1.DataMember = "ProductID"
DataGridView1.Refresh()

Edited by Sargon, 07 April 2013 - 16:29.

Pai nu asa ! Stabilesti conexiunea, dai comanda, umpli DataTable si pe acesta din urma il dai gridului.
MSDN e plin de exemple cu ADO.NET (ca sa nu mai zic de carti si tutoriale pe net).

Edited by neagu_laurentiu, 07 April 2013 - 16:31.

Am gasit un exemplu , aici:
[ https://www.youtube-nocookie.com/embed/x090vGtL2Qs?feature=oembed - Pentru incarcare in pagina (embed) Click aici ]

Imports System.Data.OleDb

Public Class frmStoc

    Dim Con As New OleDbConnection

    Private Sub frmStoc_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
  

    Con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Sargon\Desktop\Database1.accdb"
    Con.Open()

  
    datagridShow()
    End Sub
    Private Sub datagridShow()

    Dim ds As New DataSet
    Dim dt As New DataTable

    ds.Tables.Add(dt)
    Dim da As New OleDbDataAdapter
    da = New OleDbDataAdapter("SELECT Nz([SumOfInputs],0)-Nz([SumOfOutputs],0) AS Stoc, Nz([SumOfValInp],0)-Nz([SumOfValOut],0) AS ValStoc, IIf([Stoc]=0,0,([ValStoc]/[Stoc])) AS CMP, [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM
FROM (SELECT Sum(Tab1.Inputs) AS SumOfInputs, Sum(Tab1.ValInp) AS SumOfValInp, Sum(Tab1.Outputs) AS SumOfOutputs, Sum(Tab1.ValOut) AS SumOfValOut, Products.Product, Products.VAT, Products.UM
FROM Tab1 INNER JOIN Produse ON Tab1.ProductID = Products.ProductID
GROUP BY Products.Product, Products.VAT, Product.UM, Tab1.ProductID)  AS [Sum Products]
   GROUP BY [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM
HAVING (((Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))>0.09 Or (Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))<-0.09));", Con)
    da.Fill(dt)
    DataGridView1.DataSource = dt.DefaultView

    Con.Close()
    End Sub

End Class

Dar imi da o eroare:  OleDbException was unhandled ; "Undefined function 'Nz' in expression"  pentru  da.Fill(dt)