Loading

Display multiple table data with the correct relationship+Navigation Button

Submitted by: 
garyu87
Saturday, August 18, 2012 - 23:51


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


admin's picture

Submitted by admin on Sun, 08/19/2012 - 07:47

In your WHERE Clause apply the value of Food_And_Beverage from tblExpenses

Example: WHERE tblFoodAndBeverage.Food_And_BeverageID = " & Me.Food_And_Beverage.Text

Submitted by garyu87 on Sun, 08/19/2012 - 12:45

I understand some of the logics but I don't know how to apply it to my code.

In future I'll add the insert and update query to each of the tables or single line. So, the insert query will automatic add the ID number then the tblExpenses.Food is Null number but the tblFoodAndBeverage will have its own ID increased.(I may be wrong here. So please correct me.)

Submitted by Anonymous on Mon, 08/27/2012 - 20:23

Sir kindly send your project, here's my email: harvey_quijano@rocketmail.com, maybe i can help you...

Submitted by garyu87 on Thu, 09/06/2012 - 22:03

Hi, thanks for the kind way of help.
I have already sent you my program please check and sorry to ask additional things.

Submitted by Anonymous on Wed, 09/26/2012 - 15:41

Can I comment after?

Submitted by Anonymous on Sat, 10/06/2012 - 21:30

Please send your project to me also, here's my email: tanweer_q@yahoo.com, Thanks

Add new comment

More information about text formats

Filtered HTML

  • You may insert videos with [video:URL]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.