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();
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);
}
}
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);
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:
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....
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.
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);
}
}
}
}
}
Member
8 Points
81 Posts
what wrong with this code?
Jan 27, 2014 02:28 AM|jammyhunt|LINK
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); } }Member
202 Points
91 Posts
Re: what wrong with this code?
Jan 27, 2014 02:37 AM|sandeepchrs|LINK
What type error are you getting while debugging code.
Sandeep Chourasia
http://www.aspnetcodes.com
Member
8 Points
81 Posts
Re: what wrong with this code?
Jan 27, 2014 02:40 AM|jammyhunt|LINK
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:
All-Star
185979 Points
26946 Posts
Moderator
Re: what wrong with this code?
Jan 27, 2014 02:41 AM|Mikesdotnetting|LINK
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);ASP.NET Tutorials | Learn Entity Framework Core | Learn Razor Pages
All-Star
185979 Points
26946 Posts
Moderator
Re: what wrong with this code?
Jan 27, 2014 02:44 AM|Mikesdotnetting|LINK
Make sure the user account that ASP.NET runs under has MODIFY permissions on the folder that the mdb file is in.
ASP.NET Tutorials | Learn Entity Framework Core | Learn Razor Pages
Member
8 Points
81 Posts
Re: what wrong with this code?
Jan 27, 2014 02:52 AM|jammyhunt|LINK
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
All-Star
113010 Points
18361 Posts
MVP
Re: what wrong with this code?
Jan 27, 2014 10:10 AM|Rion Williams|LINK
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); } }Member
8 Points
81 Posts
Re: what wrong with this code?
Jan 27, 2014 11:23 AM|jammyhunt|LINK
what do you mean by AC parameter?
and of which datatype the query willl be
All-Star
113010 Points
18361 Posts
MVP
Re: what wrong with this code?
Jan 27, 2014 12:28 PM|Rion Williams|LINK
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 :
Were you attempting to pass the following value in and use it for your WHERE clause?
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);Member
8 Points
81 Posts
Re: what wrong with this code?
Jan 27, 2014 11:54 PM|jammyhunt|LINK
of which datatype query variable will be?
All-Star
185979 Points
26946 Posts
Moderator
Re: what wrong with this code?
Jan 28, 2014 12:35 AM|Mikesdotnetting|LINK
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:
ASP.NET Tutorials | Learn Entity Framework Core | Learn Razor Pages
Member
8 Points
81 Posts
Re: what wrong with this code?
Jan 28, 2014 06:23 AM|jammyhunt|LINK
now getting this error
System.Data.OleDb.OleDbConnection doesnot contain a defination for query
and
the name cn doesnot exist in current context
Member
202 Points
91 Posts
Re: what wrong with this code?
Jan 28, 2014 07:58 AM|sandeepchrs|LINK
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;";
}
Sandeep Chourasia
http://www.aspnetcodes.com
Member
8 Points
81 Posts
Re: what wrong with this code?
Jan 28, 2014 08:07 AM|jammyhunt|LINK
why xls again?
All-Star
113010 Points
18361 Posts
MVP
Re: what wrong with this code?
Jan 28, 2014 09:06 AM|Rion Williams|LINK
Could you post the code that you are currently using to receive this error?
All-Star
25756 Points
7019 Posts
Re: what wrong with this code?
Jan 28, 2014 10:07 AM|hans_v|LINK
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....
All-Star
113010 Points
18361 Posts
MVP
Re: what wrong with this code?
Jan 28, 2014 10:52 AM|Rion Williams|LINK
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.
Member
8 Points
81 Posts
Re: what wrong with this code?
Jan 28, 2014 11:36 AM|jammyhunt|LINK
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
All-Star
25756 Points
7019 Posts
Re: what wrong with this code?
Jan 28, 2014 02:41 PM|hans_v|LINK
And the problem/error you've now?
Member
8 Points
81 Posts
Re: what wrong with this code?
Jan 28, 2014 02:49 PM|jammyhunt|LINK
now getting this error
system.data.oledb.oledbconnection doesnot contain a defination for query and the name cn doesnot exist in current context
All-Star
113010 Points
18361 Posts
MVP
Re: what wrong with this code?
Jan 28, 2014 02:55 PM|Rion Williams|LINK
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 :
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); } } } }Member
8 Points
81 Posts
Re: what wrong with this code?
Jan 28, 2014 03:02 PM|jammyhunt|LINK
this was also giving me error, thats why declared like above
All-Star
113010 Points
18361 Posts
MVP
Re: what wrong with this code?
Jan 28, 2014 04:05 PM|Rion Williams|LINK
Are you still having issues with the most recent code?
Member
8 Points
81 Posts
Re: what wrong with this code?
Jan 28, 2014 11:44 PM|jammyhunt|LINK
yes still having issues,
here is the source http://www.mediafire.com/download/7f15yev192m484y/to+send.rar
kindly check it
Member
8 Points
81 Posts
Re: what wrong with this code?
Jan 30, 2014 12:54 AM|jammyhunt|LINK
no reply. strange