Hi everyone,
I am now having problem with the First,Previous,Next,Last button to retrieve data.
> Public Class DailyExpenses
>
> Dim connection As New SqlClient.SqlConnection
> Dim cmd 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_Expenses()
> Call Fill_Template_Food()
> Call Fill_Template_Housing()
> Call Fill_Template_Transportation()
> Call Fill_Template_Entertainment()
> Call Fill_Template_PersonalCareAndItems()
> Call Fill_Template_Loan()
> Call Fill_Template_Legal()
> Call Fill_Template_PaymentsAndOthers()
> Call Fill_Template_Banking()
>
>
> End Sub
>
> #Region "Fill Data Expenses"
> Sub Fill_Template_Expenses()
>
> cmd.CommandText = "SELECT Date,Allowance,Total,Wallet FROM tblExpenses"
> cmd.Connection = connection
> da.SelectCommand = cmd
> da.Fill(ds, "tblExpenses")
>
>
> DateTimePicker1.Value = Convert.ToDateTime(ds.Tables("tblExpenses").Rows(i).Item(0))
> txtAllowance.Text = ds.Tables("tblExpenses").Rows(i).Item(10)
> txtTotalUsage.Text = ds.Tables("tblExpenses").Rows(i).Item(11)
> txtWalletCash.Text = ds.Tables("tblExpenses").Rows(i).Item(12)
>
>
> End Sub
> #End Region
>
> #Region "Fill Data Food"
> Sub Fill_Template_Food()
>
> cmd.CommandText = "SELECT Breakfast,Lunch,Dinner,Supper,Snack,Beverage,Grocerries,Sub_Total FROM tblFoodAndBeverage"
> cmd.Connection = connection
> da.SelectCommand = cmd
> da.Fill(ds, "tblFoodAndBeverage")
>
>
> txtBreakFeast.Text = ds.Tables("tblFoodAndBeverage").Rows(i).Item(0)
> txtLunch.Text = ds.Tables("tblFoodAndBeverage").Rows(i).Item(1)
> txtDinner.Text = ds.Tables("tblFoodAndBeverage").Rows(i).Item(2)
> txtSupper.Text = ds.Tables("tblFoodAndBeverage").Rows(i).Item(3)
> txtSnack.Text = ds.Tables("tblFoodAndBeverage").Rows(i).Item(4)
> txtBeverage.Text = ds.Tables("tblFoodAndBeverage").Rows(i).Item(5)
> txtGroceries.Text = ds.Tables("tblFoodAndBeverage").Rows(i).Item(6)
> txtSubFnB.Text = ds.Tables("tblFoodAndBeverage").Rows(i).Item(7)
>
> End Sub
> #End Region
>
> #Region "Fill Data Housing"
> Sub Fill_Template_Housing()
>
> cmd.CommandText = "SELECT Rental,Electricity,Water,Gas,Supplies,Other,Sub_Total FROM tblHousing"
> cmd.Connection = connection
> da.SelectCommand = cmd
> da.Fill(ds, "tblHousing")
>
>
> txtRental.Text = ds.Tables("tblHousing").Rows(i).Item(0)
> txtElectricity.Text = ds.Tables("tblHousing").Rows(i).Item(1)
> txtWater.Text = ds.Tables("tblHousing").Rows(i).Item(2)
> txtGas.Text = ds.Tables("tblHousing").Rows(i).Item(3)
> txtSupplies.Text = ds.Tables("tblHousing").Rows(i).Item(4)
> txtHousingOthers.Text = ds.Tables("tblHousing").Rows(i).Item(5)
> txtSubHousing.Text = ds.Tables("tblHousing").Rows(i).Item(6)
>
>
> End Sub
> #End Region
>
> #Region "Fill Data Transportation"
> Sub Fill_Template_Transportation()
>
> cmd.CommandText = "SELECT Bus_Fare,Taxi_Fare,Train_Fare,Fuel,Maintainence,Others,Sub_Total FROM tblTransportation"
> cmd.Connection = connection
> da.SelectCommand = cmd
> da.Fill(ds, "tblTransportation")
>
>
> txtBusFare.Text = ds.Tables("tblTransportation").Rows(i).Item(0)
> txtTaxiFare.Text = ds.Tables("tblTransportation").Rows(i).Item(1)
> txtTrainFare.Text = ds.Tables("tblTransportation").Rows(i).Item(2)
> txtFuel.Text = ds.Tables("tblTransportation").Rows(i).Item(3)
> txtMaintainence.Text = ds.Tables("tblTransportation").Rows(i).Item(4)
> txtTansportOthers.Text = ds.Tables("tblTransportation").Rows(i).Item(5)
> txtSubTransport.Text = ds.Tables("tblTransportation").Rows(i).Item(6)
>
>
> End Sub
> #End Region
>
> #Region "Fill Data Entertainment"
> Sub Fill_Template_Entertainment()
>
> cmd.CommandText = "SELECT Video_Rental,Cinema,Concert,Sport,Games,Club_Bar_Disco, Others, Sub_Total FROM tblEntertainment"
> cmd.Connection = connection
> da.SelectCommand = cmd
> da.Fill(ds, "tblEntertainment")
>
>
> txtVideo.Text = ds.Tables("tblEntertainment").Rows(i).Item(0)
> txtCinema.Text = ds.Tables("tblEntertainment").Rows(i).Item(1)
> txtConcert.Text = ds.Tables("tblEntertainment").Rows(i).Item(2)
> txtSport.Text = ds.Tables("tblEntertainment").Rows(i).Item(3)
> txtGames.Text = ds.Tables("tblEntertainment").Rows(i).Item(4)
> txtClubBarDisco.Text = ds.Tables("tblEntertainment").Rows(i).Item(5)
> txtEntertainmentOthers.Text = ds.Tables("tblEntertainment").Rows(i).Item(6)
> txtSubEntertainment.Text = ds.Tables("tblEntertainment").Rows(i).Item(7)
>
> End Sub
> #End Region
>
> #Region "Fill Data PersonalCare&Items"
> Sub Fill_Template_PersonalCareAndItems()
>
> cmd.CommandText = "SELECT Medical_Fees,Hair_Or_Nail,Clothing,Supplements,Soap_Or_Shampoo,Facial_Cleanser, Others, Sub_Total FROM tblPersonalCareAndItems"
> cmd.Connection = connection
> da.SelectCommand = cmd
> da.Fill(ds, "tblPersonalCareAndItems")
>
>
> txtMedical.Text = ds.Tables("tblPersonalCareAndItems").Rows(i).Item(0)
> txtHairNail.Text = ds.Tables("tblPersonalCareAndItems").Rows(i).Item(1)
> txtClothing.Text = ds.Tables("tblPersonalCareAndItems").Rows(i).Item(2)
> txtSupplement.Text = ds.Tables("tblPersonalCareAndItems").Rows(i).Item(3)
> txtSoapShampoo.Text = ds.Tables("tblPersonalCareAndItems").Rows(i).Item(4)
> txtFacial.Text = ds.Tables("tblPersonalCareAndItems").Rows(i).Item(5)
> txtPCnIOthers.Text = ds.Tables("tblPersonalCareAndItems").Rows(i).Item(6)
> txtSubPCnI.Text = ds.Tables("tblPersonalCareAndItems").Rows(i).Item(7)
>
> End Sub
> #End Region
>
> #Region "Fill Data Loan"
> Sub Fill_Template_Loan()
>
> cmd.CommandText = "SELECT Personal,Student,Business,Mortgage, Others, Sub_Total FROM tblLoan"
> cmd.Connection = connection
> da.SelectCommand = cmd
> da.Fill(ds, "tblLoan")
>
>
> txtPersonal.Text = ds.Tables("tblLoan").Rows(i).Item(0)
> txtStudent.Text = ds.Tables("tblLoan").Rows(i).Item(1)
> txtBusiness.Text = ds.Tables("tblLoan").Rows(i).Item(2)
> txtMortgage.Text = ds.Tables("tblLoan").Rows(i).Item(3)
> txtLoanOthers.Text = ds.Tables("tblLoan").Rows(i).Item(4)
> txtSubLoan.Text = ds.Tables("tblLoan").Rows(i).Item(5)
>
> End Sub
> #End Region
>
> #Region "Fill Data Legal"
> Sub Fill_Template_Legal()
>
> cmd.CommandText = "SELECT Attorney,Alimony,Summons,Others, Sub_Total FROM tblLegal"
> cmd.Connection = connection
> da.SelectCommand = cmd
> da.Fill(ds, "tblLegal")
>
>
> txtAttorney.Text = ds.Tables("tblLegal").Rows(i).Item(0)
> txtAlimony.Text = ds.Tables("tblLegal").Rows(i).Item(1)
> txtSummon.Text = ds.Tables("tblLegal").Rows(i).Item(2)
> txtLegalOthers.Text = ds.Tables("tblLegal").Rows(i).Item(4)
> txtSubLegal.Text = ds.Tables("tblLegal").Rows(i).Item(5)
>
> End Sub
> #End Region
>
> #Region "Fill Data PaymentsAndOthers"
> Sub Fill_Template_PaymentsAndOthers()
>
> cmd.CommandText = "SELECT Phone_Bill, College_Fee_And_Misc,Vehicle_Payment,Vehicle_License,Vehicle_Insurance,Credit_Card, Debit_Card,Others, Sub_Total FROM tblPaymentsAndOthers"
> cmd.Connection = connection
> da.SelectCommand = cmd
> da.Fill(ds, "tblPaymentsAndOthers")
>
>
> txtPhoneBill.Text = ds.Tables("tblPaymentsAndOthers").Rows(i).Item(0)
> txtCollegeFeeAndMisc.Text = ds.Tables("tblPaymentsAndOthers").Rows(i).Item(1)
> txtVehiclePayment.Text = ds.Tables("tblPaymentsAndOthers").Rows(i).Item(2)
> txtVehicleLicense.Text = ds.Tables("tblPaymentsAndOthers").Rows(i).Item(3)
> txtVehicleInsurance.Text = ds.Tables("tblPaymentsAndOthers").Rows(i).Item(4)
> txtCardCredit.Text = ds.Tables("tblPaymentsAndOthers").Rows(i).Item(5)
> txtDebitCard.Text = ds.Tables("tblPaymentsAndOthers").Rows(i).Item(6)
> txtPnOOthers.Text = ds.Tables("tblPaymentsAndOthers").Rows(i).Item(7)
> txtSubPaymentnOthers.Text = ds.Tables("tblPaymentsAndOthers").Rows(i).Item(8)
>
> End Sub
> #End Region
>
> #Region "Fill Data Banking"
> Sub Fill_Template_Banking()
>
> cmd.CommandText = "SELECT Maybank_Balance,Maybank_Withdraw,Maybank_Deposit,CIMB_Balance,CIMB_Withdraw,CIMB_Deposit, Public_Bank_Balance, Public_Bank_Withdraw, Public_Bank_Deposit,Other_Bank_Balance,Other_Bank_Withdraw,Other_Bank_Deposit FROM tblBanking"
> cmd.Connection = connection
> da.SelectCommand = cmd
> da.Fill(ds, "tblBanking")
>
>
> txtPhoneBill.Text = ds.Tables("tblBanking").Rows(i).Item(0)
> txtCollegeFeeAndMisc.Text = ds.Tables("tblBanking").Rows(i).Item(1)
> txtVehiclePayment.Text = ds.Tables("tblBanking").Rows(i).Item(2)
> txtVehicleLicense.Text = ds.Tables("tblBanking").Rows(i).Item(3)
> txtVehicleInsurance.Text = ds.Tables("tblBanking").Rows(i).Item(4)
> txtCardCredit.Text = ds.Tables("tblBanking").Rows(i).Item(5)
> txtDebitCard.Text = ds.Tables("tblBanking").Rows(i).Item(6)
> txtPnOOthers.Text = ds.Tables("tblBanking").Rows(i).Item(7)
> txtSubPaymentnOthers.Text = ds.Tables("tblBanking").Rows(i).Item(8)
>
> End Sub
> #End Region
>
> Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
> If (i <= 0) Then
> MsgBox("Already At The First Record")
> Else
> i = 0
> Call Fill_Template_Expenses()
> Call Fill_Template_Food()
> Call Fill_Template_Housing()
> Call Fill_Template_Transportation()
> Call Fill_Template_Entertainment()
> Call Fill_Template_PersonalCareAndItems()
> Call Fill_Template_Loan()
> Call Fill_Template_Legal()
> Call Fill_Template_PaymentsAndOthers()
> Call Fill_Template_Banking()
> 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
> MsgBox("Already At The First Record")
> Else
> i = i - 1
> Call Fill_Template_Expenses()
> Call Fill_Template_Food()
> Call Fill_Template_Housing()
> Call Fill_Template_Transportation()
> Call Fill_Template_Entertainment()
> Call Fill_Template_PersonalCareAndItems()
> Call Fill_Template_Loan()
> Call Fill_Template_Legal()
> Call Fill_Template_PaymentsAndOthers()
> Call Fill_Template_Banking()
> End If
> End Sub
>
> Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
> If (i = ds.Tables("tblHousing").Rows.Count - 1) Then
> MsgBox("Already At The Last Record")
> Else
> i = i + 1
> Call Fill_Template_Expenses()
> Call Fill_Template_Food()
> Call Fill_Template_Housing()
> Call Fill_Template_Transportation()
> Call Fill_Template_Entertainment()
> Call Fill_Template_PersonalCareAndItems()
> Call Fill_Template_Loan()
> Call Fill_Template_Legal()
> Call Fill_Template_PaymentsAndOthers()
> Call Fill_Template_Banking()
> End If
> End Sub
>
> Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
> If (i = ds.Tables("tblHousing").Rows.Count - 1) Then
> MsgBox("Already At The Last Record")
> Else
> ds.Tables("tblHousing").Rows.Count(-1)
> Call Fill_Template_Expenses()
> Call Fill_Template_Food()
> Call Fill_Template_Housing()
> Call Fill_Template_Transportation()
> Call Fill_Template_Entertainment()
> Call Fill_Template_PersonalCareAndItems()
> Call Fill_Template_Loan()
> Call Fill_Template_Legal()
> Call Fill_Template_PaymentsAndOthers()
> Call Fill_Template_Banking()
> End If
> End Sub
> End Class
So how to code for the navigation button and also my expenses table is the main table.
Tables:
tblExpenses(ExpensesID,Date,Food....Allowance...so on)
tblFood(FoodID, breakfast.....Sub_Total)
So, tblExpenses.Food is connected to tblfood.FoodID.
tblExpenses may have many tblFood
tblFood must have a tblExpenses.
Hope this info helps.