INSERT INTO error

TSEROOGY · December 10th, 2004, 09:47 AM

I have already checked the following topic from Stephan - http://p2p.wrox.com/topic.asp?TOPIC_...s=INSERT,error.

I am having similar problems but have not been able to correct the problem.

Unlike Stephan, I have connected to the database using a DSN-less connection and it seems to be working fine from the DisplayStock.asp page.

The BuyItem.asp page also seems to work. It is picking up the StockID passed thru the URL.

The error occurrs when trying to perform the INSERT INTO sql.

Here is the error I receive:
_______________________________________________
Technical Information (for support personnel)

Error Type:
Microsoft JET Database Engine (0x80004005)
Operation must use an updateable query.
/WROXDBSample/AddOrderDetails.asp, line 29

Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.40607)

Page:
POST 151 bytes to /WROXDBSample/AddOrderDetails.asp

POST Data:
txtStockID=5&txtCustomerName=Thomas+Seroogy&txtCus tomerAddress=201+Christa+Lane%2C+Nicholasville%2C+ KY+40356&txtTelNumber=8599835958&submit1=Send+Orde r

Time:
Friday, December 10, 2004, 8:34:15 AM
__________________________________________________ _______

my script for the AddOrderDetails.asp page are as follows:

Code:

<%@ LANGUAGE = JavaScript %>
<html>
<head>
</head>

<body bgcolor="#FFFFFF" text="#000000" link="#0000FF" vlink="#800080" alink="#FF0000">

<%

    var month = new Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Nov", "Dec");
    var nowDate = new Date();
    var nowDate = nowDate.getDate() + " " + month[nowDate.getMonth()] + " " + nowDate.getFullYear();
    var orderNo;

    var myDb = "F:\\dB";
    var adoRecordSet;
    var mySQL;

var adoConnection = Server.CreateObject("ADODB.Connection");

adoConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + myDb + "\\CompBitsCo.mdb");

mySQL = "INSERT INTO CustomerOrder(CustomerName, CustomerAddress, TelNumber, OrderDate)";
mySQL = mySQL + " VALUES('" + Request.Form("txtCustomerName") + "','";
mySQL = mySQL + Request.Form("txtCustomerAddress") + "','";
mySQL = mySQL + Request.Form("txtTelNumber") + "','";
mySQL = mySQL + nowDate + "')";

adoConnection.Execute(mySQL);

mySQL = "SELECT Max(OrderNo) AS MaxOrderNo FROM CustomerOrder WHERE ";
mySQL = mySQL + " OrderDate = #" + nowDate + "# AND ";
mySQL = mySQL + " CustomerName = '" + Request.Form("txtCustomerName") + "'";

adoRecordSet = adoConnection.Execute(mySQL);

orderNo = adoRecordSet("MaxOrderNo").Value;

adoRecordSet.Close();
adoRecordSet = null;

mySQL = "INSERT INTO OrderItem (OrderNo, StockID, QtyOrdered)";
mySQL = mySQL + " VALUES (" + orderNo + ",";
mySQL = mySQL + Request.Form("txtStockId") + ",";
mySQL = mySQL + "1)";

adoConnection.Execute(mySQL);

adoConnection.Close();
adoConnection= null;

Response.Write("<h2><center>Your order has completed successfully</center></h2>");

%>

</body>
</html>

Any help is appreciated.

Best regards,
sabertec2

PS - You can access all of the pages by going to www.saber-tool.com/displaystock.asp

stu9820 · December 10th, 2004, 10:05 AM

Does the Internet User account have access to the folder that the DB is in?

TSEROOGY · December 10th, 2004, 11:19 AM

Yes, I believe so.

I am the administrator, and I have been running the trial from my computer. The DB is on a separate harddrive attached to this computer.

Thinking it may have to do with permissions (I know very little about permissions), I experimented by changing the Web Sharing properties. But no luck.

My last attempt was to locate the database in the web's root directory. Still no luck. Same error message.

stu9820 · December 10th, 2004, 12:08 PM

What OS do you have? Can you see the security tab when you right click the folder the DB is in?

TSEROOGY · December 10th, 2004, 12:30 PM

XP Pro SP2. Yes, I can see the Sharing & Security. This is where I experimented by selecting single and then all of the permissions check boxes under the Web Sharing tab.

Still, shouldn't I have access to the DB when it is in the root directory?

Thanks for working on this!

stu9820 · December 10th, 2004, 02:02 PM

Click on the security tab and make sure the Internet User account for your machine has access to write to the DB folder.

You should have admin but the I_USR<machine_name> account is what needs access.

TSEROOGY · December 10th, 2004, 05:16 PM

There is no SECURITY tab - per se. Only SHARING and WEB SHARING tabs. Am I looking in the wrong place (I right click the folder and go to properties)?

I also tried making the WRITE changes to the folder and file through IIS. Still a no go.

I went to MSDN to check on this error. Error seems common with IUSER_MACHINE permissions being the most common problem. I'm not knowledgeable enough on permissions to make these changes without guidance.

Still, this won't help if it works on my machine but not when it's deployed to my hosting IS server.

PS - I tried running a very basic INSERT and UPDATE SQL script to a simple flat table. Still, no luck - the very same error.

Seems MS Access may not be the best DB tool with which to learn. Still, SQL is way more than I need for my simple application.

Any guidance is still happily accepted.

Best regards,
TSeroogy

stu9820 · December 10th, 2004, 05:43 PM

Open 'My Computer'. Click 'Tools' and then 'Folder Options'. Then click on the 'View' Tab and scroll all the way to the bottom. Uncheck 'Use Simple File Sharing(Recommended)' and click 'Ok' or 'Yes' to the warning.

Once that has been done you should be able to go the the folder where your file will be created and right click on the folder and give the internet user account write access to the folder.

You must be using NTFS and NOT FAT16 or FAT32.

TSEROOGY · December 10th, 2004, 06:02 PM

Thanks for the guidance. It did work.

I assume this means that the folder permissions will have to be changed on the server to where this is eventually deployed?

Thanks for your patience.

stu9820 · December 13th, 2004, 09:11 AM

Any time you need to write to the database you will have to give permissions to the Internet User account.