Jump to content

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

Problema tensiune Generator Ford ...

Protejare sunca

Cum se procedeaza daca jandarmii ...
 Honor 70

Accident cu auto fara RCA (fara p...

Probleme arzator Ferroli SUN P7

Recomandare telefon in jur de 150...
 Întrebare despre banda de derulare

Atlantic Money

Ska-nk - Arata-i c-o iu…

Nu mi-au iesit analizele chiar ok.
 Hub Macbook Air M1 - 144hz HDMI

Pendulul Foucault

Golf 4 probleme difuzoare

Telecomanda StarLight
 

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,843
  • Î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,843
  • Î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,843
  • Î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,843
  • Î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,843
  • Î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,843
  • Î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,843
  • Înscris: 30.07.2003

View PostSargon, 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,843
  • Î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

Chirurgia spinală minim invazivă Chirurgia spinală minim invazivă

Chirurgia spinală minim invazivă oferă pacienților oportunitatea unui tratament eficient, permițându-le o recuperare ultra rapidă și nu în ultimul rând minimizând leziunile induse chirurgical.

Echipa noastră utilizează un spectru larg de tehnici minim invazive, din care enumerăm câteva: endoscopia cu variantele ei (transnazală, transtoracică, transmusculară, etc), microscopul operator, abordurile trans tubulare și nu în ultimul rând infiltrațiile la toate nivelurile coloanei vertebrale.

www.neurohope.ro

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