Member Avatar for johnnyboy123

Hi everyone, I am quite new to vb.net. Recently, I've been learning about Oledb commands and it's ability to save records in a form to a database. So, I started a project and now I am stuck. Here's the scenario: The form comprises of several comboboxes, a listview box and a command button. The combobox will get values from an access database. The users will be able to select from the comboboxes and on command button click, the entries will be added to the list view in a single row. All the above steps work perfecty except the part where the button click doesn't save the listview box entries to another access database which it should. I have been trying for over 4 hours now and finally, I decided to ask. Please have a look at my code.

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
       Dim con As New OleDbConnection
       con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\UserSelection.mdb"
       Dim cmd As New OleDbCommand()
       InitializeComponent()
       con.Open()
       For i As Integer = 0 To ListView1.Items.Count - 1
           Dim query As String = "INSERT INTO Selected VALUES (CusName, Product, ProductPrice, Tax)"
           cmd = New OleDbCommand(query, con)
           cmd.Parameters.Add("CusName", OleDbType.[Integer]).Value = (ListView1.Items(i).SubItems(1).Text)
           '!!
           cmd.Parameters.Add("Product", OleDbType.VarChar, 50).Value = ListView1.Items(i).SubItems(2).Text
           cmd.Parameters.Add("Nama_produk", OleDbType.VarChar, 50).Value = ListView1.Items(i).SubItems(3).Text
           cmd.Parameters.Add("ProductPrice", OleDbType.VarChar, 50).Value = ListView1.Items(i).SubItems(4).Text
           cmd.Parameters.Add("Tax", OleDbType.VarChar, 50).Value = ListView1.Items(i).SubItems(5).Text

           cmd.ExecuteNonQuery()
           con.Close()
       Next


       cmd.Dispose()

   End Sub

Nothing happens. No error messages, but the table doesn't get updated.

P.S: I particularly want to save to access database and not sql database as I don't have a server running. Also, the database which needs to be added with the list view records contains a table with just one column (the ID column).

Thank you.

Edited by johnnyboy123 because: Needed editing
  • 2 Contributors
  • 1 Reply
  • 466 Views
  • 51 Minutes Discussion Span
  • Latest Post Latest Post by Reverend Jim
Member Avatar for Reverend Jim

I'll assume that con.Open() was successful due to the lack of an error message.

Have a look at this example to see how to use parameterized queries with OleDB. You are not using the correct format.

While it is possible to have an INSERT where you don't specify the field names, this is not a good practice because you can't guarantee that the values match up with the correct fields in the table. Even if they do now, a reorg on the table will break your query.

If you have a table with the fields, prodID, prodName, prodDesc and your query looks like

INSERT INTO myTable VALUES(prodID, prodName, prodDesc)

then everything is fine until some yahoo adds a prodQty column after prodID. Use the form

INSERT INTO myTable (fld1, fld2, fld3) VALUES(val1, val2, val3)
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.