0

I used SQLite. The user will pull the days from checkbox and I'll show it in data grid view but the date is recorded as day and time so I have to use like instead of in command.

enter image description here

        DataSet dataSet122;
        listBox1.Items.Clear();
        SQLiteConnection connection = new SQLiteConnection("Data Source =log.sqlite;Version=3;");
        string search = checkBoxComboBox1.Text;
        string[] array = search.Split(',');
        for (int i = 0; i < array.Length; i++)
        {
            array[i] = "'" + array[i] + "'";
        }
        string names = String.Join(",", array);
        listBox2.Items.Add(names);
        string query = "SELECT * FROM Gemkay1 WHERE ZAMAN LIKE  (" + names + ")";
        command = new SQLiteCommand(query, connection);
        connection.Open();
        adapter = new SQLiteDataAdapter(command);
        dataSet122 = new DataSet();
        adapter.Fill(dataSet122, "Gemkay1");
        dataGridViewSummary1.DataSource = dataSet122.Tables["Gemkay1"];
Improve this question
2
  • 1
    In addition to the answer below, please don't use string concatenation to build SQL statements, use parameters. Google it for how to do it with C# and SQLite. Commented Feb 2, 2021 at 8:08
  • not knowing how many days the user will choose, so I assigned the days to an array, but how can I show them Commented Feb 2, 2021 at 8:11

2 Answers 2

Reset to default
1

SQL syntax for all people where name ends with SMITH or WRIGHT:

WHERE name LIKE '%SMITH' OR name LIKE '%WRIGHT'

LIKE is not the same as IN - it accepts a single string argument on the right hand side. If you want multiple LIKEs you must repeat the LIKE clause separated by OR

IN can be used with multiple string but it does not accept wildcards:

WHERE name IN ('playwright', 'cartwright', 'shipwright')

If you try and put a wildcard in it will literally match that character.

-

As an aside, don't make SQL like you're doing there, with string concatenation of the values. Concatenate parameters in instead and give them values, for example:

var names = new []{"%wright", "%smith"};

var sql = new SqliteCommand("SELECT * FROM t WHERE 1=0 ");

for(int p = 0; p<names.Length; p++){
  sql.CommandText += " OR name like @p" + p;
  sql.Parameters.AddWithValue("@p"+p, names[p]);
}

This I what I mean when I say "concatenate parameters in, then give them a value".

If you ever work with sqlserver read this blog post

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Thanks for your answer but it won't work for me beacuse if user chooses 10 days I have to use 10 OR. The code must be dynamic according to the user's choice.
The reason I can't use the IN command is that the column I search has date and time , so I'm only searching by date, so I have to use LIKE command
Then you have to use it like I say
Yes I have to use LIKE but I dont know how many days the user chose.
For example If user choose 10 days LIKE day1 OR LIKE day2...... its not good
|
0

Use IN operator to select data where multiple values

"SELECT * FROM Gemkay1 WHERE ZAMAN IN ('2021-02-01','2021-02-02')";

to ignore time from date you can use date function:

"SELECT * FROM Gemkay1 WHERE date(ZAMAN) IN ('2021-02-01','2021-02-02')";

See SQLite date and time functions documentation for more info.

Improve this answer

11 Comments

Thanks for your answer I tried this SELECT * FROM Gemkay1 WHERE date(ZAMAN) LIKE. If I choose 1 day it works, but if I choose more than 1, it gives an error. This is my error SQL logic error row value misused'
use IN not Like
I can't use IN command because ZAMAN column consists of date and time. For example 2021-02-01 14:38:00 but I just have to search by date.
use date function "date(ZAMAN) IN (....)" to ignore time in date
No, date function removes time from saman column so IN will work
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.