Chirurgia cranio-cerebrală minim invazivă
Tehnicile minim invazive impun utilizarea unei tehnologii ultramoderne. Endoscoapele operatorii de diverse tipuri, microscopul operator dedicat, neuronavigația, neuroelectrofiziologia, tehnicile avansate de anestezie, chirurgia cu pacientul treaz reprezintă armamentarium fără de care neurochirurgia prin "gaura cheii" nu ar fi posibilă. Folosind tehnicile de mai sus, tratăm un spectru larg de patologii cranio-cerebrale. 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