SQL语法问题和或代码问题？ [英] sql syntax problem and or code problem?
问题描述
我必须对数据库进行一些重构，它看起来像这样：
但我必须将WallPosting更改为：
现在我的问题是如何解决我的SQL语法，所以我的代码再次工作，我现在做了一些手动entrys，看看我是否可以让他们显示：
FriendUserID与另一个UserID相关usertable谁显然有不同的图片和信息，但我不知道如何显示来自不同用户atm的并发WallPosting。
我的代码创建一个动态div给出div一个ID =给用户ID并输入名为wallpostings的wallpost消息，它将存储关于用户ID的信息并应用与该userid相关的图像，有没有什么办法可以用sql更改？还是我走了一条单向的胡同？ atm我只想看看我是否可以让populatewallposts select语句正常工作。
我的代码：
<$ p
保护无效Page_Load（对象发件人，EventArgs e）
{$ b $ {
$ b保护无效b $ b if（Page.IsPostBack）
{
//这是一个回传，因此检查它是否是div点击（不工作，因为javascript不是回发）
string target = Request [ __EVENTTARGET];
if（target ==DivClicked）
{
string id = Request [__ EVENTARGUMENT];
//使用（OdbcConnection cn = new OdbcConnection（Driver = {MySQL ODBC 3.51 Driver}; Server = localhost; Database = gymwebsite2; User = root; Password = commando）调用我的delete函数传递记录ID
;））
{
cn.Open（）;
using（OdbcCommand cmd = new OdbcCommand（DELETE FROM WallPosting WHERE idWallPosting =+ id，cn））
{
cmd.ExecuteNonQuery（）;
$ b 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 ）
{
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）;
//将div ID存储为字符串
Image img = new Image（）;
img.ImageUrl = String.Format（{0}，reader.GetString（2））;
img.AlternateText =测试图片;
div.Controls.Add（img）;
div.Controls.Add（ParseControl（String.Format（& nbsp& nbsp& nbsp;+{0}，reader.GetString（1））））;
div.Attributes.Add（onclick，confirm_delete（+ id +）;）;
//发送div id到javascript
div.Style [clear] =both;
test1.Controls.Add（div）;
$ b保护无效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）;
编辑：
我得到错误：索引超出了数组的范围。
使用（OdbcCommand cmd = new OdbcCommand（SELECT wp.WallPostings，p.PicturePath FROM WallPosting wp INNER JOIN User u ON u.UserID = wp.FriendUserID INNER JOIN图片p ON p.UserID = u.UserID WHERE wp.UserID = + userId +ORDER BY idWallPosting DESC，cn））
总是存储谁发布它，即使墙的所有者自己做了它。这将简化你的SQL。
SELECT wp.WallPostings，p.PicturePath
从WallPosting wp
INNER JOIN [User] u ON u.UserID = wp.PostedByUserID
INNER JOIN图片p ON p.UserID = u.UserID
WHERE wp.UserID =+ userId +
ORDER BY idWallPosting DESC
为什么图片在1到1的单独表格中？
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 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("   " + "{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语法问题和或代码问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
