1. Forums
  2. Programming
  3. Visual Basic .NET
  4. Display multiple table data with the correct relationship+Navigation Button

Display multiple table data with the correct relationship+Navigation Button

Submitted by garyu87 on
Hi everyone, firstly I have 2 Problems: 1. When I click next it shows the other rows data correctly but (let's say my tblExpenses has 4 rows of data and my tblFoodAndBeverage has 2 rows of data) then when I click next after the 2nd row data the FoodAndBeverage shouldn't display any data because its null after the 2nd row but it displays duplicate data. 2. My table Expenses actually has a column(Food_And_Beverage(int)) that connects to the table FoodAndBeverage(Food_And_BeverageID). My problem is how to retrieve data based on that 2 relationship. For example: tblExpenses(Date(18/8/2012)) has also data on Foods but then (19/8/2012) has its own data in its table but the Foods data is null but still displays other data on that date except for food with blank data. Here's my code:
  1. Public Class DailyExpenses
  2.  
  3.     Dim connection As New SqlClient.SqlConnection
  4.     Dim cmd As New SqlClient.SqlCommand
  5.     Dim cmd1, cmd2, cmd3, cmd4, cmd5, cmd6, cmd7, cmd8, cmd9 As New SqlClient.SqlCommand
  6.     Dim da As New SqlClient.SqlDataAdapter
  7.     Dim ds As New DataSet
  8.     Dim i As Integer = 0
  9.  
  10.  
  11.     Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  12.         If connection.State <> ConnectionState.Open Then
  13.             connection.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\PersonalExpenses.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
  14.             connection.Open()
  15.         End If
  16.  
  17.         Call Fill_Template()
  18.  
  19.  
  20.     End Sub
  21.  
  22. #Region "Fill Data"
  23.     Sub Fill_Template()
  24.  
  25.  
  26.         'Expenses
  27.         cmd.CommandText = "SELECT Date,Allowance,Total,Wallet FROM tblExpenses"
  28.         cmd.Connection = connection
  29.         da.SelectCommand = cmd
  30.         da.Fill(ds, "tblExpenses")
  31.         If (ds.Tables("tblExpenses").Rows.Count > 0) Then
  32.             DateTimePicker1.Value = ds.Tables("tblExpenses").Rows(i)(0).ToString
  33.             txtAllowance.Text = ds.Tables("tblExpenses").Rows(i)(1).ToString
  34.             txtTotalUsage.Text = ds.Tables("tblExpenses").Rows(i)(2).ToString
  35.             txtWalletCash.Text = ds.Tables("tblExpenses").Rows(i)(3).ToString
  36.         End If
  37.  
  38.         'Food&Beverage
  39.         cmd1.CommandText = "SELECT tblFoodAndBeverage.Breakfast,tblFoodAndBeverage.Lunch,tblFoodAndBeverage.Dinner,"
  40.         cmd1.CommandText += "tblFoodAndBeverage.Supper,tblFoodAndBeverage.Snack,tblFoodAndBeverage.Beverage,"
  41.         cmd1.CommandText += "Grocerries,Sub_Total FROM tblFoodAndBeverage,tblExpenses WHERE tblFoodAndBeverage.Food_And_BeverageID = tblExpenses.Food_And_Beverage"
  42.         cmd1.Connection = connection
  43.         da.SelectCommand = cmd1
  44.         da.Fill(ds, "tblFoodAndBeverage")
  45.         If (ds.Tables("tblFoodAndBeverage").Rows.Count > 0) Then
  46.             txtBreakFeast.Text = ds.Tables("tblFoodAndBeverage").Rows(i)(0).ToString
  47.             txtLunch.Text = ds.Tables("tblFoodAndBeverage").Rows(i)(1).ToString
  48.             txtDinner.Text = ds.Tables("tblFoodAndBeverage").Rows(i)(2).ToString
  49.             txtSupper.Text = ds.Tables("tblFoodAndBeverage").Rows(i)(3).ToString
  50.             txtSnack.Text = ds.Tables("tblFoodAndBeverage").Rows(i)(4).ToString
  51.             txtBeverage.Text = ds.Tables("tblFoodAndBeverage").Rows(i)(5).ToString
  52.             txtGroceries.Text = ds.Tables("tblFoodAndBeverage").Rows(i)(6).ToString
  53.             txtSubFnB.Text = ds.Tables("tblFoodAndBeverage").Rows(i)(7).ToString
  54.         End If
  55.  
  56.  
  57.  
  58.     End Sub
  59. #End Region
  60.  
  61. #Region "Data_Fill"
  62.  
  63.  
  64.  
  65.  
  66. #End Region
  67.  
  68.     Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
  69.         If (i <= 0) Then
  70.             MsgBox("You are already at first position.")
  71.         Else
  72.             i = 0
  73.             Call Fill_Template()
  74.         End If
  75.  
  76.     End Sub
  77.  
  78.     Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
  79.         If (i <= 0) Then
  80.             MsgBox("You are already at first position.")
  81.         Else
  82.             i = i - 1
  83.             Call Fill_Template()
  84.         End If
  85.  
  86.  
  87.     End Sub
  88.  
  89.     Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
  90.         Dim RowCount, RowCount1 As String
  91.         RowCount = ds.Tables("tblExpenses").Rows.Count - 1
  92.         RowCount1 = ds.Tables("tblFoodAndBeverage").Rows.Count - 1
  93.  
  94.         If (i = RowCount) And (i = RowCount1) Then
  95.             MsgBox("You are already at Last position.")
  96.         Else
  97.             i = i + 1
  98.             Call Fill_Template()
  99.         End If
  100.  
  101.     End Sub
  102.  
  103.     Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
  104.         Dim RowCount, RowCount1 As String
  105.         RowCount = ds.Tables("tblExpenses").Rows.Count - 1
  106.         RowCount1 = ds.Tables("tblFoodAndBeverage").Rows.Count - 1
  107.  
  108.         If (i = RowCount) And (i = RowCount1) Then
  109.             MsgBox("You are already at Last position.")
  110.         Else
  111.             i = ds.Tables("tblExpenses").Rows.Count
  112.             i = ds.Tables("tblFoodAndBeverage").Rows.Count
  113.             Call Fill_Template()
  114.         End If
  115.     End Sub
  116. End Class