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 |
Interogare din interogare
Last Updated: Apr 10 2013 22:59, Started by
Sargon
, Apr 06 2013 08:39
·
0
#1
Posted 06 April 2013 - 08:39
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
Posted 06 April 2013 - 08:43
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
Posted 06 April 2013 - 09:16
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
Posted 06 April 2013 - 10:33
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
Posted 06 April 2013 - 15:37
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
Posted 06 April 2013 - 15:43
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
Posted 06 April 2013 - 15:58
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
Posted 06 April 2013 - 16:09
...) AS [Sum Products]
Cu paranteze patrate. Edited by neagu_laurentiu, 06 April 2013 - 16:10. |
#9
Posted 06 April 2013 - 17:11
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
Posted 06 April 2013 - 19:24
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
Posted 07 April 2013 - 00:11
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
Posted 07 April 2013 - 11:19
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
Posted 07 April 2013 - 11:30
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
Posted 07 April 2013 - 12:02
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
Posted 07 April 2013 - 12:28
|
#16
Posted 07 April 2013 - 16:25
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
Posted 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. |
#18
Posted 07 April 2013 - 18:27
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
▶ 0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users