How will I execute another SELECT command after I executed the first one?

Question

    protected void Button1_Click(object sender, EventArgs e)
    {
        string client = TextBox1.Text;
        string selected = RadioButtonList1.SelectedValue;
        string calendar = Calendar1.SelectedDate.ToShortDateString();
        string disease = txtDisease.Text;

        SqlCommand insert = new SqlCommand("insert into Appointment(Client_ID, DateofAppointment, TimeofAppointment, Disease) values(@Client_ID, @DateofAppointment, @TimeofAppointment, @Disease)", conn);
        try
        {
            conn.Open();

            SqlCommand cmd = new SqlCommand("select * from Client where Client_ID = @Client_ID", conn);
            cmd.Parameters.AddWithValue("@Client_ID", TextBox1.Text);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            if (dt.Rows.Count > 0)
            {       
                    insert.Parameters.AddWithValue("@Client_ID", client);
                    insert.Parameters.AddWithValue("@DateofAppointment", calendar);
                    insert.Parameters.AddWithValue("@TimeofAppointment", selected);
                    insert.Parameters.AddWithValue("@Disease", disease);
                    insert.ExecuteNonQuery();
                    TextBox1.Text = "";
                    RadioButtonList1.SelectedIndex = -1;
                    txtDisease.Text = "";
                    ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('Appointment submitted! Please wait for your SMS confirmation. Thank you!')</script>");
            }
            else
            {
                TextBox1.Text = "";
                RadioButtonList1.SelectedIndex = -1;
                txtDisease.Text = "";
                ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('You are not a registered client!')</script>");
            }
        }
        catch
        {
            conn.Close();
        }

    }


}

I want to create another command to search if the date and tie is already taken before inserting it to my db.

Answer 1

Not sure what your original exception was but it is helpful to include that when posting a question.

Also about your connection, class level connections are usually not recommended. Sql Server handles connection pooling for you so best practice is to dispose your connection as soon as you are done using it. When you want to requery (in another method or class) then create a new connection. Also wrap your connections in using blocks to ensure they are closed and disposed.

And regarding your date, it is best to always persist data in its native type. This means that your column type for your date should be a Sql Date, not a string. This also means your parameter value for your calendar should be DateTime calendar = Calendar1.SelectedDate; and not string.

protected void Button1_Click(object sender, EventArgs e)
{
    string client = TextBox1.Text;
    string selected = RadioButtonList1.SelectedValue;

    // this should be a DateTime instance, not a string
    string calendar = Calendar1.SelectedDate.ToShortDateString();

    string disease = txtDisease.Text;

    try
    {
        conn.Open();

        SqlCommand cmd = new SqlCommand("select 1 from Appointment WHERE DateofAppointment = @DateofAppointment AND TimeofAppointment = @TimeofAppointment", conn);
        cmd.Parameters.AddWithValue("@DateofAppointment", calendar);
        cmd.Parameters.AddWithValue("@TimeofAppointment", selected);
        bool exists = false;
        using(var reader = cmd.ExecuteReader()) {
           exists = reader.Read(); // if this returns true there is a record
        }

        // do something based on exists

        SqlCommand insert = new SqlCommand("insert into Appointment(Client_ID, DateofAppointment, TimeofAppointment, Disease) values(@Client_ID, @DateofAppointment, @TimeofAppointment, @Disease)", conn);

        // repurpose same pointer but to different instance
        cmd = new SqlCommand("select * from Client where Client_ID = @Client_ID", conn);
        cmd.Parameters.AddWithValue("@Client_ID", TextBox1.Text);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        if (dt.Rows.Count > 0)
        {       
                insert.Parameters.AddWithValue("@Client_ID", client);
                insert.Parameters.AddWithValue("@DateofAppointment", calendar);
                insert.Parameters.AddWithValue("@TimeofAppointment", selected);
                insert.Parameters.AddWithValue("@Disease", disease);
                insert.ExecuteNonQuery();
                TextBox1.Text = "";
                RadioButtonList1.SelectedIndex = -1;
                txtDisease.Text = "";
                ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('Appointment submitted! Please wait for your SMS confirmation. Thank you!')</script>");
        }
        else
        {
            TextBox1.Text = "";
            RadioButtonList1.SelectedIndex = -1;
            txtDisease.Text = "";
            ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('You are not a registered client!')</script>");
        }
    }
    catch
    {
        conn.Close();
    }
}

Answer 2

You can create a command before your first one and check the result before executing the second:

cmd.CommandText = "SELECT COUNT(*) FROM Client where {you conditions}";
Int32 count = (Int32) cmd.ExecuteScalar();

And then check if count is greater than 0 then execute the second.

Answer 3

protected void Button1_Click(object sender, EventArgs e) { string client = TextBox1.Text; string selected = RadioButtonList1.SelectedValue; string calendar = Calendar1.SelectedDate.ToShortDateString(); string disease = txtDisease.Text;

        SqlCommand insert = new SqlCommand("insert into Appointment(Client_ID, DateofAppointment, TimeofAppointment, Disease) values(@Client_ID, @DateofAppointment, @TimeofAppointment, @Disease)", conn);
        try
        {
            conn.Open();

            SqlCommand cmd = new SqlCommand("select * from Client where Client_ID = @Client_ID", conn);
            cmd.Parameters.AddWithValue("@Client_ID", TextBox1.Text);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                SqlCommand search = new SqlCommand("select * from Appointment where DateofAppointment = @DateofAppointment and TimeofAppointment = @TimeofAppointment", conn);
                search.Parameters.AddWithValue("@DateofAppointment", calendar);
                search.Parameters.AddWithValue("@TimeofAppointment", selected);
                Int32 count = (Int32)search.ExecuteScalar();
                if (count > 0)
                {
                    ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('Error!')</script>");
                }
                else
                {
                    insert.Parameters.AddWithValue("@Client_ID", client);
                    insert.Parameters.AddWithValue("@DateofAppointment", calendar);
                    insert.Parameters.AddWithValue("@TimeofAppointment", selected);
                    insert.Parameters.AddWithValue("@Disease", disease);
                    insert.ExecuteNonQuery();
                    TextBox1.Text = "";
                    RadioButtonList1.SelectedIndex = -1;
                    txtDisease.Text = "";
                    ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('Appointment submitted! Please wait for your SMS confirmation. Thank you!')</script>");
                }
            }
            else
            {
                TextBox1.Text = "";
                RadioButtonList1.SelectedIndex = -1;
                txtDisease.Text = "";
                ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('You are not a registered client!')</script>");
            }
        }
        catch
        {
            conn.Close();
        }

    }


}

It's still not working... :(