Visual Basic

Imports Microsoft.Office.Interop
' You will need to include a reference to the Microsoft Excel 14 Object library in order for this to work. (Excel 2010 example)

Private Function GetExcelComments(ByVal sFname As String) As String
     Dim sRetVal As String = ""
     Dim ExcelApp As New Excel.Application
     Dim ExcelBook As Excel.Workbook
     Dim DocProps As Object

     ExcelApp.Workbooks.Open(sFname, , True)
     ExcelBook = ExcelApp.Workbooks(1)

     docprops = ExcelBook.BuiltinDocumentProperties

     sRetVal = DocProps("Comments").Value.ToString

     ' Use this block of code to list all of the
     ' document properties available.

     'If Not (DocProps Is Nothing) Then
     '    Dim i As Integer
     '    For i = 1 To DocProps.Count
     '        Debug.Print(DocProps(i).Name)
     '    Next i
     'End If

     ExcelBook.Close(True)
ExcelApp.Quit()


    
     Return sRetVal

 End Function

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.