Jump to content

SUBIECTE NOI
« 1 / 5 »
RSS
Info Coronavirus/Vaccinare vs Fake News

Ce intelegeti din aceasta formula...

Passat B8 Variant 4motion 2.0 dsg

Problema aparat multifuncțio...
 Curea distributie slaba?

ZF vs DSG / 2.0 TDI vs B47

Lenovo - Google Meet microfon - ...

Plata montare mobila Ikea
 Incarcator laptop 19 V

Probleme mufa incarcare HP Pavilion

Sfaturi achiziție pentru inc...

Inocuitor dioda redresoare smd ha...
 Problema cu tuia, se usuca

2 invertoare la acelasi banc de b...

Stocare ieftina si consistenta pe...

Cu masina spre Portugalia
 

Interogare din interogare

- - - - -
  • Please log in to reply
25 replies to this topic

#1
Sargon

Sargon

    Junior Member

  • Grup: Members
  • Posts: 119
  • Înscris: 20.09.2007
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.

#2
neagu_laurentiu

neagu_laurentiu

    Guru Member

  • Grup: Senior Members
  • Posts: 39,724
  • Înscris: 30.07.2003
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.


#3
Sargon

Sargon

    Junior Member

  • Grup: Members
  • Posts: 119
  • Înscris: 20.09.2007
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.

#4
neagu_laurentiu

neagu_laurentiu

    Guru Member

  • Grup: Senior Members
  • Posts: 39,724
  • Înscris: 30.07.2003
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.


#5
Sargon

Sargon

    Junior Member

  • Grup: Members
  • Posts: 119
  • Înscris: 20.09.2007
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.


#6
neagu_laurentiu

neagu_laurentiu

    Guru Member

  • Grup: Senior Members
  • Posts: 39,724
  • Înscris: 30.07.2003
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.

#7
Sargon

Sargon

    Junior Member

  • Grup: Members
  • Posts: 119
  • Înscris: 20.09.2007
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.


#8
neagu_laurentiu

neagu_laurentiu

    Guru Member

  • Grup: Senior Members
  • Posts: 39,724
  • Înscris: 30.07.2003
...) AS [Sum Products]

Cu paranteze patrate.

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


#9
Sargon

Sargon

    Junior Member

  • Grup: Members
  • Posts: 119
  • Înscris: 20.09.2007
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.


#10
Sargon

Sargon

    Junior Member

  • Grup: Members
  • Posts: 119
  • Înscris: 20.09.2007
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...

#11
neagu_laurentiu

neagu_laurentiu

    Guru Member

  • Grup: Senior Members
  • Posts: 39,724
  • Înscris: 30.07.2003
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.


#12
Sargon

Sargon

    Junior Member

  • Grup: Members
  • Posts: 119
  • Înscris: 20.09.2007
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

#13
neagu_laurentiu

neagu_laurentiu

    Guru Member

  • Grup: Senior Members
  • Posts: 39,724
  • Înscris: 30.07.2003
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.


#14
Sargon

Sargon

    Junior Member

  • Grup: Members
  • Posts: 119
  • Înscris: 20.09.2007
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

#15
neagu_laurentiu

neagu_laurentiu

    Guru Member

  • Grup: Senior Members
  • Posts: 39,724
  • Înscris: 30.07.2003

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

Products].UM"
HAVING (((Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))>0.09 Or (Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))<-0.09));"
Intre cele doua linii nu are ce cauta ".

#16
Sargon

Sargon

    Junior Member

  • Grup: Members
  • Posts: 119
  • Înscris: 20.09.2007
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.


#17
neagu_laurentiu

neagu_laurentiu

    Guru Member

  • Grup: Senior Members
  • Posts: 39,724
  • Înscris: 30.07.2003
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.


#18
Sargon

Sargon

    Junior Member

  • Grup: Members
  • Posts: 119
  • Înscris: 20.09.2007
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)

Anunturi

Bun venit pe Forumul Softpedia!

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Forumul Softpedia foloseste "cookies" pentru a imbunatati experienta utilizatorilor Accept
Pentru detalii si optiuni legate de cookies si datele personale, consultati Politica de utilizare cookies si Politica de confidentialitate