open SQL command for sorting data

Question

Hi and thank you in advance for any help.

I have a problem with reading from a SQL database.

The command works 100% if i specify the column name

SqlCommand com = new SqlCommand("Select * From Stock where Fuad > 0 ", con);

but the problem is that my program tracks stock movement, between users, and i need to use a command similar to

String currentuser = //(current user logged in, passed when logged in)
SqlCommand com = new SqlCommand("Select * From Stock where '" + currentuser + "' > 0 ", con);

but when i run this code i get an error: "Conversion failed when converting varchar value 'Fuad' to datatype int." Now i know my column in sql is set to Int. but how does that affect the column name?

i can go and write this out for every user, but that would be pointless in the long run as for every new staff member i will have to write a update. is there a way to use a generic caller ie: currentuser to help with this.

the SQL database is setup with int columns, each column has the technicians/staff members name, and tracks how many of each part he currently has booked out.

i just cant figure out how to call the column name if its an Int, because any string that i use will be varchar.

the full code for loading this is

            SqlConnection con = new SqlConnection(Connectstring)
            con.Open();
            SqlCommand com = new SqlCommand("Select * From Stock where '" + currentuser + "' > 0 ", con);

            try
            {

                SqlDataReader dr = com.ExecuteReader();
                while (dr.Read())
                {
                    ListViewItem item = new ListViewItem(dr["ItemName"].ToString());
                    item.SubItems.Add(dr["ItemCode"].ToString());
                    item.SubItems.Add(dr[currentuser].ToString());
                    listView1.Items.Add(item);

                }
                dr.Close();

and as said earlier this works 100% if i replace the "currentuser" with the column name. is it possible to call it with a string, if not i will have to find another way to do this.

'Now i know my column in sql is set to Int' --- can you paste the column definition and a few rows of sample data in sql? — Lei Yang
– Lei Yang, Commented Mar 6, 2017 at 8:08

Koby Douek · Accepted Answer · 2017-03-06 08:06:34Z

2

Try using [Fieldname] this way:

String currentuser = //(current user logged in, passed when logged in)
SqlCommand com = new SqlCommand("Select * From Stock where [" + currentuser + "] > 0 ", con);

answered Mar 6, 2017 at 8:06

Koby Douek

16.7k21 gold badges79 silver badges110 bronze badges

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

2 Comments

Lei Yang Over a year ago

Fuad is not keyword, so no need for []?

Koby Douek Over a year ago

Using [] is best practice for all select commands. If this is the reason you downvoted, your downvote is incorrect.

Whencesoever · Accepted Answer · 2017-03-06 08:14:04Z

0

String currentuser = //(current user logged in, passed when logged in)
SqlCommand com = new SqlCommand("Select * From Stock where " + currentuser + " > 0 ", con);

Just don't use that single brackets for column name "'".

answered Mar 6, 2017 at 8:14

Whencesoever

2,30617 silver badges26 bronze badges

Collectives™ on Stack Overflow

open SQL command for sorting data

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related