Select with multiple condition from views of sql in asp.net

Question

_{Monday, January 28, 2019 8:58 AM}

I have a view that contains some tables. and now I want to get value from my view that has two conditions.

I use the below code but it doesn't return any value to me.

/////Get the Id of View_SubjectStudy_Lesson 
            SqlCommand cmd2 = new SqlCommand("select Id from View_SubjectStudy_Lesson WHERE LessonTittle LIKE'%" + ListLesson.SelectedItem.Text + "%' and SubjectStudyId = '" + LblSubjectStudyID.Text + "'", con);
            SqlDataReader dr2;
            con.Open();
            sqlcom.Connection = con;
            dr2 = cmd2.ExecuteReader();
            while (dr2.Read())
            {
                LblLessonId.Text = dr2["Id"].ToString();
            }
            sqlcom.Connection.Close();

I tried the code with any of the conditions. and it returns the value to me.but when I want to merge the conditions it doesn't return any value to me.

How can I do that Correctly?!

All replies (2)

_{Friday, February 1, 2019 5:38 PM ✅Answered}

You can create your SQL Query in a string variable like this:

string myqry="select Id from View_SubjectStudy_Lesson WHERE LessonTittle LIKE'%" + ListLesson.SelectedItem.Text + "%' and SubjectStudyId = '" + LblSubjectStudyID.Text + "'";

SqlCommand cmd2 = new SqlCommand(myqry, con);

You can put a breakpoint on the string myqry line and copy the query. 

Then execute this query in your sql server. 

If the query gives some error then you will know that the problem is in your query. Else you can proceed checking the next code lines.

This is the best way to debug database related codes.

Regards....

_{Tuesday, January 29, 2019 7:36 AM}

Hi dorsa2,

According to your description and code, I've tested on my side.

My sample is to simulate choosing the person whose Ename include "o" and age is 27, then return the Eid.It works well.

I would like to ask that when you run if there is any error.

I suggest that you could check if there is the matching result depending on your condition.

And you could use breakpoint to check if there is matching result from the database.

Here is my testing sample.

.Aspx

            <asp:DropDownList ID="DropDownList1" runat="server">
                <asp:ListItem>o</asp:ListItem>
                <asp:ListItem>a</asp:ListItem>
                <asp:ListItem>b</asp:ListItem>
            </asp:DropDownList>
            <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
            <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
            <asp:Label ID="Label4" runat="server" Text="Label"></asp:Label>

Code behind.

        string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        protected void Button1_Click(object sender, EventArgs e)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                string myquery = "SELECT * FROM [tb_info] WHERE Ename LIKE '%"+ DropDownList1.SelectedItem.Text+"%' and age='"+ TextBox1.Text+ "'";
                SqlCommand cmd = new SqlCommand(myquery, con);
                SqlDataReader sdr;
                con.Open();
                sdr = cmd.ExecuteReader();
                while (sdr.Read())
                {
                    Label4.Text = sdr["Eid"].ToString();
                }
                con.Close();
            }
        }

result:

the database:

Best Regards,

Jenifer