Passing parameter in select statement in postgresql

Question

I am trying to pass parameter for below select statement in postgresql, but it is not returning any row,

cmd.Parameters.AddWithValue("@name", richTextBox_searchEmp.Text);                
string sql = "select * from tbl_emp_Info where LOWER(firstname) like LOWER('@name%');";

where- richTextBox_searchEmp.Text is “first” have also tried -

cmd.Parameters.AddWithValue("@name", NpgsqlDbType.Char , searchEmp.Text);

while, parameter less query below always returning correct results.

 string sql = "select * from tbl_emp_Info where LOWER(firstname) like LOWER('first%');";

Please help!!!

Complete Code-

            conn.Open();
            cmd.Parameters.AddWithValue("@name", NpgsqlDbType.Char , richTextBox_searchEmp.Text);

            string sql = "select * from tbl_emp_Info where LOWER(firstname) like LOWER('@name%');";

            NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);

            cmd.Connection = conn;
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();

            ds.Reset();
            da.Fill(ds);
            dt = ds.Tables[0];
            dataGridView.DataSource = dt;

Dnyanesh · Accepted Answer · 2015-04-04 07:02:28Z

7

Pass your parameter with % like

Change you query to

string sql = "select * from tbl_emp_Info where LOWER(firstname) like LOWER(@name);";

And Pass @name like

cmd.Parameters.AddWithValue("@name", "%" + searchEmp.Text + "%");

edited Apr 4, 2015 at 7:02

answered Mar 30, 2015 at 17:41

Dnyanesh

2,3633 gold badges20 silver badges17 bronze badges

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

2 Comments

harper Over a year ago

thanks Dnyanesh, tried this but not worked :( I have implemented it in following way, but still searching for Parameterized way to do it. string sql = "select * from tbl_emp_Info where LOWER(firstname) like LOWER('" + richTextBox_searchEmp.Text + "%');";

Dnyanesh Over a year ago

My bad remove single quote from sql string. so it will be @name and not '@name'. Now this should work. I have tested and it is working well. % before is optional if you want to search text anywhere in string.

Collectives™ on Stack Overflow

Passing parameter in select statement in postgresql

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related