Add values from from Listview to Ms access database

[blueprints]

Hi,

i am currently doing a school project. Am i able to insert values from listview into ms access?

What i have done is that i have 2 listview box the top of the box actually read all the data from my database. (2 columns)

I added a one click activation listener, so if i select one row from the first listview it will automatic add to my second listview box.

So now is my question, In my second listview box how am i able to add all my row of listview items into my ms access database.

Here is my source code:

// This is when my form load i will load all my data from my database to my first list view

Private Sub services_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
Dim objItem As ListViewItem
searchList.View = View.Details
Try
searchList.Items.Clear()
oledbcon.Open()
strSQL = "SELECT sname, cat from productname"
Reading(strSQL)
While objRead.Read
objItem = searchList.Items.Add(objRead("sname"))
objItem.SubItems.Add(objRead("cat").ToString())
End While
objRead.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
oledbcon.Close()
End Try
End Sub

//Here is my oneclick activation where i click one row it will add to another listview

Private Sub searchList_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles searchList.SelectedIndexChanged
Dim Item As ListViewItem
Dim sItem As String = ""
Dim sItems As String = ""
Dim x As Integer = 0
Dim iSubItemCount As Integer = 0
confirmList.View = View.Details
If searchList.Activation = ItemActivation.OneClick Then
With searchList
For Each Item In .SelectedItems()
sItem &= Item.SubItems(0).Text
sItems &= Item.SubItems(1).Text
Item = confirmList.Items.Add(sItem)
Item.SubItems.Add(sItems)
Next
End With
End If

End Sub

//Here is my second Listview box where if i click one row it will delete away.

Private Sub confirmList_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles confirmList.SelectedIndexChanged
Dim Item2 As ListViewItem
Dim sItem2 As String = ""
Dim sItems2 As String = ""
If confirmList.Activation = ItemActivation.OneClick Then
If (confirmList.SelectedItems.Count > 0) Then
confirmList.SelectedItems(0).Remove()
End If

End If
End Sub

So how am i able to add value from my second listview box into my database table?
where should i add it at?
I try puting at my first list view box when the user click one row it will insert into the database and also insert another row into my second listview box but it does not work.

After doing some research i manage to write this code but this is not working nothing is written to my database table.
I have a confirm button that write my second listview box into the database.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sid As Integer
Dim pname As String
sid = 5
Try
For Each lvitem As ListViewItem In Me.confirmList.SelectedItems
pname = lvitem.SubItems(0).Text
oledbcon.Open()
strSQL = "INSERT INTO services (servicesID, Pname) VALUES ('" & sid & "', '" & pname & "')"
cmd = New OleDbCommand(strSQL, oledbcon)
cmd.ExecuteNonQuery()
Next
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
oledbcon.Close()
End Try
Me.Close()
End Sub

I need help urgently Thanks in advanced.

 

[blueprints]

ok,
i manage to slove the problem myself thanks anyway for this forum.

For people who wan to know how i do it the code is as follow:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim pname As String
Try
oledbcon.Open()
strSQL = "SELECT * from quotation X where QID = (select Max(QID) from quotation where Cname = X.Cname)"
Reading(strSQL)
While objRead.Read
sid = objRead("QID") + 1
End While
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
oledbcon.Close()
End Try
For Each lvitem As ListViewItem In Me.confirmList.Items
pname = lvitem.SubItems(0).Text
oledbcon.Open()
strSQL = "INSERT INTO services (servicesID, Pname) VALUES (" & sid & "," & "'" & pname & "'" & ")"
cmd = New OleDbCommand(strSQL, oledbcon)
cmd.ExecuteNonQuery()
oledbcon.Close()
Next
Me.Close()
End Sub