Creating an Income and Expenditure Statement. - Visual Basic Discussion Boards

One method is to purchase a Barcode font, then "print" the value using that font.

It is important for you to know what barcode encoding method your scanner gun is going to accept.

Do some more research on this.

Here is an example of a free barcode font. (This is code 39, there are many more.)

http://download.cnet.com/Free-TrueType-Code-39-Barcode-Font/3000-2190_4-10067143.html[^]

Hi I am working with Visual Basic application on one of my data logging unit in which data speed is 1000 sample per second the data logging unit will run for one or two complete day. Which means for one day data amount will be 86400000 samples can easily be stored in below buffer. Or partially after 2-3 hr data can automatically save to selected drive.

How I can save data above data in excel format. Or which is the best way to store data. Below is my buffer in which data get store:

Dim DataSet(0 To 1000000000) As String

Public Sub OnRead(ByVal pHandle As Integer)
If StartLogging = 1 Then
Time(SamplePerSecond) = TimeValue / 1000
DataNo = DataNo + 1
DataSet(DataNo) &= TimeValue / 1000 & vbTab & (Int((CH1_RawADC - CH1_Tare) * CH1_CalibrationFactor) / CH1_DP)
End If
End Sub

Himanshu

If you need to save it so it can be read by Excel, I'd say at the end of the sampling period, write the dataset to a CSV (comma separated value) file.
A quick check of my Excel (Office 2010) shows a maximum row count of a little over 1,000,000.
Since you expect 86,000,000 samples, you will have to have 86 or more samples per row.

Hope that helps.

Tim

Hi Thanks for your replay. It is quick way to store data in this CSV format but main disadvantage is "lot of file is created". Excel got one advantage that new sheets can be open with in one file.

Yet now I am able to open excel file and export data to different sheet but because of big no of sample Excel keep on working for about some minutes. So I am looking to do it in fast way.

If this can done it will be great. Using CSV with 1,000,000 row limit and than work with 2sheet of same CSV file?

Store the data in a format that allows you to write fast. DON'T try to store samples directly in an Excel sheet. Excel interop is way too slow for that.

You do the conversion to Excel after the samples are collected.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

As I gone through some search. If data amount is big than one should used TDMS format is someone use this before and how to incorporate with VB

http://www.ni.com/white-paper/3727/en/

Traditional Approaches to Measurement Data Storage

modified 18-Dec-13 3:11am.

You can use whatever format you want, so long as you're not spending any kind of time doing any processing of the data or work to format the data how you want. The priority here is to write the data as fast as possible so you don't bog your code down and lose samples.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

Hi all,

I want to create an Income and Expenditure Statement in Crystal Report (I am using VB.NET 2010, MySQL and Crystal Report with XML datasource). I have two different tables namely

1. income
2. expenditure

Structure of income table is...

--------------------------------------------------------------
|  ID   |   IncomeDate   |  Details         |  IncomeAmt     |
-------------------------------------------------------------
|  a1   |  2/12/2013     | Sale to Mr. ABC  |      34,000.00 |
|  a2   |  3/12/2013     | Sale to Mr. DEF  |      14,000.00 |
|  a3   |  4/12/2013     | Sale to Mr. IJK  |      22,500.00 |
|  a4   |  5/12/2013     | Sale to Mr. LMN  |       1,500.00 |
|  a5   |  6/12/2013     | Sale to Mr. OPQ  |       9,235.00 |
|  a6   |  7/12/2013     | Sale to Mr. RST  |      66,000.00 |
--------------------------------------------------------------

In the above table Mr. ABC, DEF, IJK are all different customers.
and Structure of expenditure table is

--------------------------------------------------------------
|  ID   |   ExpDate      |  Details            |  ExpAmt     |
--------------------------------------------------------------
|  b1   |  1/12/2013     | Purchase from  ABC  |   34,000.00 |
|  b2   |  10/12/2013    | Purchase from  DEF  |   14,000.00 |
|  b3   |  11/12/2013    | Purchase from  IJK  |   22,500.00 |
|  b4   |  16/12/2013    | Purchase from  LMN  |    1,500.00 |
|  b5   |  26/12/2013    | Purchase from  OPQ  |    9,235.00 |
|  b6   |  27/12/2013    | Purchase from  RST  |   66,000.00 |
--------------------------------------------------------------

In the above table ABC, DEF, IJK are all different distributors.
The above tables are completely different although the structures are almost same. But data is different.
I want to create the report...

---------------------------------------------------||---------------------------------------------------
| ID | IncomeDate |  Details         | IncomeAmt | || ID | ExpDate    |  Details           | ExpAmt    |
---------------------------------------------------||---------------------------------------------------
| a1 | 2/12/2013  | Sale to Mr. ABC  | 34,000.00 | || b1 | 1/12/2013  | Purchase from  ABC | 34,000.00 |
| a2 | 3/12/2013  | Sale to Mr. DEF  | 14,000.00 | || b2 | 10/12/2013 | Purchase from  DEF | 14,000.00 |
| a3 | 4/12/2013  | Sale to Mr. IJK  | 22,500.00 | || b3 | 11/12/2013 | Purchase from  IJK | 22,500.00 |
| a4 | 5/12/2013  | Sale to Mr. LMN  |  1,500.00 | || b4 | 16/12/2013 | Purchase from  LMN |  1,500.00 |
| a5 | 6/12/2013  | Sale to Mr. OPQ  |  9,235.00 | || b5 | 26/12/2013 | Purchase from  OPQ |  9,235.00 |
| a6 | 7/12/2013  | Sale to Mr. RST  | 66,000.00 | || b6 | 27/12/2013 | Purchase from  RST | 66,000.00 |
---------------------------------------------------||---------------------------------------------------

I have 3 options.

1. Join two tabels using mysql join/union/union all or other possible commands,
2. Create two XML files of two different tables and merge them,
3. Crystal Report DataSource has two different XML tables.

I tried all, but failed.

my code for option 1 is...

VB

            Try
OpenConnection
                Dim sb As New StringBuilder '<--------------------------------------------------------------------- Table 1
                sb.Append("SELECT * FROM income WHERE income.IncDate BETWEEN '" & DateFrom & "' AND '" & DateTo & "' UNION SELECT * FROM expenditure WHERE expenditure.ExpDate BETWEEN '" & DateFrom & "' AND '" & DateTo & "'")
                Dim dbcommand As New MySqlCommand
                Dim dbadapter As New MySqlDataAdapter
                Dim stdata As New DataSet()
                dbcommand.Connection = conn
                dbcommand.CommandText = sb.ToString
                dbadapter.SelectCommand = dbcommand
                dbadapter.Fill(stdata)
                stdata.WriteXml(Application.StartupPath & "\ReportXml\IncomeExpenditure.xml", XmlWriteMode.WriteSchema)
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try

and for option 2 is...

VB

'// Retrieve the income details
            Try
                Dim sb As New StringBuilder '<--------------------------------------------------------------------- Table 1
                sb.Append("SELECT * FROM income WHERE IncDate BETWEEN '" & DateFrom & "' AND '" & DateTo & "'")
                Dim dbcommand As New MySqlCommand
                Dim dbadapter As New MySqlDataAdapter
                Dim stdata As New DataSet()
                dbcommand.Connection = conn
                dbcommand.CommandText = sb.ToString
                dbadapter.SelectCommand = dbcommand
                dbadapter.Fill(stdata)
                stdata.WriteXml(Application.StartupPath & "\ReportXml\Income.xml", XmlWriteMode.WriteSchema)
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try


            '// Retrieve the Expenditure details
            Try
                Dim sb1 As New StringBuilder '<--------------------------------------------------------------------- Table 2
                sb1.Append("SELECT * FROM expenditure WHERE ExpDate BETWEEN '" & DateFrom & "' AND '" & DateTo & "'")
                Dim dcommand As New MySqlCommand
                Dim dadapter As New MySqlDataAdapter
                Dim sdata As New DataSet()
                dcommand.Connection = conn
                dcommand.CommandText = sb1.ToString
                dadapter.SelectCommand = dcommand
                dadapter.Fill(sdata)
                sdata.WriteXml(Application.StartupPath & "\ReportXml\Expenditure.xml", XmlWriteMode.WriteSchema)
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try


            '// Merge Into One (Two XML Files)  <---------------------------------------------------------- Table 1 + Table2 = Merge1
            Dim Table1 As New XmlTextReader(Application.StartupPath & "\ReportXml\Income.xml")
            Dim Table2 As New XmlTextReader(Application.StartupPath & "\ReportXml\Expenditure.xml")
            Dim XmlDataSet1 As New DataSet
            Try
                XmlDataSet1.ReadXml(Table1)
                Dim XmlDataSet2 As New DataSet
                XmlDataSet2.ReadXml(Table2)

                XmlDataSet1.Merge(XmlDataSet2)
                XmlDataSet1.WriteXml(Application.StartupPath & "\ReportXml\IncomeExpenditure.xml", XmlWriteMode.WriteSchema)
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Critical, "Error!!!")
            End Try

But not getting my expected result. I tried to find the solutions in the internet but everywhere I found, at least one matching column should be there to join/merge. But I want to simply join these two tables side by side. One more thing to do. I want to show the result filtering between two dates.

What should I do ?

Biplob.

Option 4 Create 2 sub reports with the same date parameters and forget about merging/union etc.

Never underestimate the power of human stupidity
RAH

Thank you Holmes, your single sentence gave my answer. I tried it and somewhat successful. If there is any problem, I will definitely inform you.

Thanks again.

Regards,
Biplob

How about something like Option #2, but in a Temp table.

Something like

SELECT xdate,description,income_amt Into #Temp1
Union
SELECT xdate,description,exp_amt Into #Temp1

(Apply the correct field names for your solution)

This way, you have a merged table which you can sort by date and generate your report.

My 2 cents.
Cool | :cool:

Option 2 without the merge. Use subreports in Crystal Reports, one for income and one for expenses. This also allows you to put the tables side by side. The only disadvantage might be if you want to show a net profit/loss, though this could be handled easily by pre-calculating and passing in as a formula field.

"Go forth into the source" - Neal Morse

I Need to load data from SQL server. i wrote this. Can anyone help me please.
I have:
txt.ID
txt.Name
txt.Adress (as textboxes)
cbDept (as a combobox)
The textboxes stays empty. and in the combobox i get the ID, and in the combobox i need to get the name.

VB

Private Sub cmdRequery_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdRequery.Click
       DBRequery()
       Me.BindingContext(_DataSet.Tables(0)).CancelCurrentEdit()
       Me.BindingContext(_DataSet.Tables(0)).Position = 0
       RefreshData(True)
   End Sub
   Private Sub DBRequery()
       cmdSave.Enabled = False
       _DataSet.Clear()
       Try
           _DataAdapter.Fill(_DataSet)
       Catch Eror As Exception
           cmdAdd.Enabled = False
           cmdUpdate.Enabled = False
           cmdDelete.Enabled = False
           MsgBox(Eror.Message, MsgBoxStyle.Exclamation, "Error Opening Database")
           Close()
           Exit Sub
       End Try
       txtCount.Text = Format(_DataSet.Tables(0).Rows.Count, "#,##0")
       If _DataSet.Tables(0).Rows.Count > 0 Then
           txtCurrent.Text = "1"
           EnableNavigation()
       End If
       cmdAdd.Enabled = True
   End Sub

Thank you

I wrote this on form load. But none of them is working.

VB

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim strconnection As String = "Data Source=ELIANE-VAIO\SQLEXPRESS;Initial Catalog=DatabaseConnection;Integrated Security=True;"
        Dim _cn As SqlConnection = New SqlConnection(strconnection)
        _cn.Open()
        cmd.Connection = _cn
        _DataAdapter.SelectCommand = New SqlCommand("SELECT * FROM tblCustomer", _cn)

        Try
            _DataAdapter.Fill(_DataSet)
        Catch eror As Exception
            MsgBox(eror.Message)
        End Try
        cbDept.DataSource = _DataSet.Tables(0)
        cbDept.DisplayMember = "Namee"
        cbDept.ValueMember = "DeptID"
        Me.txtName.Text = ""
        txtName.DataBindings.Add("text", _DataSet.Tables("tblCustomer"), "Name")
        txtEmail.DataBindings.Add("text", _DataSet.Tables(0), "Email")
        txtAddress.DataBindings.Add("text", _DataSet.Tables(0), "Address")
        cbDept.DataBindings.Add("text", _DataSet.Tables(0), "Namee")
    End Sub

Member 10388494 wrote:
I wrote this on form load. But none of them is working.

Which "them"? And what do you mean by "not working"? Does it throw an error, does it show a blank form?

Put a breakpoint after that try, and hover the mouse over the word "_DataSet" to inspect it's values; see if there are records in the dataset. If not, verify whether you're selecting from the correct database.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

I tried. And it worked.

VB

Private Sub BindingContext_Load(sender As Object, e As EventArgs) Handles Me.Load
        Dim strconnection As String = "Data Source=ELIANE-VAIO\SQLEXPRESS;Initial Catalog=DatabaseConnection;Integrated Security=True;"
        _cn = New SqlConnection(strconnection)


        Dim da As New SqlDataAdapter("select * from tbldept", _cn)
        Dim ds As New DataSet
        da.Fill(ds, "tbldept")
        cbDept.DataSource = Nothing
        cbDept.Items.Clear()
        cbDept.DataSource = ds.Tables("tbldept")
        cbDept.DisplayMember = "namee"
        cbDept.ValueMember = "deptid"
        If cbDept.Items.Count <> 0 Then
            cbDept.SelectedIndex = 0
            cbDept.SelectedValue = 0
            cbDept.Text = ""
        End If
        ds.Dispose()
        da.Dispose()


        _DataAdapter = New SqlDataAdapter("select * from customervw", _cn)
        _DataAdapter.Fill(_DataSet)
        txtName.DataBindings.Add("text", _DataSet.Tables(0), "Name")
        txtEmail.DataBindings.Add("text", _DataSet.Tables(0), "Email")
        txtAddress.DataBindings.Add("text", _DataSet.Tables(0), "Address")
        cbDept.DataBindings.Add("text", _DataSet.Tables(0), "Namee")
        txtserial.DataBindings.Add("text", _DataSet.Tables(0), "serial")
        txtCount.Text = _DataSet.Tables(0).Rows.Count
    End Sub

Hello guys. I need your help Please. I have this code.

VB

Private Sub cmdDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDelete.Click
        BindingContext(_DataSet.Tables(0)).RemoveAt(BindingContext(_DataSet.Tables(0)).Position)
        txtCount.Text = CStr(CInt(txtCount.Text) - 1)
        RefreshData(True)
        cmdSave.Enabled = True
    End Sub

I want to delete a record from my database.
The record is being removed from the form. but not from the database.
Any suggestion?
Thank you Smile | :)

That is because you are doing only half the job, you update the datasource collection but nowhere do you delete the record from the database. Whatever you are using as a tutorial/book/learning aid should have this information. It is impossible to guess your DAL methodology.

Never underestimate the power of human stupidity
RAH

Hi everyone

I was able to load a pdf into a picturebox and save it into a table using data type varbinary successfully.

I load the table with the pdf file onto a form with a picturebox control.

now i want to doubleclick the picturebox to view the pdf file.

I tried few things, don't seem to work out.

Guide me on this one.

Thanks

You need a PDF Viewer[^]

When will you people learn to do a basic bit of research before posting a question?

Never underestimate the power of human stupidity
RAH

Thanks for replying,
But my problem still exists.
The problem is is not loading the pdf file into a pdf reader through an Adobe PDF Reader.
But rather doing the following scenario
Here is the scenario again

1) Have a form (PDF_file)

2) A picturebox (pdf_filePictureBox)that bides with a table with datatype varbinary(Max)

3) (PDFPicture) The Adobe PDF Reader control to load the PDF.

4) A load_button to load the PDF into the reader.

5) An Open_button to read the PDF from the pdf_filePictureBox after being save into the sql database

Now I need to take the pdf showing in the acrobat read and put it in the pdf_filePictureBox so that it can be saved. and load it from the database while I can click open to read it back into the acrobat reader.

I know I need to convert it as a tiff file and put it in the picturebox. unable to get it to work.

This what I've done thus far:

.....
pdf_fielPictureBox.Hide()
PDFPictureBox.Loadfile(openFileDialog.filename)
PDFPictureBox.Show()

.......

This is what I want to accomplish..your guidance is most welcome on this issue.

Thanks again..Hope I am more clearer on my issue.

-- modified 19-Dec-13 12:24pm.

Hi Good morning to All.
I developed & deliverd a Billing application in VB6 with built in data report for printing. Everything is fine. But the problem is My Client using dot matrix printer (TVS MSP 240 Star). They are using paper roll for printing. The printer ejacts much paper after printing the data. My paper size is 4.5*3.5 inches. I expect the printer must stop after printing the last line (may be 3 or 4 lines). But it ejacts the whole 3.5" paper. And it does not reverse the paper for next printing. Please help me.. Thanks in advance...

Hi all,

For last three days, I am getting confused by thinking of a logic.

Ok, let me tell you the confusion.

I am developing an Inventory Software of a Retailer. There are 3 involved here. Like

 -------------     Sale       -----------    Sale        -----------
|             |  ------->    |           |  ------->    |           |
| Distributor |              | Retailer  |              | Customer  |
|             |  <-------    |           |  <-------    |           |
 -------------    Return      -----------   Return       -----------

The above diagram is to make you understand the workflow.

1. Distributor sold to Retailer
2. Retailer sometime return the goods to Distributor for breakage. This can be single item/product or the entire invoice.

Same,

3. Retailer sold to Customer.
4. Customer sometime return the goods to Retailer for breakage. This can be a single item/product or the entire invoice.

My confusion is how should I maintain the stock quantity ?

This is not a software problem but a business decision, you need to talk to your business and find out how they treat the returned product but I can see at least 2 possibilities. The returned product is either able to be resold or it is rubbish, and those are only the 2 extremes.

Never underestimate the power of human stupidity
RAH

Hi Holmes,
Thanks for your answer. Yes the returned product can be resold. I mean sometime the retailer by mistake sales over quantity product to the customer and same Distributor. Like....

Suppose a product name is ItemA.
1. Customer need 3 ItemA.
2. Retailer (by mistake) sold 4 ItemA and generate Invoice.
3. Customer wants to return the excess ItemA (i.e. 1 qty.) to the retailer.
4. Retailer returns 1 ItemA and returns the ItemA value to the customer and generate a Credit/Return Note.

Same thing happens between Distributor and Retailer.

Yes, it is business decision and I have to implement it.
So, I just want to know "What should be the logic and how could I implement ?"