CODEWITHSUNDEEP
sqlstring
ayna
ayna

Reputation: 77

I need a sql query to match two strings in where clause

'IP' is text field in both whiteList and BlackList tables(two different tables).'ip' is string storing ip address of current client.i want to check first if the ip address of client is in black list and then in whitelist(if not in blacklist).As it is string matching so '=' operator is not generating correct result.Also dont want to use 'like' because i am not searching for pattern rather exact match.

 string[] ipPort = txtCIP.Text.Split(new char[] { ':' }, 2);
            string ip = ipPort[0];
            con.Open();
            //MessageBox.Show("Connection Open ! ");
            string query = "SELECT count(*) from BlackList WHERE IP=ip";

            SqlCommand cmd = new SqlCommand(query, con);
            int rowCount =1;
            rowCount = (int)cmd.ExecuteScalar();
            string wlq = "SELECT count(*) from WhiteList WHERE IP=ip";
            SqlCommand cmd_wl = new SqlCommand(wlq, con);
            int rowcount_wl;
          rowcount_wl = (int)cmd_wl.ExecuteScalar();

          if (rowCount > 1)
          {
              MessageBox.Show("This IP is black Listed.Please retry after a few seconds.");
          }

          else if (rowcount_wl > 0)
          {
              MessageBox.Show("This ip is present in white list.");
          }
          else
          { MessageBox.Show("IP is not present in whitelist"); }
            con.Close();

Upvotes: 1

Views: 1433

Answers (3)

Mudassir Hasan
Mudassir Hasan

Reputation: 28751

String concatenation needs to be done to pass value of variable using plus operator

string query = "SELECT count(*) from BlackList WHERE IP="+ip;

without it the WHERE clause is searching for word ip rather than value contained in variable ip

Executing your query

 string query = "SELECT count(*) from BlackList WHERE IP=ip";

will throw error invalid column name ip

will return all rows as you are matching column IP with itself i.e ip

To be searched as a string literal in WHERE clause , ip would need to be enclosed in single qoutes but ofcourse you don't want this.

Upvotes: -1

Sky
Sky

Reputation: 3360

string query = "SELECT count(*) from BlackList WHERE IP=" + ip;
string wlq = "SELECT count(*) from WhiteList WHERE IP=" + ip;

Your IP is stored in a variable, so use it as a variable. When you do WHERE IP=ip it's searching for the string = ip. So when you compare an IP 192.168.1.1 you are comparing against the word ip e.g.

if ("192.168.1.1" == "ip")

This will return false obviously.

Upvotes: 0

AK47
AK47

Reputation: 3797

I think issue is in your select,varialbe [ip] should be outside double quotes,

 string query = "SELECT count(*) from BlackList WHERE IP=ip";

should be like this,

 string query = "SELECT count(*) from BlackList WHERE IP=" + ip;

AND

string wlq = "SELECT count(*) from WhiteList WHERE IP=ip";

should be like this,

string wlq = "SELECT count(*) from WhiteList WHERE IP=" + ip;

Upvotes: 2

Related Questions