inconsistancy in database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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?
 
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
%>
 
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!
 
Back
Top