[RESOLVED] Questions on Database Update

[Simply Me]

HI! I have to two tables tblStocks and tblSales (MS ACCESS) which i would like to manipulate. I am using ADODB. My questions are:

1. Which is better in updating table Update tbl Set text......ObjConn.Execute or rs.update?
2. How do i update two tables at the same time? I want to update one (1) field in my tblStocks and five (5) fields in tblSales. The fields in two tables are not the same.
3. I have this code in cmdAddItem_Click().

rsStocks.Fields("Quantity") = (Val(rsStocks.Fields("Quantity") + Val(iQtyTemp)) - Val(txtQty.Text))
    rsStocks.Update 'Update the database

I have another code in

Private Sub lstStocks_KeyDown(KeyCode As Integer, Shift As Integer)
  If rsStocks.State = adStateOpen Then rsStocks.Close
  rsStocks.Open sSQL, oConn, adOpenStatic, adLockOptimistic
  
  Select Case KeyCode
    Case vbKeyDelete
      If lstStocks.ListItems.Count <> 0 Then
        edwin = lstStocks.SelectedItem.Index
        sSQL = "SELECT * FROM tblStocks WHERE Code = '" & lstStocks.ListItems.Item(edwin).Text & "'"
        'rsStocks.Open("SELECT * FROM tblStocks WHERE Code = '" & lstStocks.ListItems.Item(edwin).Text & "'")
        rsStocks.Fields("Quantity") = Val(rsStocks.Fields("Quantity") + Val(lstStocks.ListItems.Item(edwin).SubItems(2)))
        rsStocks.Update
        lstStocks.ListItems.Remove (edwin)
        iValTemp = 0
        For i = 1 To lstStocks.ListItems.Count
          iValTemp = Val(iValTemp) + Val(lstStocks.ListItems.Item(i).SubItems(5))
          txtAmountDue.Text = iValTemp
        Next
      End If
  End Select
End Sub

the codes above works fine. But why is it that when i change the first code in its SQL equivalent it behaves differently.

sSQL = "UPDATE tblStocks SET Quantity = '" & Val(rsStocks.Fields("Quantity")) & "' + '" & Val(iQtyTemp) & "' - '" & Val(txtQty.Text) & "'"
    oConn.Execute sSQL

Please Help! Example code is needed.

[Simply Me]

I change my last code with this

sSQL = "UPDATE TblStocks SET Quantity = [Quantity] WHERE Quantity= '" & (Val(rsStocks.Fields("Quantity") & "' + '" & Val(iQtyTemp)) & "' - '" & Val(txtQty.Text)) & "'"
    oConn.Execute sSQL

this time the database is not subtracting the value i typed txtQty from the value stored in field Quantity.

Any one Please?

[Simply Me]

Any Database Guru help please???

[litlewiki]

ialways use the objconn.execute method ,i think its a personal prefernce as to which method u choose

all your fields are numerics so why are you sending them as strings ,numerics are sent as ' & variable & ' .also make sure that you are using different connections and recordsets ,one for retreiving and other for uppdating or better store them in local variables then execute them

[randem]

If you use the .Execute method you can update many records in one SQL statement. Using the .Update method will only be able to update one record at a time. I always find it easier and better documented when you use SQL to do processes on your database when ever possible. one statement in readable english (or whatever language you use) can be very useful months down the road when one has to revist the code to find out what is happening.

They won't have to figure out what the originator was attempting to do (for the most part).

[Simply Me]

Thanks litlewiki and randem.

I was able to make the last code i pasted in post #1 work.

[quote]all your fields are numerics so why are you sending them as strings ,numerics are sent as ' & variable & ' .[/qoute]

If I send value to my fields like what you are saying it gives me error but if i do it like this '" & variable & "' it works find.

Right now what i am trying to figure out is the SQL equivalent of this line found in post #1

rsStocks.Fields("Quantity") = Val(rsStocks.Fields("Quantity") + Val(lstStocks.ListItems.Item(edwin).SubItems(2)))
        rsStocks.Update

[GaryMazzone]

strSQL = "Update tableName Set Quantity = Quantity + " & Val(lstStocks.ListItems.Item(edwin).SubItems(2)) & " Where PrimaryKey for Table = " & the record key

cbconn.Execute strSQL

[Simply Me]

i did this

sSQL = "UPDATE TblStocks SET Quantity = Quantity + '" & Val(lstStocks.ListItems.Item(edwin).SubItems(2)) & "' WHERE Code='" & Val(lstStocks.ListItems.Item(edwin).Text) & "'"

its not adding giving me the correct value.

[Pasvorto]

I think you mean this:

sSQL = "UPDATE TblStocks SET Quantity = Quantity + " & Val(lstStocks.ListItems.Item(edwin).SubItems(2)) & " WHERE Code= " & Val(lstStocks.ListItems.Item(edwin).Text) & """

[GaryMazzone]

What is the actual SQL statement that is being generated? Can you post that.

[Simply Me]

I think you mean this:

sSQL = "UPDATE TblStocks SET Quantity = Quantity + " & Val(lstStocks.ListItems.Item(edwin).SubItems(2)) & " WHERE Code= " & Val(lstStocks.ListItems.Item(edwin).Text) & """

I am getting this error message "Syntax error in String in Query expression 'Code=2"'"

[Pasvorto]

sSQL = "UPDATE TblStocks SET Quantity = Quantity + "
sSql = sSql & Val(lstStocks.ListItems.Item(edwin).SubItems(2))
sSql = sSql & " WHERE Code= " & Val(lstStocks.ListItems.Item(edwin).Text) & chr(34)

[GaryMazzone]

The extra double qoute at the end of the SQL string should not be there if the code is a number.

[Simply Me]

What I am trying to do is like this. I have a listview where i can delete any row in it. When I delete a row I would like that the value in the column QTY will added back to Quantity field in my database.

Say I have in my field Quantity=500. If I will issue one (1) item then my Quantity field becomes 499. That one (1) item will then be displayed in my listview but once I delete that row in my listview (the item one (1)) 1 should be added back to 499.

Here's my code:

Private Sub lstStocks_KeyDown(KeyCode As Integer, Shift As Integer)
  If rsStocks.State = adStateOpen Then rsStocks.Close
  rsStocks.Open sSQL, oConn, adOpenStatic, adLockOptimistic
  
  Select Case KeyCode
    Case vbKeyDelete
      If lstStocks.ListItems.Count <> 0 Then
        edwin = lstStocks.SelectedItem.Index
        sSQL = "SELECT * FROM tblStocks WHERE Code = ' & lstStocks.ListItems.Item(edwin).Text & '"
    sSQL = "UPDATE TblStocks SET Quantity = Quantity + " & Val(lstStocks.ListItems.Item(edwin).SubItems(2)) & " WHERE Code= ' & Val(lstStocks.ListItems.Item(edwin).Text) & '"
    oConn.Execute sSQL
        
        
        lstStocks.ListItems.Remove (edwin)
        iValTemp = 0
        For i = 1 To lstStocks.ListItems.Count
          iValTemp = Val(iValTemp) + Val(lstStocks.ListItems.Item(i).SubItems(5))
          txtAmountDue.Text = iValTemp
        Next
      End If
  End Select
End Sub

[GaryMazzone]

Is code a numeric in the Database? What sSQL are you runing the Select statement or the Update statement? Why both of them in the code section? The select is never going to run as coded.

if Code is a numeric in the DB then

sSQL = "UPDATE TblStocks SET Quantity = Quantity + " & Val(lstStocks.ListItems.Item(edwin).SubItems(2)) & " WHERE Code= " & Val(lstStocks.ListItems.Item(edwin).Text)

if code is text in the DB then

sSQL = "UPDATE TblStocks SET Quantity = Quantity + " & Val(lstStocks.ListItems.Item(edwin).SubItems(2)) & " WHERE Code= '" & (lstStocks.ListItems.Item(edwin).Text) & "'"

[Simply Me]

Im sorry SELECT should have been commented in my post. Yes Code is Numeric in DB.
I tried you code and give "Data Type mismatch in criteria expression."

[GaryMazzone]

Please post the generated SQL statement that is being run.

[Simply Me]

Please post the generated SQL statement that is being run.

Im sorry I dont seem to understand what you mean.

[GaryMazzone]

The program is generating the SQL string to send to the database what is the complete string that is being sent by the program. Use debug print statements to place it into the immediate window or place a textbox on the form and place the SQL statement there once the generation is complete. Then copy and paste that code into the thread.

[mohit_5783]

I have a suggesstion for u i dont know whether i m correct or not but i think this will help u to generate queries as and when required

1. Open Data base in MS - Access
2. Then Open Query tab
3. Try to make Query overthere (Using Design View)
4. Then see the sql code generated for it and
5. try to implement that same in the sqlqry stat that was being told by our friends

Tell me if it works or anyhow i could be able to help

[Simply Me]

The program is generating the SQL string to send to the database what is the complete string that is being sent by the program. Use debug print statements to place it into the immediate window or place a textbox on the form and place the SQL statement there once the generation is complete. Then copy and paste that code into the thread.

Do you mean this?

UPDATE TblStocks SET Quantity = Quantity + 1 WHERE Code= 1

When the error "Data Type mismatch in criteria expression." displayed on screen i clicked the debug button and I type ?sSQL in the immediate window and it gave that result.

[GaryMazzone]

OK next question is what are the data types in the database for Quanity and Code? Also ensure that code in in brackets.

[Simply Me]

Quantity is of number data type and Code is of text data type

[GaryMazzone]

Then code needs to be enclosed in single qoutes.

[Simply Me]

Then code needs to be enclosed in single qoutes.

GaryMazzone, I have the following code working:
TO DISPLAY ITEM IN LISTVIEW AND SUBSEQUENTLY SUBTRACT SUCH ITEM FROM THE DATABASE.

Private Sub cmdAddItem_Click()
  Dim lst As ListItem, itemFound As ListItem
  
  If lstStocks.Enabled = False Then
    lstStocks.Enabled = True
  End If
  
  sSQL = "SELECT * FROM tblStocks WHERE Code = '" & txtCode.Text & "'"

  If rsStocks.State = adStateOpen Then rsStocks.Close
  rsStocks.Open sSQL, oConn, adOpenStatic, adLockOptimistic
  
  If txtQty.Text <> 0 Then
    cAmountDueTemp = 0
    If txtDiscount.Text = "" Then
      txtDiscount.Text = 0
      cSubtotal = (CLng(txtQty.Text) * (rsStocks.Fields("SellingPrice")))
    Else
      cSubtotal = (CLng(txtQty.Text) * (rsStocks.Fields("SellingPrice"))) - (CLng(txtQty.Text) * (CCur(txtDiscount.Text))) 'Val(iTempSubTotal))
    End If

    sSQL = "UPDATE TblStocks SET Quantity = Quantity + '" & iQtyTemp & "' - '" & CLng(txtQty.Text) & "' WHERE Code='" & txtCode.Text & "'"
    oConn.Execute sSQL
    
    Set lst = lstStocks.ListItems.Add(, , txtCode.Text) 'DISPLAY IN LISTVIEW
      lst.SubItems(1) = txtDesc.Text
      lst.SubItems(2) = CLng(txtQty.Text)
      lst.SubItems(3) = Format(rsStocks.Fields("SellingPrice"), "###,###,##0.00")
      lst.SubItems(4) = Format(rsStocks.Fields("PurchasePrice"), "###,###,##0.00")
      lst.SubItems(5) = Format(CCur(txtDiscount.Text) * CLng(txtQty.Text), "###,###,##0.00")
      lst.SubItems(6) = cSubtotal
    
    txtCode.Text = ""
    txtDiscount.Text = ""
    txtQty.Text = ""
    txtCode.SetFocus
  
    For i = 1 To lstStocks.ListItems.Count
      cAmountDueTemp = CCur(cAmountDueTemp) + CCur(lstStocks.ListItems.Item(i).SubItems(6))
      txtAmountDue.Text = cAmountDueTemp
    Next
  Else
    MsgBox "Quantity must not be zero"
    txtQty.SetFocus
    SendKeys "{home}+{end}"
  End If
End Sub

AND THIS CODE TO ADD BACK AN ITEM TO THE DATABASE IF BUYER DECIDE NOT TO BUY PREVIOUSLY ADDED ITEM

Private Sub lstStocks_KeyDown(KeyCode As Integer, Shift As Integer)
  If rsStocks.State = adStateOpen Then rsStocks.Close
  rsStocks.Open sSQL, oConn, adOpenStatic, adLockOptimistic
  
  Select Case KeyCode
    Case vbKeyDelete
      If lstStocks.ListItems.Count <> 0 Then
        edwin = lstStocks.SelectedItem.Index
    sSQL = "UPDATE TblStocks SET Quantity = Quantity + ' & Val(lstStocks.ListItems.Item(edwin).SubItems(2)) & ' WHERE Code= ' & Val(lstStocks.ListItems.Item(edwin).Text) & '"
    oConn.Execute sSQL
        
        
        lstStocks.ListItems.Remove (edwin)
        cAmountDueTemp = 0
        For i = 1 To lstStocks.ListItems.Count
          cAmountDueTemp = CCur(cAmountDueTemp) + CCur(lstStocks.ListItems.Item(i).SubItems(5))
          txtAmountDue.Text = cAmountDueTemp
        Next
      End If
  End Select
End Sub

PROBLEM: Everytime I delete an Item instead of adding back the item to the database it subtract it to the database.

DATA TYPES USED:
Code= Text
ProductDescription = text
SellingPrice = Currency
PurchasePrice = Currency
Quantity = Long
Unit = text
EntryDate = Date
Discount = Currency

IN THE DECLARATION AREA I HAVE THE FF:
Dim rsSales As adodb.Recordset
Dim rsStocks As adodb.Recordset
Dim sSQL$, sSQL1$
Dim cSubtotal As Currency, cAmountDueTemp As Currency
Dim iQtyTemp As Long, i%, edwin%, itmFound%

[GaryMazzone]

I think I'm confused here. Let's start with the remove from inventory

the code:

sSQL = "UPDATE TblStocks SET Quantity = Quantity + '" & iQtyTemp & "' - '" & CLng(txtQty.Text) & "' WHERE Code='" & txtCode.Text & "'"

What is the iQttTemp & "'-'" & clng(txtQty.Text) for. Why not just

sSQL = "UPDATE TblStocks SET Quantity = Quantity - + & CLng(txtQty.Text) & " WHERE Code='" & txtCode.Text & "'"

That should subtract the quanty in the txtQty field on the from from the Quanity field in the database.

[Simply Me]

the code:

sSQL = "UPDATE TblStocks SET Quantity = Quantity + '" & iQtyTemp & "' - '" & CLng(txtQty.Text) & "' WHERE Code='" & txtCode.Text & "'"

i used the iQtyTemp in my Private sub lstStocks_DblClick().----for editing purposes. When i double click an item in listview the item will be romove from the listview and display in textboxes.

What is the iQttTemp & "'-'" & clng(txtQty.Text) for. Why not just

sSQL = "UPDATE TblStocks SET Quantity = Quantity - + & CLng(txtQty.Text) & " WHERE Code='" & txtCode.Text & "'"

That should subtract the quanty in the txtQty field on the from from the Quanity field in the database.

I tried your code and it gave me type mismatch error. When i debug it this is what is written in the immediate window.

UPDATE TblStocks SET Quantity = Quantity + '0' - '1' WHERE Code='4800888121929'

I think i should not be using the txtQty.text when i use the delete key there's no value in the textbox that's why i used this line

" & CLng(lstStocks.ListItems.Item(edwin).SubItems(2)) & " WHERE Code= ' & (lstStocks.ListItems.Item(edwin).Text) & '"

[Simply Me]

Thanks to My Buddy Erick Widya for helping me!
and to all who contributed their ideas.

here's the correct code:
[code]sSQL = "UPDATE tblStocks SET Quantity = Quantity + " & CLng(lstStocks.ListItems.Item(edwin).SubItems(2)) & " WHERE

= '" & (lstStocks.ListItems.Item(edwin).Text) & "'"