how to pass the result of one query in other query in vb.net

[rhepsi]

Hii all,

i have 2 queries, query1 and query 2

Query1 is selecting from results of Query2... is it possible???

Can somebody provide with code snippets in VB.NET to call such queries.


thnx..

[radcaesar]

Its not vb.net its general SQL Sub query

SELECT * FROM employees
WHERE id =
(SELECT EmployeeID FROM invoices WHERE EmployeeID='E0006');

[rhepsi]

Its not vb.net its general SQL Sub query

SELECT * FROM employees
WHERE id =
(SELECT EmployeeID FROM invoices WHERE EmployeeID='E0006');

thnx,..
i wud be more grateful if u can solve my queries

query 1)

Dim myselectquery As String = "SELECT pr_project_id_pk FROM tbl_projects WHERE tbl_projects.pr_project_location = ?PName "

cmSQL.Parameters.Add("?PName", objListItem)

query 2)

Dim myselectquerya As String = "SELECT sta_category_id_fk FROM tbl_staff_assignments WHERE tbl_staff_assignments.sta_project_id_fk =<<i need the result of query 1>> tbl_projects.pr_project_id_pk = ?CID "

cmSQL.Parameters.Add("?CID", 1)

thnx ...

[Vidhura]

thnx,..
i wud be more grateful if u can solve my queries

query 1)

Dim myselectquery As String = "SELECT pr_project_id_pk FROM tbl_projects WHERE tbl_projects.pr_project_location = ?PName "

cmSQL.Parameters.Add("?PName", objListItem)

query 2)

Dim myselectquerya As String = "SELECT sta_category_id_fk FROM tbl_staff_assignments WHERE tbl_staff_assignments.sta_project_id_fk =<<i need the result of query 1>> tbl_projects.pr_project_id_pk = ?CID "

cmSQL.Parameters.Add("?CID", 1)

thnx ...

Dim myselectquerya As String = "SELECT sta_category_id_fk FROM tbl_staff_assignments WHERE tbl_staff_assignments.sta_project_id_fk =(SELECT pr_project_id_pk FROM tbl_projects WHERE tbl_projects.pr_project_location = ?PName ) and tbl_projects.pr_project_id_pk = ?CID "

[gagandeepgupta16]

thnx,..
i wud be more grateful if u can solve my queries

query 1)

Dim myselectquery As String = "SELECT pr_project_id_pk FROM tbl_projects WHERE tbl_projects.pr_project_location = ?PName "

cmSQL.Parameters.Add("?PName", objListItem)

query 2)

Dim myselectquerya As String = "SELECT sta_category_id_fk FROM tbl_staff_assignments WHERE tbl_staff_assignments.sta_project_id_fk =<<i need the result of query 1>> tbl_projects.pr_project_id_pk = ?CID "

cmSQL.Parameters.Add("?CID", 1)

thnx ...

query 2)

Dim myselectquerya As String = "SELECT sta_category_id_fk FROM tbl_staff_assignments WHERE tbl_staff_assignments.sta_project_id_fk =(SELECT pr_project_id_pk FROM tbl_projects WHERE tbl_projects.pr_project_location = ?PName ) tbl_projects.pr_project_id_pk = ?CID "

cmSQL.Parameters.Add("?PName", objListItem)
cmSQL.Parameters.Add("?CID", 1)

hope this helps you.

[rhepsi]

query 2)

Dim myselectquerya As String = "SELECT sta_category_id_fk FROM tbl_staff_assignments WHERE tbl_staff_assignments.sta_project_id_fk =(SELECT pr_project_id_pk FROM tbl_projects WHERE tbl_projects.pr_project_location = ?PName ) tbl_projects.pr_project_id_pk = ?CID "

cmSQL.Parameters.Add("?PName", objListItem)
cmSQL.Parameters.Add("?CID", 1)

hope this helps you.

thnx so much tht helped me....

i have other doubr nw....



Try
cnSQL.Open()
drSQL = cmSQL.ExecuteReader()

If drSQL.HasRows = True Then

While drSQL.Read

If drSQL.Item("sta_category_id_fk ") = "?CID" Then

MsgBox("Manager available in this project.. select other project or insert new project")
Else
insert record


my doubt is near ( If drSQL.Item("sta_category_id_fk ") = "?CID" Then )


is this right??? im not getting the result..

plz help..
thnx

[gagandeepgupta16]

thnx so much tht helped me....

i have other doubr nw....



Try
cnSQL.Open()
drSQL = cmSQL.ExecuteReader()

If drSQL.HasRows = True Then

While drSQL.Read

If drSQL.Item("sta_category_id_fk ") = "?CID" Then

MsgBox("Manager available in this project.. select other project or insert new project")
Else
insert record


my doubt is near ( If drSQL.Item("sta_category_id_fk ") = "?CID" Then )


is this right??? im not getting the result..

plz help..
thnx

hi,

here you are using a datareader and can not use "?CID", you can directly compare it with the value like:
If drSQL.Item("sta_category_id_fk ") = CID Then