Click here to Skip to main content
15,888,113 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
How can i search for a record in a particular sql table listed in combo box

What I have tried:

C#
Sqlconnection conn = new Sqlconnection ("select * from INFORMATION_SCHEMA.TABLES where firstname like'" + txtIndexSearch. Text + "%'", conn)
Posted
Updated 2-Jan-23 4:31am
v2

The same way that you search for the tables; you build a SELECT command with the relevant keywords. Also, you should not use string concatenation to build SQL commands, use proper parameterised queries: see bobby-tables.com: A guide to preventing SQL injection[^].
 
Share this answer
 
Combo boxes are dangerous: they allow the user to type what they want, it isn't restricted to just the items in it's list. A drop down list is safer, but still requires care. The reason why is simple; if the user can directly influence what becomes part of an SQL Command, your database is at risk of SQL Injection which can damage or destroy it.
And unfortunately, table names cannot be passed as parameters due to teh order in which the SQL DB engine processes commands.

The search text has exactly the same problem: when you concatenate strings, you cause problems because SQL receives commands like:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
SQL
DROP TABLE MyTable;
A perfectly valid "delete the table" command
SQL
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

So while it's possible to do what you want, it's not a good idea unless you ensure that only "safe" commands can be entered - which means a collection of valid SQLK table names which are allowed to be used.

Then it's a simple matter: add the table name from a collection, and the search text as a parameter:
C#
string sql = $"SELECT * FROM {validTables[index]} WHERE firstName LIKE '%' + @FN + '%'";
using (SqlCommand cmd = new SqlCommand(sql, con))
   {
   cmd.Parameters.AddWithValue("@FN", txtIndexSearch.Text);
   ...
   }
 
Share this answer
 
Comments
Joel Silva 2022 3-Jan-23 9:25am    
Well, the only thing I was able to do is to read the table by using if("cmbox. selectedIndex.Tostring() ==" 2")
So, if I selected Index 2 in cmbox, then I used the sql query to read the Data.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900