SQL语法问题，或code万阿英，蒋达清？ [英] sql syntax problem and or code probelm?
问题描述
嘿，我不得不做一些重构到我的数据库它没有看起来像这样：
但我不得不WallPosting改成这样：
现在我的问题是如何再次修复我的SQL语法，所以我的code工作，我做了一些人工entrys现在，看看我是否能得到要显示他们：
该FriendUserID涉及谁拥有明显不同的图片和信息UserTable中另一个用户名，但我不知道如何显示并发WallPosting的来自不同用户的自动取款机。
我的code创建一个动态的div给DIV一个ID =的用户名和输入命名wallpostings的wallpost messgaes，它需要存储有关用户标识的信息，applys与该用户ID形象，有什么办法这可以用SQL来改变？还是我去了一个单向的胡同？ ATM我只是想看看我是否能得到populatewallposts select语句的工作任何一个可以帮助？
我的code：
公共部分类UserProfileWall：System.Web.UI.Page
{ 保护无效的Page_Load（对象发件人，EventArgs的发送）
{
如果（Page.IsPostBack）
{
//这是一个回传，以便检查它是否是由格键（不工作，因为JavaScript的心不是回发）
目标字符串=请求[__ EVENTTARGET];
如果（目标==DivClicked）
{
字符串ID =请求[__ EVENTARGUMENT];
//调用我的删除功能传球记录id
使用（OdbcConnection CN =新OdbcConnection（驱动程序= {MySQL的ODBC驱动程序3.51};服务器=本地主机;数据库= gymwebsite2;用户=根;密码=突击队;））
{
cn.Open（）;
使用（CMD的OdbcCommand =新的OdbcCommand（DELETE FROM WallPosting WHERE idWallPosting =+ ID，CN））
{
cmd.ExecuteNonQuery（）;
}
} }
}
。字符串theUserId =会话[用户名]的ToString（）;
PopulateWallPosts（theUserId）;
}
私人无效PopulateWallPosts（字符串userid）
{ 使用（OdbcConnection CN =新OdbcConnection（驱动程序= {MySQL的ODBC驱动程序3.51};服务器=本地主机;数据库= gymwebsite2;用户=根;密码=突击队;））
{
cn.Open（）;
使用（CMD的OdbcCommand =新的OdbcCommand（选择idWallPosting，wp.WallPostings，p.PicturePath FROM WallPosting WP LEFT JOIN用户U ON u.UserID = wp.UserID LEFT JOIN图片P于p.UserID = u.UserID WHERE wp.UserID =+用户id +ORDER BY idWallPosting DESC，CN））
{ 使用（OdbcDataReader读卡器= cmd.ExecuteReader（））
{
test1.Controls.Clear（）; 而（reader.Read（））
{ System.Web.UI.HtmlControls.HtmlGenericControl DIV =新System.Web.UI.HtmlControls.HtmlGenericControl（分区）;
div.Attributes [类] =测试;
div.ID =的String.Format（{0}，reader.GetString（0））;
字符串ID = Convert.ToString（div.ID）;
//在div ID存储为一个字符串
图片IMG =新的图像（）;
img.ImageUrl =的String.Format（{0}，reader.GetString（2））;
img.AlternateText =测试图像; div.Controls.Add（IMG）;
div.Controls.Add（ParseControl（的String.Format（与＆amp; NBSP＆放大器; NBSP＆放大器; NBSP;+{0}，reader.GetString（1））））;
div.Attributes.Add（的onclick，confirm_delete（+ ID +）;）;
//股利ID发送到的JavaScript
div.Style [清除] =既;
test1.Controls.Add（DIV）; }
}
}
}
} 保护无效的button1_Click（对象发件人，EventArgs的发送）
{
。字符串theUserId =会话[用户名]的ToString（）;
使用（OdbcConnection CN =新OdbcConnection（驱动程序= {MySQL的ODBC驱动程序3.51};服务器=本地主机;数据库= gymwebsite2;用户=根;密码=突击队;））
{
cn.Open（）;
使用（CMD的OdbcCommand =新的OdbcCommand（INSERT INTO WallPosting（用户ID，Wallpostings）VALUES（+ theUserId +，'+ TextBox1.Text +'），CN））
{
cmd.ExecuteNonQuery（）;
}
}
PopulateWallPosts（theUserId）;
}
}
编辑：
我得到的错误：指数数组的边界之外
。 使用（CMD的OdbcCommand =新的OdbcCommand（SELECT wp.WallPostings，p.PicturePath FROM WallPosting WP INNER JOIN用户U ON u.UserID = wp.FriendUserID INNER JOIN图像P P上.UserID = u.UserID WHERE wp.UserID =+用户id +ORDER BY idWallPosting DESC，CN））
始终存放谁贴吧，即使墙的主人做到了自己。这将简化SQL为您服务。
SELECT wp.WallPostings，p.PicturePath
从WallPosting WP
INNER JOIN [用户] V ON u.UserID = wp.PostedByUserID
INNER JOIN图片P于p.UserID = u.UserID
WHERE wp.UserID =+用户id +
ORDER BY idWallPosting DESC
为什么是在一个单独的表画面，如果它是1对1
Hey 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 entrys 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 dont 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 messgaes 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 can any one help?
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("   " + "{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);
}
}
EDIT:
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?
这篇关于SQL语法问题，或code万阿英，蒋达清？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
