1.11M Members

How to save data from ListView to SQL Database

 
0
 

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
:)

Attachments listview.png 4.83KB
 
0
 

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

Unhnd_Exception
Deleted Member
 
2
 
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
 
0
 

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
Unhnd_Exception
Deleted Member
 
0
 

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.

 
0
 

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

Unhnd_Exception
Deleted Member
 
1
 

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.

 
0
 

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
Deleted Member
 
0
 

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

Natural Light is my favorite. Especially right now.

 
-1
 

:D
this will not solve it

where is the code :P

 
0
 

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

You
This article has been dead for over six months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article