How to know if a record is empty or not, without using QueryString?

Hi,

I wrote two queries to search in three tables mp_parent, mp_page and mp_parent

The first one is:

SELECT mp_page.PID, mp_page.PageID, mp_page.PageContent, mp_page.ParID, mp_page.ChiID, mp_parent.ParentName FROM mp_page INNER JOIN mp_parent ON mp_page.ParID = mp_parent.ParentID

The second one is:

SELECT mp_page.PID, mp_page.PageID, mp_page.PageContent, mp_page.ParID, mp_page.ChiID, mp_child.ChildName FROM mp_page INNER JOIN mp_child ON mp_page.ChiID = mp_child.ChildID

I used this way to display the records in a FormView

public HttpContext context = HttpContext.Current;
public void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
ViewState["srch"] = context.Items["srch"];
}
FormView1.DataSource = GetTable();
FormView1.DataBind();
}

private DataTable GetTable()
{
SqlConnection SqlCon = new SqlConnection("Data Source=AJ-166DCCD87;Initial Catalog=mp;Integrated Security=True;Pooling=False");
String SQL1 = "select mp_page.PID, mp_page.PageID, mp_page.PageContent, mp_page.ParID, mp_page.ChiID, mp_parent.ParentID, mp_parent.ParentName from mp_page INNER JOIN mp_parent ON mp_page.ParID = mp_parent.ParentID where PageContent like '%" + Convert.ToString(ViewState["srch"]) + "%'";
SqlDataAdapter Adptr = new SqlDataAdapter(SQL1, SqlCon);
SqlCommandBuilder CB = new SqlCommandBuilder(Adptr);
DataTable Dt = new DataTable();
Adptr.Fill(Dt);
return Dt;
SqlCon.Close();
}

the question is how can I check if one record is empty to witch to another query ??

Is it possible to know without using QueryString?

Thank you

If you are using Sql Server, or another db that supports stored procedures, you can move all of it to one of those, then use If Exists (Query1) Else (Query2). This would also protect you from SQL Injection, which, at the moment, since you are passing raw user input into your SQL, you are seriously at the risk of being the victim of.

If someone searched for "apples';Drop Table mp_page;--" with your current SQL, bang goes your table.

|||

Is there another way instead of using stored procedure ?

|||

Parameterized Queries will also work.

|||

Would you please give me any example to explain more.

Thank you,

|||

Yes. You can run the first query from your page, then examine the results in your code. If there are some, display them, otherwise run the second query. If you are going to do this, look at using parameters in your code. You should never accept user input directly into your SQL.

[Edit]

There is another way too. Run both queries at the same time - separated with a semicolon (;) and return them in a dataset. The first result will be in the first datatable, and the second in the second. If there are no rows in the first, display the second one.