CODEWITHSUNDEEP
c#sqlsql-server
Hein
Hein

Reputation: 57

open SQL command for sorting data

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.

Upvotes: 0

Views: 67

Answers (2)

Whencesoever
Whencesoever

Reputation: 2306

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 "'".

Upvotes: 0

Koby Douek
Koby Douek

Reputation: 16693

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);

Upvotes: 2

Related Questions