Display all columns except the PRIMARY KEY in a listbox in C#

Question

0.00/5 (No votes)

See more:

I want to display all the columns column names except the PRIMARY KEY in a listbox in C#. How do I do that?

My code gives me the following error:
Invalid column name 'column_type'.

Please Help. I am using MS-SQL for the same.

What I have tried:

C#

private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
       {

               string cmdstr = @"select * from information_schema.columns where table_name = '" + comboBox1.SelectedItem + "' and column_type <> 'PRI'";
               string conStr = @"Data Source=INPDDBA027\NGEP;Initial Catalog=Dev_Server;Integrated Security=True";
               DataTable dt = new DataTable();
               SqlDataAdapter sda = new SqlDataAdapter(cmdstr, conStr);
               sda.Fill(dt);
               listBox2.DataSource = dt;
               listBox2.DisplayMember = "Column_Name";
       }

Posted 26-Jul-16 16:57pm

Ankush Soni

Updated 27-Jul-16 4:05am

Add a Solution

Comments

Richard Deeming 27-Jul-16 10:16am

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Karthik_Mahalingam · Answer 1 · 2016-07-27T04:05:00

try this

SQL

select COLUMN_NAME from information_schema.columns where table_name = 'YourTableName' 
except 
SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
        JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON tc.CONSTRAINT_NAME = ccu.Constraint_name
    WHERE tc.CONSTRAINT_TYPE = 'Primary Key' and  tc.table_name  = 'YourTableName'

Note: Your code is Vulnerable to SQL injection [^] attacks
Always use Parameterized queries to prevent SQL Injection Attacks in SQL Server[^]

RossMW · Answer 2 · 2016-07-26T17:43:00

Column_Type is not a valid field in the table you are accessing in SQL

Try

SQL

SELECT *
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
        JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON tc.CONSTRAINT_NAME = ccu.Constraint_name
    WHERE tc.CONSTRAINT_TYPE = 'Primary Key'

to get which columns that are primary keys and join to the table you are referring to.

Display all columns except the PRIMARY KEY in a listbox in C#

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0