Reputation: 57
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
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
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