CODEWITHSUNDEEP
c#asp.netsql-server-2008
samuel henk
samuel henk

Reputation: 13

how to query select data from table where first 3 character defined

i want to filter dropdownlist by first 3 charater on job code, this is my query

string result3 = Checked_By.ToString().Substring(0, 3);


SqlCommand cmd = new SqlCommand(" SELECT [Kode], [Nama] 
FROM [Job] WHERE LEFT(Kode, = '" + result3 + "') ORDER BY 
Nama ASC", con);

 SqlDataAdapter sda = new SqlDataAdapter(cmd);
 DataTable dt = new DataTable();
 con.Open();
 sda.Fill(dt);
 con.Close();

Upvotes: 0

Views: 243

Answers (2)

Alex Kudryashev
Alex Kudryashev

Reputation: 9470

This is straightforward fix. First, fix SQL syntax error (SQL LEFT function takes 2 parameter, a string and a number. LEFT('abcdef', 3) returns abc), next rewrite using parameters. Something like this.

string result3 = Checked_By.ToString().Substring(0, 3);

SqlCommand cmd = new SqlCommand(" SELECT [Kode], [Nama] FROM [Job] WHERE LEFT(Kode, 3) = @result3 ORDER BY Nama", con); //ASC is OK but not required as it is default option
cmd.Parameters.Add("@result3", SqlDbType.Char, 3).Value = result3;

 SqlDataAdapter sda = new SqlDataAdapter(cmd);
 DataTable dt = new DataTable();
 //con.Open(); //not necessary. SqlDataAdapter.Fill opens connection if it needs
 sda.Fill(dt);
 //con.Close(); //and closed if it wasn't open before .Fill

Upvotes: 2

user8217724
user8217724

Reputation:

"SELECT [Kode], [Nama] FROM [Job] WHERE SUBSTR(Kode,1,3) = '" + result3 + "' ORDER BY Nama ASC"

Upvotes: 0

Related Questions