mysql 索引 边界,sql语法和/或代码问题？ “索引在数组的边界之外。”

I had to do some refactoring to my database it did look like this:

But I had to change WallPosting to this:

Now my problem is how to fix my sql syntax so my code works again, I made a few manual entries for now to see if I can get them to be displayed:

The FriendUserID relates to another UserID in the usertable who has obviously a different picture and information but I don't know how to display to concurrent WallPosting's from different users atm.

my code creates a dynamic div gives the div an ID = to the userid and input the wallpost messages named wallpostings, it takes the information stored about the userid and applys the image related to that userid, is there any way this can be changed with sql? or have I went down a one way alley? atm I just want to see if I can get the populatewallposts select statement to work.

My code:

public partial class UserProfileWall : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

if (Page.IsPostBack)

{

//It is a postback so check if it was by div click (NOT WORKING because the javascript isnt posting back)

string target = Request["__EVENTTARGET"];

if (target == "DivClicked")

{

string id = Request["__EVENTARGUMENT"];

//Call my delete function passing record id

using (OdbcConnection cn = new OdbcConnection("Driver={MySQL ODBC 3.51 Driver}; Server=localhost; Database=gymwebsite2; User=root; Password=commando;"))

{

cn.Open();

using (OdbcCommand cmd = new OdbcCommand("DELETE FROM WallPosting WHERE idWallPosting="+id, cn))

{

cmd.ExecuteNonQuery();

}

}

}

}

string theUserId = Session["UserID"].ToString();

PopulateWallPosts(theUserId);

}

private void PopulateWallPosts(string userId)

{

using (OdbcConnection cn = new OdbcConnection("Driver={MySQL ODBC 3.51 Driver}; Server=localhost; Database=gymwebsite2; User=root; Password=commando;"))

{

cn.Open();

using (OdbcCommand cmd = new OdbcCommand("SELECT idWallPosting, wp.WallPostings, p.PicturePath FROM WallPosting wp LEFT JOIN User u ON u.UserID = wp.UserID LEFT JOIN Pictures p ON p.UserID = u.UserID WHERE wp.UserID=" + userId + " ORDER BY idWallPosting DESC", cn))

{

using (OdbcDataReader reader = cmd.ExecuteReader())

{

test1.Controls.Clear();

while (reader.Read())

{

System.Web.UI.HtmlControls.HtmlGenericControl div = new System.Web.UI.HtmlControls.HtmlGenericControl("div");

div.Attributes["class"] = "test";

div.ID = String.Format("{0}", reader.GetString(0));

string id = Convert.ToString(div.ID);

//store the div id as a string

Image img = new Image();

img.ImageUrl = String.Format("{0}", reader.GetString(2));

img.AlternateText = "Test image";

div.Controls.Add(img);

div.Controls.Add(ParseControl(String.Format("&nbsp&nbsp " + "{0}", reader.GetString(1))));

div.Attributes.Add("onclick", "confirm_delete(" + id + ");");

// send the div id to javascript

div.Style["clear"] = "both";

test1.Controls.Add(div);

}

}

}

}

}

protected void Button1_Click(object sender, EventArgs e)

{

string theUserId = Session["UserID"].ToString();

using (OdbcConnection cn = new OdbcConnection("Driver={MySQL ODBC 3.51 Driver}; Server=localhost; Database=gymwebsite2; User=root; Password=commando;"))

{

cn.Open();

using (OdbcCommand cmd = new OdbcCommand("INSERT INTO WallPosting (UserID, Wallpostings) VALUES (" + theUserId + ", '" + TextBox1.Text + "')", cn))

{

cmd.ExecuteNonQuery();

}

}

PopulateWallPosts(theUserId);

}

}

I get the error: Index was outside the bounds of the array.

using (OdbcCommand cmd = new OdbcCommand("SELECT wp.WallPostings, p.PicturePath FROM WallPosting wp INNER JOIN User u ON u.UserID = wp.FriendUserID INNER JOIN Pictures p ON p.UserID = u.UserID WHERE wp.UserID=" + userId + " ORDER BY idWallPosting DESC", cn))

解决方案

Always store who posted it, even if the owner of the wall did it herself. This will simplify the sql for you.

SELECT wp.WallPostings, p.PicturePath

FROM WallPosting wp

INNER JOIN [User] u ON u.UserID = wp.PostedByUserID

INNER JOIN Pictures p ON p.UserID = u.UserID

WHERE wp.UserID=" + userId + "

ORDER BY idWallPosting DESC

Why is pictures in a separate table if it is 1 to 1?