i have a database created in access showing in multi text-boxes in visual basic 2008 express edition. the database is showing music information and sales (tune name, artist, sales, and year issued) there are a few processes i want to do but can not work out the coding.

1. display the top ten selling tunes in a particular year in order ( the year needs to be selected from a list box and displayed in a list box)

2.display the total sales in a particular year ( year selected from a list box)

3. display the tune with the greatest sales in a particular year ( year selected from a list box)

help will be much appreciated
andy


My code so far -

Public Class Form1
Dim sqlstring As String
Dim connectionstring As String
Dim dataadapter As OleDb.OleDbDataAdapter
Dim mytable As New DataTable
Dim row As Integer
Dim ID, Tunename, artist, sales, yearissued As String


Private Sub load_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles load.Click
Timer1.Enabled = True
Timer1.Interval = 1
ProgressBar1.Value = 0
ProgressBar1.Maximum = 10
ProgressBar1.Minimum = 0
ProgressBar1.Enabled = True

connectionstring = "PROVIDER = microsoft.jet.OLEDB.4.0;" & "Data source = assignment.mdb"
sqlstring = "SELECT * FROM assignment"
dataadapter = New OleDb.OleDbDataAdapter(sqlstring, connectionstring)
dataadapter.Fill(mytable)
End Sub

Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
If ProgressBar1.Value >= 10 Then
ProgressBar1.Enabled = False
Else
ProgressBar1.Value = ProgressBar1.Value + 1
End If
End Sub

Private Sub BtnShow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnShow.Click
row = 0
displayintextboxes(row)


End Sub


Private Sub displayintextboxes(ByVal row As Integer)
reloadedatatable()

TxtID.Text = CStr(mytable.Rows(row)(0))
Txttunename.Text = CStr(mytable.Rows(row)(1))
txtartist.Text = CStr(mytable.Rows(row)(2))
Txtsales.Text = CStr(mytable.Rows(row)(3))
txtyearissued.Text = CStr(mytable.Rows(row)(4))
End Sub

Private Sub reloadedatatable()
mytable.Clear()
dataadapter.Fill(mytable)

End Sub

Private Sub Btnfirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btnfirst.Click
row = 0
displayintextboxes(row)
End Sub

Private Sub Btnlast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btnlast.Click
row = mytable.Rows.Count - 1
displayintextboxes(row)
End Sub

Private Sub Btnback_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btnback.Click
If row > 0 Then
row = row - 1
End If
displayintextboxes(row)
End Sub

Private Sub Btnforward_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btnforward.Click
If row < mytable.Rows.Count - 1 Then
row = row + 1
End If
displayintextboxes(row)
End Sub

Private Sub Btncount_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btncount.Click
MessageBox.Show(mytable.Rows.Count & " records")

End Sub

Private Sub Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Save.Click
mytable.Rows(row)(0) = CInt(TxtID.Text)
mytable.Rows(row)(1) = Txttunename.Text
mytable.Rows(row)(2) = txtartist.Text
mytable.Rows(row)(3) = CInt(Txtsales.Text)
mytable.Rows(row)(4) = CInt(txtyearissued.Text)

updatedatabase()
reloaddatatable()
End Sub

Private Sub updatedatabase()
Dim dataadapter1 As New OleDb.OleDbDataAdapter(sqlstring, connectionstring)
Dim cb As New OleDb.OleDbCommandBuilder(dataadapter1)
dataadapter1.Update(mytable)
End Sub

Private Sub reloaddatatable()
mytable.Clear()
dataadapter.Fill(mytable)
End Sub

Private Sub DeleteData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteData.Click
row = CInt(InputBox("delete which row?"))
If row < mytable.Rows.Count Then
mytable.Rows(ID).Delete()
updatedatabase()
reloaddatatable()
End If
End Sub

Private Sub Newdata_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Newdata.Click
getdata()
Dim row As Integer
row = mytable.Rows.Count

mytable.Rows.Add()

mytable.Rows(row)(0) = ID
mytable.Rows(row)(1) = Tunename
mytable.Rows(row)(2) = artist
mytable.Rows(row)(3) = sales
mytable.Rows(row)(4) = yearissued

updatedatabase()
reloaddatatable()
End Sub

Private Sub getdata()
ID = InputBox("Type in the ID")
Tunename = InputBox("Type in the TuneName")
artist = InputBox("Type in the Artist")
sales = InputBox("Type in the Sales")
yearissued = InputBox("Type in the YearIssued")
End Sub

Private Sub Btnsum_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btnsum.Click

Dim frm As Form2 = New Form2

Dim dr As DialogResult = frm.ShowDialog()

If dr = Windows.Forms.DialogResult.OK Then

MessageBox.Show(frm.getSelectedValue())

End If

End Sub

Private Sub Exitdatabase_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Exitdatabase.Click
End
End Sub


End Class