Visual Basic

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
            MsgBox("You are already at first position.")
        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
            MsgBox("You are already at first position.")
        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
            MsgBox("You are already at Last position.")
        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
            MsgBox("You are already at Last position.")
        Else
            i = ds.Tables("tblExpenses").Rows.Count
            i = ds.Tables("tblFoodAndBeverage").Rows.Count
            Call Fill_Template()
        End If
    End Sub
End Class

garyu87 wrote:

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.

garyu87 wrote:

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.

public function GetFoodAndBeverageRecord(PrimaryKey As Integer) as List(Of String) 'or a class, or an array, or..
  Dim result as New List(Of Object)

  ClearTextBoxes()

  Using con As New SqlConnection(connectionstring)
  Using cmd As con.CreateCommand
    cmd.CommandText = File.Load("GetFoodAndBeverageRecord.sql")
    con.Open
    using rdr = con.ExecuteReader
    ' fill List, array or class
      if rdr.Read()
        for i = 0 to rdr.FieldCount -1
          result.Add(rdr.GetField(i)
        next
      end if
     end using
   end using
   end using
End Function

Private Sub ClearTextBoxes()
  txtBoxBla.Text = ""
  txtBoxBla2.Text = ""
End Sub

Public Class FoodAndBeverage
  Public Property Id As Integer
  Public Property FoodName As String
  Public Property Cost As Double
End Class

Bastard Programmer from Hell
if you can't read my code, try converting it here[^]

Karthik Reddy wrote:

not able to give the output in vb6 application.

• Do all class have a Guid? Similar to the example below?
  
  C#

  [GuidAttribute("9ED54F84-A89D-4fcd-A854-44251E925F09")]
  public class SampleClass

  
  
• Are you on Win7? Did you register the component under the Admin-account?
  
• If you did not install your assembly with the control in the GAC, you'll need to use the /CODEBASE parameter when invoking regasm.
  
• Write a minimal test-application for your control, and use it for testing; are you sure that you installed all the requirements for your control? That is has read-rights on the files it needs to access? You can use DependencyWalker[^] to check if your assemblies p/invoke to other (native) dll's that you might need to provide on the target system too.
  

Bastard Programmer from Hell
if you can't read my code, try converting it here[^]

Karthik Reddy wrote:

Do i need to register the .tlb files with regtlibv12.exe ?

Karthik Reddy wrote:

I guess inorder to use /codebase option, my dll need to be strongly name, but my dll is not strongly name because my third party dll is not stronly named .

Bastard Programmer from Hell
if you can't read my code, try converting it here[^]