adding data to listview

[BlkR]

Hi,

Currently having troubles in adding data to the listview i have created. It actually will add the first set of data to both the database and listview, but when i try to add a second lot of data it displays the error message that I have setup. Also how would I go about updating the same item with different amount of quantity but not adding a new set of row to it ? using the UPDATE method?

Private Function listview()

        Dim lSingleItem As ListViewItem  'The variable will hold the ListItem information so that you can add values to the column you want to change


        lSingleItem = ListView1.Items.Add(txtItemNo.Text.Trim)  'Create a new line, and assign the ListItem into the variable so we can add sub items
        lSingleItem.SubItems.Add(txtItemName.Text.Trim) 'The first sub item for the first line
        lSingleItem.SubItems.Add(FormatCurrency(txtUnitPrice.Text.Trim))  'The second sub item for the first line
        lSingleItem.SubItems.Add(txtQty.Text.Trim)
        lSingleItem.SubItems.Add(FormatCurrency(txtUnitTotal.Text.Trim))

        ' total = total + Val(txtTotal.Text.Trim)
        'lblSubTotal.Text = total

    End Function


Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        
        Try


            Dim strSQL As String

            strSQL = "INSERT INTO trans2 (trans_ID, Item_ID, ItemName, Price, Qty, Total)VALUES (""" & txttran.Text & """ , """ & txtItemNo.Text & """ , """ & txtItemName.Text & """ , """ & txtUnitPrice.Text & """, """ & txtQty.Text & """ , """ & txtUnitTotal.Text & """)"


            dc.Open()
            MessageBox.Show(strSQL)

        

            Dim cmd As New OleDbCommand(strSQL, dc)
           

            cmd.ExecuteNonQuery()
          

            Call listview()

        Catch
            MessageBox.Show("Error occured when trying to save data!", "Sale", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            txtItemNo.Focus()

        End Try


        'dc.Close()
        txtItemNo.Clear()
        txtItemName.Clear()
        txtQty.Clear()
        txtUnitPrice.Clear()
        txtUnitTotal.Clear()


    End Sub

[babbu]

wht is the error tht u r getting?

[babbu]

u can use data table along with command builder to add data.
but u will have to create a new row in the data table.
the inserting part will be taken care by the command builder

[BlkR]

the 2nd time i press the add button it goes straight to the error message that i put ie : "Error occured when trying to save data!"

[babbu]

why have u commented
dc.close.
the error might be because the connection is already open and u r again trying to open it

[BlkR]

oh i have changed that back before i posted it up here... still the same

[kvprajapati]

Single quote needed.

strSQL = "INSERT INTO trans2 (trans_ID, Item_ID, ItemName, Price, Qty, Total)VALUES ('" & txttran.Text & "'" , "'" & txtItemNo.Text & "'" , "'" & txtItemName.Text & "'" , "'" & txtUnitPrice.Text & "'", "'" & txtQty.Text & "'" , "'" & txtUnitTotal.Text & "')"

[BlkR]

already tried that too still same

[BlkR]

already tried that but still the same .... and i think its only '" & textbox.text & "' rather than "'" ?... i dont think its the sql statement as the first time i add it, it works

[kvprajapati]

Do not use single quote with numeric fields. Non-numeric & date items must be surrounded with single quotes.

[BlkR]

hmm don't think its the quotes ? i have this at the moment and still the same problem

strSQL = "INSERT INTO trans2 (trans_ID, Item_ID, ItemName, Price, Qty, Total)VALUES ('" & txttran.Text & "' , '" & txtItemNo.Text & "' , '" & txtItemName.Text & "' , """ & txtUnitPrice.Text & """ , """ & txtQty.Text & """, """ & txtUnitTotal.Text & """ )"

[BlkR]

oh i've fixed it, I set a primary key in my database for that table.... all is good now hehehe...

now to deal with adding qty over an item that has been added rather than adding a completely new item

[babbu]

ok remove the try catch statement
and get the exact error tht u r getting

[babbu]

either delete the complete row and add a new row for the qty to be added or else use insert statement and specify only the qty column