Hi I have an sql syntax error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[User] u ON u.UserID = wp.UserID LEFT JOIN Pictures p ON p.UserID = u.UserID WHE' at line 1
My code:
using (OdbcCommand cmd = new OdbcCommand("SELECT 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 UserID=" + userId + " ORDER BY idWallPosting DESC", cn))
{
//("SELECT 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 UserID=" + userId + " ORDER BY idWallPosting DESC", cn))
using (OdbcDataReader reader = cmd.ExecuteReader())
{
test1.Controls.Clear();
开发者_Go百科
while (reader.Read())
{
System.Web.UI.HtmlControls.HtmlGenericControl div = new System.Web.UI.HtmlControls.HtmlGenericControl("div");
div.Attributes["class"] = "test";
//div.Style["float"] = "left";
div.ID = "test";
Image img = new Image();
img.ImageUrl = String.Format("{1}", reader.GetString(0));
// this line needs to be represented in sql syntax
img.AlternateText = "Test image";
div.Controls.Add(img);
div.Controls.Add(ParseControl(String.Format("{0}", reader.GetString(0))));
div.Style["clear"] = "both";
test1.Controls.Add(div);
}
}
}
}
}
My database structure:
EDIT:
If i take the brackets out of the User in mysql syntax I then get an error:
Column 'UserID' in where clause is ambiguous
RE EDIT:
adding wp. to UserID in WHERE clause gives a new error:
Index (zero based) must be greater than or equal to zero and less than the size of the argument list.
Im sure this is due to this line:
img.ImageUrl = String.Format("{1}", reader.GetString(0));
UserID exists in multiple tables so you have to fully qualify it in the WHERE clause.
V
... WHERE wp.UserID= ...
^
Replace this:
WHERE UserID=
With this:
WHERE wp.UserID=
You use both Picture
and User
in your query, both of which have a UserId
column. You'll need to tell your query which one to use.
Eg.
WHERE wp.UserId =
EDIT 1
Also,
In your String.Format
calls, replace "{1}"
with "{0}"
. "{1}"
tells Format to look for the 2nd value you're passing, not the first (since it's zero based).
EDIT 2
As for the original issue (for completeness' sake), remove the square brackets from around [User]
in the SQL in your first line.
精彩评论