[winform] c# 忽略 sql 條件 is null

c# sql

mayyola 2020-11-14 10:24:17 ‧ 1738 瀏覽

大家好:

主要是combotext[1]如果沒取值是空白，where會忽略他，等於沒有設條件
像這樣

var txtCommand = "SELECT * FROM [" + combotext[0] + "] where null is null";
沒設參數這樣可以
但如果combotext[1]如果沒取值是空白，or到is null 那段會有問題..想請是哪裡有錯呢?謝謝

var txtCommand = "SELECT * FROM [" + combotext[0] + "] where [" + labeltext[1] + "]='"+ combotext[1] +"' or ["+combotext[1] +"] is null";
//錯在null這行


foreach (Control ctrl in tableLayoutPanel1.Controls)
{
      string ControlNames = ctrl.Name;
      string ControlText = ctrl.Text;
      string Controltype = ctrl.GetType().Name;
               
    if (ctrl.GetType() == typeof(ComboBox))
       {
         if (String.IsNullOrEmpty(ControlText))

      {
        combotext[t] = "null";
       }
       else
       {
        combotext[t] = ControlText;
        }
           t++;
         }
        else if (ctrl.GetType() == typeof(Label))
         {
              labeltext[u] = ControlText;
              u++;
          }

           }
var DBPath = Application.StartupPath + "\\database\\email.mdb";
OleDbConnection conn;
conn = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + DBPath);
conn.Open();
var txtCommand = "SELECT * FROM [" + combotext[0] + "] where [" + labeltext[1] + "]='"+ combotext[1] +"' or ["+combotext[1] +"] is null";
//錯在null這行

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

3 個回答

通靈亡

iT邦高手 1 級 ‧ 2020-11-15 00:40:48

最佳解答

var txtCommand = "SELECT * FROM [" + combotext[0] + "] where null is null";
沒設參數這樣可以
但如果combotext[1]如果沒取值是空白，or到is null 那段會有問題..想請是哪裡有錯呢?謝謝

如果值是空值或是null，出來並不會是where null is null，而是where [] is null，造成錯誤
以下是還原的示意程式碼和結果：
https://dotnetfiddle.net/1Gm0fV

using System;
					
public class Program
{
	public static void Main()
	{
		string[] combotext = {"combotext0", null};
		string[] labeltext = {"labeltext0", "labeltext1"};
		string sql = "SELECT * FROM [" + combotext[0] + "] where [" + labeltext[1] + "]='"+ combotext[1] +"' or ["+combotext[1] +"] is null";
		Console.WriteLine(sql);
	}
}

SELECT * FROM [combotext0] where [labeltext1]='' or [] is null

此外，where [null] is null 也會抱錯，這行代表有個欄位null，他的值要是null

因此要將sql的字串改成:

string sql = "SELECT * FROM [" + combotext[0] + "] where [" + labeltext[1] + "]='"+ combotext[1] +"' or "+ (combotext[1] != null ? "[" + combotext[1] + "]" : "null") +" is null";

當combotext[1]=null的時候會變成

SELECT * FROM [combotext0] where [labeltext1]='' or null is null

就可以順利查詢

回應
分享
檢舉

登入發表回應

japhenchen

iT邦超人 1 級 ‧ 2020-11-14 12:03:32

var txtCommand = "SELECT * FROM [" + combotext[0] + "] where [" + labeltext[1] + "]='"+ combotext[1] +"' or isnull(["+combotext[1] +"],'') = '';

回應 1
分享
檢舉

mayyola iT邦研究生 2 級 ‧ 2020-11-14 23:21:07 檢舉

j大您好: 有試跑過，但還是會有例外，只好多加一個參數了..
想再請問Access sql 有語法可以採前兩個欄位的嗎?
select con1,con2 from table
con1 不是名稱是欄位指標之類的@ @
謝謝

 if (ctrl.GetType() == typeof(ComboBox))
{
if (String.IsNullOrEmpty(ControlText))
{
busLate[t]= true; 
}

else {

busLate[t] = false;
}
combotext[t] = ControlText;
t++;
                }
var txtCommand = "SELECT * FROM [" + combotext[0] + "] where [" + labeltext[1] + "]=IIF("+ busLate[1] + ","+ labeltext[1] + ",'"+ combotext[1] + "')";

登入發表回應