How to save data from ListView to SQL Database

Question

el3ashe2 -2 Newbie Poster

13 Years Ago

hello
my first thread to as for something here
but i like daniweb a lot
i'm trying to save data from listview to sql server

Dim iCount As Integer
        Dim iLoop As Integer
        Dim query3 As New SqlCommand
        Dim lvitem
        query3.Connection = New SqlConnection("Password=sasa;Persist Security Info=True;User ID=sa;Initial Catalog=Tel;Data Source=HA-PC")
        query3.Connection.Open()
        ' cn.ConnectionString = "Provider=SQLOLEDB.1;Password=sasa;Persist Security Info=True;User ID=sa;Initial Catalog=CRM0;Data Source=HA-PC"
        iCount = lvLogs.Items.Count
        ' For iLoop = 0 To lvLogs.Items.Count - 1
        If Not lvLogs.Items.Count = 0 Then
            Do Until iLoop = lvLogs.Items.Count

                lvitem = lvLogs.Items.Item(iLoop)
                With lvitem
                    query3.CommandText = "insert into A(a,b,c,d,e,f,g) values " _
                    & "('" & .SubItems(0).Text & "','" & .SubItems(1).Text & "','" & .SubItems(2).Text & "','" & .SubItems(3).Text & "','" & .SubItems(4).Text & "','" & .SubItems(5) & "','" & .SubItems(6).Text & "')"
                    '" & .SubItems(6).Text & "','" & .SubItems(7).Text & "','" & .SubItems(8).Text & "')"
                    query3.ExecuteNonQuery()
                End With

                iLoop = iLoop + 1
                lvitem = Nothing
            Loop

        End If

i dunno why a lot of errors
and i want more info about this listview what is mean of subitem and item and what is .subitem (0)
should i add something??
find attached pic for more info

thanks for help
:)

vb.net

2 Contributors
10 Replies
2K Views
1 Day Discussion Span
Latest Post 13 Years Ago Latest Post by el3ashe2

All 10 Replies

Unhnd_Exception

13 Years Ago

lvLogs.Items.Clear()

        'Subitem 0 is the text of the List View Item

        Dim ListViewItem1 As New ListViewItem("Hello List View Item")

        'The following does the same.
        Dim ListViewItem2 As New ListViewItem
        ListViewItem2.SubItems(0).Text = "Hello List View Item #2"

        lvLogs.Items.Add(ListViewItem1)
        lvLogs.Items.Add(ListViewItem2)

        'You've now created 2 List View Items and added them to the lvlogs.
        MsgBox(ListViewItem1.SubItems(0).Text)
        MsgBox(ListViewItem1.Text)

        MsgBox(ListViewItem2.SubItems(0).Text)
        MsgBox(ListViewItem2.Text)

        'If you want to add subitems to the list view items then do so.
        ListViewItem1.SubItems.Add("SubItem 1")
        ListViewItem1.SubItems.Add("SubItem 2")
        ListViewItem1.SubItems.Add("Subitem 3")
        ListViewItem1.SubItems.Add("SubItem 4")
        ListViewItem1.SubItems.Add("SubItem 5")
        ListViewItem1.SubItems.Add("SubItem 6")

        For i = 1 To 6
            ListViewItem2.SubItems.Add("SubItem " & i)
        Next


        MsgBox("Item #1's subitem #2 = " & ListViewItem1.SubItems(2).Text)
        MsgBox("Item #2's subitem #5 = " & ListViewItem2.SubItems(5).Text)

        MsgBox("lvLogs contains " & lvLogs.Items.Count & " Items.")


        'Now to save them to a table
        Dim con As New SqlConnection("Password=sasa;Persist......")
        Dim cmd As New SqlCommand

        cmd.Connection = con
        cmd.CommandText = "Insert into A" & vbCrLf & _
                          "(a,b,c,d,e,f,g)" & vbCrLf & _
                          "values(@a,@b,@c,@d,@e,@f,@g)"

        cmd.Parameters.Add("@a", SqlDbType.NVarChar)
        cmd.Parameters.Add("@b", SqlDbType.NVarChar)
        cmd.Parameters.Add("@c", SqlDbType.NVarChar)
        cmd.Parameters.Add("@d", SqlDbType.NVarChar)
        cmd.Parameters.Add("@e", SqlDbType.NVarChar)
        cmd.Parameters.Add("@f", SqlDbType.NVarChar)
        cmd.Parameters.Add("@g", SqlDbType.NVarChar)

        Try
            con.Open()

            For Each LI As ListViewItem In lvLogs.Items
                If LI.SubItems.Count < 7 Then Continue For

                For i = 0 To LI.SubItems.Count - 1
                    cmd.Parameters(i).Value = LI.SubItems(0).Text
                Next

                '
                'or
                '
                'cmd.Parameters("@a").Value = LI.SubItems(0).Text
                'cmd.Parameters("@b").Value = LI.SubItems(1).Text
                'cmd.Parameters("@c").Value = LI.SubItems(2).Text
                'cmd.Parameters("@d").Value = LI.SubItems(3).Text
                'cmd.Parameters("@e").Value = LI.SubItems(4).Text
                'cmd.Parameters("@f").Value = LI.SubItems(5).Text
                'cmd.Parameters("@g").Value = LI.SubItems(6).Text

                cmd.ExecuteNonQuery()
            Next

            '
            'or
            '
            'For l = 0 To lvLogs.Items.Count - 1
            '    With lvLogs.Items(l)
            '        For i = 0 To .SubItems.Count - 1
            '            cmd.Parameters(i).Value = .SubItems(0).Text
            '        Next
            '    End With
            '
            '   cmd.ExecuteNonQuery()
            'Next

            con.Close()
            MsgBox("Done")

        Catch ex As Exception
            MsgBox("Error -> " & ex.Message)
        Finally
            con.Dispose()
            cmd.Dispose()
        End Try

Edited 13 Years Ago by Unhnd_Exception because: n/a

el3ashe2 commented: it's good one +0

kvprajapati commented: Very helpful! +11

Unhnd_Exception

13 Years Ago

I would just down vote the post.

Anything I provide you would be misleading and you would A. Get you fired or B. Go Bankrupt.

Unhnd_Exception

13 Years Ago

el3ashe2

I'm more than happy to help you.

I don't deal with datasets much but deal with databases on a daily basis. I can help you with general database questions but not syncronization of in memory tables and databases.

What do you mean by not duplicating.

Reply to this topic

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.

el3ashe2 -2 Newbie Poster · Answer 1 · 2010-12-10T00:02:01+00:00

it works like that

but it's not insert the data that into listview but insert the headers only

it's insert the column name not the data into the column

Dim iCount As Integer
        Dim iLoop As Integer
        Dim query3 As New SqlCommand
        Dim lvitem
        query3.Connection = New SqlConnection("Password=sasa;Persist Security Info=True;User ID=sa;Initial Catalog=Tel;Data Source=HA-PC")
        query3.Connection.Open()
        ' cn.ConnectionString = "Provider=SQLOLEDB.1;Password=sasa;Persist Security Info=True;User ID=sa;Initial Catalog=CRM0;Data Source=HA-PC"
        iCount = lvLogs.Items.Count
        ' For iLoop = 0 To lvLogs.Items.Count - 1
        If Not lvLogs.Items.Count = 0 Then
            Do Until iLoop = lvLogs.Items.Count

                lvitem = lvLogs.Items.Item(iLoop)
                With lvitem
                    query3.CommandText = "insert into A(a,b,c,d,e,f,g) values " _
                    & "('" & lvLogsch1.Text & "','" & lvLogsch2.Text & "','" & lvLogsch3.Text & "','" & lvLogsch4.Text & "','" & lvLogsch5.Text & "','" & lvLogsch6.Text & "','" & lvLogsch7.Text & "')"
                    '" & .SubItems(6).Text & "','" & .SubItems(7).Text & "','" & .SubItems(8).Text & "')"
                    query3.ExecuteNonQuery()
                End With

                iLoop = iLoop + 1
                lvitem = Nothing
            Loop
            MsgBox("done")
        End If

any help to insert the data not the headers

el3ashe2 -2 Newbie Poster · Answer 2 · 2010-12-11T05:50:22+00:00

this works for me what's the different between both of them in results??

and what about to don't duplicate data in table

Dim iCount As Integer
        Dim iLoop As Integer
        Dim query3 As New SqlCommand
        Dim lvitem
        query3.Connection = New SqlConnection("Password=sasa;Persist Security Info=True;User ID=sa;Initial Catalog=Tel;Data Source=HA-PC")
        query3.Connection.Open()

        iCount = lvLogs.Items.Count
        ' For iLoop = 0 To lvLogs.Items.Count - 1
        If Not lvLogs.Items.Count = 0 Then
            Do Until iLoop = lvLogs.Items.Count

                lvitem = lvLogs.Items.Item(iLoop)

                With lvitem

                    query3.CommandText = "insert into A(a,b,c,d,e) values " _
                    & "('" & lvitem.subitems(0).text & "','" & lvitem.subitems(1).text & "','" & lvitem.subitems(2).Text & "','" & lvitem.subitems(3).Text & "','" & lvitem.subitems(4).Text & "')"
                    '" & .SubItems(6).Text & "','" & .SubItems(7).Text & "','" & .SubItems(8).Text & "')"
                    query3.ExecuteNonQuery()
                End With

                iLoop = iLoop + 1
                lvitem = Nothing
            Loop
            MsgBox("done")
        End If

el3ashe2 -2 Newbie Poster · Answer 3 · 2010-12-11T06:10:39+00:00

another comment i test your code
it works great
it is the same as my code "i think"
but what about to don't duplicate data

i dunno how i can use trim with this listview and if trim suitable to this synchronization or not

if it isn't so please post the code to sync data between the listview and the table so i can just update the new data only

thanks a lot

el3ashe2 -2 Newbie Poster · Answer 4 · 2010-12-11T06:35:05+00:00

i mean to don't insert all data in listview just insert the new data

i mean if the list view include some data today and after 1 hour will be more 2 lines to insert

if we use the same code again it will store all data again include the 2 lines

but i want to add the new lines only
is it clear now?

Unhnd_Exception · Answer 5 · 2010-12-11T06:48:41+00:00

I'll hit you up tommorrow If no one else has.

Natural Light is my favorite. Especially right now.

el3ashe2 -2 Newbie Poster · Answer 6 · 2010-12-11T06:52:46+00:00

el3ashe2 -2 Newbie Poster

13 Years Ago

:D
this will not solve it

where is the code :P

kvprajapati commented: Please learn something from the post of @unhnd_exception -2

el3ashe2 -2 Newbie Poster · Answer 7 · 2010-12-11T17:27:42+00:00

any reply??

i need to insert new data that's not inserted yet to database and data not duplicated

i Won't to duplicate data that's it

thanks

How to save data from ListView to SQL Database

Recommended Answers Collapse Answers

All 10 Replies

Recommended Answers