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