WHERE Clause in SQL statement with 2 conditions

c# sql asp.net sql-server if-statement

18,961

Solution 1

Based on your current code, the first if block below will always be executed when you select a country, whether you choose a currency or not

if (country != "select")
{
    query = "select * from customer where country = '" + country + "'";
}

You need to check the selected currency inside the first if block as well. I've also fixed the incorrect SQL syntax.

if (country != "select")
{
    if (idcurrency != "selected")
    {
        query = "select * from customer where country = '" + country + "' and idcurrency = '" + idcurrency + "'";
    }
    else
    {
        query = "select * from customer where country = '" + country + "'";
    }
}

Solution 2

There is a syntax error in your SQL; instead of:

query = "select * from customer where country = '" + country + "select * from customer where idcurrency = '" + idcurrency + "'";

it should be like the following:

query = "select * from customer where country = '" + country + "' AND idcurrency = '" + idcurrency + "'";

Hope this may help.

18,961

Author by

laurence keith albano

A Computer Engineer. Music enthusiasts, Programmer.

Updated on June 24, 2022

Comments

laurence keith albano almost 2 years

I have this aspx(html) code:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="SearchCustomer.aspx.cs" Inherits="WebApplication1.eyeofheaven.SearchCustomer" %>

<!DOCTYPE html>
<html lang="en" xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" type="text/css" href="StyleSheets/SearchCustomerStyle.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/css/bootstrap.min.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/css/bootstrap-theme.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/js/bootstrap.min.js"></script>
<title>Search Customer</title>
</head>
<body>
<form id="form1" runat="server">
 <div class="row">
    <div class="twelve columns">
            <!-- Header-->
            <div class="container">
                <nav role="navigation" class="navbar navbar-inverse navbar-fixed-top">
                    <!-- Brand and toggle get grouped for better mobile display -->
                    <div class="navbar-header">
                        <button type="button" data-target="#navbarCollapse" data-toggle="collapse" class="navbar-toggle">
                            <span class="sr-only">Toggle navigation</span>
                            <span class="icon-bar"></span>
                            <span class="icon-bar"></span>
                            <span class="icon-bar"></span>
                        </button>
                    </div>
                    <!-- Collection of nav links, forms, and other content for toggling -->
                    <div id="navbarCollapse" class="collapse navbar-collapse">
                        <ul class="nav navbar-nav">
                            <li><a href="EyeOfHeaven.aspx">Home</a></li>
                            <li class="dropdown">
                                <a data-toggle="dropdown" class="dropdown-toggle active" href="#">Search<b class="caret"></b></a>
                                <ul role="menu" class="dropdown-menu">
                                    <li><a href="SearchCustomer.aspx">Search Form(Customer)</a></li>
                                    <li><a href="SearchVehicle.aspx">Search Form(Vehicle)</a></li>
                                </ul>
                            </li>
                        </ul>
                    </div>
                </nav>
            </div>
    </div>
</div>

<!-- Search form customer-->
<div id="searchcustomer" class="page-header">
    <h3><span class="glyphicon glyphicon-th-large"></span>Search Customer</h3>
</div>
<div class="row">
    <div class="col-md-4">
        <input type="text" runat="server" id="search" size="20" class="form-control" placeholder="Customer ID">
    </div>
    <div class="col-md-4">
        <select class="form-control" runat="server" id="Country">
            <option value="select" selected disabled>Search by Country</option>
            <option value="A:C ESTUDIO">A:C ESTUDIO</option>
            <option value="Aaron McEwen-194712">Aaron McEwen-194712</option>
            <option value="Accra">Accra</option>
            <option value="Adoany">Adoany</option>
            <option value="Aduanas">Aduanas</option>
            <option value="Alex Sanchez-259029">Alex Sanchez-259029</option>
            <option value="ALG Consulting-288078">ALG Consulting-288078</option>
            <option value="Algeria">Algeria</option>
            <option value="Algimantas Ramaskevicius">Algimantas Ramaskevicius</option>
            <option value="Allan Demeritte-233953">Allan Demeritte-233953</option>
            <option value="Althea  Gibson-223990">Althea  Gibson-223990</option>
            <option value="Alvaro Delgado-279974">Alvaro Delgado-279974</option>
            <option value="Amber Williamsen-212332">Amber Williamsen-212332</option>
            <option value="Amos Peters-217784">Amos Peters-217784</option>
            <option value="andersongordon-234453">andersongordon-234453</option>
            <option value="Andrey Bolshakov-189544">Andrey Bolshakov-189544</option>
            <option value="Angela green-220269">Angela green-220269</option>
            <option value="ANGELMILLER-179184">ANGELMILLER-179184</option>
            <option value="Angola">Angola</option>
            <option value="Anguilla">Anguilla</option>
            <option value="Anthony Knight-235064">Anthony Knight-235064</option>
            <option value="Antigua and Barbuda">Antigua and Barbuda</option>
            <option value="Anton Transport">Anton Transport</option>
            <option value="Argentina">Argentina</option>
            <option value="Armenia">Armenia</option>
            <option value="Aruba">Aruba</option>
            <option value="Australia">Australia</option>
            <option value="Austria">Austria</option>
            <option value="Bahamas">Bahamas</option>
            <option value="Bahrain">Bahrain</option>
            <option value="Bangladesh">Bangladesh</option>
            <option value="Barbados">Barbados</option>
            <option value="Belgium">Belgium</option>
            <option value="Belize">Belize</option>
            <option value="Benin">Benin</option>
            <option value="Bermuda">Bermuda</option>
        </select>
    </div>  

    <div class="col-md-4">
        <select class="form-control" runat="server" id="Currency">
            <option value="selected" selected disabled>Search by Currency</option>
            <option value="AUD">AUD Australian Dollar</option>
            <option value="EUR">EUR</option>
            <option value="GBP">GBP United Kingdom Pounds</option>
            <option value="JPY">JPY Japan Yen</option>
            <option value="NZD">NZD New Zealand Dollar</option>
        </select>
    </div>
</div>
<div class="row">
    <div class="col-md-4">
<button type="button" runat="server" onserverclick="Button1_Click" id="searchinfo" class="btn btn-primary"><span class="glyphicon glyphicon-search"></span> Search Info</button>
    </div>
</div>

<!-- Information Table-->
<div id="gridview">
    <asp:GridView runat="Server" id="data" CssClass="table table-striped table-bordered table-responsive">
    </asp:GridView>
</div>

</form>
</body>
</html>

And its aspx.cs code behind:

using MSSQLConnector;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace WebApplication1.eyeofheaven
{
    public partial class SearchCustomer : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        }
        protected void Button1_Click(object sender, EventArgs e)
        {

            MSConnector connector = new MSConnector();
            connector.ConnectionString = "SERVER=xbetasql,52292;UID=username;Password=secret;DATABASE=ATDBSQL;";

            string customer = (this.search.Value);
            string country = (this.Country.Value);
            string idcurrency = (this.Currency.Value);
            string query = null;

            if (country != "select")
            {
                query = "select * from customer where country = '" + country + "'";
            }
            else if (idcurrency != "selected")
            {
                query = "select * from customer where idcurrency = '" + idcurrency + "'";
            }
            else if ((country != "select") && (idcurrency != "selected"))
            {
                query = "select * from customer where country = '" + country + "select * from customer where idcurrency = '" + idcurrency + "'";
            }
            else if (customer == customer)
            {
                query = "select * from customer where idcustomer = '" + customer + "'";
            }
            else if (customer == "")
            {
                Response.Write("<script>alert('No Id Inputted, Data Not Found.')</script>");
            }
            DataSet selectedData = connector.ExecuteQuery(query);
            DataTable dt = selectedData.Tables[0];
            data.DataSource = dt;
            data.DataBind();
        }
    }
}

I have a problem about the condition, I want to make my select tag to display the data when I select the two option, ex: I select "Aduanas" from country and then select another option "USD" from Currency. But it will only get the data of the first select tag(Country) that I selected, my condition does not read and get the data of the two values of the Country and Currency. I'm new to c# programming and ms sql and I'm learning the syntax and functions of it.

My condition about the country and currency (select tag) below:

else if ((country != "select") && (idcurrency != "selected"))
{
    query = "select * from customer where country = '" + country + "select * from customer where idcurrency = '" + idcurrency + "'";
}

Alexander Bell almost 9 years

You are welcome! I am glad it works for you. Please check the answer accepted, and should you have more questions, please post them separately. Good luck with your project. Best regards,
laurence keith albano almost 9 years

This work sir thanks a lot! :) one question where do I put my alert tag if my country and currency does not match to its database?