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:
Public Class DailyExpenses Dim connection As New SqlClient.SqlConnection Dim cmd As New SqlClient.SqlCommand Dim cmd1, cmd2, cmd3, cmd4, cmd5, cmd6, cmd7, cmd8, cmd9 As New SqlClient.SqlCommand Dim da As New SqlClient.SqlDataAdapter Dim ds As New DataSet Dim i As Integer = 0 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load If connection.State <> ConnectionState.Open Then connection.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\PersonalExpenses.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True" connection.Open() End If Call Fill_Template() End Sub #Region "Fill Data" Sub Fill_Template() 'Expenses cmd.CommandText = "SELECT Date,Allowance,Total,Wallet FROM tblExpenses" cmd.Connection = connection da.SelectCommand = cmd da.Fill(ds, "tblExpenses") If (ds.Tables("tblExpenses").Rows.Count > 0) Then DateTimePicker1.Value = ds.Tables("tblExpenses").Rows(i)(0).ToString txtAllowance.Text = ds.Tables("tblExpenses").Rows(i)(1).ToString txtTotalUsage.Text = ds.Tables("tblExpenses").Rows(i)(2).ToString txtWalletCash.Text = ds.Tables("tblExpenses").Rows(i)(3).ToString End If 'Food&Beverage cmd1.CommandText = "SELECT tblFoodAndBeverage.Breakfast,tblFoodAndBeverage.Lunch,tblFoodAndBeverage.Dinner," cmd1.CommandText += "tblFoodAndBeverage.Supper,tblFoodAndBeverage.Snack,tblFoodAndBeverage.Beverage," cmd1.CommandText += "Grocerries,Sub_Total FROM tblFoodAndBeverage,tblExpenses WHERE tblFoodAndBeverage.Food_And_BeverageID = tblExpenses.Food_And_Beverage" cmd1.Connection = connection da.SelectCommand = cmd1 da.Fill(ds, "tblFoodAndBeverage") If (ds.Tables("tblFoodAndBeverage").Rows.Count > 0) Then txtBreakFeast.Text = ds.Tables("tblFoodAndBeverage").Rows(i)(0).ToString txtLunch.Text = ds.Tables("tblFoodAndBeverage").Rows(i)(1).ToString txtDinner.Text = ds.Tables("tblFoodAndBeverage").Rows(i)(2).ToString txtSupper.Text = ds.Tables("tblFoodAndBeverage").Rows(i)(3).ToString txtSnack.Text = ds.Tables("tblFoodAndBeverage").Rows(i)(4).ToString txtBeverage.Text = ds.Tables("tblFoodAndBeverage").Rows(i)(5).ToString txtGroceries.Text = ds.Tables("tblFoodAndBeverage").Rows(i)(6).ToString txtSubFnB.Text = ds.Tables("tblFoodAndBeverage").Rows(i)(7).ToString End If End Sub #End Region #Region "Data_Fill" #End Region Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click If (i <= 0) Then Else i = 0 Call Fill_Template() End If End Sub Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click If (i <= 0) Then Else i = i - 1 Call Fill_Template() End If End Sub Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click Dim RowCount, RowCount1 As String RowCount = ds.Tables("tblExpenses").Rows.Count - 1 RowCount1 = ds.Tables("tblFoodAndBeverage").Rows.Count - 1 If (i = RowCount) And (i = RowCount1) Then Else i = i + 1 Call Fill_Template() End If End Sub Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click Dim RowCount, RowCount1 As String RowCount = ds.Tables("tblExpenses").Rows.Count - 1 RowCount1 = ds.Tables("tblFoodAndBeverage").Rows.Count - 1 If (i = RowCount) And (i = RowCount1) Then Else i = ds.Tables("tblExpenses").Rows.Count i = ds.Tables("tblFoodAndBeverage").Rows.Count Call Fill_Template() End If End Sub End Class
- Add new comment
- 7 views
In your WHERE Clause apply
WHERE tblFoodAndBeverage.Food_And_BeverageID = " & Me.Food_And_Beverage.Text
I understand some of the
Sir kindly send your project,
Sir kindly send your project, here's my email: [email protected], maybe i can help you...
Need help with other things
Can I comment after?
Please send your project to
Add new comment