当我开帐单软件时，这是正确的吗？ [英] Is This Correct When I Do Billing Software

问题描述

Imports System.Data
Imports System.Data.OleDb
Public Class Printing
    Private prnDialog As System.Windows.Forms.PrintDialog
    Private prnPreview As System.Windows.Forms.PrintPreviewDialog
    Private prnDocument As System.Drawing.Printing.PrintDocument

    ' for Invoice Head:
    Private InvTitle As String
    Private InvSubTitle1 As String
    Private InvSubTitle2 As String
    Private InvSubTitle3 As String
    Private InvImage As String
    Dim cur As Integer = 0

    ' for Database:
    Private cnn As OleDbConnection
    Private cmd As OleDbCommand
    Private rdrInvoice As OleDbDataReader
    Private strCon As String
    Private InvSql As String
    Private InvSql2 As String

    ' for Report:
    Private CurrentY As Integer
    Private CurrentX As Integer
    Private leftMargin As Integer
    Private rightMargin As Integer
    Private topMargin As Integer
    Private bottomMargin As Integer
    Private InvoiceWidth As Integer
    Private InvoiceHeight As Integer
    Private CustomerName As String
    Private PhoneNumber As String
    Private BillingNumber As String
    Private BillingDate As String
    Private SubTotal As Decimal
    Private InvoiceTotal As Decimal
    Private ReadInvoice As Boolean
    Private AmountPosition As Integer

    ' Font and Color:------------------
    ' Title Font
    Private InvTitleFont As Font = New Font("Book Antiqua", 24, FontStyle.Bold)
    ' Title Font height
    Private InvTitleHeight As Integer
    ' SubTitle Font
    Private InvSubTitleFont As Font = New Font("Book Antiqua", 14, FontStyle.Regular)
    ' SubTitle Font height
    Private InvSubTitleHeight As Integer
    ' Invoice Font
    Private InvoiceFont As Font = New Font("Book Antiqua", 14, FontStyle.Regular)
    ' Invoice Font height
    Private InvoiceFontHeight As Integer
    ' Blue Color
    Private BlueBrush As SolidBrush = New SolidBrush(Color.Blue)
    ' Red Color
    Private RedBrush As SolidBrush = New SolidBrush(Color.Red)
    ' Black Color
    Private BlackBrush As SolidBrush = New SolidBrush(Color.Black)
    Private Sub LoadOrder()
        Dim intOrder As Integer = Int32.Parse(txtBillingNumber.Text)

        Dim MyPass As String = ""
        Dim MyDataFile As String = "C:\Users\tha4630\Documents\BeFashion.mdb"
        strCon = "provider=microsoft.jet.oledb.4.0;data source=" _
         & MyDataFile & ";"

        Try
            ' Get Invoice Data:
            InvSql = "SELECT Orders.ItemName, Orders.Rate, Orders.Quantity, Orders.Amount FROM Billing INNER JOIN Orders ON Billing.BillingNumber = Orders.BillingNumber where Billing.BillingNumber = """ & intOrder & """"
            InvSql2 = "SELECT CustomerName, PhoneNumber, BillingDate, BillingNumber From Billing where BillingNumber = """ & intOrder & """"
            ' create an OleDbDataAdapter
            Dim datAdp As OleDbDataAdapter = New OleDbDataAdapter(InvSql, strCon)
            Dim datAdp2 As OleDbDataAdapter = New OleDbDataAdapter(InvSql2, strCon)
            ' create a command builder
            Dim cBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(datAdp)
            Dim cBuilder2 As OleDbCommandBuilder = New OleDbCommandBuilder(datAdp2)
            ' create a DataTable to hold the query results
            Dim dTable As DataTable = New DataTable
            Dim dTable2 As DataTable = New DataTable
            ' fill the DataTable
            datAdp.Fill(dTable)
            datAdp2.Fill(dTable2)
            If (dTable.Rows.Count = 0) Then
                MessageBox.Show("This Order not found, Please enter another order.")
                Me.Close()
            Else
                dgvPrint.DataSource = dTable
                txtCustomerName.Text = dTable2.Rows(cur)(0).ToString
                txtPhoneNumber.Text = dTable2.Rows(cur)(1).ToString
                txtInvoiceDate.Text = dTable2.Rows(cur)(2).ToString
                txtInvoiceNumber.Text = dTable2.Rows(cur)(3).ToString
            End If
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Sub

    Private Sub GetInvoiceTotal()
        SubTotal = 0

        cnn = New OleDbConnection(strCon)
        cmd = New OleDbCommand(InvSql, cnn)
        cnn.Open()
        rdrInvoice = cmd.ExecuteReader()

        While (rdrInvoice.Read())
            SubTotal = SubTotal + Convert.ToDecimal(rdrInvoice("Amount"))
        End While

        rdrInvoice.Close()
        cnn.Close()
        ' Set Total
        txtSubTotal.Text = SubTotal
    End Sub

    Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
        LoadOrder()
        GetInvoiceTotal()
    End Sub
    Private Sub DisplayInvoice()
        PrintPreviewDialog1.Document = Me.PrintDocument1
        Try
            PrintPreviewDialog1.ShowDialog()
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Sub

    Private Sub btnPrintPreview_Click(sender As Object, e As EventArgs) Handles btnPrintPreview.Click
        ReadInvoice = False
        DisplayInvoice()
    End Sub
    Private Sub ReadInvoiceData()
        cnn.Open()
        rdrInvoice = cmd.ExecuteReader()
        rdrInvoice.Read()
    End Sub

    Private Sub ReadInvoiceHead()
        'Titles and Image of invoice:
        InvTitle = "BE FASHION"
        InvSubTitle1 = "Shop No.4, Corporation Complex, BrookeFields Road,"
        InvSubTitle2 = "Coimbatore-641 001"
        InvSubTitle3 = "Phone: [phone]"
        InvImage = "C:\Users\tha4630\Desktop\befashionlogi.jpg"
    End Sub
    Private Sub SetInvoiceHead(ByVal g As Graphics)
        ReadInvoiceHead()
        CurrentY = topMargin
        CurrentX = leftMargin
        Dim ImageHeight As Integer = 0

        ' Draw Invoice image:
        If (System.IO.File.Exists(InvImage)) Then
            Dim oInvImage As Bitmap = New Bitmap(InvImage)
            ' Set Image Left to center Image:
            Dim xImage As Integer = CurrentX + (InvoiceWidth - oInvImage.Width) / 2
            ImageHeight = oInvImage.Height ' Get Image Height
            g.DrawImage(oInvImage, xImage, CurrentY)
        End If

        InvTitleHeight = Convert.ToInt32(InvTitleFont.GetHeight(g))
        InvSubTitleHeight = Convert.ToInt32(InvSubTitleFont.GetHeight(g))
        ' Get Titles Length:
        Dim lenInvTitle As Integer = Convert.ToInt32(g.MeasureString(InvTitle, InvTitleFont).Width)
        Dim lenInvSubTitle1 As Integer = Convert.ToInt32(g.MeasureString(InvSubTitle1, InvSubTitleFont).Width)
        Dim lenInvSubTitle2 As Integer = Convert.ToInt32(g.MeasureString(InvSubTitle2, InvSubTitleFont).Width)
        Dim lenInvSubTitle3 As Integer = Convert.ToInt32(g.MeasureString(InvSubTitle3, InvSubTitleFont).Width)
        ' Set Titles Left:
        Dim xInvTitle As Integer = CurrentX + (InvoiceWidth - lenInvTitle) / 2
        Dim xInvSubTitle1 As Integer = CurrentX + (InvoiceWidth - lenInvSubTitle1) / 2
        Dim xInvSubTitle2 As Integer = CurrentX + (InvoiceWidth - lenInvSubTitle2) / 2
        Dim xInvSubTitle3 As Integer = CurrentX + (InvoiceWidth - lenInvSubTitle3) / 2

        ' Draw Invoice Head:
        If (InvTitle <> "") Then
            CurrentY = CurrentY + ImageHeight
            g.DrawString(InvTitle, InvTitleFont, BlueBrush, xInvTitle, CurrentY)
        End If
        If (InvSubTitle1 <> "") Then
            CurrentY = CurrentY + InvTitleHeight
            g.DrawString(InvSubTitle1, InvSubTitleFont, BlueBrush, xInvSubTitle1, CurrentY)
        End If
        If (InvSubTitle2 <> "") Then
            CurrentY = CurrentY + InvSubTitleHeight
            g.DrawString(InvSubTitle2, InvSubTitleFont, BlueBrush, xInvSubTitle2, CurrentY)
        End If
        If (InvSubTitle3 <> "") Then
            CurrentY = CurrentY + InvSubTitleHeight
            g.DrawString(InvSubTitle3, InvSubTitleFont, BlueBrush, xInvSubTitle3, CurrentY)
        End If
        ' Draw line:
        CurrentY = CurrentY + InvSubTitleHeight + 8
        g.DrawLine(New Pen(Brushes.Black, 2), CurrentX, CurrentY, rightMargin, CurrentY)
    End Sub
    Private Sub SetOrderData(ByVal g As Graphics)

        Dim FieldValue As String = ""
        InvoiceFontHeight = Convert.ToInt32(InvoiceFont.GetHeight(g))


        CurrentX = leftMargin
        CurrentY = CurrentY + 8
        FieldValue = "Invoice Number        : " & txtInvoiceNumber.Text
        g.DrawString(FieldValue, InvoiceFont, BlackBrush, CurrentX, CurrentY)

        CurrentX = leftMargin
        CurrentY = CurrentY + InvoiceFontHeight
        FieldValue = "Invoice Date               : " & txtInvoiceDate.Text
        g.DrawString(FieldValue, InvoiceFont, BlackBrush, CurrentX, CurrentY)

        CurrentX = leftMargin
        CurrentY = CurrentY + InvoiceFontHeight
        FieldValue = "Customer Name        : " & txtCustomerName.Text
        g.DrawString(FieldValue, InvoiceFont, BlackBrush, CurrentX, CurrentY)

        CurrentX = leftMargin
        CurrentY = CurrentY + InvoiceFontHeight
        FieldValue = "Phone Number          : " & txtPhoneNumber.Text
        g.DrawString(FieldValue, InvoiceFont, BlackBrush, CurrentX, CurrentY)

        ' Draw line:
        CurrentY = CurrentY + InvoiceFontHeight + 8
        g.DrawLine(New Pen(Brushes.Black), leftMargin, CurrentY, rightMargin, CurrentY)
    End Sub
    Private Sub SetInvoiceData(ByVal g As Graphics, ByVal e As System.Drawing.Printing.PrintPageEventArgs)
        ' Set Invoice Table:
        Dim FieldValue As String = ""
        Dim CurrentRecord = 0
        Dim RecordsPerPage = 20
        Dim SerialNo = 0
        'Dim Amount As Decimal = 0
        Dim StopReading As Boolean = False

        InvoiceFontHeight = Convert.ToInt32(InvoiceFont.GetHeight(g))

        ' Set Table Head:
        Dim xSerialNo As Integer = leftMargin
        CurrentY = CurrentY + InvoiceFontHeight
        g.DrawString("S.No", InvoiceFont, BlackBrush, xSerialNo, CurrentY)

        Dim xProductName As Integer = xSerialNo + Convert.ToInt32(g.MeasureString("S.No", InvoiceFont).Width) + 30
        g.DrawString("Product Name", InvoiceFont, BlackBrush, xProductName, CurrentY)

        Dim xUnitPrice As Integer = xProductName + Convert.ToInt32(g.MeasureString("Product Name", InvoiceFont).Width) + 120
        g.DrawString("Unit Price", InvoiceFont, BlackBrush, xUnitPrice, CurrentY)

        Dim xQuantity As Integer = xUnitPrice + Convert.ToInt32(g.MeasureString("Unit Price", InvoiceFont).Width) + 30
        g.DrawString("Quantity", InvoiceFont, BlackBrush, xQuantity, CurrentY)

        Dim xAmount As Integer = xQuantity + Convert.ToInt32(g.MeasureString("Quantity", InvoiceFont).Width) + 30
        g.DrawString("Amount", InvoiceFont, BlackBrush, xAmount, CurrentY)

        CurrentY = CurrentY + InvoiceFontHeight + 8
        g.DrawLine(New Pen(Brushes.Black), leftMargin, CurrentY, rightMargin, CurrentY)

        ' Set Invoice Table:
        CurrentY = CurrentY + InvoiceFontHeight

        While (CurrentRecord < RecordsPerPage)
            FieldValue = SerialNo + 1
            g.DrawString(FieldValue, InvoiceFont, BlackBrush, xSerialNo, CurrentY)
            FieldValue = rdrInvoice("ItemName").ToString()
            ' if Length of (Product Name) > 20, Draw 20 character only
            'If (FieldValue.Length > 20) Then
            '    FieldValue = FieldValue.Remove(20, FieldValue.Length - 20)
            'End If
            g.DrawString(FieldValue, InvoiceFont, BlackBrush, xProductName, CurrentY)
            FieldValue = rdrInvoice("Rate")
            g.DrawString(FieldValue, InvoiceFont, BlackBrush, xUnitPrice, CurrentY)
            FieldValue = rdrInvoice("Quantity").ToString()
            g.DrawString(FieldValue, InvoiceFont, BlackBrush, xQuantity, CurrentY)
            FieldValue = rdrInvoice("Amount")
            g.DrawString(FieldValue, InvoiceFont, BlackBrush, xAmount, CurrentY)
            CurrentY = CurrentY + InvoiceFontHeight

            If (Not rdrInvoice.Read()) Then
                StopReading = True
                Exit While
            End If
            SerialNo += 1
            CurrentRecord += 1
        End While

        If (CurrentRecord < RecordsPerPage) Then
            e.HasMorePages = False
        Else
            e.HasMorePages = True
        End If

        If (StopReading) Then
            rdrInvoice.Close()
            cnn.Close()
            SetInvoiceTotal(g)
        End If

        g.Dispose()
    End Sub

    Private Sub SetInvoiceTotal(ByVal g As Graphics)
        ' Set Invoice Total:
        ' Draw line:
        CurrentY = CurrentY + 8
        g.DrawLine(New Pen(Brushes.Black), leftMargin, CurrentY, rightMargin, CurrentY)

        ' Write Sub Total:
        Dim xSubTotal As Integer = 550
        CurrentY = CurrentY + 8
        g.DrawString("Sub Total", InvoiceFont, RedBrush, xSubTotal, CurrentY)
        Dim TotalValue As String = String.Format("{0:0.00}", txtSubTotal.Text)
        Dim xTotalValue As Integer = 720 - Convert.ToInt32(g.MeasureString(TotalValue, InvoiceFont).Width)
        g.DrawString(TotalValue, InvoiceFont, BlackBrush, xTotalValue, CurrentY)


        ' Write Order Freight:
        Dim xOrderFreight As Integer = 490
        CurrentY = CurrentY + InvoiceFontHeight
        g.DrawString("Change in Total", InvoiceFont, RedBrush, xOrderFreight, CurrentY)
        Dim FreightValue As String = String.Format("{0:0.00}", txtChangeInTotal.Text)
        Dim xFreight As Integer = 720 - Convert.ToInt32(g.MeasureString(FreightValue, InvoiceFont).Width)
        g.DrawString(FreightValue, InvoiceFont, BlackBrush, xFreight, CurrentY)

        CurrentY = CurrentY + 30
        g.DrawLine(New Pen(Brushes.Black), leftMargin, CurrentY, rightMargin, CurrentY)

        Dim xTotal As Integer = 550
        CurrentY = CurrentY + 8
        g.DrawString("Total", InvoiceFont, RedBrush, xSubTotal, CurrentY)
        Dim Total As String = String.Format("{0:0.00}", txtFinalTotal.Text)
        Dim xTotalv As Integer = 720 - Convert.ToInt32(g.MeasureString(Total, InvoiceFont).Width)
        g.DrawString(Total, InvoiceFont, BlackBrush, xTotalv, CurrentY)

        CurrentY = CurrentY + 30
        g.DrawLine(New Pen(Brushes.Black), leftMargin, CurrentY, rightMargin, CurrentY)
        CurrentY = CurrentY + 150
        g.DrawLine(New Pen(Brushes.BurlyWood), leftMargin, CurrentY, rightMargin, CurrentY)
        Dim footer As Font = New Font("Tempus Sans ITC", 14, FontStyle.Bold)
        g.DrawString("Thank You for your business......Visit Again", footer, Brushes.Brown, 100, CurrentY)
        CurrentY = CurrentY + 50
        g.DrawString("AIR TRADING COMPANY", footer, Brushes.Brown, 100, CurrentY)
        CurrentY = CurrentY + 30
        g.DrawLine(New Pen(Brushes.BurlyWood), leftMargin, CurrentY, rightMargin, CurrentY)


    End Sub


    Private Sub PrintDocument1_PrintPage(sender As Object, e As Drawing.Printing.PrintPageEventArgs) Handles PrintDocument1.PrintPage
        leftMargin = Convert.ToInt32(e.MarginBounds.Left)
        rightMargin = Convert.ToInt32(e.MarginBounds.Right)
        topMargin = Convert.ToInt32(e.MarginBounds.Top)
        bottomMargin = Convert.ToInt32(e.MarginBounds.Bottom)
        InvoiceWidth = Convert.ToInt32(e.MarginBounds.Width)
        InvoiceHeight = Convert.ToInt32(e.MarginBounds.Height)

        If (Not ReadInvoice) Then ReadInvoiceData()

        SetInvoiceHead(e.Graphics) ' Draw Invoice Head
        SetOrderData(e.Graphics) ' Draw Order Data
        SetInvoiceData(e.Graphics, e) ' Draw Invoice Data

        ReadInvoice = True
    End Sub

    Private Sub txtChangeInTotal_SelectedIndexChanged(sender As Object, e As EventArgs) Handles txtChangeInTotal.SelectedIndexChanged
        Dim ChangeTotal As Integer = Val(txtChangeInTotal.Text)
        Dim subTotal As Integer = Val(txtSubTotal.Text)

        Dim FinalAmount As Integer = subTotal - ChangeTotal
        txtFinalTotal.Text = FinalAmount

    End Sub
End Class

[edit]Code block added - OriginalGriff[/edit]

[edit]Code block added - OriginalGriff[/edit]

推荐答案

No!



First of all... your code is sql injection[^] vulnerable.



Instead of this:

No!

First of all... your code is sql injection[^] vulnerable.

Instead of this:

InvSql = "SELECT Orders.ItemName, Orders.Rate, Orders.Quantity, Orders.Amount FROM Billing INNER JOIN Orders ON Billing.BillingNumber = Orders.BillingNumber where Billing.BillingNumber = """ & intOrder & """"

you have to use parametrized query:

you have to use parametrized query:

InvSql = "SELECT O.ItemName, O.Rate, O.Quantity, O.Amount FROM Billing AS B INNER JOIN Orders AS O ON B.BillingNumber = O.BillingNumber WHERE B.BillingNumber = ?"

You should use AddWithValue method[^] to add parameter to the OleDbParameterCollection[^]



Second of all... use SQL method to calculate totals:

You should use AddWithValue method[^] to add parameter to the OleDbParameterCollection[^]

Second of all... use SQL method to calculate totals:

SELECT O.ItemName, SUM(O.Amount) AS Total
FROM Orders AS O INNER JOIN Billing AS B ON O.BillingNumber = B.BillingNumber
WHERE B.BillingNumber = @billingNumber
GROUP BY O.ItemName

or use Linq[^].



Third... Fourth... etc.

Please, read this book: Practical Database Programming with Visual Basic.NET[^]



Final note: Nobody is able to verify such piece of c ode...

or use Linq[^].

Third... Fourth... etc.
Please, read this book: Practical Database Programming with Visual Basic.NET[^]

Final note: Nobody is able to verify such piece of code...

这篇关于当我开帐单软件时，这是正确的吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！