Problem while populating datagrid from sqlserver table
Hi,
I want to populate datagrid from sqlserver-2000 table.The following code exactly does that. But, Problem occurs when I specify a WHERE clause.
i.e When I append "WHERE user_id = uid" to "select" string below, application just blows up.
user_courses Table
user_id course_id
tim CRS235
tim CRS611
bob CRS724
string uid=(string)Session["cur_user"];
string conString ="Server = SRINIVAS\\HOMEDB;Database= AuthenticateGroups;Trusted_Connection=True;";
SqlConnection con = new SqlConnection(conString);
string select= "SELECT course_id FROM user_courses";//problem in this line
SqlDataAdapter da = new SqlDataAdapter(select,conString);
DataSet ds = new DataSet();
da.Fill(ds,"user_courses");
dgCourses.DataSource = ds.Tables["user_courses"].DefaultView;
dgCourses.DataBind();
Without where clause it displays all the courses in the datagrid. But, what I am trying to get with WHERE clause is just bind(show) the courses taken by the current user. But it's not allowing me. So, can some one please correct my code.
Thanks,
# 1 Re: Problem while populating datagrid from sqlserver table
You don't want the query to be "SELECT course_id FROM user_courses WHERE user_id = uid", you want it to be "WHERE user_id = <value of uid>". Try this:
string select= String.Format("SELECT course_id FROM user_courses WHERE user_id = {0}", uid);
or simply:
string select= "SELECT course_id FROM user_courses WHERE user_id = " + uid;
# 2 Re: Problem while populating datagrid from sqlserver table
Hi,
Thanks for the reply. I tried changing the string, the way you specified. But, application still blows up.
Error message:-
System.Data.SqlClient.SqlException: Invalid column name 'tim@infor'.
actually uid ='tim@infor' and user_courses table contains user_id and course_id, where user_id =tim@infor etc and course_id=CRS235 etc.
So with the above code I am trying to retrive all the courses taken by the user "tim@infor" in the current session and display in the datagrid. But, application blows up with the above code.
Thanks,
# 4 Re: Problem while populating datagrid from sqlserver table
The value of the variable uid is "tim@infor".
Actually, instead of writing inline code I used stored procedure. So, now I got the desired results. This was the new approach:-
Response.Write("Welcome" + " "+ uid);
string conString ="Server = SRINIVAS\\HOMEDB;Database= AuthenticateGroups;Trusted_Connection=True;";
SqlConnection con = new SqlConnection(conString);
con.Open();
SqlDataAdapter da = new SqlDataAdapter("getUserCourses",con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Parameters.Add("@user_id",SqlDbType.VarChar);
da.SelectCommand.Parameters["@user_id"].Value = uid;
DataSet ds = new DataSet();
da.Fill(ds,"user_courses");
dgCourses.DataSource = ds.Tables["user_courses"].DefaultView;
dgCourses.DataBind();
And I wrote a simple stored procedure at the backend. But, I still want to know why the first approach is not working.Bcz, if we can achieve something by stored procedure. We can as well do it with inline code.
Thanks,
# 5 Re: Problem while populating datagrid from sqlserver table
OK, glad you got it working. I didn't ask, "What is the value of the uid variable?" I asked, "What is the value of the select variable?" That is, what is the exact select statement that causes the error?
# 6 Re: Problem while populating datagrid from sqlserver table
The value of the select statement tha's causing error was "SELECT course_id FROM user_courses WHERE user_id = uid"
Insetad of the above statement. If I hardcode the value of "uid", it's giving me the desired results. that is if I change select to:-
"SELECT course_id FROM user_courses WHERE user_id = 'tim@infor'".
I think the problem is with the syntax i.e I think I need to specify it in a different way. May be something closer to what you specified previously.
Thanks,
