help anyone! i can add records to mysql database but it won't appear in the listview.
can you check out what's wrong with my code. pleeaaasssee.. help needed badly.. :(

Imports System
Imports System.Windows.Forms
Imports MySql.Data.MySqlClient

Public Class bookForm

    Dim myconn As New MySqlConnection
    Dim sqlQry As String = Nothing
    Dim mycmd As New MySqlCommand
    Dim dReader As MySqlDataReader
    'Dim dAdap As New MySqlDataAdapter
    'Dim dSet As New DataSet

    Dim connString As String
    Dim str As String = Nothing

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

        Call FillListview()
        Call LVsettings()
        Call ConnToDB()
        Call FillListview()
        Call Set1()

    End Sub
#Region "FillListView"

    Sub FillListview()
        lvList.Items.Clear()
        sqlQry = "SELECT * from book ORDER BY book_title ASC"
        Dim myconn As New MySqlConnection
        mycmd = New MySqlCommand(sqlQry, myconn)
        Try
            myconn.Open()
            dReader = mycmd.ExecuteReader

            While dReader.Read
                With lvList
                    .Items.Add(dReader("book_id"))
                    With .Items(.Items.Count - 1).SubItems
                        .Add(dReader("book_title"))
                        .Add(dReader("book_author"))
                        .Add(dReader("book_publisher"))
                        .Add(dReader("isbn"))
                        .Add(dReader("call_no"))
                    End With
                End With
            End While
            dReader.Close()
        Catch ex As Exception
            myconn.Close()
        End Try
    End Sub
#End Region

#Region "Connection"
    Sub ConnToDB()
        Try
            With myConn
                If .State = ConnectionState.Open Then .Close()
                .ConnectionString = "datasource=localhost; username=root; password=; database=admin"
                .Open()
            End With
        Catch ex As Exception
            MessageBox.Show("Unable to connect", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Application.Exit()
        End Try
    End Sub
#End Region


#Region "LVSettings"
    Sub LVsettings()
        With lvList.Columns
            .Add("book_id", 50, HorizontalAlignment.Left)
            .Add("book_title", 200, HorizontalAlignment.Left)
            .Add("book_author", 200, HorizontalAlignment.Left)
            .Add("isbn", 100, HorizontalAlignment.Left)
            .Add("call_no", 100, HorizontalAlignment.Left)
        End With
    End Sub
#End Region

#Region "object Settings "

    Sub Set1()
        cmdAdd.Enabled = True
        cmdEdit.Enabled = True
        cmddelete.Enabled = True
        cmdExit.Enabled = True

        cmdsave.Enabled = False
        cmdCancel.Enabled = False
        Dim txt As Control
        For Each txt In Me.Controls
            If TypeOf txt Is TextBox Then
                txt.Enabled = False
            End If
        Next

        lvList.Enabled = True
    End Sub

    Sub Set2()
        cmdAdd.Enabled = False
        cmdEdit.Enabled = False
        cmddelete.Enabled = False
        cmdExit.Enabled = False

        cmdsave.Enabled = True
        cmdCancel.Enabled = True

        Dim txt As Control
        For Each txt In Me.Controls
            If TypeOf txt Is TextBox Then
                txt.Enabled = True
            End If
        Next
        lvList.Enabled = False
    End Sub
#End Region

#Region "CLEAR TEXTBOX"
    Sub ClearAlltextBox()
        Dim a As Control
        For Each a In Me.Controls
            If TypeOf a Is TextBox Then
                a.Text = Nothing
            End If
        Next
    End Sub
#End Region



    Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAdd.Click
        str = "add"
        Call Set2()
        Call ClearAlltextBox()
        idtxt.Text = "Computer Generated"
        idtxt.Enabled = False
    End Sub

    Private Sub cmdCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCancel.Click
        ' myConn.Close()
        Me.Close()
        Call Set1()
        Call ClearAlltextBox()

    End Sub

    Private Sub cmdEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdEdit.Click
        If idtxt.Text = "" Then
            MessageBox.Show("Please Select Record to Update", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            str = "edit"
            Call Set2()
            idtxt.Enabled = False
        End If


    End Sub

    Private Sub cmdExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdExit.Click
        Application.Exit()
    End Sub

    Private Sub cmdsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdsave.Click

        If str = "add" Then
            ''''''ADD NEW RECORD'''''''

            If idtxt.Text = "" Or titletxt.Text = "" Or authortxt.Text = "" Or Pubtxt.Text = "" Or isbntxt.Text = "" Or calltxt.Text = "" Then
                MessageBox.Show("All fields Are Required", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Else
                sqlQry = "INSERT INTO book(book_title,book_author,book_publisher,isbn,call_no) "
                sqlQry = sqlQry + "VALUES('" & titletxt.Text & "','" & authortxt.Text & "','" & Pubtxt.Text & "','" & isbntxt.Text & "','" & calltxt.Text & "')"
                MessageBox.Show("Record is Successfully save")
                mycmd = New MySqlCommand
                Try
                    With mycmd
                        .CommandText = sqlQry
                        .Connection = myconn
                        .ExecuteNonQuery()
                    End With
                    Call Set1()
                Catch ex As MySqlException
                    MsgBox(ex.ToString)
                End Try
            End If

        Else
            ''''''''''UPDATE RECORD'''''''
            If titletxt.Text = "" Or authortxt.Text = "" Or Pubtxt.Text = "" Or isbntxt.Text = "" Or calltxt.Text Then
                MessageBox.Show("All fields Are Required", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Else
                sqlQry = "UPDATE book SET "
                sqlQry = sqlQry + " book_title = '" & titletxt.Text & "',"
                sqlQry = sqlQry + " book_author = '" & authortxt.Text & "',"
                sqlQry = sqlQry + " book_publisher = '" & Pubtxt.Text & "',"
                sqlQry = sqlQry + " isbn = '" & isbntxt.Text & "',"
                sqlQry = sqlQry + " call_no = '" & authortxt.Text & "'"
                sqlQry = sqlQry + " WHERE "
                sqlQry = sqlQry + " book_id = " & idtxt.Text

                mycmd = New MySqlCommand(sqlQry, myconn)
                mycmd.ExecuteNonQuery()
                Call Set1()
            End If
        End If

        Call FillListview()
        Call ClearAlltextBox()

    End Sub

    Private Sub cmddelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmddelete.Click
        If MsgBox("Are you sure you want to delete this record?", MsgBoxStyle.Question + MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
            sqlQry = "DELETE FROM book WHERE book_id = " & idtxt.Text
            mycmd = New MySqlCommand(sqlQry, myConn)
            mycmd.ExecuteNonQuery()
        End If
        Call ClearAlltextBox()
        Call FillListview()
    End Sub

    Private Sub lvList_SelectedIndexChanged_1(ByVal sender As System.Object, ByVal e As System.EventArgs)
        If lvList.SelectedItems.Count > 0 Then
            With lvList.SelectedItems(0)
                idtxt.Text = .Text
                titletxt.Text = .SubItems(1).Text
                authortxt.Text = .SubItems(2).Text
                Pubtxt.Text = .SubItems(3).Text
                isbntxt.Text = .SubItems(4).Text
                calltxt.Text = .SubItems(5).Text
            End With
        End If
    End Sub

    Private Sub cmdSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub
End Class

Hi lanitooot. Locate the code:

MessageBox.Show("Record is Successfully save")

Move it from line 179 to line 187

THink it should go after the attempt to ExecuteNonQuery not before.

You may find you no longer receive the message telling you have successfully saved your record.
Do you receive an error message?
If you do, it may be down to the text of your query. Is call_no a text field or a numeric field? If it is numeric, you should remove the single quotes.

thank you for the big help!!.


its a numeric field.. remove single quotes in what line??..all of it?.. because call_no and isbn are numeric fields. what should i do know?.. please help.. i need to aappear it in the listview and it won't.. :(

No, just remove the quotes from the numeric fields - just like you've already done it with the book_id. TRy this:

sqlQry = "UPDATE book SET "
        sqlQry = sqlQry + " book_title = '" & titletxt.Text & "',"
        sqlQry = sqlQry + " book_author = '" & authortxt.Text & "',"
        sqlQry = sqlQry + " book_publisher = '" & Pubtxt.Text & "',"
        sqlQry = sqlQry + " isbn = " & isbntxt.Text & ","
        sqlQry = sqlQry + " call_no = " & authortxt.Text
        sqlQry = sqlQry + " WHERE "
        sqlQry = sqlQry + " book_id = " & idtxt.Text

And when you need a date/time field you would use the hash symbol:

sqlQry = sqlQry + " first_published = #" & pubdate.Text & "#"

AS a rule:
Text use single quotes - 'Harry Potter and the GOblet of Fire'
Date/Time use hash - #22 aug 2010 13:32#
Otherwise use nothing

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