mysql join causing exception

[the maya]

Hi Guys,

I'm hoping someone out there can help me with a little problem I am using a join statement to join 4 tables together. It works fine on my pc with local mysql but when i upload to x10hosting i get an exception error.

My join statementis:

cmd = new MySqlCommand("select *, p.nameP, s.SiteName from dates AS d"
+ " join sites AS s on d.SiteID = s.SiteID"
+ " left join people AS p on d.idpeople = p.idpeople"
+ " left join monuments AS m on d.idmonument = m.idmonuments", conn);

As I say, this works well l,ocally and gives me the results I want. However on x10 i get:

Server Error in '/' Application

Array index is out of range.

Description: HTTP 500. Error processing request.
Stack Trace:
System.IndexOutOfRangeException: Array index is out of range.
at System.Data.Common.DataAdapter.FillTable (System.Data.DataTable dataTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords, System.Int32& counter) [0x00000]
at System.Data.Common.DataAdapter.FillInternal (System.Data.DataSet dataSet, System.String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) [0x00000]
at System.Data.Common.DataAdapter.Fill (System.Data.DataSet dataSet, System.String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) [0x00000]
at System.Data.Common.DbDataAdapter.Fill (System.Data.DataSet dataSet, Int32 startRecord, Int32 maxRecords, System.String srcTable, IDbCommand command, CommandBehavior behavior) [0x00000]
at System.Data.Common.DbDataAdapter.Fill (System.Data.DataSet dataSet, System.String srcTable) [0x00000]
at (wrapper remoting-invoke-with-check) System.Data.Common.DbDataAdapter:Fill (System.Data.DataSet,string)
at dates.Page_Load (System.Object sender, System.EventArgs e) [0x00000]
at System.Web.UI.Control.OnLoad (System.EventArgs e) [0x00000]
at System.Web.UI.Control.LoadRecursive () [0x00000]
at System.Web.UI.Page.ProcessLoad () [0x00000]
at System.Web.UI.Page.ProcessPostData () [0x00000]
at System.Web.UI.Page.InternalProcessRequest () [0x00000]
at System.Web.UI.Page.ProcessRequest (System.Web.HttpContext context) [0x00000] Version information: Mono Version: 2.0.50727.1433; ASP.NET Version: 2.0.50727.1433


Any help or suggestions would be greatly appreciated.

Thx
Edit:
forgive a newbies ignorance. I tried the sql statement in phpadmin and it worked! If i had read the error message correc tly it would seem that the error is occurring in the data adapter. code below:

conn.Open();

cmd.ExecuteNonQuery();

sda = new MySqlDataAdapter(cmd);
ds = new DataSet();
sda.Fill(ds);

ListView1.DataSource = ds.Tables[0].DefaultView;
ListView1.DataBind();

// close database connection
conn.Close();

i have used this same code on other pages without problems. Can anybody suggest anything pls.

thx.
Edit:
more info:

if i use a simple select statement i.e. select a, b from c it works, no exception problems.
Edit:
CRACKED IT!

was:

cmd = new MySqlCommand("select *, p.nameP, s.SiteName, m.nameM from dates AS d"
+ " join sites AS s on (d.SiteID = s.SiteID)"
+ " left join people AS p on (d.idpeople = p.idpeople)"
+ " left join monuments AS m on (d.idmonument = m.idmonuments)", conn);

should be:

cmd = new MySqlCommand("select d.*, p.nameP, s.SiteName, m.nameM from dates AS d"
+ " join sites AS s on (d.SiteID = s.SiteID)"
+ " left join people AS p on (d.idpeople = p.idpeople)"
+ " left join monuments AS m on (d.idmonument = m.idmonuments)", conn);

it's a small difference but makes a huge difference. What don't understand is why it worked on my localhost but not on x10. Anyway thats for another day.