将列表视图中的一些数据插入到 vb.net 中的 ms access 数据库中

Insert some data from a listview into a ms access database in vb.net

提问by renge

i am trying to insert my listview items into my MS access database.

我正在尝试将我的列表视图项目插入到我的 MS Access 数据库中。

here is the code:

这是代码：

Public newConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & My.Application.Info.DirectoryPath.ToString() & "\BackUp\Inbox.Accdb;Persist Security Info=False;"

Private Sub btnNewSMS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNewSMS.Click

    cn.ConnectionString = newConn
    If cn.State = ConnectionState.Closed Then
        cn.Open()

        For x = 0 To ListView2.Items.Count - 1

            Dim sqlQuery As String = "INSERT INTO InboxTable (Contact_Name,Contact_Number,DateAndTime,Message) Values ('" & ListView2.Items(x).SubItems(0).Text & "', '" & ListView1.Items(x).SubItems(1).Text & "','" & ListView1.Items(x).SubItems(2).Text & "','" & ListView1.Items(x).SubItems(3).Text & ")"

            Dim cmd As New OleDbCommand

            With cmd
                .CommandText = sqlQuery
                .Connection = cn
                .ExecuteNonQuery()


            End With
            MsgBox("Messages Saved")
            ListView2.Items.Clear()

            'End With

        Next

    End If
    cn.Close()


End Sub

my error is:

我的错误是：

Value of '0' is not valid for 'index'

“0”的值对“索引”无效

my problem is in inserting the values. Please Help me... thanks everyone - Chris

我的问题是插入值。请帮助我...谢谢大家 - 克里斯

采纳答案by Diaton

'Try this.. 

Public newConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & My.Application.Info.DirectoryPath.ToString() & "\BackUp\Inbox.Accdb;Persist Security Info=False;"


Private Sub btnNewSMS_Click(sender As Object, e As EventArgs) Handles btnNewSMS.Click


    For Each x As ListViewItem In ListView2.Items
        Dim sqlQuery As String = "INSERT INTO InboxTable (Contact_Name,Contact_Number,DateAndTime,Message) Values ('" & _
                        x.SubItems(0).Text & "', '" & _
                        x.SubItems(1).Text & "','" & _
                        x.SubItems(2).Text & "','" & _
                        x.SubItems(3).Text & "')"
        'Add also the last single qoute before the parenthesis ^_^
        'to make sure the query works..
        Dim cmd As New OleDbCommand

        With cmd
            .CommandText = sqlQuery
            .Connection = cn
            .ExecuteNonQuery()
        End With
        MsgBox("Messages Saved")
        ListView2.Items.Clear()

        End With
    Next

End Sub

回答by Somi

Use this code for more than one form's

DataTransfer.vb

将此代码用于多个表单的

DataTransfer.vb

Public Class DataTransfer

Public Sub ListToData(ByVal strcon As String, ByVal strcmd As String, ByVal listview As ListView)
Dim i As Integer = 0
Dim k As Integer = 0
Dim item As New ListViewItem
Dim con As New System.Data.OleDb.OleDbConnection(strcon)
Dim cmd As New System.Data.OleDb.OleDbCommand(strcmd, con)
Try
MessageBox.Show(listview.Items.Count)
  While i < listview.Items.Count
      item = New ListViewItem()
      item = listview.Items(i).Clone
      itemToString(item, strcmd)
      con.Open()
      cmd.CommandText = strcmd
      cmd.ExecuteNonQuery()
      MessageBox.Show("saved")
      i += 1
      End While
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
 MessageBox.Show(strcmd)
End Sub

Private Sub itemToString(ByVal item As ListViewItem, ByRef strcmd As String)
Dim k As Integer = 1
strcmd += "VALUES ("
strcmd += "'" & item.Text & "'"
MessageBox.Show("subitems" + item.SubItems.Count.ToString)
While k < item.SubItems.Count
strcmd += ",'" & item.SubItems(k).Text & "'"
k += 1
End While
strcmd += ")"
End Sub

End Class

Form1.vb

表格1.vb

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim datatransfer As New DataTransfer
Dim con As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\User\Documents\Visual Studio 2010\Projects\WindowsApplication1\WindowsApplication1\bin\Debug\schoolDB.accdb"
Dim cmd As String = "INSERT INTO Stu_Info  (C_ID, ADM_No, S_Name)"
datatransfer.ListToData(con, cmd, ListView1)
End Sub
End Class