Cannot Update

Question

violette 0 Junior Poster in Training

12 Years Ago

I'm having problem to update the data into mysql database. for insert new data, it works nicely but if i want to update the existing data, it will hang my pc. even if i debug line by line, it shows like infinite loop. which part has gone wrong? i'm totally no idea.

Do While IsEmpty(Cells(lastRowCom, "D").Value) = False
    
        stockCodeExist = False
       stockCodeCom = IIf(Cells(lastRowCom, "B").Value <> "#N/A N/A", Trim(Cells(lastRowCom, "B").Value), 0)
       marketCapCom = IIf(Cells(lastRowCom, "C").Value <> "#N/A N/A", Trim(Cells(lastRowCom, "C").Value), 0)
       revenueCom = IIf(Cells(lastRowCom, "D").Value <> "#N/A N/A", Trim(Cells(lastRowCom, "D").Value), 0)
       TotLiability = IIf(Cells(lastRowCom, "E").Value <> "#N/A N/A", Trim(Cells(lastRowCom, "E").Value), 0)
       curLiability = IIf(Cells(lastRowCom, "F").Value <> "#N/A N/A", Trim(Cells(lastRowCom, "F").Value), 0)
       tradeReceivable = IIf(Cells(lastRowCom, "G").Value <> "#N/A N/A", Trim(Cells(lastRowCom, "G").Value), 0)
       inventory = IIf(Cells(lastRowCom, "H").Value <> "#N/A N/A", Trim(Cells(lastRowCom, "H").Value), 0)
       shareholderEqyCom = IIf(Cells(lastRowCom, "I").Value <> "#N/AN/A", Trim(Cells(lastRowCom, "I").Value), 0)
       totLiabToSE = IIf(Cells(lastRowCom, "J").Value <> "#N/A N/A", Trim(Cells(lastRowCom, "J").Value), 0)
       currentAss = IIf(Cells(lastRowCom, "K").Value <> "#N/A N/A", Trim(Cells(lastRowCom, "K").Value), 0)
       fixedAsset = IIf(Cells(lastRowCom, "L").Value <> "#N/A N/A", Trim(Cells(lastRowCom, "L").Value), 0)
       operatingExpenses = IIf(Cells(lastRowCom, "M").Value <> "#N/AN/A", Trim(Cells(lastRowCom, "M").Value), 0)
       ClosingPrice = IIf(Cells(lastRowCom, "N").Value <> "#N/A N/A", Cells(lastRowCom, "N").Value, 0)
       priceEarningRatio = IIf(Cells(lastRowCom, "O").Value <> "#N/A N/A", Trim(Cells(lastRowCom, "O").Value), 0)
       earningPerShare = IIf(Cells(lastRowCom, "P").Value <> "#N/A N/A", Trim(Cells(lastRowCom, "P").Value), 0)
       salesToTotalAss = IIf(Cells(lastRowCom, "Q").Value <> "#N/A N/A", Trim(Cells(lastRowCom, "Q").Value), 0)
       totalAss = IIf(Cells(lastRowCom, "R").Value <> "#N/A N/A", Trim(Cells(lastRowCom, "R").Value), 0)
       operatingCashFlow = IIf(Cells(lastRowCom, "S").Value <> "#N/A N/A", Trim(Cells(lastRowCom, "S").Value), 0)
       peRatio = IIf(Cells(lastRowCom, "T").Value <> "#N/A N/A", Cells(lastRowCom, "T").Value, 0)
       altmanZ = IIf(Cells(lastRowCom, "U").Value <> "#N/A N/A", Cells(lastRowCom, "U").Value, 0)
       cashNeqvalent = IIf(Cells(lastRowCom, "V").Value <> "#N/A N/A", Trim(Cells(lastRowCom, "V").Value), 0)
       EbitCom = IIf(Cells(lastRowCom, "W").Value <> "#N/A N/A", Trim(Cells(lastRowCom, "W").Value), 0)
       sharesOutstdgCom = IIf(Cells(lastRowCom, "X").Value <> "#N/A N/A", Trim(Cells(lastRowCom, "X").Value), 0)
       netAttributableCom = IIf(Cells(lastRowCom, "Y").Value <> "#N/A N/A", Trim(Cells(lastRowCom, "Y").Value), 0)
       incStock = IIf(Cells(lastRowCom, "Z").Value <> "#N/A N/A", Trim(Cells(lastRowCom, "Z").Value), 0)
       netInvest = IIf(Cells(lastRowCom, "AA").Value <> "#N/A N/A", Trim(Cells(lastRowCom, "AA").Value), 0)
       retainedPOL = IIf(Cells(lastRowCom, "AB").Value <> "#N/A N/A", Cells(lastRowCom, "AB").Value, 0)
       ReOTA = IIf(Cells(lastRowCom, "AC").Value <> "#N/A N/A", Cells(lastRowCom, "AC").Value, 0)
       ReOE = IIf(Cells(lastRowCom, "AD").Value <> "#N/A N/A", Trim(Cells(lastRowCom, "AD").Value), 0)
       AvergShares = IIf(Cells(lastRowCom, "AE").Value <> "#N/A N/A", Trim(Cells(lastRowCom, "AE").Value), 0)
       netPOL = IIf(Cells(lastRowCom, "AF").Value <> "#N/A N/A", Trim(Cells(lastRowCom, "AF").Value), 0)
       DebtEqy = IIf(Cells(lastRowCom, "AG").Value <> "#N/A N/A", Trim(Cells(lastRowCom, "AG").Value), 0)
       paidUpCapital = IIf(Cells(lastRowCom, "AH").Value <> "#N/A N/A", Trim(Cells(lastRowCom, "AH").Value), 0)
       fiyeMonth = IIf(Cells(lastRowCom, "AI").Value <> "#N/A N/A", Trim(Cells(lastRowCom, "AI").Value), 0)
       yearIdCom = Cells(1, 2)
       periodIdCom = Cells(1, 7)

        
        'check company financial exist
        Set rs = New ADODB.Recordset
        sqlStr = "SELECT year_id, period_id, stock_code FROM company_financial WHERE year_id = '" & yearIdCom & "' AND period_id = '" & periodIdCom & "' AND stock_code = '" & stockCodeCom & "' "
        rs.Open sqlStr, conn, adOpenStatic

        If rs.EOF Then
            'record not exist in DB
            recordExistCom = False
        Else
            stockCodeCom = rs.Fields("stock_code")
            recordExistCom = True
        End If

        rs.Close
        Set rs = Nothing

If recordExistCom = False Then
    'insert new company
    If recordExistCom = False Then
'insert new company
If stockCodeCom <> "" Then
 If tradeReceivable = Null Then
 tradeReceivable = 0
 End If
 If TotLiability = Null Then
 TotLiability = 0
 End If
  If marketCapCom = Null Then
 marketCapCom = 0
 End If
  If salesToTotalAss = Null Then
 salesToTotalAss = 0
 End If
  If fiyeMonth = Null Then
 fiyeMonth = 0
 End If
 
 
   sqlStr = "INSERT INTO company_financial "
   sqlStr = sqlStr & "(year_id, period_id, stock_code, receivable, revenue, total_liabilities, shareholders_equity, total_liabilities_to_shareholders_equity, current_liabilities, total_current_assets, net_attributable, inventories, fixed_assets, operating_cost, net_cash_operation, price_close, pe, eps, pe_relative_sector, altman_z_score, paid_up_capital, net_profit_or_loss, dec_stock, dec_debtors, inc_creditors, net_investments, cash_and_equivalents, retained_profit_or_loss, ebit, mkt_cap, sales_to_assets, rota, roe, debt_to_equity, average_shares, total_assets, shares_outstanding, report_period_end_mth)"
   sqlStr = sqlStr & " VALUES "
   sqlStr = sqlStr & " ('" & yearIdCom & "', '" & periodIdCom & "', '" & stockCodeCom & "', '" & tradeReceivable & "', '" & revenueCom & "', '" & TotLiability & "', '" & shareholderEqyCom & "', '" & totLiabToSE & "', '" & curLiability & "', '" & currentAss & "', '" & netAttributableCom & "', '" & inventory & "', '" & fixedAsset & "', '" & operatingExpenses & "', '" & operatingCashFlow & "', '" & ClosingPrice & "', '" & priceEarningRatio & "', '" & earningPerShare & "', '" & peRatio & "', '" & altmanZ & "', '" & paidUpCapital & "', '" & netPOL & "', '" & incStock & "', '" & netInvest & "', '" & netInvest & "', '" & netInvest & "', '" & cashNeqvalent & "', '" & retainedPOL & "', '" & EbitCom & "', '" & marketCapCom & "', '" & salesToTotalAss & "', '" & ReOTA & "', '" & ReOE & "', '" & DebtEqy & "', '" & AvergShares & "', '" & totalAss & "', '" & sharesOutstdgCom & "', '" & fiyeMonth & "')"
 
   conn.Execute sqlStr
      End If
     
          lastRow = lastRow + 1
    
    Else
    sqlStr = "UPDATE company_financial SET "
    sqlStr = sqlStr & " receivable = '" & tradeReceivable & "'"
    sqlStr = sqlStr & " revenue = '" & revenueCom & "'"
    sqlStr = sqlStr & " total_liabilities = '" & TotLiability & "'"
    sqlStr = sqlStr & " shareholders_equity = '" & shareholderEqyCom & "'"
    sqlStr = sqlStr & " total_liabilities_to_shareholders_equity = '" & totLiabToSE & "'"
    sqlStr = sqlStr & " current_liabilities = '" & curLiability & "'"
    sqlStr = sqlStr & " total_current_assets = '" & currentAss & "'"
    sqlStr = sqlStr & " net_attributable = '" & netAttributableCom & "'"
    sqlStr = sqlStr & " inventories = '" & inventory & "'"
    sqlStr = sqlStr & " fixed_assets = '" & fixedAsset & "'"
    sqlStr = sqlStr & " operating_cost = '" & operatingExpenses & "'"
    sqlStr = sqlStr & " net_cash_operation = '" & operatingCashFlow & "'"
    sqlStr = sqlStr & " price_close = '" & ClosingPrice & "'"
    sqlStr = sqlStr & " pe = '" & priceEarningRatio & "'"
    sqlStr = sqlStr & " eps = '" & earningPerShare & "'"
    sqlStr = sqlStr & " pe_relative_sector = '" & peRatio & "'"
    sqlStr = sqlStr & " altman_z_score = '" & altmanZ & "'"
    sqlStr = sqlStr & " paid_up_capital = '" & paidUpCapital & "'"
    sqlStr = sqlStr & " net_profit_or_loss = '" & netPOL & "'"
    sqlStr = sqlStr & " dec_stock = '" & incStock & "'"
    sqlStr = sqlStr & " dec_debtors = '" & netInvest & "'"
    sqlStr = sqlStr & " inc_creditors = '" & netInvest & "'"
    sqlStr = sqlStr & " net_investments = '" & netInvest & "'"
    sqlStr = sqlStr & " cash_and_equivalents = '" & cashNeqvalent & "'"
    sqlStr = sqlStr & " retained_profit_or_loss = '" & retainedPOL & "'"
    sqlStr = sqlStr & " ebit = '" & EbitCom & "'"
    sqlStr = sqlStr & " mkt_cap = '" & marketCapCom & "'"
    sqlStr = sqlStr & " sales_to_assets = '" & salesToTotalAss & "'"
    sqlStr = sqlStr & " rota = '" & ReOTA & "'"
    sqlStr = sqlStr & " roe = '" & ReOE & "'"
    sqlStr = sqlStr & " debt_to_equity = '" & DebtEqy & "'"
    sqlStr = sqlStr & " average_shares = '" & AvergShares & "'"
    sqlStr = sqlStr & " total_assets = '" & totalAss & "'"
    sqlStr = sqlStr & " shares_outstanding = '" & sharesOutstdgCom & "'"
    sqlStr = sqlStr & " report_period_end_mth = '" & fiyeMonth & "'"
 
    sqlStr = sqlStr & " update_by = 'user'"
    sqlStr = sqlStr & " update_date = '" & Format(Now(), "yyyy-MM-dd HH:mm") & "'"
    sqlStr = sqlStr & " WHERE year_id = '" & yearIdCom & "' AND period_id = '" & periodIdCom & "' AND stock_code = '" & stockCodeCom & "')'"
     conn.Execute sqlStr
 
        End If
         lastRowCom = lastRowCom + 1

End If
Loop

mssql

3 Contributors
3 Replies
163 Views
3 Days Discussion Span
Latest Post 12 Years Ago Latest Post by violette

adam_k 239 Master Poster

12 Years Ago

I believe that you've messed up your IFs.
You've got 2 IF recordExistCom = False Then and the second of them ends just before the loop.
The Else you've got between insert and update will only work for the inner if, and you'll never update.

PS: It would make it easier to control the IFs if you kept changed the

IF tradeReceivable = NULL Then
 tradeReceivable = 0
 End IF

to

IF tradeReceivable = NULL Then tradeReceivable = 0

and similar for all the field values you're doing this for.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

urtrivedi 276 Nearly a Posting Virtuoso · Answer 1 · 2011-11-12T23:16:47+00:00

For sheet to work, I think you must have loop in lopp

for 1 to n worksheet
while cell empty
end while
end for

and for duplicate checking ur codes may hang due to deadlock, you can follow the link given here
http://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server

violette 0 Junior Poster in Training · Answer 2 · 2011-11-15T14:10:55+00:00

thanks guys. :) i've found my mistakes. missing commas in the Update sql. that's why it's not work. i've simplified my code according to adam_k has suggested as well. thanks a lot! :)