Anon Anon - 3 months ago 9
SQL Question

How do I Inform user that an element does not exist in a table?

I have a get details form, and I know that using try and catch as a way of validation here is bad practice. How would I check to see if the CustID exists and then tell the user that what they entered does not exist?

Apologies if this is a silly question and it's obvious and..., I'm a beginner.

public void getdetails()
{
lblMessage.Text = "";
if (txtCID.Text == "")
{
lblMessage.Text = "Please enter a Customer ID before obtaining details.";
}
else
{
command.Connection.Open();
command.Connection = conn;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "GetCustomer";
SqlParameter param = new SqlParameter();
param.ParameterName = "@CustID";
param.SqlDbType = SqlDbType.Int;
param.Direction = ParameterDirection.Input;
param.Value = txtCID.Text;

command.Parameters.Add(param);
adapter.SelectCommand = command;
adapter.Fill(table);

txtFName.Text = table.Rows[0].Field<string>("FirstName");
txtFName.DataBind();
txtLName.Text = table.Rows[0].Field<string>("Surname");
txtLName.DataBind();
rdoGender.Text = table.Rows[0].Field<string>("Gender").ToString();
txtAge.DataBind();
txtAge.Text = table.Rows[0].Field<int>("Age").ToString();
txtAge.DataBind();
txtAdd1.Text = table.Rows[0].Field<string>("Address1").ToString();
txtAge.DataBind();
txtAdd2.Text = table.Rows[0].Field<string>("Address2").ToString();
txtAge.DataBind();
txtCity.Text = table.Rows[0].Field<string>("City").ToString();
txtAge.DataBind();
txtPhone.Text = table.Rows[0].Field<string>("Phone").ToString();
txtAge.DataBind();
txtMobile.Text = table.Rows[0].Field<string>("Mobile").ToString();
txtAge.DataBind();
txtEmail.Text = table.Rows[0].Field<string>("Email").ToString();
txtEmail.DataBind();

command.Connection.Close();
}
}

Answer

I'm not sure I fully understood your question. What I would do is:

public bool getdetails()
{
    bool found = false;
    int id;
    bool isnumber;
    lblMessage.Text = "";
    isnumber = int.TryParse(txtCID.Text, out id);
    if (!isnumber)
    {
        lblMessage.Text = "Please enter a valid Customer ID before obtaining details.";
    }
    else
    {
        command.Connection.Open();
        command.Connection = conn;                
        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = "GetCustomer";
        SqlParameter param = new SqlParameter();
        param.ParameterName = "@CustID";
        param.SqlDbType = SqlDbType.Int;
        param.Direction = ParameterDirection.Input;
        param.Value = id;

        command.Parameters.Add(param);
        adapter.SelectCommand = command;
        adapter.Fill(table);

        if (table.Rows.Count > 0)
        {
          txtFName.Text = table.Rows[0].Field<string>("FirstName");
          txtFName.DataBind();
          txtLName.Text = table.Rows[0].Field<string>("Surname");
          txtLName.DataBind();
          rdoGender.Text = table.Rows[0].Field<string>("Gender").ToString();
          txtAge.DataBind();
          txtAge.Text = table.Rows[0].Field<int>("Age").ToString();
          txtAge.DataBind();
          txtAdd1.Text = table.Rows[0].Field<string>("Address1").ToString();
          txtAge.DataBind();
          txtAdd2.Text = table.Rows[0].Field<string>("Address2").ToString();
          txtAge.DataBind();
          txtCity.Text = table.Rows[0].Field<string>("City").ToString();
          txtAge.DataBind();
          txtPhone.Text = table.Rows[0].Field<string>("Phone").ToString();
          txtAge.DataBind();
          txtMobile.Text = table.Rows[0].Field<string>("Mobile").ToString();
          txtAge.DataBind();
          txtEmail.Text = table.Rows[0].Field<string>("Email").ToString();
          txtEmail.DataBind();
          found = true;
        }
        else
        {
           lblMessage.Text = "User with ID " + id + " does not exists";
        }
        command.Connection.Close();
    }
    return found;
}

The function will return false if either the id is not specified or does not exist. Another problem is that you don't check if txtCID.Text contains a valid number: in this case a SQL error is would thrown! I added a number conversion check that ensures that at least the stored procedure execution runs without errors. Anyway, you should wrap the whole procedure in a try-catch to intercept any unpredictable error (db offline or internal db error, etc). Then, I use table.Rows.Count to verify if the stored procedure returned a result.

Mario.

Comments