.asp problem

trax2011

hey all,
im new to all this and need a bit of help. im creating a form and so far as i can see the html is fine and cant find the problem in the .asp. when its tested all i get is


Microsoft JET Database Engine error '80004005'
Operation must use an updateable query. /updatetable.asp, line 68

now i tried using a response.write to make sure the input was taken and it was so i cant figure this one.
can anyone help. oh heres the .asp.

<%@ LANGUAGE="VBSCRIPT" %>
<!--#include file='adovbs.inc'-->
<%
'lines starting with ' are comments and are ignored when run
'declare recordset variable to store query results
Dim rs
'database connection variable
Dim conn
'string variables for SQL queries
Dim strSelect, strUpdate, strInsert
'form field variables
Dim fDealercode
'get the form field values
fDealercode=Request.Form("dealercode")
fAddress1=Request.Form("address1")
fAddress2=Request.Form("address2")
fCounty=Request.Form("county")
fTelephone=Request.Form("telephone")
fFax=Request.Form("fax")
fEmail=Request.Form("email")
fManagerName=Request.Form("managername")
'SQL query to check if dealer code is already in our table
' eg Select * from tblDealers where DealerCode='O232'
strSelect="Select * from tblDealers where DealerCode='" & fDealercode & "'"
'open the database connection and point to c:\inetpub\wwwroot\dealers\dealers.mdb
'THE DATABASE MUST BE CLOSED WHEN FINISHED

Set conn = Server.CreateObject("ADODB.Connection")
conn.Mode = 3
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\inetpub\wwwroot\dealers\dealers.mdb;User Id=admin;Password=;"
'create the recordset
Set rs = Server.CreateObject("ADODB.Recordset")
'run the SQL query
rs.Open strSelect,conn,adOpenForwardOnly,adLockReadOnly
'check the result
If rs.BOF AND rs.EOF Then
'there is no record for this dealer code so we create an INSERT SQL query
sInsert="INSERT INTO tblDealers (DealerCode,Address1,Address2,County,Telephone,Fax,Email,ManagerName) VALUES ('" & fDealercode & "','" & fAddress1 & "','" & fAddress2 & "','" & fCounty & "','" & fTelephone & "','" & fFax & "','" & fEmail & "','" & fManagerName & "')"

'run the query to insert the record
Response.Write sInsert
Response.end

conn.execute sInsert

else
'there is already a record for this dealer code so we create an UPDATE SQL query
sUpdate="Update tblDealers set DealerCode='" & fDealercode & "',Address1='" & fAddress1 & "',Address2='" & fAddress2 & "',County='" & fCounty & "',Telephone='" & fTelephone & "',Fax='" & fFax & "',ManagerName='" & fManagerName & "' WHERE DealerCode='" & fDealercode & "'"

'run the query to insert the record
conn.execute sUpdate

end if
'every if must have an end if
'THE DATABASE MUST BE CLOSED WHEN FINISHED
conn.Close
set conn=Nothing

%>

Seth Brundle

is the error on the update or the insert (line 68 should tell you)?
Can you put a prompt box in just after where you define sInsert and sUpdate with these in as the default text and copy from there. Then try and run the query in MS Access

edit: I see you have response.write after the insert but not after the update - put it there and maybe ignore the prompts. If necessary, comment out the lines
conn.execute sInsert
and
conn.execute sUpdate

Seth Brundle

Oh and another thing - SQL Injection!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

amen

SQL Injection

+100

you should look at Stored Procedure.

Also consider creating class that handles the Database access.

you just pass SP/SQL to the class and it does the rest.

c:\inetpub\wwwroot\dealers\dealers.mdb

personally I wouldn't have the DB in the same location as inetpub (and prob not on the same machine)

admin

another bad idea. You shouldn't be using any account that even looks like an admin account to connect to the database.

You should have an account for the application that only has rights to what it need. I would suggest using SPs and only let the account execute SPs (maybe call views if needed).

McGintyMcGoo

Your .asp app does not have write permissions to the Access database.:cool: