sql语法问题和/或代码问题? “索引在数组的边界之外." [英] sql syntax problem and or code problem? "Index was outside the bounds of the array."
问题描述
我不得不对数据库进行一些重构，看起来确实像这样:
I had to do some refactoring to my database it did look like this:
但是我不得不将WallPosting更改为:
But I had to change WallPosting to this:
现在我的问题是如何修复我的sql语法，以便我的代码可以再次工作，我现在做了一些手动输入，看是否可以显示它们:
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:
FriendUserID与用户表中的另一个UserID相关，后者显然具有不同的图片和信息，但是我不知道如何显示来自不同用户atm的并发WallPosting.
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.
我的代码创建了一个动态div，给div一个ID =用户ID，然后输入名为wallpostings的墙贴消息，它获取有关该用户ID的信息并应用与该用户ID相关的图片，是否有任何方法可以用SQL改变?还是我走过一条单向小巷? atm我只想看看是否可以使populatewallposts select语句正常工作.
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.
我的代码:
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("   " + "{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))
推荐答案
始终存储张贴者，即使隔离墙的所有者自己做了.这将为您简化sql.
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
为什么图片与图片的比例是1比1?
Why is pictures in a separate table if it is 1 to 1?
这篇关于sql语法问题和/或代码问题? “索引在数组的边界之外."的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
