Pa help po mga idol ayaw kase mag insert ng data sa table pag pipindotin ko ang add to list button1 :<
Code:
Imports System.Data.SqlClient
Imports System.Net
Imports System.Security.Authentication.ExtendedProtection
Public Class Form1
Private Sub PictureBox1_Click(sender As Object, e As EventArgs) Handles PictureBox1.Click
Me.Dispose()
End Sub
Private Sub txtQty_KeyPress(sender As Object, e As KeyPressEventArgs) Handles txtQty.KeyPress
Select Case Asc(e.KeyChar)
Case 48 To 57
Case 46
Case 8
Case Else
e.Handled = True
End Select
End Sub
Private Sub txtUnitPrice_KeyPress(sender As Object, e As KeyPressEventArgs) Handles txtUnitPrice.KeyPress
Select Case Asc(e.KeyChar)
Case 48 To 57
Case 46
Case 8
Case Else
e.Handled = True
End Select
End Sub
Private Sub txtDiscount_KeyPress(sender As Object, e As KeyPressEventArgs) Handles txtDiscount.KeyPress
Select Case Asc(e.KeyChar)
Case 48 To 57
Case 46
Case 8
Case Else
e.Handled = True
End Select
End Sub
Function GetInvoice() As String
Try
Dim sdate As String = Now.ToString("MMddyy")
cn.Open()
cm = New SqlCommand("select top 1 invoiceno from tblbill where invoiceno like '" & sdate & "%' order by id desc", cn)
dr = cm.ExecuteReader
dr.Read()
If dr.HasRows Then GetInvoice = CLng(dr.Item("invoiceno").ToString) + 1 Else GetInvoice = sdate & "1001"
dr.Close()
cn.Close()
Catch ex As Exception
cn.Close()
MsgBox(ex.Message, vbCritical)
End Try
End Function
Private Sub Billing_Click(sender As Object, e As EventArgs) Handles billing.Click
txtInvoiceNo.Text = GetInvoice()
End Sub
Sub UpdateBill()
Dim found As Boolean = False
cn.Open()
cm = New SqlCommand("select * from tblbill where invoiceno Like '" & txtInvoiceNo.Text & "'", cn)
dr = cm.ExecuteReader
dr.Read()
If dr.HasRows Then found = True Else found = False
cn.Close()
dr.Close()
If found = True Then
cn.Open()
cm = New SqlCommand("update tblbill Set invoicedate=@invoicedate, billto=@billto, address=@address, invoicefor=@invoicefor, subtotal=@subtotal, taxrate=@taxrate, other=@other where invoiceno = @invoiceno", cn)
With cm.Parameters
.AddWithValue("@invoicedate", dtInvoice.Value)
.AddWithValue("@billto", txtBill.Text)
.AddWithValue("@address", txtAddress.Text)
.AddWithValue("@invoicefor", txtInvoiceFor.Text)
.AddWithValue("@subtotal", CDbl(txtSubTotal.Text))
.AddWithValue("@taxrate", CDbl(txtTax.Text))
.AddWithValue("@other", CDbl(txtOthers.Text))
.AddWithValue("@invoiceno", txtInvoiceNo.Text)
End With
cm.ExecuteNonQuery()
cn.Close()
Else
cn.Open()
cm = New SqlCommand("insert into tblbill (invoiceno, invoicedate, billto, address, invoicefor, subtotal, taxrate, other)values(@invoiceno, @invoicedate, @billto, @address, @invoicefor, @subtotal, @taxrate, @other)", cn)
With cm.Parameters
.AddWithValue("@invoiceno", txtInvoiceNo.Text)
.AddWithValue("@invoicedate", dtInvoice.Value)
.AddWithValue("@billto", txtBill.Text)
.AddWithValue("@address", txtAddress.Text)
.AddWithValue("@invoicefor", txtInvoiceFor.Text)
.AddWithValue("@subtotal", CDbl(txtSubTotal.Text))
.AddWithValue("@taxrate", CDbl(txtTax.Text))
.AddWithValue("@other", CDbl(txtOthers.Text))
End With
cm.ExecuteNonQuery()
cn.Close()
End If
End Sub
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Connection()
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Try
If txtBill.Text = String.Empty Or txtAddress.Text = String.Empty Or txtInvoiceNo.Text = String.Empty Or txtInvoiceFor.Text = String.Empty Then
MsgBox("Required Missing Field!", vbCritical)
Return
End If
If txtDescription.Text = String.Empty Or txtQty.Text = String.Empty Or txtUnitPrice.Text = String.Empty Or txtDiscount.Text = String.Empty Then
MsgBox("Required Missing Field!", vbCritical)
Return
End If
cn.Open()
cm = New SqlCommand("insert into tblbill_items (invoiceno, desccription, qty, unitprice, discount)values(@invoiceno, @description,@qty,@unitprice,@discount)", cn)
With cm.Parameters
.AddWithValue("@invoiceno", txtInvoiceNo.Text)
.AddWithValue("@description", txtDescription.Text)
.AddWithValue("@qty", CDbl(txtQty.Text))
.AddWithValue("@unitprice", CDbl(txtUnitPrice.Text))
.AddWithValue("@discount", CDbl(txtDiscount.Text))
End With
cm.ExecuteNonQueryAsync()
cn.Close()
Loadrecords()
Catch ex As Exception
cn.Close()
MsgBox(ex.Message, vbCritical)
End Try
End Sub
Sub CalculateTotal()
Try
txtTotal.Text = Format((CDbl(txtUnitPrice.Text) * CDbl(txtQty.Text)) - CDbl(txtDiscount.Text), "#,##0.00")
Catch ex As Exception
txtTotal.Text = "0.00"
End Try
End Sub
Private Sub txtQty_TextChanged(sender As Object, e As EventArgs) Handles txtQty.TextChanged
CalculateTotal()
End Sub
Sub Loadrecords()
Dim _subtotal As Double = 0
DataGridView1.Rows.Clear()
Dim i As Integer
cn.Open()
cm = New SqlCommand("select * from tblbill_items where invoiceno = '" & txtInvoiceNo.Text & "'", cn)
dr = cm.ExecuteReader
While dr.Read
i += 1
_subtotal += CDbl(dr.Item("total").ToString)
DataGridView1.Rows.Add(dr.Item("id").ToString, i, dr.Item("description").ToString, Format(CDbl(dr.Item("qSty").ToString), "#,##0.00"), Format(CDbl(dr.Item("unitprice").ToString), "#,##0.00"), Format(CDbl(dr.Item("discount").ToString), "#,##0.00"), Format(CDbl(dr.Item("total").ToString), "#,##0.00"), "EDIT", "DELETE")
End While
dr.Close()
cn.Close()
txtSubTotal.Text = Format(_subtotal, "#,##0.00")
CalculateBill()
End Sub
Sub CalculateBill()
Try
txtSales.Text = Format(CDbl(txtSubTotal.Text) * CDbl(txtTax.Text), "#,##0.00")
txtGrandTotal.Text = Format(CDbl(txtSubTotal.Text) + CDbl(txtTax.Text) + CDbl(txtOthers.Text), "#,##0.00")
Catch ex As Exception
txtGrandTotal.Text = "0.00"
End Try
End Sub
Private Sub txtTax_KeyPress(sender As Object, e As KeyPressEventArgs) Handles txtTax.KeyPress
If Asc(e.KeyChar) = 13 Then
UpdateBill()
End If
End Sub
Private Sub txtOthers_KeyPress(sender As Object, e As KeyPressEventArgs) Handles txtOthers.KeyPress
If Asc(e.KeyChar) = 13 Then
UpdateBill()
End If
End Sub
Private Sub txtDescription_TextChanged(sender As Object, e As EventArgs) Handles txtDescription.TextChanged
End Sub
Private Sub txtDescription_KeyPress(sender As Object, e As KeyPressEventArgs) Handles txtDescription.KeyPress
End Sub
End Class