retriving data from 2 views by the value in a textbox

i have a textbox which a user enters a numeric value

i want it to use SqlDataSource and check if the value exists in any of the tables.

in my text box the users would enter starting from '100000' or '200000'

i want it to check the view that starts the # with '100000' and 2ed view starts '200000'

With this i can check in one of the tables and make the selection.

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:imacstestConnectionString %>"

SelectCommand="SELECT [ReportNumber] FROM [AppraisalSummaryBlue] WHERE ([ReportNumber] = @.ReportNumber)">

<SelectParameters>

<asp:ControlParameterControlID="txtReport"Name="ReportNumber"PropertyName="Text"

Type="String"/>

</SelectParameters>

</asp:SqlDataSource>

How can i make this possible ?

i was thinking putting a second sqldatasource and have that check the second view but how can i make the textbox goto the correct selectcommand ?

You'll need to do a conditional if in your select command:

IF @.ReportNumber > 10000 BEGINSELECT [ReportNumber] FROM [AppraisalSummaryBlue] WHERE ([ReportNumber] = @.ReportNumber) END ELSE BEGINSELECT [ReportNumber] FROM [AppraisalSummaryTHEOTHERTABLE] WHERE ([ReportNumber] = @.ReportNumber) END

Retrieve data from another table in subquery

Hi there, here is my current query:

sqlTemp = "SELECT id,code,description FROM tbl_content " &_
"WHERE EXISTS " &_
"(SELECT * FROM tbl_published_content " &_
"WHERE tbl_content.id = tbl_published_content.id ORDER BY tbl_published_content.nCorrectOrder)"

I don't get results sorted by tbl_published_content.nCorrectOrder, and have also no iea how to retrieve that values.

Please can any one tell me how can I have access to tbl_published_content.nCorrectOrder and get it sorted by this field?

So I can use objRS.("tbl_published_content.nCorrectOrder") or some alias?

thanks a million!Try this instead
select
[id]
,code
,description
from tbl_content tc join
tbl_published_content tpc
on tc.[id] = tpc.[id]
order by tpc.ncorrectorder

now the correct way to do this is to create a stored procedure
then execute the procedure from your client
your clients will thank you.|||Great! thanks :)

I forgot to mention it was for a JetSQL database, so here is the final query if anyone is interested

sqlTemp = "SELECT tc.id,tc.code,tc.description,tpc.nCorrectOrder FROM tbl_content tc INNER JOIN " &_
"tbl_published_content tpc on tc.id = tpc.id " &_
"ORDER BY tpc.nCorrectOrder"

Best regards, Eth.