cek valid data in table with input ";" and where in

Question

i want to check valid data...

i have a table Divisi with sample data like this:

=====================
IdDivisi   NamaDivisi
=====================
1           DivisiA
2           DivisiB
3           DivisiC

in my code, i get value : string data = DivisiA;DivXXX

so, when checked, the alert will appear invalid data. I want to get a query like this:

select NamaDivisi from Divisi where NamaDivisi IN('DivisiA','DivXXX')

and the result is null or empty or invalid. because there are values / data 'DivXXX' is not valid on the table Divisi

But this time, when I debug, I get the query result like this:

select NamaDivisi from Divisi where NamaDivisi IN ('DivisiA;DivXXX')

===================================================

This is the full code.

private string CekValidDivisi(string data)
{
    DivisiFacade div = new DivisiFacade();
    string getDivisi = div.CekValidData(data);
    return getDivisi;
}

public string CekValidData(string data)
{
    SqlConnection Conn = DataSetting.GetSqlConnection();
    SqlCommand Comm = new SqlCommand();
    try
    {
       Conn.Open();
       string sql = @"select NamaDivisi from Divisi where NamaDivisi IN('" + data + "')";
       Comm = new SqlCommand(sql, Conn);
       data = Convert.ToString(Comm.ExecuteScalar());
    }
    finally
    {
       Conn.Close();
       Conn.Dispose();
    }
    return data;
}

please help me to resolve the problem in my code. thank you ...

I don't understand your question. - Saya kurang mengerti pertanyaan anda. — , Sep 05 '14 at 02:59
Look into using a parameterized query -- this might help: http://stackoverflow.com/questions/337704/parameterize-a-sql-in-clause — sgeddes, Sep 05 '14 at 03:05

score 0 · Accepted Answer · answered Sep 05 '14 at 03:04

You have multiple problems in your code, but this is not a place to teach you basics, so I'll try to stick to the topic. If you want to have a parameter like that, you have to create it like that first. I guess the data contains string with value DivisiA;DivXXX (and I presume DivXXX is just a generic name meaning you have multiple divisions there). Probably the easiest way would be to do something like this with it

public string CekValidData(string data)
{
    SqlConnection Conn = DataSetting.GetSqlConnection();
    SqlCommand Comm = new SqlCommand();
    try
    {
       Conn.Open();
       string paramData = ParseData(data);
       string sql = @"select NamaDivisi from Divisi where NamaDivisi IN('" + paramData + "')";
       Comm = new SqlCommand(sql, Conn);
       data = Convert.ToString(Comm.ExecuteScalar());
    }
    finally
    {
       Conn.Close();
       Conn.Dispose();
    }
    return data;
}

private string ParseData(string data)
{
    return data.Replace(";", "','");
}

Haven't tried it, but hope you get the idea. Either way, please for your own sake, do some research on what is the best way to handle sql connections in c# and also how to prevent SQL injections.

from your code, return data = DivisiA. so, how do I make it invalid, because the data that contains the data 'DivXXX' causing invalid. are there any other solutions? thanks — user2538170, Sep 05 '14 at 03:17
@user2538170, causing invalid what? What is the error message you're getting? I'm having troubles understanding your English. It's really hard to decipher what do you actually want. Please take some time and rephrase your question, so that we can comprehend your intent. — walther, Sep 05 '14 at 03:37

cek valid data in table with input ";" and where in

1 Answers1