shawnpbrady
asked on
ExecuteNoQuery giving unspecified error
Hello all,
I have an ASP.Net App the works perfectly in the developement environment, but when I publish it to my server and try to use it off of the server it works until I try to update the database. The database is in access. I get the following error "An unspecified error occured." I have narrowed it down to a specific line of code that is causing the problem, when I ExecuteNonQuery.
Here is the code for the function:
try
{
// Path on server
string connectionstring = "Provider=Microsoft.Jet.OL EDB.4.0;Da ta Source=" + AccessDBPath;
// create database connection
OleDbConnection dbconn = new OleDbConnection(connection string);
dbconn.Open();
string sqlcommand = "update Customers SET CompanyName='" + CompanyName + "',ContactFirstName='" + ContactFirstName +
"',ContactLastName='" + ContactLastName + "',CompanyOrDepartment='" + CompanyOrDepartment + "',BillingAddress='" + BillingAddress +
"',City='" + City + "',StateOrProvince='" + StateOrProvince + "',PostalCode='" + PostalCode + "',CountryRegion='" + CountryRegion +
"',ContactTitle='" + ContactTitle + "',PhoneNumber='" + PhoneNumber + "',Extension='" + Extension + "',FaxNumber='" + FaxNumber +
"',EmailAddress='" + EmailAddress + "',Notes='" + Notes + "',LabPCWired='" + LabPCWired + "',RegisteredModules='" + RegisteredModules +
"',UserDefinedRegistered=' " + UserDefinedRegistered +
"' WHERE CustomerID=" + CustomerID;
// 1. Instantiate a new command with a query and connection
OleDbCommand cmd = new OleDbCommand(sqlcommand, dbconn);
// 2. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery(); // <--- !!! This is what is causing the error !!!
// 4. close connection
dbconn.Close();
// return
return true;
}
catch (Exception ex)
{
throw ex;
return false;
}
Any help would be gratly appreciated. I am thinking I have something configured incorrectly on the server or in my web.config file.
Thanks
I have an ASP.Net App the works perfectly in the developement environment, but when I publish it to my server and try to use it off of the server it works until I try to update the database. The database is in access. I get the following error "An unspecified error occured." I have narrowed it down to a specific line of code that is causing the problem, when I ExecuteNonQuery.
Here is the code for the function:
try
{
// Path on server
string connectionstring = "Provider=Microsoft.Jet.OL
// create database connection
OleDbConnection dbconn = new OleDbConnection(connection
dbconn.Open();
string sqlcommand = "update Customers SET CompanyName='" + CompanyName + "',ContactFirstName='" + ContactFirstName +
"',ContactLastName='" + ContactLastName + "',CompanyOrDepartment='" + CompanyOrDepartment + "',BillingAddress='" + BillingAddress +
"',City='" + City + "',StateOrProvince='" + StateOrProvince + "',PostalCode='" + PostalCode + "',CountryRegion='" + CountryRegion +
"',ContactTitle='" + ContactTitle + "',PhoneNumber='" + PhoneNumber + "',Extension='" + Extension + "',FaxNumber='" + FaxNumber +
"',EmailAddress='" + EmailAddress + "',Notes='" + Notes + "',LabPCWired='" + LabPCWired + "',RegisteredModules='" + RegisteredModules +
"',UserDefinedRegistered='
"' WHERE CustomerID=" + CustomerID;
// 1. Instantiate a new command with a query and connection
OleDbCommand cmd = new OleDbCommand(sqlcommand, dbconn);
// 2. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery(); // <--- !!! This is what is causing the error !!!
// 4. close connection
dbconn.Close();
// return
return true;
}
catch (Exception ex)
{
throw ex;
return false;
}
Any help would be gratly appreciated. I am thinking I have something configured incorrectly on the server or in my web.config file.
Thanks
try stepping through your code then grabbing the sqlCommand string and try running it in your sql server. Maybe there's something in there that you're just not seeing.
ASKER
The CustomerID is passed to the function as a string, but is actually a smallint in the access database.
ASKER
fanopoe,
Thanks for the suggestion, that did not work. I am pretty sure the problem is somewhere in the WHERE or after the WHERE.
Thanks for the suggestion, that did not work. I am pretty sure the problem is somewhere in the WHERE or after the WHERE.
try this...
"' WHERE CustomerID= cast(" + CustomerID + ") as smallint";
"' WHERE CustomerID= cast(" + CustomerID + ") as smallint";
ASKER
Still no go, using the cast in the SQL statement I get the following error.
Syntax error (missing operator) in query expression 'CustomerID= cast(3) as smallint'.
Could it be possible I do not have something setup on the server correctly? In my .NET dev environment the code works flawlessly, on the server it fail every time. This is happening for all functions I have that execute similar writes/deletes to the access db.
Syntax error (missing operator) in query expression 'CustomerID= cast(3) as smallint'.
Could it be possible I do not have something setup on the server correctly? In my .NET dev environment the code works flawlessly, on the server it fail every time. This is happening for all functions I have that execute similar writes/deletes to the access db.
My bad..
"' WHERE CustomerID= cast(" + CustomerID + " as smallint)";
"' WHERE CustomerID= cast(" + CustomerID + " as smallint)";
ASKER
Still the same syntax error.
Here is what the sql string looks like (without the cast):
"update Customers SET CompanyName='ShaggyDog Software',ContactFirstName ='',Contac tLastName= '',Company OrDepartme nt='',Bill ingAddress ='',City=' ',StateOrP rovince='A labama',Po stalCode=' ',CountryR egion='Afg hanistan', ContactTit le='',Phon eNumber='' ,Extension ='',FaxNum ber='',Ema ilAddress= '',Notes=' ',LabPCWir ed='1',Reg isteredMod ules='0',U serDefined Registered ='0' WHERE CustomerID=3"
Here is what the sql string looks like (without the cast):
"update Customers SET CompanyName='ShaggyDog Software',ContactFirstName
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Might be a stupid question, but If I am using an access database with sql commands to get add/edit/delete data do I need to worry about sql server permission. My DB is not is sql, I guess I am just missing something.
Sorry about that, sometimes I just get lost in what I do (Sql). This article should help you out though...
https://www.experts-exchange.com/questions/21516227/Permissions-issue-ASP-NET-and-MS-Access.html?sfQueryTermInfo=1+access+ms+permiss+set
https://www.experts-exchange.com/questions/21516227/Permissions-issue-ASP-NET-and-MS-Access.html?sfQueryTermInfo=1+access+ms+permiss+set
ASKER
Ok looked at the folder permissions. Found that the folder did not have write permissions. DotNetThinker your permissions suggestions zoned me into the solution. Thanks.
ASKER
It was a great mistake...thanks.
"' WHERE CustomerID=" + CustomerID;
should be either
"' WHERE CustomerID=' + CustomerID;
or
"' WHERE CustomerID="' + CustomerID + '';
does that help?