locked
AddWithValue Parameters problem RRS feed

  • Question

  • User-1998474842 posted

    HI guys

    please see error below? I don't know why?

    Oops!! An error occurred: The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect. 

    <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="_Default2" %>
    
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
        <asp:ScriptManager ID="ScriptManager1" runat="server">
            </asp:ScriptManager>
            <asp:UpdatePanel ID="UpdatePanel1" runat="server">
            <ContentTemplate>       
    <fieldset style="width:365px;">
        <legend>Load more data on demand in repeater</legend>
        <table>
        <tr>
        <td>
          <asp:Repeater ID="rptStudentDetails" runat="server">
            <HeaderTemplate>
            <table border="1" cellpadding="1">
            <tr style="background-color:#fa7b16; color:#FFF; height:35px;" align="center">
            <th>Name</th>
            </tr>
            </HeaderTemplate>
            <ItemTemplate>
            <tr style="background-color:white;" align="center">
            <td><%#Eval("Name") %></td>
            </tr>
            </ItemTemplate>      
            <FooterTemplate>
            </table>       
            </FooterTemplate>
            </asp:Repeater> 
        </td>
        </tr>
        <tr>
    
        <td align="center">
            <asp:Button ID="btnLoadMore" runat="server" Text="Load More Data"
                onclick="btnLoadMore_Click" />
                </td>
                </tr>
                <tr>
                <td align="center">
                <asp:UpdateProgress ID="UpdateProgress1" runat="server" ClientIDMode="Static" DisplayAfter="10">
        <ProgressTemplate>
            <img src="ajax-loader.gif" alt="wait image" />   
        </ProgressTemplate>
        </asp:UpdateProgress>
                </td></tr>
        </table>
        </fieldset>
        </ContentTemplate>   
            </asp:UpdatePanel>   
        </div>   
        </form>
    </body>
    </html>
    using System;
    using System.Configuration;
    using System.Data;
    using System.Data.OleDb;
    using System.Web.UI;
    
    public partial class _Default2 : System.Web.UI.Page
    {
         OleDbConnection con = new OleDbConnection(ConfigurationManager.ConnectionStrings["AccessPageBenadb"].ConnectionString);
    
        int num = 0;
    
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                //Set the num variable equal to the value that you want to load data in repeater.
                //e.g if initially you want to load 2 rows in repeater then set num=2.
                num = 2;
                //store this num value in ViewState so that we can get this value on Load more data button click 
                ViewState["num"] = num;
                BindRepeater(num);
            }
        }
    
        private void BindRepeater(int numOfRows)
        {
            DataTable dt = new DataTable();
            OleDbCommand cmd = null;
            OleDbDataAdapter adp = null;
            try
            {
                //get number rows in table by calling the rowCount function i created.
                int rCount = rowCount();
                // hide the "Load More Data button" if the number of request rows becomes greater than the rows in table
                if (numOfRows > rCount)
                {
                    btnLoadMore.Visible = false;
                }
                cmd = new OleDbCommand("Select top (@zhmara) * FROM Face_BusinessPage_Pages", con);
                //Passs numOfRows variable value to stored procedure to get desired number of rows
                cmd.Parameters.AddWithValue("@zhmara", numOfRows);
                cmd.CommandType = CommandType.Text;
                adp = new OleDbDataAdapter(cmd);
                adp.Fill(dt);
    
                if (dt.Rows.Count > 0)
                {
                    rptStudentDetails.DataSource = dt;
                    rptStudentDetails.DataBind();
                }
                else
                {
                    rptStudentDetails.DataSource = null;
                    rptStudentDetails.DataBind();
                }
            }
            catch (Exception ex)
            {
                ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Oops!! Error occured: " + ex.Message.ToString() + "');", true);
            }
            finally
            {
                con.Close();
                cmd.Dispose();
                adp = null;
                dt.Clear();
                dt.Dispose();
            }
        }
    
        protected int rowCount()
        {
            int NoOfRows = 0;
            OleDbCommand cmd = new OleDbCommand("SELECT COUNT(*) FROM Face_BusinessPage_Pages", con);
            cmd.CommandType = CommandType.Text;
            try
            {
                con.Open();
                NoOfRows = Convert.ToInt32(cmd.ExecuteScalar());
            }
            catch (Exception ex)
            {
                ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Oops!! Error occured: " + ex.Message.ToString() + "');", true);
            }
            finally
            {
                con.Close();
                cmd.Dispose();
            }
            return NoOfRows;
        }
        protected void btnLoadMore_Click(object sender, EventArgs e)
        {
            //On every click of this button it will add 2 to the ViewState["num"] whose value was set to 2 initially on page load. So numval is 4 now.
            int numVal = Convert.ToInt32(ViewState["num"]) + 2;
            //Now pass numVal whose value is 4 to the BindRepeater function to get 4 rows.
            BindRepeater(numVal);
            //Set ViewState["num"] is equal to the numVal i.e. 4 so that when we again click this button it will be 4 + 2= 6 and so on.
            ViewState["num"] = numVal;
        }
    }

    Sunday, September 29, 2019 8:05 PM

Answers

  • User288213138 posted

    Hi Peshangm,

    I tested the code you posted, it works fine in my side.

    The result:

    cmd = new OleDbCommand("Select top (@zhmara) * FROM Face_BusinessPage_Pages", con);
                //Passs numOfRows variable value to stored procedure to get desired number of rows
                cmd.Parameters.AddWithValue("@zhmara", numOfRows);

    You said pass numOfRows variable value to stored procedure, but I did not find that you have called the stored procedure in the code.

    So if you still have questions, please post your full code and stored procedure.

    Best regards,

    Sam

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 30, 2019 2:47 AM
  • User288213138 posted

    Hi peshangm,

    could you please share your code?

    I am using your post code.

    Best regards,

    Sam

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 1, 2019 1:19 AM

All replies

  • User288213138 posted

    Hi Peshangm,

    I tested the code you posted, it works fine in my side.

    The result:

    cmd = new OleDbCommand("Select top (@zhmara) * FROM Face_BusinessPage_Pages", con);
                //Passs numOfRows variable value to stored procedure to get desired number of rows
                cmd.Parameters.AddWithValue("@zhmara", numOfRows);

    You said pass numOfRows variable value to stored procedure, but I did not find that you have called the stored procedure in the code.

    So if you still have questions, please post your full code and stored procedure.

    Best regards,

    Sam

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 30, 2019 2:47 AM
  • User-1998474842 posted

    could you please share your code?

    Monday, September 30, 2019 7:22 PM
  • User288213138 posted

    Hi peshangm,

    could you please share your code?

    I am using your post code.

    Best regards,

    Sam

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 1, 2019 1:19 AM