Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1104
  • Last Modified:

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.OleDbException: 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.OleDb" %>
<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)totalRecords/RECORDS_PER_PAGE).ToString();
                  
            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.OLEDB.4.0;" + "Data Source=" + Server.MapPath("/Test3/PressReleases.xls")+ ";Extended Properties=Excel 8.0;";

      connection = new OleDbConnection(strConn);

      String query = "select * from PressReleases";

  OleDbCommand command = new OleDbCommand("SelectByPage", connection);

            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@PageNumber", pageNumber);
            command.Parameters.Add("@PageSize", 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.DataSource = pr;
            PressReleasesRepeater.DataBind();

            
            // Display the page links
            PagesDisplay.Text = "";
            for (int i=1; i<=totalPages; i++)
            {
                  if (pageNumber != i)
                        PagesDisplay.Text += "<a href=\"javascript:ChangePage("+i+")\">"+i+"</a>&nbsp;&nbsp;";
                  else
                        PagesDisplay.Text += "[" + i + "]&nbsp;&nbsp;";
            }
            
            // 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.OLEDB.4.0;" + "Data Source=" + Server.MapPath("/Test3/PressReleases.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.ExecuteScalar());

            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.Value) -1).ToString();
                        break;
                  case "NextPage":
                        PageNumber.Value = (int.Parse(PageNumber.Value) +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.value = 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:button>
            <asp:label ID="Info" runat="server"></asp:label>
            <asp:linkbutton ID="FirstPage" runat="server" CommandName="FirstPage" OnCommand="Page_Changed">First</asp:linkbutton>&nbsp;
            <asp:linkbutton ID="PrevPage" runat="server" CommandName="PrevPage" OnCommand="Page_Changed">Prev</asp:linkbutton>&nbsp;
            <asp:label ID="PagesDisplay" runat="server"></asp:label>
            <asp:linkbutton ID="NextPage" runat="server" CommandName="NextPage" OnCommand="Page_Changed">Next</asp:linkbutton>&nbsp;
            <asp:linkbutton ID="LastPage" runat="server" CommandName="LastPage" OnCommand="Page_Changed">Last</asp:linkbutton>
            <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?
0
cartom
Asked:
cartom
  • 2
1 Solution
 
Sammy AgeilCommented:
command.CommandType = CommandType.StoredProcedure; should be changed to command.CommandType = CommandType.Text;

You are using a select statement from a string and telling the command object to look for a stored procedure

HTH
0
 
cartomAuthor Commented:
Thank you sammy,

finally something changed!  Now it returns an error:
"System.InvalidOperationException: Fill: SelectCommand.Connection property has not been initialized."

Any idea?

cheers
0
 
Sammy AgeilCommented:
Thats because you didnt open the connection and tried to use it :-)
after this line
 connection = new OleDbConnection(strConn);
add this
connection.Open();


Good luck
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now