inconsistancy in database

S

shashikant

I am using ODBC to access my data from database with ASP. My databse contains
a table name cartridege which has fields item_id, quantity and bill_no.When a
updation is made in a the table in quantity field, all the other rows who
having the same quantity value but different item_id is also updated while
the recordset is selected for only one type of the item_id.This database is
made with MS-Access 2003. I also try compact and repaire tools but the
problem could not be removed
 
D

Douglas J. Steele

Is the update happening through your ASP page, or external to your
application?

If it's through your ASP page, what's the relevant code that does the
update?
 
S

shashikant

Update happening through the ASP code.The function used is below


<%
'**************Function to find Item quantity and Update the
table**********************
Function findCost(item,quantity,category)
item=Trim(item)
quantity=Clng(quantity)
Dim temp_item
Dim remaining
Dim cmd1
Dim irecord1
cmd1="SELECT bill_no,quantity FROM "&Trim(category)&" WHERE quantity >0
AND item_id="&Clng(item)&""
Set irecord1=Server.CreateObject("ADODB.Recordset")
'*************Open 'CATEGORY' TABLES to find the quantity ***********
irecord1.open cmd1,conn,,AdLockOptimistic
if irecord1("quantity")>=Clng(quantity) then
'************Requirement fulfilled by a single row*****************
irecord1("quantity")=irecord1("quantity")-CInt(quantity)
irecord1.Update
else
'************Have to fetch multiple row to fulfill the requirement************

remaining=Clng(quantity) '####### Hold remaining quantity after fetching
a row #######
temp_item=quantity '####### Used to swap remaining items #######
Do Until irecord1.EOF OR flag
if remaining >irecord1("quantity") then
'########### If remaining quantity is greater than quantity available in
the row #####
temp_item=remaining-irecord1("quantity")
irecord1("quantity")=0 '###### Assign zero as all quantity from that
row have taken up in 'category' table ########
irecord1.Update
remaining=temp_item '############# Assign remaining quantity to
fetch ##########
irecord1.MoveNext
else
'########### if remaining quantity is less than or equal to quantity
available in the row #######
irecord1("quantity")=irecord1("quantity")-remaining
irecord1.Update
flag=true
end if
Loop
end if
irecord1.close
Set irecord1=nothing


End Function
%>
 
D

Douglas J. Steele

And what calls that function?

BTW, looking at the function, it looks as though you have a separate table
for each category. That's seldom a good idea!
 
Top