Link to home
Start Free TrialLog in
Avatar of shawnpbrady
shawnpbradyFlag for United States of America

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.OLEDB.4.0;Data Source=" + AccessDBPath;
            // create database connection
            OleDbConnection dbconn = new OleDbConnection(connectionstring);
            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
Avatar of fanopoe
fanopoe
Flag of United States of America image

looks like
"' WHERE CustomerID=" + CustomerID;

should be either
"' WHERE CustomerID=' + CustomerID;

or
"' WHERE CustomerID="' + CustomerID + '';

does that help?
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.
Avatar of shawnpbrady

ASKER

The CustomerID is passed to the function as a string, but is actually a smallint in the access database.
fanopoe,

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";
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.
My bad..

 "' WHERE CustomerID= cast(" + CustomerID + " as smallint)";
Still the same syntax error.

Here is what the sql string looks like (without the cast):
"update Customers SET CompanyName='ShaggyDog Software',ContactFirstName='',ContactLastName='',CompanyOrDepartment='',BillingAddress='',City='',StateOrProvince='Alabama',PostalCode='',CountryRegion='Afghanistan',ContactTitle='',PhoneNumber='',Extension='',FaxNumber='',EmailAddress='',Notes='',LabPCWired='1',RegisteredModules='0',UserDefinedRegistered='0' WHERE CustomerID=3"
ASKER CERTIFIED SOLUTION
Avatar of DotNetThinker
DotNetThinker
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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.
It was a great mistake...thanks.