Ms access Locktype

[yatie]

I have problem with my ms access database to adding new record. i get error like this:

Error Type:
ADODB.Recordset (0x800A0CB3)
Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.
/cuba.asp, line 11


My code sample such below:

<%
myDSN="driver={Microsoft Access Driver (*.mdb)};DBQ=c:/DataAmbridge.mdb"
set getData=server.createobject("adodb.recordset")
getData.open "Select * from INVENTORY",myDSN,1

if getData.EOF and getData.BOF then
getData.AddNew
end if

getData.Fields("CHECKED_BY") = "Mus"

getData.Update
getData.Close
set getData = nothing


%>

[galvatron]

try changing this :-

getData.open "Select * from INVENTORY",myDSN,1

to this :-

getData.open "Select * from INVENTORY",myDSN,2,2

[yatie]

I already try your coding like this :

<%

myDSN="driver={Microsoft Access Driver (*.mdb)};DBQ=c:/DataAmbridge.mdb"
set getData=server.createobject("adodb.recordset")
getData.open "Select * from INVENTORY",myDSN,2,2

if getData.EOF and getData.BOF then
getData.AddNew

end if

getData.Fields("CHECKED_BY") = "Mus"

getData.Update
getData.Close
set getData = nothing

%>

but still have error. The error is :

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] The table 'INVENTORY' is already opened exclusively by another user, or it is already open through the user interface and cannot be manipulated programmatically.
/cuba.asp, line 6

[galvatron]

I've never used access before so I probably don't know about this. but I'm guessing you have the document (the table) opened in another window, for an example you are running ms access and the table at the same time. if this is true then try closing the ms access window or any other programs running the database table.

[yatie]

Hi,

I try closed all table related Inventory. And That method is success :). Ok know i got 1 problem . My code is like this:

<%

dim model

myDSN="driver={Microsoft Access Driver (*.mdb)};DBQ=c:/DataAmbridge.mdb"
set getData=server.createobject("adodb.recordset")
getData.open "Select * from INVENTORY",myDSN,2,2

Model = trim(Request.Form("model"))

if getData.EOF and getData.BOF then
Response.Write "Duplicated Record!"
getData.AddNew
end if

getData.Fields("MODEL") = Model


getData.Update
Response.Redirect "Inventory_C.asp"

getData.Close
set getData = nothing

%>

my error is :
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] Field 'INVENTORY.MODEL' cannot be a zero-length string.
/cuba.asp, line 39


For your infomation, datatype is text. TQ.

[jsawkang]

hi,

don't u think the "end if" should be placed after ".Update"? like this:

if getData.EOF and getData.BOF then
Response.Write "Duplicated Record!"
getData.AddNew
getData.Fields("MODEL") = Model
getData.Update
end if

[galvatron]

do you dissalow nulls in that particular MODEL column?

[yatie]

TQ galvatron....

I successfully Added my record. TQ very much ;)

May i know what is the meaning

getData.open "Select * from INVENTORY",myDSN,2,2

2,2 beside myDSN?

[galvatron]

2 (adopendynamic), 2 (adlockpessimistic)

still not clear of it myself. you could also use adopenstatic (3) instead of adopendynamic. it should be much safer I think. these lock types determine what you could do with the recordsets. there are also others. check ADO references for them.

btw. normally peeps use constant, (such as the file adovbs.inc) or declaring it earlier (e.g const adopendynamic = 2) so that they could just write the function instead of the numbers.

[yatie]

I make array for my system. And when i want to save that record i do like this. But have error..y?

dim aItem(7,3)
myDSN="driver={Microsoft Access Driver (*.mdb)};DBQ=c:/DataAmbridge.mdb"
set getData=server.createobject("adodb.recordset")
getData.open "Select * from INVENTORY",myDSN,2,2

for b = 1 to 7
aItem(b,1) = request.form("ChkBox"&b&"")
aItem(b,2) = request.form("no"&b&"")
aItem(b,3) = request.form("S"&b&"")
if trim(aItem(b,1)) = "ON" and not(trim(aItem(b,2))="") and not(trim(aItem(b,3))="")then
Delete aItem(b,2)
end if
next

RetrieveRecord
if itemNo <> "" then
SaveItem(itemNo)
end if

select case funct
case "c"
Response.Redirect "Inventory_C.asp?a=1&b=1&tabValue=0"
case "e"
Response.Redirect "Inventory_E.asp?a=1&b=1&tabValue=0"
end select

function RetrieveRecord

itemNo = trim(Request.Form("itemNo"))
model = trim(Request.Form("model"))


sub SaveItem(itemNo)

getData.Source = "select * from INVENTORY "
getData.Open

if getData.EOF and getData.BOF then
Response.Write "Duplicated Record!"
getData.AddNew
end if

getData.Fields("NO") = itemNo
getData.Fields("MODEL") = model

getData.Update
getData.Close
set getData = nothing
end sub

%>

the error out is :

Error Type:
Microsoft VBScript compilation (0x800A03EA)
Syntax error
/cuba.asp, line 33
sub SaveItem(itemNo)

[galvatron]

you didn't end your function.