saving record from listview into access database

[Arie_1]

I know to save records from database into listview, but how to save listview to database instead..

this is the code for showing record into list view..but can any help me how to change the code to save listview into database?

Imports System.Data.OleDb
Public Class Form1

    Dim con As New OleDbConnection

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            con.ConnectionString = "provider=microsoft.ACE.oledb.12.0;data source=C:\Users\asus\Documents\Visual Studio 2010\Projects\Banananana\Banananana\bin\Debug\Database.accdb"
            con.Open()

            showMyRecords()
            con.Close()

        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try
    End Sub

    Public Sub showMyRecords()
        Dim dt As New DataTable
        Dim ds As New DataSet
        ds.Tables.Add(dt)

        Dim da As New OleDbDataAdapter("Select * from Table1", con)
        da.Fill(dt)

        Dim myRow As DataRow

        For Each myRow In dt.Rows
            ListView1.Items.Add(myRow.Item(0))
            ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(myRow.Item(1))
            ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(myRow.Item(2))
            ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(myRow.Item(3))
            ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(myRow.Item(4))
        Next

    End Sub
End Class

[Reverend Jim]

Here is an OleDB example using a sample database with fields of type int, int, varchar(50, int.

Dim con As New OleDbConnection("Provider=SQLNCLI10;Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=Yes;Connect Timeout=15;")
con.Open()

For Each item As ListViewItem In ListView1.Items

    Dim cmd As New OleDbCommand("", con)
    cmd.CommandText = "INSERT INTO teams " &
                        "(recordID, OrgID, Team, boolIsActive) " &
                    " VALUES(" & item.SubItems(0).Text & " ," &
                    "        " & item.SubItems(1).Text & " ," &
                    "       '" & item.SubItems(2).Text & "'," &
                    "        " & item.SubItems(3).Text & ")"

    cmd.ExecuteNonQuery()

Next

con.Close()

This example illustrates the INSERT syntax but you should really use a Parameterized Query which is a little more trouble to set up but worth it for the extra protection.

[Arie_1]

i build a code, but it didn't save anything on the microsoft access database (without sql server)

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        Dim con As New OleDbConnection
        con.ConnectionString = "provider=microsoft.ACE.oledb.12.0;data source=C:\Users\asus\Desktop\eRemake program - Copy\FYP2\bin\Debug\Database.accdb"
        Dim cmd As New OleDbCommand
        InitializeComponent()
        con.Open()


        For Each row As ListViewItem In ListView1.Items

            Dim query As String = "insert into mytable (Index,Message status,Number,Date and Time,Message)
            values(" & "'" & row.SubItems(1).Text & "'," 
            & "'" & row.SubItems(2).Text & "'," 
            & row.SubItems(3).Text & "'," & "'" 
            & row.SubItems(4).Text & "'," & "'" 
            & row.SubItems(5).Text & ")"


            Debug.WriteLine(query)
            con.Close()
        Next

        cmd.Dispose()
    End Sub
End Class

where did i do wrong in this code? Can help me?

[Reverend Jim]

The first problem is that you have a field name with an embedded blank. The field is Message status. There are two ways to fix this. One way is to always refer to the field as [Message status]. A better way is to rename the field to either MessageStatus or Message_status. Same problem for Date and time.

The second problem is that you built the query string but you didn't execute it. Try adding

cmd.CommandText = query
cmd.ExecuteNonQuery()

The third problem is that different field types use different delimiters. Numeric fields do not require delimiters. Text fields must be surrounded by single quotes. I believe MS Access uses a pound sign to surround datetime fields. However, if you use parameterized queries the proper delimiters will be added for you. Also, quote marks in text fields will be handled without any extra code on your part.