Member Avatar for jessica_10

ive got a problems in updating the database in ms access, parameters wont work for me.
MS Access + vb.net 2010, can someone tell me the other ways of updating? im a biggener in vb.net

  • 2 Contributors
  • 3 Replies
  • 214 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Santanu.Das

Dani AI

Generated

Quick diagnosis and a safe path forward: there are three separate issues in the snippets above — the SQL string is malformed and unsafe (you were concatenating values and missing punctuation), ExecuteReader() is the wrong method for an UPDATE, and qty/price are stored as text so numeric addition won’t work. was right to push parameterised updates and ExecuteNonQuery(); add the following practical fixes.

Convert the schema first: change qty to a Number (Integer) and price to Currency/Decimal in Access so the DB can do arithmetic and enforce valid data. If you can’t change the table immediately, parse the listview values into numeric types in VB and send numeric parameters (don’t concatenate into SQL).

Example pattern (validate inputs, use positional parameters for OleDb, explicit types, and ExecuteNonQuery):

' parse inputs safely
Dim addQty As Integer = 0
Dim addPrice As Decimal = 0D
Integer.TryParse(lvlist.FocusedItem.Text, addQty)
Decimal.TryParse(lvlist.FocusedItem.SubItems(2).Text, addPrice)

Using Con As New OleDb.OleDbConnection(connString)
    Con.Open()
    Dim sql As String = "UPDATE tblsale SET qty = qty + ?, price = price + ? WHERE name = ?"
    Using cmd As New OleDb.OleDbCommand(sql, Con)
        cmd.Parameters.Add(New OleDb.OleDbParameter("qty", OleDb.OleDbType.Integer)).Value = addQty
        cmd.Parameters.Add(New OleDb.OleDbParameter("price", OleDb.OleDbType.Decimal)).Value = addPrice
        cmd.Parameters.Add(New OleDb.OleDbParameter("name", OleDb.OleDbType.VarChar, 255)).Value = lvlist.FocusedItem.SubItems(1).Text.Trim()
        Dim rowsAffected As Integer = cmd.ExecuteNonQuery()   ' number of rows updated
    End Using
End Using

Notes and troubleshooting: ExecuteNonQuery() returns affected rows — use it to verify success. Do not use AddWithValue indiscriminately (it can infer wrong types); prefer explicit parameter types. Confirm lvlist.FocusedItem and SubItem indexes are correct, open the connection before executing, and wrap DB calls in Try/Catch. If multiple updates must succeed together, use a transaction. If name is not unique, update by ID instead.

Recommended Answers

All 3 Replies

Member Avatar for Santanu.Das

No one has an "Aladin's Light".
Post your code, how far you go and the exceptions you are facing.
Describe your problems, everyone hear to help you to solve your problems.

Member Avatar for jessica_10

@Shark_1 , when i order i want the qty and price to update in database
tblsale : name[text] , qty [text], price[text]
heres my code:

sql = "UPDATE tblsale set qty = qty + '" & lvlist.FocusedItem.text & "price = price + qty + '" & lvlist.FocusedItem..SubItems(2).text & where name = '" & lvlist.FocusedItem.SubItems(1)

    Dim acscmd = New OleDb.OleDbCommand(sql, Con)
    acsdr = acscmd.ExecuteReader()

[its not working and i dont know where to put the calculation]

Edited by jessica_10
Member Avatar for Santanu.Das

You want to update the database. But, ExecuteReader() uses to read data from a query .

To update data you will have to use ExecuteNonQuery() and by using ExecuteScalar() you can get the number of rows which are satiesfied your condition.

From my point of view, there is something wrong in your SQL statement. Use parameterised Sql query.

sql = "UPDATE tblsale set qty = qty + @qty, price = price + @price where name = @name"
Dim acscmd As OleDb.OleDbCommand= New OleDb.OleDbCommand(sql, Con)

acscmd.Parameters.AddWithValue("@qty", lvlist.FocusedItem.text)
acscmd.Parameters.AddWithValue("@price", lvlist.FocusedItem..SubItems(2).text)
acscmd.Parameters.AddWithValue("@name", lvlist.FocusedItem.SubItems(1).Text)

acscmd.ExecuteNonQuery()

acscmd.Dispose()
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.