Imports System.Data.SQLite
Public Class Form1
Dim con As New SQLiteConnection("Data Source=DB.s3db")
'Dim con As New SQLiteConnection(String.Format("Data Source= {0}\DB.s3db", Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)))
Dim da As New SQLiteDataAdapter("SELECT * from [DBD] ", con)
Dim dt As New DataTable
Dim page As Integer = 1
Dim totalpages As Integer = 0
Dim perpage As Integer = 15
Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
lst1.FullRowSelect = True
Me.Label1.Text = "0 / 0"
fill()
End Sub
Sub fill()
lst1.Items.Clear()
dt.Clear()
If da.Fill(dt) > 0 Then
totalpages = Math.Ceiling(dt.Rows.Count / perpage)
viewpage(page)
End If
End Sub
Private Sub lst1_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles lst1.SelectedIndexChanged
If lst1.SelectedItems.Count = 0 Then Return
Dim itm As ListViewItem = lst1.SelectedItems(0)
TextBox1.Text = itm.SubItems(0).Text
TextBox2.Text = itm.SubItems(1).Text
TextBox3.Text = itm.SubItems(2).Text
TextBox4.Text = itm.SubItems(3).Text
TextBox5.Text = itm.SubItems(4).Text
TextBox6.Text = itm.SubItems(5).Text
TextBox7.Text = itm.SubItems(6).Text
TextBox8.Text = itm.SubItems(7).Text
TextBox9.Text = itm.SubItems(8).Text
TextBox10.Text = itm.SubItems(9).Text
TextBox11.Text = itm.SubItems(10).Text
TextBox12.Text = itm.SubItems(11).Text
TextBox13.Text = itm.SubItems(12).Text
End Sub
Private Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
nextpage()
End Sub
Private Sub Button2_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button2.Click
previouspage()
End Sub
Private Sub Button3_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button3.Click
lastspage()
End Sub
Private Sub Button4_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button4.Click
firstspage()
End Sub
Private Sub firstspage()
page = 1
viewpage(page)
End Sub
Private Sub lastspage()
page = totalpages
viewpage(page)
End Sub
Private Sub previouspage()
If page > 1 Then
page -= 1
viewpage(page)
End If
End Sub
Private Sub nextpage()
If page < totalpages Then
page += 1
viewpage(page)
End If
End Sub
Private Sub viewpage(ByVal p As Integer)
Me.Label1.Text = "0 / 0"
If totalpages = 0 Then Return
If p > totalpages Then
p = totalpages
page = p
End If
Me.Label1.Text = page & " / " & totalpages
p -= 1
lst1.Items.Clear()
For i = (p * perpage) To (p * perpage) + perpage - 1
If i = dt.Rows.Count Then Exit Sub
Dim r As DataRow = dt.Rows(i)
lst1.Items.Add(New ListViewItem(
{r("id"),
r("CALLop"),
r("MOHT"),
r("DAY"),
r("UTC"),
r("MHz"),
r("MODE"),
r("sent_number"),
r("rcvd_number"),
r("multi1"),
r("multi2"),
r("point"),
r("country")
}))
Next
End Sub
Private Sub Button5_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button5.Click
If Val(TextBox1.Text) = 0 Then Return
Dim cmd As New SQLiteCommand(
"UPDATE [DBD] " & _
"SET [CALLop]=@CALLop, " & _
" [MOHT]=@MOHT, " & _
" [DAY]=@DAY, " & _
" [UTC]=@UTC, " & _
" [MHz]=@MHz, " & _
" [MODE]=@MODE, " & _
" [sent_number]=@sent_number, " & _
" [rcvd_number]=@rcvd_number, " & _
" [multi1]=@multi1, " & _
" [multi2]=@multi2, " & _
" [point]=@point, " & _
" [country]=@country " & _
"WHERE [id]=@id ", con)
cmd.Parameters.AddWithValue("@CALLop", TextBox2.Text)
cmd.Parameters.AddWithValue("@MOHT", TextBox3.Text)
cmd.Parameters.AddWithValue("@DAY", TextBox4.Text)
cmd.Parameters.AddWithValue("@UTC", TextBox5.Text)
cmd.Parameters.AddWithValue("@MHz", TextBox6.Text)
cmd.Parameters.AddWithValue("@MODE", TextBox7.Text)
cmd.Parameters.AddWithValue("@sent_number", TextBox8.Text)
cmd.Parameters.AddWithValue("@rcvd_number", TextBox9.Text)
cmd.Parameters.AddWithValue("@multi1", TextBox10.Text)
cmd.Parameters.AddWithValue("@multi2", TextBox11.Text)
cmd.Parameters.AddWithValue("@point", TextBox12.Text)
cmd.Parameters.AddWithValue("@country", TextBox13.Text)
cmd.Parameters.AddWithValue("@id", Val(TextBox1.Text))
If con.State <> ConnectionState.Open Then con.Open()
Dim ret As Integer = cmd.ExecuteNonQuery()
con.Close()
If ret > 0 Then
fill()
MsgBox("Updated")
clear()
End If
End Sub
Private Sub Button6_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button6.Click
If Val(TextBox1.Text) = 0 Then Return
If MsgBox("Delete?", MsgBoxStyle.YesNo) <> vbYes Then Return
Dim cmd As New SQLiteCommand("DELETE FROM DBD WHERE [id]=@id", con)
cmd.Parameters.AddWithValue("@id", Val(TextBox1.Text))
If con.State <> ConnectionState.Open Then con.Open()
Dim ret As Integer = cmd.ExecuteNonQuery()
con.Close()
If ret > 0 Then
fill()
MsgBox("Deleted")
clear()
End If
End Sub
Sub clear()
Dim tbs() As TextBox = {TextBox1, TextBox2, TextBox3, TextBox4, TextBox5, TextBox6, TextBox7, TextBox8, TextBox9, TextBox10, TextBox11, TextBox12, TextBox13}
For Each tb As TextBox In tbs
tb.Clear()
Next
End Sub
End Class
