cartom
asked on
Expected query name after EXECUTE
Hi I am new to ASP.NET and I am trying to manage a DataGrid from an Excel file. The name of the Excel file data range is PressReleases.
I always get a "System.Data.OleDb.OleDbEx ception: Expected query name after EXECUTE" at:
Line 48: adapter.Fill(pr);
I have also tried adapter.Fill(pr, "PressReleases");
but nothing change...
below is my code:
<%@ Page Language="C#" ContentType="text/html" ResponseEncoding="iso-8859 -1" Debug="true"%>
<%@ import Namespace="System" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.Ole Db" %>
<script runat="server">
const int RECORDS_PER_PAGE = 5;
int totalRecords;
private void Page_Load(Object sender, System.EventArgs e) {
// get the number of records found in the database
totalRecords = GetProductsCount();
// calculate and save the number of pages to the Pages hidden field
Pages.Value = Math.Ceiling((double)total Records/RE CORDS_PER_ PAGE).ToSt ring();
if (!Page.IsPostBack)
{
// Bind records to repeater
BindData();
}
}
void BindData()
{
int pageNumber = int.Parse(PageNumber.Value );
int totalPages = int.Parse(Pages.Value);
OleDbConnection connection;
string strConn = "Provider=Microsoft.Jet.OL EDB.4.0;" + "Data Source=" + Server.MapPath("/Test3/Pre ssReleases .xls")+ ";Extended Properties=Excel 8.0;";
connection = new OleDbConnection(strConn);
String query = "select * from PressReleases";
OleDbCommand command = new OleDbCommand("SelectByPage ", connection);
command.CommandType = CommandType.StoredProcedur e;
command.Parameters.Add("@P ageNumber" , pageNumber);
command.Parameters.Add("@P ageSize", RECORDS_PER_PAGE);
command.CommandText = query;
connection.Open();
DataSet pr = new DataSet();
OleDbDataAdapter adapter = new OleDbDataAdapter(query ,connection);
adapter.SelectCommand = command;
adapter.Fill(pr, "Name");
connection.Close();
// Bind Data to the repeater
PressReleasesRepeater.Data Source = pr;
PressReleasesRepeater.Data Bind();
// Display the page links
PagesDisplay.Text = "";
for (int i=1; i<=totalPages; i++)
{
if (pageNumber != i)
PagesDisplay.Text += "<a href=\"javascript:ChangePa ge("+i+")\ ">"+i+"</a > &nb sp;";
else
PagesDisplay.Text += "[" + i + "] ";
}
// enable/disable the links to navigate through the pages
FirstPage.Enabled = (pageNumber != 1);
PrevPage.Enabled = (pageNumber != 1);
NextPage.Enabled = (pageNumber != totalPages);
LastPage.Enabled = (pageNumber != totalPages);
Info.Text = totalRecords + " records are found and divided into " + Pages.Value + " pages<br><br>";
}
// return the number of total records in database
int GetProductsCount()
{
String strFile = "/Test3/PressReleases.xls" ;
string strConn = "Provider=Microsoft.Jet.OL EDB.4.0;" + "Data Source=" + Server.MapPath("/Test3/Pre ssReleases .xls")+ ";Extended Properties=Excel 8.0;";
OleDbConnection connection = new OleDbConnection(strConn);
//OleDbDataAdapter command=new OleDbDataAdapter("select count(*) from PressReleases", connection);
OleDbCommand command = new OleDbCommand("select count(*) from PressReleases", connection);
connection.Open();
//object result = command.ExecuteScalar();
//int count = Convert.ToInt32(result);
int count = Convert.ToInt32(command.Ex ecuteScala r());
connection.Close();
return count;
}
// execute when user clicks on the next/prev/first/last button
void Page_Changed(object sender, CommandEventArgs e)
{
switch (e.CommandName)
{
case "3":
PageNumber.Value = "1";
break;
case "PrevPage":
PageNumber.Value = (int.Parse(PageNumber.Valu e) -1).ToString();
break;
case "NextPage":
PageNumber.Value = (int.Parse(PageNumber.Valu e) +1).ToString();
break;
case "LastPage":
PageNumber.Value = Pages.Value;
break;
}
// rebind data
BindData();
}
// execute when user clicks on the page link
void Page_Click(object sender, System.EventArgs e)
{
// rebind data
BindData();
}
</script>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="javascript">
function ChangePage(id)
{
// save the page clicked
document.all.PageNumber.va lue = id;
// call the __doPostBack function to post back the form and execute the PageClick event
__doPostBack('PageClick',' ');
}
</script>
</head>
<body bgcolor="black">
<form id="Topics" method="post" runat="server">
<div style="background-color: white; width: 300">
<!-- Hidden fields store the current page number and total pages -->
<input type="hidden" runat="server" id="PageNumber" value="1">
<input type="hidden" runat="server" id="Pages" value="0">
<!-- Hidden button to handle the click event when user clicks on a pge link-->
<asp:button ID="PageClick" OnClick="Page_Click" runat="server" Visible="false"></asp:butt on>
<asp:label ID="Info" runat="server"></asp:label >
<asp:linkbutton ID="FirstPage" runat="server" CommandName="FirstPage" OnCommand="Page_Changed">F irst</asp: linkbutton >
<asp:linkbutton ID="PrevPage" runat="server" CommandName="PrevPage" OnCommand="Page_Changed">P rev</asp:l inkbutton>
<asp:label ID="PagesDisplay" runat="server"></asp:label >
<asp:linkbutton ID="NextPage" runat="server" CommandName="NextPage" OnCommand="Page_Changed">N ext</asp:l inkbutton>
<asp:linkbutton ID="LastPage" runat="server" CommandName="LastPage" OnCommand="Page_Changed">L ast</asp:l inkbutton>
<br><br>
<table width="300" style="border: 1 solid gray" align="center">
<tr>
<td bgcolor="gray" style="color: white">Product ID</td>
<td bgcolor="gray" style="color: white">Product Name</td>
</tr>
<asp:repeater ID="PressReleasesRepeater" runat="server">
<itemtemplate>
<tr>
<td><%# DataBinder.Eval(Container. DataItem, "PressReleaseID") %></td>
<td><%# DataBinder.Eval(Container. DataItem, "PressReleaseTitle") %></td>
</tr>
</itemtemplate>
</asp:repeater>
</table>
</div>
</form>
</body>
</html>
Any hints?
I always get a "System.Data.OleDb.OleDbEx
Line 48: adapter.Fill(pr);
I have also tried adapter.Fill(pr, "PressReleases");
but nothing change...
below is my code:
<%@ Page Language="C#" ContentType="text/html" ResponseEncoding="iso-8859
<%@ import Namespace="System" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.Ole
<script runat="server">
const int RECORDS_PER_PAGE = 5;
int totalRecords;
private void Page_Load(Object sender, System.EventArgs e) {
// get the number of records found in the database
totalRecords = GetProductsCount();
// calculate and save the number of pages to the Pages hidden field
Pages.Value = Math.Ceiling((double)total
if (!Page.IsPostBack)
{
// Bind records to repeater
BindData();
}
}
void BindData()
{
int pageNumber = int.Parse(PageNumber.Value
int totalPages = int.Parse(Pages.Value);
OleDbConnection connection;
string strConn = "Provider=Microsoft.Jet.OL
connection = new OleDbConnection(strConn);
String query = "select * from PressReleases";
OleDbCommand command = new OleDbCommand("SelectByPage
command.CommandType = CommandType.StoredProcedur
command.Parameters.Add("@P
command.Parameters.Add("@P
command.CommandText = query;
connection.Open();
DataSet pr = new DataSet();
OleDbDataAdapter adapter = new OleDbDataAdapter(query ,connection);
adapter.SelectCommand = command;
adapter.Fill(pr, "Name");
connection.Close();
// Bind Data to the repeater
PressReleasesRepeater.Data
PressReleasesRepeater.Data
// Display the page links
PagesDisplay.Text = "";
for (int i=1; i<=totalPages; i++)
{
if (pageNumber != i)
PagesDisplay.Text += "<a href=\"javascript:ChangePa
else
PagesDisplay.Text += "[" + i + "] ";
}
// enable/disable the links to navigate through the pages
FirstPage.Enabled = (pageNumber != 1);
PrevPage.Enabled = (pageNumber != 1);
NextPage.Enabled = (pageNumber != totalPages);
LastPage.Enabled = (pageNumber != totalPages);
Info.Text = totalRecords + " records are found and divided into " + Pages.Value + " pages<br><br>";
}
// return the number of total records in database
int GetProductsCount()
{
String strFile = "/Test3/PressReleases.xls"
string strConn = "Provider=Microsoft.Jet.OL
OleDbConnection connection = new OleDbConnection(strConn);
//OleDbDataAdapter command=new OleDbDataAdapter("select count(*) from PressReleases", connection);
OleDbCommand command = new OleDbCommand("select count(*) from PressReleases", connection);
connection.Open();
//object result = command.ExecuteScalar();
//int count = Convert.ToInt32(result);
int count = Convert.ToInt32(command.Ex
connection.Close();
return count;
}
// execute when user clicks on the next/prev/first/last button
void Page_Changed(object sender, CommandEventArgs e)
{
switch (e.CommandName)
{
case "3":
PageNumber.Value = "1";
break;
case "PrevPage":
PageNumber.Value = (int.Parse(PageNumber.Valu
break;
case "NextPage":
PageNumber.Value = (int.Parse(PageNumber.Valu
break;
case "LastPage":
PageNumber.Value = Pages.Value;
break;
}
// rebind data
BindData();
}
// execute when user clicks on the page link
void Page_Click(object sender, System.EventArgs e)
{
// rebind data
BindData();
}
</script>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="javascript">
function ChangePage(id)
{
// save the page clicked
document.all.PageNumber.va
// call the __doPostBack function to post back the form and execute the PageClick event
__doPostBack('PageClick','
}
</script>
</head>
<body bgcolor="black">
<form id="Topics" method="post" runat="server">
<div style="background-color: white; width: 300">
<!-- Hidden fields store the current page number and total pages -->
<input type="hidden" runat="server" id="PageNumber" value="1">
<input type="hidden" runat="server" id="Pages" value="0">
<!-- Hidden button to handle the click event when user clicks on a pge link-->
<asp:button ID="PageClick" OnClick="Page_Click" runat="server" Visible="false"></asp:butt
<asp:label ID="Info" runat="server"></asp:label
<asp:linkbutton ID="FirstPage" runat="server" CommandName="FirstPage" OnCommand="Page_Changed">F
<asp:linkbutton ID="PrevPage" runat="server" CommandName="PrevPage" OnCommand="Page_Changed">P
<asp:label ID="PagesDisplay" runat="server"></asp:label
<asp:linkbutton ID="NextPage" runat="server" CommandName="NextPage" OnCommand="Page_Changed">N
<asp:linkbutton ID="LastPage" runat="server" CommandName="LastPage" OnCommand="Page_Changed">L
<br><br>
<table width="300" style="border: 1 solid gray" align="center">
<tr>
<td bgcolor="gray" style="color: white">Product ID</td>
<td bgcolor="gray" style="color: white">Product Name</td>
</tr>
<asp:repeater ID="PressReleasesRepeater"
<itemtemplate>
<tr>
<td><%# DataBinder.Eval(Container.
<td><%# DataBinder.Eval(Container.
</tr>
</itemtemplate>
</asp:repeater>
</table>
</div>
</form>
</body>
</html>
Any hints?
ASKER
Thank you sammy,
finally something changed! Now it returns an error:
"System.InvalidOperationEx ception: Fill: SelectCommand.Connection property has not been initialized."
Any idea?
cheers
finally something changed! Now it returns an error:
"System.InvalidOperationEx
Any idea?
cheers
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You are using a select statement from a string and telling the command object to look for a stored procedure
HTH