Help with my database code!
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
Re: Help with my database code!
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.
Re: Help with my database code!
Code:
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
Re: Help with my database code!
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."
Re: Help with my database code!
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.
