Dear All,
I'm presently working through the ASP.NET 1.1 c/w Visual C#.NET 2003 book, and have come slightly unstuck.
Rather than use the wizards as dictated by the book, I have re-written a section of code to handle the retrieval of DataSets. This code seems to work fine, and retrieves an OleDB dataset without any problems. Here's the code...
DataSet getData(String pConnect, String pSQL, String pName) {
DataSet dstData = new DataSet();
OleDbConnection objConn = new OleDbConnection(pConnect);
OleDbDataAdapter objComm = new OleDbDataAdapter(pSQL, objConn);
objComm.Fill(dstData, pName);
return dstData;
}
See - no great shakes! I supply the connection string, SQL string and a name for the dataset. This works until I attempt to retrieve a second dataset for a completely different object. Then, the infamous E_FAIL(0x80004005) is returned, basically telling me that the SQL string is a bit dubious. Well, it isn't - it works fine. I've tested it in Access and had results returned.
Here's the excercise that I've typed up from the book with my own code implementation in...
<%@ Page Language="C#" Debug="true" Trace="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">
//Constants...
public const String cProviderMS = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;";
public const String connDB = cProviderMS + "Data Source=C:\\BegASPNET11\\WroxUnited\\Database\\WroxUnited.mdb";
//Trace.Write(connDB);
public const String sqlTeam = "SELECT * FROM Teams";
//Trace.Write(sqlTeam);
string sqlPlayer = "SELECT PlayerName, PositionName " +
"FROM Players, Positions, PlayerTeam, Teams " +
"WHERE ((PlayerTeam.PlayerID = Players.PlayerID) " +
"AND (PlayerTeam.Position = Positions.PositionID) " +
"AND (PlayerTeam.PlayerID = Teams.TeamID)) ";
string selectedTeam;
//Embedded functions...
DataSet getData(String pConnect, String pSQL, String pName) {
DataSet dstData = new DataSet();
OleDbConnection objConn = new OleDbConnection(pConnect);
OleDbDataAdapter objComm = new OleDbDataAdapter(pSQL, objConn);
objComm.Fill(dstData, pName);
objConn = null;
return dstData;
}
//Event handlers
void Page_Load() {
//Initialise the data controls...
TeamList.DataSource = getData(connDB, sqlTeam , "dataTeam");
TeamList.DataBind();
//Debugging...
//string test = cProviderMS + "C:\\BegASPNET11\\WroxUnited\\Database\\WroxUnited.mdb";
}
void TeamList_ItemCommand(object sender, DataListCommandEventArgs e) {
Trace.Write("Start of TeamList_ItemCommand");
if (e.CommandName == "ShowTeam") {
LinkButton button = (LinkButton)e.CommandSource;
selectedTeam = button.Text;
Trace.Write("Yup - I'm here! " + e.CommandArgument.ToString());
string playerSQL = sqlPlayer + ";";
Trace.Write(playerSQL);
PlayerList.DataSource = getData(connDB, playerSQL, "dataPlayer");
PlayerList.DataBind();
}
}
</script>
<html>
<head>
</head>
<body>
<form runat="server">
<h1>Wrox United
</h1>
<h2>Teams
</h2>
<p>
<table>
<tbody>
<tr>
<td>
<asp
ataList id="TeamList" runat="server" OnItemCommand="TeamList_ItemCommand">
<ItemTemplate>
<asp:linkbutton
text='<%# DataBinder.Eval(Container.DataItem, "TeamName") %>'
CommandArgument='<%# DataBinder.Eval(Container.DataItem, "TeamID") %>'
id="TeamNameLink"
style="color:darkred"
CommandName="ShowTeam"
runat="server" />
<br />
<asp:Label
text='<%# DataBinder.Eval(Container.DataItem, "Notes") %>'
id="TeamNotes"
runat="server" />
</ItemTemplate>
<SeparatorTemplate>
<br />
<hr color="#b0c4de" width="200px" />
</SeparatorTemplate>
</aspataList>
</td>
<td style="VERTICAL-ALIGN: top">
<asp:Repeater id="PlayerList" runat="server">
<ItemTemplate>
<asp:linkbutton
text='<%# DataBinder.Eval(Container.DataItem, "PlayerName") %>'
style="color:darkred"
runat="server"
width="120" />
<asp:Label
text='<%# DataBinder.Eval(Container.DataItem, "PositionName") %>'
id="playerposition"
runat="server" />
</ItemTemplate>
<HeaderTemplate>
Players in: <%= selectedTeam %>
</HeaderTemplate>
<FooterTemplate>
<hr color="#b0c4de" width="200px" />
</FooterTemplate>
</asp:Repeater>
</td>
</tr>
</tbody>
</table>
<!-- Insert content here -->
</p>
</form>
</body>
</html>
Can you have a look at it for me, and tell me where I'm going wrong. Please don't just post code - I'd really like to see what I've done wrong.
Thank-you all in advance,
Paul.The error is your SQL string. OLE/DB for Jet does not like join statements like you have. Any results from a different table with field names that are the same must be alaised to something else or selected only explicitly without duplicated field names in the field list.I see. That's quite odd!
Thanks, afterburn - I'll bear that in mind.
I'm presently working through the ASP.NET 1.1 c/w Visual C#.NET 2003 book, and have come slightly unstuck.
Rather than use the wizards as dictated by the book, I have re-written a section of code to handle the retrieval of DataSets. This code seems to work fine, and retrieves an OleDB dataset without any problems. Here's the code...
DataSet getData(String pConnect, String pSQL, String pName) {
DataSet dstData = new DataSet();
OleDbConnection objConn = new OleDbConnection(pConnect);
OleDbDataAdapter objComm = new OleDbDataAdapter(pSQL, objConn);
objComm.Fill(dstData, pName);
return dstData;
}
See - no great shakes! I supply the connection string, SQL string and a name for the dataset. This works until I attempt to retrieve a second dataset for a completely different object. Then, the infamous E_FAIL(0x80004005) is returned, basically telling me that the SQL string is a bit dubious. Well, it isn't - it works fine. I've tested it in Access and had results returned.
Here's the excercise that I've typed up from the book with my own code implementation in...
<%@ Page Language="C#" Debug="true" Trace="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">
//Constants...
public const String cProviderMS = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;";
public const String connDB = cProviderMS + "Data Source=C:\\BegASPNET11\\WroxUnited\\Database\\WroxUnited.mdb";
//Trace.Write(connDB);
public const String sqlTeam = "SELECT * FROM Teams";
//Trace.Write(sqlTeam);
string sqlPlayer = "SELECT PlayerName, PositionName " +
"FROM Players, Positions, PlayerTeam, Teams " +
"WHERE ((PlayerTeam.PlayerID = Players.PlayerID) " +
"AND (PlayerTeam.Position = Positions.PositionID) " +
"AND (PlayerTeam.PlayerID = Teams.TeamID)) ";
string selectedTeam;
//Embedded functions...
DataSet getData(String pConnect, String pSQL, String pName) {
DataSet dstData = new DataSet();
OleDbConnection objConn = new OleDbConnection(pConnect);
OleDbDataAdapter objComm = new OleDbDataAdapter(pSQL, objConn);
objComm.Fill(dstData, pName);
objConn = null;
return dstData;
}
//Event handlers
void Page_Load() {
//Initialise the data controls...
TeamList.DataSource = getData(connDB, sqlTeam , "dataTeam");
TeamList.DataBind();
//Debugging...
//string test = cProviderMS + "C:\\BegASPNET11\\WroxUnited\\Database\\WroxUnited.mdb";
}
void TeamList_ItemCommand(object sender, DataListCommandEventArgs e) {
Trace.Write("Start of TeamList_ItemCommand");
if (e.CommandName == "ShowTeam") {
LinkButton button = (LinkButton)e.CommandSource;
selectedTeam = button.Text;
Trace.Write("Yup - I'm here! " + e.CommandArgument.ToString());
string playerSQL = sqlPlayer + ";";
Trace.Write(playerSQL);
PlayerList.DataSource = getData(connDB, playerSQL, "dataPlayer");
PlayerList.DataBind();
}
}
</script>
<html>
<head>
</head>
<body>
<form runat="server">
<h1>Wrox United
</h1>
<h2>Teams
</h2>
<p>
<table>
<tbody>
<tr>
<td>
<asp
ataList id="TeamList" runat="server" OnItemCommand="TeamList_ItemCommand"><ItemTemplate>
<asp:linkbutton
text='<%# DataBinder.Eval(Container.DataItem, "TeamName") %>'
CommandArgument='<%# DataBinder.Eval(Container.DataItem, "TeamID") %>'
id="TeamNameLink"
style="color:darkred"
CommandName="ShowTeam"
runat="server" />
<br />
<asp:Label
text='<%# DataBinder.Eval(Container.DataItem, "Notes") %>'
id="TeamNotes"
runat="server" />
</ItemTemplate>
<SeparatorTemplate>
<br />
<hr color="#b0c4de" width="200px" />
</SeparatorTemplate>
</asp
ataList></td>
<td style="VERTICAL-ALIGN: top">
<asp:Repeater id="PlayerList" runat="server">
<ItemTemplate>
<asp:linkbutton
text='<%# DataBinder.Eval(Container.DataItem, "PlayerName") %>'
style="color:darkred"
runat="server"
width="120" />
<asp:Label
text='<%# DataBinder.Eval(Container.DataItem, "PositionName") %>'
id="playerposition"
runat="server" />
</ItemTemplate>
<HeaderTemplate>
Players in: <%= selectedTeam %>
</HeaderTemplate>
<FooterTemplate>
<hr color="#b0c4de" width="200px" />
</FooterTemplate>
</asp:Repeater>
</td>
</tr>
</tbody>
</table>
<!-- Insert content here -->
</p>
</form>
</body>
</html>
Can you have a look at it for me, and tell me where I'm going wrong. Please don't just post code - I'd really like to see what I've done wrong.
Thank-you all in advance,
Paul.The error is your SQL string. OLE/DB for Jet does not like join statements like you have. Any results from a different table with field names that are the same must be alaised to something else or selected only explicitly without duplicated field names in the field list.I see. That's quite odd!
Thanks, afterburn - I'll bear that in mind.