save listview data to database

[sigridish]

hello all! i need help in saving the data inside my listview into my database.
here is my code

Dim lvitem As Object
        Dim iCount As Integer
        Dim iLoop As Integer
        Dim query3 = New SqlCommand
        query3.Connection = New SqlConnection("SERVER=localhost;UID=root;DATABASE=test;")
        iCount = lvLogs.Items.Count()
        If Not lvLogs.Items.Count = 0 Then
            Do Until iLoop = lvLogs.Items.Count
                lvitem = lvLogs.Items.Item(iLoop)
                With LvItem
                    query3.CommandText = "insert into wer(CustomerName,SalesGroup,CustomerType,TypeOfIndustry,RM,SeniorRM) values('" & .SubItems(0).Text & "','" & .SubItems(1).Text & "','" & .SubItems(2).Text & "','" & .SubItems(3).Text & "','" & .SubItems(4).Text & "','" & .SubItems(5).Text & "')"
                    query3.ExecuteNonQuery()
                End With
                iLoop = iLoop + 1
                LvItem = Nothing
            Loop
        End If
        MessageBox.Show("Record Saved!")

message box show up but does not save the data in my database


please help me

PS. IM USING MYSQL QUERY BROWSER 1.1 AND VB.NET 2008

 

[jmcilhinney]

Put the data into a DataTable and save it all in one go with a DataAdapter. In fact, why use a ListView at all? Put the data into a DataTable in the first place and just bind it to a DataGridView.

Retrieving and Saving Data in Databases

 

[jmcilhinney]

Hang on! I just noticed that you say that you're using MySQL. Is that true? If so then it's no wonder it's not working because you're using SqlClient, which is only for SQL Server. If you want to connect to MySQL then you need to use a provider that supports it. The obvious choice is to download and install Connector/Net from the MySQL web site.

 

[sigridish]

i already have a connector. can you please help me sir? i have been doing this for more than a week

 

[jmcilhinney]

So you have downloaded and installed Connector/Net? Have you, by chance, read the documentation that explains how to use it? If not then that would be the logical first step. If you have then exactly what steps have you taken? As I said, you are currently using the Microsoft SqlClient provider, which is for SQL Server only. If you've installed the MySqlClient provider then presumably you want to use it.

 

[sigridish]

 Dim lvitem As Object
        Dim iCount As Integer
        Dim iLoop As Integer
        Dim query3 = New MySqlCommand
        query3.Connection = New MySqlConnection("SERVER=localhost;UID=root;DATABASE=test")

        iCount = lvLogs.Items.Count()
        If Not lvLogs.Items.Count = 0 Then
            Do Until iLoop = lvLogs.Items.Count
                lvitem = lvLogs.Items.Item(iLoop)
                With lvitem
                    query3.CommandText = "insert into wer(CustomerName,SalesGroup,CustomerType,TypeOfIndustry,RM,SeniorRM) values('" & .SubItems(0).Text & "','" & .SubItems(1).Text & "','" & .SubItems(2).Text & "','" & .SubItems(3).Text & "','" & .SubItems(4).Text & "','" & .SubItems(5).Text & "')"
                    query3.ExecuteNonQuery()
                End With
                iLoop = iLoop + 1
                lvitem = Nothing
            Loop
        End If
        MessageBox.Show("Record Saved!")

thats my new code sir. i changed it to mysqlconnection

 

[jmcilhinney]

Would you care to tell us what happened, or is that a secret? In post #2 I told you what I consider to be the best course of action and I also provided you with a link to a page that shows you how to do it. If you're not going to follow that advice and you don't have a good reason for doing so then I can't help you further.

 

[sigridish]

well sir whenever i hit the save button, the message box "record saved" show up but the data is not saved. im only new in vb.net sir. sorry.

 

[sigridish]

Using connection As New SqlConnection("SERVER=localhost;UID=root;DATABASE=test")
            Using command As New SqlCommand("SELECT Quantity, Unit, Name FROM StockItem", connection)
                connection.Open()

                Using reader As SqlDataReader = command.ExecuteReader()
                    While reader.Read()
                        MessageBox.Show("")
                    End While
                End Using
            End Using
        End Using

i copied the code that you made sir. is this a good start sir?

 

[jmcilhinney]

No, not a good start. That thread provides code examples for several common scenarios. The code you have posted there is for a scenario described as "Retrieving multiple records that will be read and discarded". Does that sound anything like what you're trying to do? Look for the scenario that matches what you're trying to do and use that code as a starting point.

 

[sigridish]

how about this one sir

Private Sub InitialiseDataAdapter()
        Dim connection As New SqlConnection("SERVER=localhost;UID=root;DATABASE=test")
        Dim adapter As New SqlDataAdapter("CustomerName,SalesGroup,CustomerType,TypeOfIndustry,RM,SeniorRM FROM team", _
                                              connection)
        Dim table As New DataTable
        Dim insert As New SqlCommand("INSERT INTO team (CustomerName,SalesGroup,CustomerType,TypeOfIndustry,RM,SeniorRM) VALUES (@CustomerName,@SalesGroup,@CustomerType,@TypeOfIndustry,@RM,@SeniorRM)", Me.connection)
 
 
 
 
        insert.Parameters.Add("@CustomerName", SqlDbType.VarChar, 100, "CustomerName")
        insert.Parameters.Add("@SalesGroup", SqlDbType.VarChar, 100, "SalesGroup")
        insert.Parameters.Add("@CustomerType", SqlDbType.VarChar, 100, "CustomerType")
        insert.Parameters.Add("@TypeOfIndustry", SqlDbType.VarChar, 100, "TypeOfIndustry")
        insert.Parameters.Add("@RM", SqlDbType.VarChar, 100, "RM")
        insert.Parameters.Add("@SeniorRM", SqlDbType.VarChar, 100, "SeniorRM")
 
       
 
 
        Me.adapter.InsertCommand = insert
 
 
        Me.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
    End Sub
 
    Private Sub GetData()
        'Retrieve the data.
        Me.adapter.Fill(Me.table)
 
        'The table can be used here to display and edit the data.
        'That will most likely involve data-binding but that is not a data access issue.
    End Sub
 
    Private Sub SaveData()
        'Save the changes.
        Me.adapter.Update(Me.table)
    End Sub

but i have an error there. it says 'adapter' is not a member of 'new.form1'. and 'connection' is not a member of 'new.form1'.

 

[jmcilhinney]

You are still using the wrong scenario. If you're not going to read what's put in front of you then you're going to have problems doing anything. The code you're using there is from the scenario entitled "Retrieving multiple records for display and editing, then saving the changes". Again, that is not what you're doing so why would you think that that is the right code to use? Let me provide you with a clue: post #3 in that thread contains one example and the scenario is entitled "Inserting multiple records into a table". Does that maybe sound like what you're trying to do?

 

[sigridish]

Using connection As New SqlConnection("SERVER=localhost;UID=root;DATABASE=test")
            Using adapter As New SqlDataAdapter("SELECT CustomerName,SalesGroup,CustomerType,TypeOfIndustry,RM,SeniorRM FROM team", connection)
                Dim insert As New SqlCommand("INSERT INTO team (CustomerName,SalesGroup,CustomerType,TypeOfIndustry,RM,SeniorRM) VALUES (@CustomerName,@SalesGroup,@CustomerType,@TypeOfIndustry,@RM,@SeniorRM", connection)

                insert.Parameters.Add("@CustomerName", SqlDbType.VarChar, 100, "CustomerName")
                insert.Parameters.Add("@SalesGroup", SqlDbType.VarChar, 100, "SalesGroup")
                insert.Parameters.Add("@CustomerType", SqlDbType.VarChar, 100, "CustomerType")
                insert.Parameters.Add("@TypeOfIndustry", SqlDbType.VarChar, 100, "TypeOfIndustry")
                insert.Parameters.Add("@RM", SqlDbType.VarChar, 100, "RM")
                insert.Parameters.Add("@SeniorRM", SqlDbType.VarChar, 100, "SeniorRM")




                adapter.InsertCommand = insert
                adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

                Dim table As New DataTable

                'Retrieve the schema.
                adapter.FillSchema(table, SchemaType.Source)

                'Add the new rows to the DataTable, e.g.
                Dim row As DataRow = table.NewRow()

                row("CustomerName") =  [B]someName[/B] 
                 row("SalesGroup") =  [B]someQuantity[/B]
                row("CustomerType") = [B]someUnit[/B]
                row("TypeOfIndustry") = [B]someName[/B]
                row("RM") = [B]someQuantity[/B]
                row("SeniorRM") = [B]someUnit[/B]
                table.Rows.Add(row)

                'Save the changes.
                adapter.Update(table)
            End Using
        End Using

ok sir im sorry. this is the same as Inserting multiple records into a table. What should i replace with those bold text?