Problem while populating datagrid from sqlserver table


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 7 of 7

Thread: Problem while populating datagrid from sqlserver table

  1. #1
    Join Date
    Jul 2006
    Posts
    91

    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,

  2. #2
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    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;
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  3. #3
    Join Date
    Jul 2006
    Posts
    91
    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. #4
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    What is the value of your "select" variable when the error occurs?
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  5. #5
    Join Date
    Jul 2006
    Posts
    91
    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,

  6. #6
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    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?
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  7. #7
    Join Date
    Jul 2006
    Posts
    91
    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,

Similar Threads

  1. Multiple table problem
    By kenn_rosie in forum .NET
    Replies: 0
    Last Post: 01-14-2006, 03:44 AM
  2. Replies: 1
    Last Post: 05-07-2003, 06:01 PM
  3. Replies: 0
    Last Post: 08-10-2001, 03:35 PM
  4. datagrid problem - wont refresh grid
    By Steve Ash in forum VB Classic
    Replies: 5
    Last Post: 04-24-2001, 06:51 PM
  5. CrossBrowser DHTML table sorting problem
    By Joel Matto in forum Web
    Replies: 0
    Last Post: 05-02-2000, 03:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center