CODEWITHSUNDEEP
c#asp.netms-access
Jeremiah
Jeremiah

Reputation: 5

Check if Column Value Exists in Dataset using C# and ASP

I've tried various solutions I have found and either I don't know how to implement them properly or they simply won't work. I have a method that allows someone to search a table for a specific order number, then the rest of the row will display in a gridview. However, if an order number is entered that doesn't exist in the table then I can get server error/exception. How can I make it so that before the search goes through or while the search goes through, if an order number that does't exist in the database is searched for then I can create the error instead?

I am using an ms access database, C#, and ASP.

Here is some of the code I am working with:

the method for searching the order table: 

public static dsOrder SearchOrder(string database, string orderNum)
{
    dsOrder DS;
    OleDbConnection sqlConn;
    OleDbDataAdapter sqlDA;

    sqlConn = new OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + database);

    DS = new dsOrder();

    sqlDA = new OleDbDataAdapter("select * from [Order] where order_num='" + orderNum + "'" , sqlConn);

    sqlDA.Fill(DS.Order);

    return DS;
}

And using that method:

protected void btnSearch_Click(object sender, EventArgs e)
{
    Session["OrderNum"] = txtSearch.Text;
    Session["ddl"] = ddlSearch.Text;


    if (Session["ddl"].ToString() == "Order")
    {
        dsOrder dataSet2;

        dataSet2 = Operations.SearchOrder(Server.MapPath("wsc_database.accdb"), Session["OrderNum"].ToString());

        grdSearch.DataSource = dataSet2.Tables["Order"];

        grdSearch.DataBind();

    }

Do I need to do a try/catch?

A huge thanks in advance to who is able to help me!

Upvotes: 0

Views: 4461

Answers (2)

ron tornambe
ron tornambe

Reputation: 10780

I use a different approach when filling data grids and always use parameters as follows:

public static DataTable GetGridDatasource(string database, string ordnum) {
  using (OleDbConnection con = new OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source=" + database))
  {
    con.Open();
    OleDbCommand cmd = con.CreateCommand();
    cmd.CommandText = "select * from [Order] where order_num=[OrderNumber]";
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue("OrderNumber", ordnum);
    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);
    return dt;
  }
}
protected void btnSearch_Click(object sender, EventArgs e)
{
  Session["OrderNum"] = txtSearch.Text;
  Session["ddl"] = ddlSearch.Text;


  if (Session["ddl"].ToString() == "Order")
  {
    grdSearch.DataSource = GetGridDatasource(Server.MapPath("wsc_database.accdb"), Session["OrderNum"].ToString());

  }
}

Upvotes: 0

fenix2222
fenix2222

Reputation: 4730

You can simply do a check to see whether DataSet is empty

if (dataSet2 == null || dataSet2.Tables.Count == 0 || dataSet2.Tables["Order"] == null || dataSet2.Tables["Order"].Rows.Count == 0)
{
    //display error to user
}
else
{
    // your code to populate grid 
}

If you don't want to show error then just put this check before populating GridView

if (dataSet2.Tables != null && dataSet2.Tables["Order"] != null)
{
    // your code to populate grid
}

Upvotes: 2

Related Questions