Help with my database code!

[andy_waugh]

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

[Jenner]

Please use CODE tags when posting code so it doesn't loose it's formatting. Edit your post, click the CODE button, and in between the tags that come up, re-post your formatted code block.

Most developers won't even look at your code until you've done at least that much.

[andy_waugh]

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

[Jenner]

I'm sorry, when I said "re-post", I meant:

"Copy your formatted code from the Visual Basic editor and into the CODE tags of your post".

Not:

"Copy your code from your first post which has now lost all it's formatting and thus, would be useless even if placed within CODE tags."

[GaryMazzone]

To tell the truth I'm not going to spend a lot of time trying to figure out where in your code you have an issue.

Tell me where the issue is and cost the code for that section.... Tell me what is worng, the error message or if the information is not correct. If not correct what you think the proper information is.