I am working on dairy application in collection table i have a column 'CollDate' type varchar
Now i want to raise a query sum of milk collection between two date
Please help
Imports System.Data.SqlClient
Imports System.IO
Imports System.Data
Imports System.Drawing
Imports System.Globalization
Imports System.Windows.Forms
Public Class frmInvoice
Private intDaysInMonth(11) As Integer
Dim MonthYear As String
Private Sub clearAllTextBoxes()
txtMilkType.Text = ""
txtCustomerName.Text = ""
txtCustomerID.Text = ""
txtInvoiceNo.Text = ""
txtTotalAmt.Text = ""
txtTotalMilk.Text = ""
cmbMonth.Text = ""
cmbYear.Text = ""
End Sub
Private Sub GenerateInvoiceno()
Dim value As String = "0000"
con = New SqlConnection(cs)
con.Open()
cmd = New SqlCommand("SELECT TOP 1 Invid FROM invoiceinfo ORDER BY invid DESC", con)
rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
If rdr.HasRows Then
rdr.Read()
value = rdr.Item("invid")
End If
con.Close()
rdr.Close()
' Increase the ID by 1
value += 1
' Because incrementing a string with an integer removes 0's
' we need to replace them. If necessary.
If value <= 9 Then 'Value is between 0 and 10
value = "000" & value
ElseIf value <= 99 Then 'Value is between 9 and 100
value = "00" & value
ElseIf value <= 999 Then 'Value is between 999 and 1000
value = "0" & value
End If
txtInvoiceNo.Text = "INV-" + value
End Sub
Private Sub btnGeneraterInvoice_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGeneraterInvoice.Click
If MessageBox.Show("Do you want to create an invoice for " & cmbMonth.Text & "..." & cmbYear.Text & "...... for " & txtCustomerName.Text & " ?", _
"Invoice", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = DialogResult.No Then
MsgBox("Operation Cancelled")
Exit Sub
Else
GenerateInvoiceno()
' If txtPayment.Text > 0 Then
SaveInvoice_Data()
'
End If
End Sub
Private Sub frmInvoice_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim today As String = DateTime.Now.ToString("dd-MM-yyyy")
'
DateTimePicker1.Format = DateTimePickerFormat.Custom
'// Display the date as "Mon 27 Feb 2012".
DateTimePicker1.CustomFormat = "dd-MM-yyyy"
Dim [date] As DateTime = Me.DateTimePicker1.Value
searchCustomer()
txtCustomerName.Select()
btnGeneraterInvoice.Enabled = False
Button2.Enabled = False
cmbYear.Items.Clear()
cmbMonth.Items.Clear()
For i = DateTime.Now.Year - 1 To DateTime.Now.Year
cmbYear.Items.Add(i)
Next
Dim month As Integer = DateTime.Today.Month
Dim index As Integer = 0
While index < 12
Me.cmbMonth.Items.Add(month.ToString())
month -= 1
If month <= 0 Then
month = 12
End If
index += 1
End While
End Sub
Private Sub Collect_dataFromCollection()
Try
con = New SqlConnection(cs)
If con.State = ConnectionState.Open Then
con.Close()
End If
Using cmd As SqlCommand = New SqlCommand("SELECT SUM(totalqty) AS Total_Milk, SUM(amount) as Total_Amt FROM ChalanInfo WHERE custid = @CId and collmonth=@CMonth and collyear=@CYear", con)
cmd.Parameters.AddWithValue("@cmonth", cmbMonth.Text)
cmd.Parameters.AddWithValue("@cyear", cmbYear.Text)
cmd.Parameters.AddWithValue("@cid", txtId.Text)
Using da As SqlDataAdapter = New SqlDataAdapter(cmd)
Dim dt As DataTable = New DataTable()
da.Fill(dt)
If Not String.IsNullOrEmpty(dt.Rows(0)("Total_Amt").ToString()) Then
txtTotalAmt.Text = Convert.ToDecimal(dt.Rows(0)("Total_Amt").ToString())
End If
If Not String.IsNullOrEmpty(dt.Rows(0)("Total_Milk").ToString()) Then
txtTotalMilk.Text = Convert.ToDecimal(dt.Rows(0)("Total_Milk").ToString())
End If
End Using
End Using
con.Close()
Catch ex As Exception
End Try
End Sub
Private Sub SaveInvoice_Data()
Try
con = New SqlConnection(cs)
Dim cb As String = "insert into InvoiceInfo(customercode,InvoiceNo,CustomerID,invoicedate,invoicemonth,Customername,ItemName,Totalqty,grandtotal,InvoiceYear,printnotprint) Values (@d1,@d2,@d3,@d4,@d5,@d6,@d7,@d8,@d9,@d10,@d11)"
cmd = New SqlCommand(cb, con)
' cmd.Connection = con
cmd.Parameters.AddWithValue("@d1", txtId.Text)
cmd.Parameters.AddWithValue("@d2", txtInvoiceNo.Text)
cmd.Parameters.AddWithValue("@d3", txtCustomerID.Text)
cmd.Parameters.AddWithValue("@d4", DateTimePicker1.Text)
cmd.Parameters.AddWithValue("@d5", cmbMonth.Text)
cmd.Parameters.AddWithValue("@d6", txtCustomerName.Text)
cmd.Parameters.AddWithValue("@d7", txtMilkType.Text)
cmd.Parameters.AddWithValue("@d8", txtTotalMilk.Text)
cmd.Parameters.AddWithValue("@d9", txtTotalAmt.Text)
cmd.Parameters.AddWithValue("@d10", cmbYear.Text)
cmd.Parameters.AddWithValue("@d11", "Print")
If con.State = ConnectionState.Open Then
con.Close()
End If
con.Open()
cmd.ExecuteNonQuery()
con.Close()
MessageBox.Show("Invoice Generated Successfully ", "Invoice", MessageBoxButtons.OK, MessageBoxIcon.Information)
btnGeneraterInvoice.Enabled = False
Button2.Enabled = True
Catch ex As Exception
' MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
Private Sub searchCustomer()
Try
con = New SqlConnection(cs)
con.Open()
adp = New SqlDataAdapter()
adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(customername) FROM tblaccount WHERE customertype='" & "Farmer" & "'", con)
ds = New DataSet("ds")
adp.Fill(ds)
dtable = ds.Tables(0)
txtCustomerName.Items.Clear()
For Each drow As DataRow In dtable.Rows
txtCustomerName.Items.Add(drow(0).ToString())
Next
con.Close()
Catch ex As Exception
'MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
End Class
