locked
what wrong with this code? RRS feed

  • Question

  • User-633861519 posted
    int gt;
    
        protected void Page_Load(object sender, EventArgs e)
        {
            int norows=GridView1.Rows.Count;
            
            for (int i = 0; i < norows; i++)
            {
                string ac = GridView1.Rows[i].Cells[5].Text;
    
                OleDbConnection cn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=|DataDirectory|daksh.mdb");
                OleDbCommand cmd1 = new OleDbCommand();
                
                cmd1.Connection = cn;
                cmd1.Connection.Open();
                cmd1.CommandText = "SELECT SUM(Amount) FROM general_pay WHERE [general_ac no] = 'ac' ";
                gt=cmd1.ExecuteNonQuery();
                Response.Write(gt);
            }
        }



    Monday, January 27, 2014 2:28 AM

Answers

  • User281315223 posted

    I mean't that it seemed like you wanted to use the value that was in your TextBox as a parameter for your query as seen below :

    cmd1.CommandText = "SELECT SUM(Amount) FROM general_pay WHERE [general_ac no] = 'ac' ";

    Were you attempting to pass the following value in and use it for your WHERE clause?

    string ac = GridView1.Rows[i].Cells[5].Text;

    If that is the case, then you'll need to use parameterization to add your parameter in and use it with your query which is handled by the following section of code :

    //Build your query (with an AC parameter)
    var query = "SELECT SUM(Amount) FROM general_pay WHERE [general_ac no] = ?";
    
    //Build your command to execute
    using(OleDbCommand cmd1 = new OleDbCommand(query,cn))
    {
               //Add your parameter (this is going to replace your ? value with the value of your ac variable
               cmd1.Parameters.AddWithValue("@ac",ac);
    
               //Open your connection and execute it
               cn.Open();
    
               //Store the results in your gt variable
               gt = cmd1.ExecuteNonQuery();
    
               //Output your values
               Response.Write(gt);
    }

    or more specifically :

    //Add your parameter (this is going to replace your ? value with the value of your ac variable
    cmd1.Parameters.AddWithValue("@ac",ac);

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 27, 2014 12:28 PM
  • User281315223 posted

    This is because you aren't initally setting a value for your SQL query, however you could likely remove your intial declaration of it here :

    string query;

    and simply create it within your using statement as seen below :

    protected void Page_Load(object sender, EventArgs e)
    {
            int gt = 0;
            
            for (int i = 0; i < GridView1.Rows.Count; i++)
            {
                string ac = GridView1.Rows[i].Cells[5].Text;
    
                using(OleDbConnection cn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=|DataDirectory|daksh.mdb")
                {
                    string query = "SELECT SUM(Amount) FROM general_pay WHERE [general_ac no] = ?";
                    using(OleDbCommand cmd1 = new OleDbCommand(query,cn))
                    {
                       
                        cmd1.Parameters.AddWithValue("@ac",ac);
                        cn.Open();
                        gt = cmd1.ExecuteNonQuery();
                        Response.Write(gt);
                    }
    
                }
            }
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 28, 2014 2:55 PM

All replies

  • User188200844 posted

    What type error are you getting while debugging code.

    Monday, January 27, 2014 2:37 AM
  • User-633861519 posted

    Exception Details: System.Data.OleDb.OleDbException: Unspecified error

    Source Error:

    Line 27:             
    Line 28:             cmd1.Connection = cn;
    Line 29:             cmd1.Connection.Open();
    Line 30:             cmd1.CommandText = "SELECT SUM(Amount) FROM general_pay WHERE [general_ac no] = 'ac' ";
    Line 31:             gt=cmd1.ExecuteNonQuery();


    Source File: d:\work\daksh\10-8-2013\admin\allgeneral.aspx.cs    Line: 29

    Stack Trace:

    [OleDbException (0x80004005): Unspecified error]
       System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) +337
       System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) +86
       System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) +31
       System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +76
       System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +126
       System.Data.OleDb.OleDbConnection.Open() +43
       admin_allgeneral.Page_Load(Object sender, EventArgs e) in d:\work\daksh\10-8-2013\admin\allgeneral.aspx.cs:29
       System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14
       System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35
       System.Web.UI.Control.OnLoad(EventArgs e) +91
       System.Web.UI.Control.LoadRecursive() +74
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2207
    

    Monday, January 27, 2014 2:40 AM
  • User-821857111 posted

    Presumably, you want to pass in a parameter value based on the value of ac:

    cmd1.CommandText = "SELECT SUM(Amount) FROM general_pay WHERE [general_ac no] = ? ";
    cmd1.Parameters.AddWithValue("", ac);

    Monday, January 27, 2014 2:41 AM
  • User-821857111 posted

    System.Data.OleDb.OleDbException: Unspecified error

    Make sure the user account that ASP.NET runs under has MODIFY permissions on the folder that the mdb file is in. 

    Monday, January 27, 2014 2:44 AM
  • User-633861519 posted

    it does have modify permission, i can edit data using gridview

    and it giving errors in all other pages, but this code is in single page

    Monday, January 27, 2014 2:52 AM
  • User281315223 posted

    Are you sure that the connection that you are using for your OleDbConnection connection is correct? As Mike mentioned, you'll want to check and ensure that your permissions are correct so that you can actually run / access the database.

    Another recommendation would be to consider using a "using" statement to wrap around your connection to ensure that it is disposed of properly and remove any chances of improper resource disposal along with adding parameters :

    //Get your parameter value 
    string ac = GridView1.Rows[i].Cells[5].Text;
    
    //An integer to store the number of rows that were affected
    int gt = 0;
    
    //Establish your connection
    using(OleDbConnection cn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=|DataDirectory|daksh.mdb")
    {
         //Build your query (with an AC parameter)
         var query = "SELECT SUM(Amount) FROM general_pay WHERE [general_ac no] = @ac";
    
         //Build your command to execute
         using(OleDbCommand cmd1 = new OleDbCommand(query,cn))
         {
               //Add your parameter
               cmd1.Parameters.AddWithValue("@ac",ac);
    
               //Open your connection and execute it
               cn.Open();
    
               //Store the results in your gt variable
               gt = cmd1.ExecuteNonQuery();
    
               //Output your values
               Response.Write(gt);
         }
    }
    

    As Mike mentioned, depending on your connection, you may not be able to specify the parameter name (ie @ac) and instead may have to use simply '?' which would look like this (as I don't believe that OleDbConnetions allow named parameters) :

    //Get your parameter value 
    string ac = GridView1.Rows[i].Cells[5].Text;
    
    //An integer to store the number of rows that were affected
    int gt = 0;
    
    //Establish your connection
    using(OleDbConnection cn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=|DataDirectory|daksh.mdb")
    {
         //Build your query (with an AC parameter)
         var query = "SELECT SUM(Amount) FROM general_pay WHERE [general_ac no] = ?";
    
         //Build your command to execute
         using(OleDbCommand cmd1 = new OleDbCommand(query,cn))
         {
               //Add your parameter
               cmd1.Parameters.AddWithValue("@ac",ac);
    
               //Open your connection and execute it
               cn.Open();
    
               //Store the results in your gt variable
               gt = cmd1.ExecuteNonQuery();
    
               //Output your values
               Response.Write(gt);
         }
    }
    Monday, January 27, 2014 10:10 AM
  • User-633861519 posted

    //Build your query (with an AC parameter)
         var query = "SELECT SUM(Amount) FROM general_pay WHERE [general_ac no] = ?";

    what do you mean by AC parameter?

    and of which datatype the query willl be

    Monday, January 27, 2014 11:23 AM
  • User281315223 posted

    I mean't that it seemed like you wanted to use the value that was in your TextBox as a parameter for your query as seen below :

    cmd1.CommandText = "SELECT SUM(Amount) FROM general_pay WHERE [general_ac no] = 'ac' ";

    Were you attempting to pass the following value in and use it for your WHERE clause?

    string ac = GridView1.Rows[i].Cells[5].Text;

    If that is the case, then you'll need to use parameterization to add your parameter in and use it with your query which is handled by the following section of code :

    //Build your query (with an AC parameter)
    var query = "SELECT SUM(Amount) FROM general_pay WHERE [general_ac no] = ?";
    
    //Build your command to execute
    using(OleDbCommand cmd1 = new OleDbCommand(query,cn))
    {
               //Add your parameter (this is going to replace your ? value with the value of your ac variable
               cmd1.Parameters.AddWithValue("@ac",ac);
    
               //Open your connection and execute it
               cn.Open();
    
               //Store the results in your gt variable
               gt = cmd1.ExecuteNonQuery();
    
               //Output your values
               Response.Write(gt);
    }

    or more specifically :

    //Add your parameter (this is going to replace your ? value with the value of your ac variable
    cmd1.Parameters.AddWithValue("@ac",ac);

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 27, 2014 12:28 PM
  • User-633861519 posted

    var query

    of which datatype query variable will be?

    Monday, January 27, 2014 11:54 PM
  • User-821857111 posted

    of which datatype query variable will be?

    It will be a string. The compiler infers that from the value that is assigned to the varibale. If you do not assign a value at the point of declaration, you cannot use 'var'. you have to state the datatype explicitly:

    string query:
    query = "some string";



    Tuesday, January 28, 2014 12:35 AM
  • User-633861519 posted

    now getting this error

    System.Data.OleDb.OleDbConnection doesnot contain a defination for query

    and

    the name cn doesnot exist in current context

    Tuesday, January 28, 2014 6:23 AM
  • User188200844 posted

    OledbConnectionString = string.Empty;
            string ext = Path.GetExtension(ConfigurationManager.AppSettings["filename"].ToString().Trim());
            if (ext == ".xls")
            {
                OledbConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + location + ";Extended Properties=Excel 8.0;";
            }
            else if (ext == ".xlsx")
            {
                OledbConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + location + ";Extended Properties=Excel 12.0;";
            }

    Tuesday, January 28, 2014 7:58 AM
  • User-633861519 posted

    why xls again?

    Tuesday, January 28, 2014 8:07 AM
  • User281315223 posted

    now getting this error

    System.Data.OleDb.OleDbConnection doesnot contain a defination for query and the name cn doesnot exist in current context

    Could you post the code that you are currently using to receive this error?

    Tuesday, January 28, 2014 9:06 AM
  • User-1199946673 posted

    Mike, Rion

    Why are you focussing on the query? In the oriinal code, I don't see anything wrong in the query which is in line 30. But more important, the error is occuring in line 29, when opening the connection....

    Tuesday, January 28, 2014 10:07 AM
  • User281315223 posted

    The reason that I brought up the query is that it appeared since the a variable named "AC" was being stored prior to executing the query that the OP was attempting to use that value as a parameter, so I wanted to ensure that this wasn't part of the problem.

    I do agree that there may be some issues regarding the actual connection itself which is why I suggested the usage of the using syntax, however it looks like there are some additional issues related to the entire block of code itself (which is why I asked the OP for the code that he / she is currently using). Mike also touched on another possible issue regarding permissions, which could be a factor as well.

    Tuesday, January 28, 2014 10:52 AM
  • User-633861519 posted
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Collections;
    using System.Data;
    using System.Diagnostics;
    using System.Data.OleDb;

    public partial class admin_allgeneral : System.Web.UI.Page
    {

    protected void Page_Load(object sender, EventArgs e)
    {
    int norows = GridView1.Rows.Count;
    int gt=0;
    string query;
    for (int i = 0; i < norows; i++)
    {
    string ac = GridView1.Rows[i].Cells[5].Text;

    using(OleDbConnection cn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=|DataDirectory|daksh.mdb")
    {

    query = "SELECT SUM(Amount) FROM general_pay WHERE [general_ac no] = ?";
    using(OleDbCommand cmd1 = new OleDbCommand(query,cn))
    {

    cmd1.Parameters.AddWithValue("@ac",ac);
    cn.Open();
    gt = cmd1.ExecuteNonQuery();
    Response.Write(gt);
    }

    }
    }
    }
    }

    this is the code i am using

    Tuesday, January 28, 2014 11:36 AM
  • User-1199946673 posted

    this is the code i am using

    And the problem/error you've now?

    Tuesday, January 28, 2014 2:41 PM
  • User-633861519 posted

    now getting this error

    system.data.oledb.oledbconnection doesnot contain a defination for query and the name cn doesnot exist in current context

    Tuesday, January 28, 2014 2:49 PM
  • User281315223 posted

    This is because you aren't initally setting a value for your SQL query, however you could likely remove your intial declaration of it here :

    string query;

    and simply create it within your using statement as seen below :

    protected void Page_Load(object sender, EventArgs e)
    {
            int gt = 0;
            
            for (int i = 0; i < GridView1.Rows.Count; i++)
            {
                string ac = GridView1.Rows[i].Cells[5].Text;
    
                using(OleDbConnection cn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=|DataDirectory|daksh.mdb")
                {
                    string query = "SELECT SUM(Amount) FROM general_pay WHERE [general_ac no] = ?";
                    using(OleDbCommand cmd1 = new OleDbCommand(query,cn))
                    {
                       
                        cmd1.Parameters.AddWithValue("@ac",ac);
                        cn.Open();
                        gt = cmd1.ExecuteNonQuery();
                        Response.Write(gt);
                    }
    
                }
            }
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 28, 2014 2:55 PM
  • User-633861519 posted

    this was also giving me error, thats why declared like above

    Tuesday, January 28, 2014 3:02 PM
  • User281315223 posted

    Are you still having issues with the most recent code?

    Tuesday, January 28, 2014 4:05 PM
  • User-633861519 posted

    yes still having issues,

    here is the source http://www.mediafire.com/download/7f15yev192m484y/to+send.rar

    kindly check it

    Tuesday, January 28, 2014 11:44 PM
  • User-633861519 posted

    no reply. strange

    Thursday, January 30, 2014 12:54 AM