Cannot find table 0

Page 1 of 1

2 Replies - 6801 Views - Last Post: 25 August 2010 - 07:25 AM Rate Topic: -----

#1 Guest_Andrew James*


Reputation:

Cannot find table 0

Posted 25 August 2010 - 03:02 AM

When my page loads I am able to select a user from list box 1 and click add, this copies the user details from the dataset and moves it to a new dataset that creates list box 2. When I select a new person to move I get the Cannot find table 0 error

Code as follows

string sourceUser = (string)HttpContext.Current.Session["TargetUserID"];
System.Data.DataSet blank = new System.Data.DataSet();
System.Data.DataSet ds = new System.Data.DataSet();
     
protected void Page_Load(object sender, EventArgs e)
{
    if (lstCopyTargetUsers.Items.Count < 1)
    {
        if (Page.IsPostBack)
        {            
            Sage.Platform.Data.IDataService service = Sage.Platform.Application.ApplicationContext.Current.Services.Get<Sage.Platform.Data.IDataService>();
            string connStr = service.GetConnectionString();
            Sage.SalesLogix.Security.SLXUserService usersvc = (Sage.SalesLogix.Security.SLXUserService)Sage.Platform.Application.ApplicationContext.Current.Services.Get<Sage.Platform.Security.IUserService>();
            Sage.Entity.Interfaces.IUser user = usersvc.GetUser();

            string loggedinid = user.Id.ToString();

            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connStr);
            conn.Open();

            System.Data.OleDb.OleDbCommand reps = new System.Data.OleDb.OleDbCommand();
            //Build and execute SQL
            reps.Connection = new System.Data.OleDb.OleDbConnection(connStr);
            reps.Connection.Open();
            reps.CommandText = "SELECT userid,username FROM USERINFO WHERE USERID = '" + loggedinid + "' or USERID in (select userid from usersecurity where managerid ='" + loggedinid + "')";

            System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(reps.CommandText, conn);
            da.SelectCommand = reps;
            da.Fill(ds);

            lstTeam.DataSource = ds;
            lstTeam.DataValueField = ds.Tables[0].Columns["userid"].ToString();
            lstTeam.DataTextField = ds.Tables[0].Columns["username"].ToString();
            lstTeam.DataBind();

            blank = ds.Clone();

            lstTeam.Items.Remove(lstTeam.Items.FindByValue(sourceUser));

            reps.Connection.Close();
        }
    }
}

protected void btnAdd_Click(object sender, EventArgs e)
{
    int i;
    if (lstTeam.SelectedIndex != -1)
    {
        //foreach(System.Data.DataRow dr in ds.Tables[0].Rows)
        //{ 
            for (i = 0; i < lstTeam.Items.Count; i++ )
            {
                if(lstTeam.SelectedItem.Text == ds.Tables[0].Rows[i][1].ToString())
                {
                    blank.Tables[0].ImportRow(ds.Tables[0].Rows[i]);
                    lstCopyTargetUsers.DataSource = blank;
                    lstCopyTargetUsers.DataValueField = ds.Tables[0].Columns["userid"].ToString();
                    lstCopyTargetUsers.DataTextField = ds.Tables[0].Columns["username"].ToString();
                    lstCopyTargetUsers.DataBind();

                    ds.Tables[0].Rows[i].Delete();
                    lstTeam.DataSource = ds;
                    lstTeam.DataValueField = ds.Tables[0].Columns["userid"].ToString();
                    lstTeam.DataTextField = ds.Tables[0].Columns["username"].ToString();
                    lstTeam.DataBind();
                }
            }

            lstTeam.Items.Remove(lstTeam.Items.FindByValue(sourceUser));
            lstCopyTargetUsers.Items.Remove(lstTeam.Items.FindByValue(sourceUser));           
        //}
    }
}


Is This A Good Question/Topic? 0

Replies To: Cannot find table 0

#2 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1511
  • View blog
  • Posts: 5,916
  • Joined: 21-March 08

Re: Cannot find table 0

Posted 25 August 2010 - 04:32 AM

This is a common mistake that most newcomers to web development make. ASP.Net is stateless. This means that once the response is sent from the server back to the browser, the server has no idea about what the browser is doing. This also means that all variables are reset on each postback to the server.

One thing to always remember, ALL code that is outside of events and methods(ie. variable declarations) are executed on EVERY postback. Also remember that the Page_Load event is also executed on EVERY postback. So if you click a button on the form, the Page_Load event is fired, then the Button Click Event is fired.

So in your code, 

string sourceUser = (string)HttpContext.Current.Session["TargetUserID"];
System.Data.DataSet blank = new System.Data.DataSet();
System.Data.DataSet ds = new System.Data.DataSet();


blank and ds are set back to empty DataSets.

When the form first loads, you get all your data and do your bindings. However, once that is complete and the page reloads in the browser, all of that data is now gone. The server does NOT keep it. So when you click the button, you are now trying to access the data in those DataSets that is no longer there.

Solutions:

1. You could store the DataSets in the Session. This is the normal way of persisting data across postbacks. If your DataSets are fairly small, then this would be the way to go. However, if your DataSets could hold thousands of records, then the Session is not the place to store it.

2. You could simply run the SQL query again in the btnAdd Click Event. Choose this only if the query is quick. If the query takes 5 seconds to run, then you probably don't want to re-query the data every time.
Was This Post Helpful? 2
  • +
  • -

#3 Guest_Andrew James*


Reputation:

Re: Cannot find table 0

Posted 25 August 2010 - 07:25 AM

Thanks for the post, I completly forgot session variables (school boy error). I have got this working now
Was This Post Helpful? 0
Page 1 of 1