i have a code. i just want to save listview data to database. the title is the error that pops out

here is my code

Public Sub ConnectDatabase()


        conn.ConnectionString = "SERVER=localhost;User id=root;database=test"
        Try
            conn.Open()
            MessageBox.Show("Connected")
            With mycommand
                .CommandText = "Select * from wer"
                '.ExecuteNonQuery()
            End With
        Catch ex As Exception
            MessageBox.Show("Error: " & ex.Message)
        Finally
            conn.Dispose()

        End Try
    End Sub

that is declared in my module

and here is the code for my save button

Dim iCount As Integer
        Dim iLoop As Integer
        Dim query3 As New MySqlCommand
        Dim lvitem 
        ConnectDatabase()
        conn.Open()
        iCount = lvLogs.Items.Count
        If Not lvLogs.Items.Count = 0 Then
            Do Until iLoop = lvLogs.Items.Count
                lvitem = lvLogs.Items.Item(iLoop)
                With lvitem
                    .Connection = conn
                    .CommandText = "insert into wer values ('" & .SubItems(0).Text & "','" & .SubItems(1).Text & "','" & .SubItems(2).Text & "','" & .SubItems(3).Text & "','" & .SubItems(4).Text & "','" & .SubItems(5) & "')"
                    .ExecuteNonQuery()
                End With
                iLoop = iLoop + 1
                lvitem = Nothing
                MessageBox.Show("Record added!")
            Loop

        End If

kindly help me

Is the error coming from the ConnectDatabase() method? I wonder that because you don't pass the method the connection object and it isn't declared in the method. The method maybe in the same class as the connection object though, I can't tell.

You can always debug your code and find where the error is coming from.

Hi sigridish, the error occured because you did not add the MYSQL Connector in your reference.

To do that, first download a copy of the MYSQL Connector for .NET/6.4.4(is the current version), install it in your system...
You can download it here http://dev.mysql.com/downloads/connector/net/

- After installing the connector, in the menu, click on projects > Add Referrence.
- Then click on browse, then browse to C:\Program Files\MYSQL\Connector\Assembly\v2.0 or similar then choose Mysql.data.dll file then click ok...

Then in the code where your ConnectDatabase() sub is located, type "Imports Mysql.Data.MysqlClient"

It should look like this

Imports Mysql.Data.MysqlClient
Public Class Form1
    Public Sub ConnectDatabase()

        conn.ConnectionString = "SERVER=localhost;User id=root;database=test"
        Try
            conn.Open()
            MessageBox.Show("Connected")
            With mycommand
                .CommandText = "Select * from wer"
                '.ExecuteNonQuery()
            End With
        Catch ex As Exception
            MessageBox.Show("Error: " & ex.Message)
        Finally
            conn.Dispose()

        End Try
    End Sub
End Class

Edited 5 Years Ago by dashawk: n/a

Ahhh i forgot... try this one..

Imports Mysql.Data.MysqlClient
Public Class Form1

    'Add this line to fix the error
    '-----------------------------------------
    Public conn as New MysqlConnection
    '-----------------------------------------
    Public Sub ConnectDatabase()

        conn.ConnectionString = "SERVER=localhost;User id=root;database=test"
        Try
            conn.Open()
            MessageBox.Show("Connected")
            With mycommand
                .CommandText = "Select * from wer"
                '.ExecuteNonQuery()
            End With
        Catch ex As Exception
            MessageBox.Show("Error: " & ex.Message)
        Finally
            conn.Dispose()

        End Try
    End Sub
End Class

Edited 5 Years Ago by dashawk: n/a

the same error still occur. :( i have been doing this for a week and i can't solve it.

Hmmm that is odd, it worked in my end

This code solves the problem

Public conn as New MysqlConnection

This code creates a new instance of the connection of the object(the object that I mean is your conn)

ok, just revise your code like this

Public Sub ConnectDatabase()
        dim conn as New MysqlConnection("SERVER=localhost;User id=root;database=test")
        dim mycommand as New MysqlCommand
        Try
            conn.Open()
            MessageBox.Show("Connected")
            With mycommand
                .CommandText = "Select * from wer"
                '.ExecuteNonQuery()
            End With
        Catch ex As Exception
            MessageBox.Show("Error: " & ex.Message)
        Finally
            conn.Dispose()

        End Try
    End Sub

Remember to put "New" when declaring mysql objects to prevent instance errors...


Thanks

thanks. the error is gone but the record in not added in my database

here is the code for my save button

Dim iCount As Integer
        Dim iLoop As Integer
        Dim query3 As New MySqlCommand
        Dim lvitem 
        ConnectDatabase()
        'conn.Open()
        iCount = lvLogs.Items.Count
        If Not lvLogs.Items.Count = 0 Then
            Do Until iLoop = lvLogs.Items.Count
                lvitem = lvLogs.Items.Item(iLoop)
                With lvitem
                    .Connection = conn
                    .CommandText = "insert into wer values ('" & .SubItems(0).Text & "','" & .SubItems(1).Text & "','" & .SubItems(2).Text & "','" & .SubItems(3).Text & "','" & .SubItems(4).Text & "','" & .SubItems(5) & "')"
                    .ExecuteNonQuery()
                End With
                iLoop = iLoop + 1
                lvitem = Nothing
                MessageBox.Show("Record added!")
            Loop

        End If

Connection object, thats what I said back at the start;)
You don't have .text added to SubItems(5) like you do the others and, of course, you have conn.Open commented out...
What is in lvLogs exactly. Because are treating the individual items as if they are command objects.

Connection object, thats what I said back at the start;)
You don't have .text added to SubItems(5) like you do the others and, of course, you have conn.Open commented out...
What is in lvLogs exactly. Because are treating the individual items as if they are command objects.

lvlogs is the name of my listview

Maybe the lvitem that you declared did not get the values of the selected row in your lvlogs listview....

try this code

Dim  i as Integer
Dim query3 as New MysqlCommand
ConnectDatabase()

With query3
   .Connection = conn
   for i = 0 to lvlogs.items.count -1
      .CommandText = "INSERT INTO wer() VALUES('" & lvLogs.SelectedIndex(i).Items.SubItems(0).Text & "','" & lvLogs.SelectedIndex(i).Items.SubItems(1).Text & "','" & lvLogs.SelectedIndex(i).Items.SubItems(2).Text & "','" & lvLogs.SelectedIndex(i).Items.SubItems(3).Text & "','" & lvLogs.SelectedIndex(i).Items.SubItems(4).Text & "','" & lvLogs.SelectedIndex(i).Items.SubItems(5) & "')"
   Next
   .ExecuteNoneQuery()
End With

MsgBox("Record added!")

Value of type 'System.Data.OleDb.OleDbConnection' cannot be converted to 'MySql.Data.MySqlClient.MySqlConnection'.

'SelectedIndex' is not a member of 'System.Windows.Forms.ListView'.

'ExecuteNoneQuery' is not a member of 'MySql.Data.MySqlClient.MySqlCommand'.


these errors appeared when i use the code that you gave Mr. Dashawk :(

Dim query3
        Dim lvitem
        Dim iCount As Integer
        Dim iLoop As Integer
        query3 = New SqlCommand
        query3.Connection = New SqlConnection("SERVER=localhost;User id=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 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!")

i have here a new code. the record does not save in my database. please help me

Is this code after you open the connection because I can't even see a connection open or close here.

This article has been dead for over six months. Start a new discussion instead.