i have two comboboxes, after i load the data to the datagridview, i want to filter the dates by year and by month, but when i click the load, it returns the same data from the first load
What I have tried:
i have tried this code but it doesnt work
Public Sub load()
Try
DataGridView1.Rows.Clear()
Dim count = 0
cboYear1.Refresh()
If Button1.Text <> "Cancel" Then
CheckBox2.Visible = True
CheckBox2.CheckState = CheckState.Checked
Dim conn, conn2 As New MySqlConnection
Dim command, command2 As New MySqlCommand
Dim adapter As New MySqlDataAdapter
Dim dtable As New DataTable
Dim reader, reader2 As MySqlDataReader
Dim index As Integer
Dim totalBalance As Double
conn.ConnectionString = "server='" & frmParent.jlt2servername & "';user id='" & frmParent.jlt2username & "';password= '" & frmParent.jlt2password & "';database='" & frmParent.jlt2database & "';pooling=false"
conn2.ConnectionString = "server='" & frmParent.jlt2servername & "';user id='" & frmParent.jlt2username & "';password= '" & frmParent.jlt2password & "';database='" & frmParent.jlt2database & "';pooling=false"
bcode = ComboBox2.Text
conn.Open()
command.Connection = conn
command.CommandText = "select c_code from clist where c_name = '" & ComboBox1.Text.Trim & "'"
reader = command.ExecuteReader
While reader.Read
ccode = reader.Item("c_code")
End While
conn.Close()
If ComboBox2.Text <> "ALL" Then
conn.Open()
command.Connection = conn
command.CommandText = "select entry_date,dr_no,net_total from dr where c_code = '" & ccode & "' and b_code = '" & bcode & "'and entry_date LIKE '" & yr & "' order by entry_date"
adapter.SelectCommand = command
adapter.Fill(dset, "dr")
dtable = dset.Tables("dr")
index = dtable.Rows.Count
Dim pay, credit(index), balance, rm As Double
Dim drNo(index) As String
Dim strDRdate(index) As String
Dim drDate(index) As Date
For i = 0 To index - 1
drNo(i) = dset.Tables("dr").Rows(i).Item(1).ToString.Trim
credit(i) = CDbl(dset.Tables("dr").Rows(i).Item(2))
drDate(i) = dset.Tables("dr").Rows(i).Item(0)
strDRdate(i) = drDate(i).ToString("MM/dd/yyyy")
Next
conn.Close()
For i = 0 To index - 1
pay = 0
conn.Open()
command.Connection = conn
command.CommandText = "select sum(payment) as 'pay' from invp where dr_no = " & drNo(i) & " and b_code = '" & bcode & "'"
reader = command.ExecuteReader
While reader.Read
If reader.Item("pay").ToString = vbNullString Then
pay = 0
Else
pay = pay + reader.Item("pay")
End If
End While
conn.Close()
conn.Open()
command.Connection = conn
command.CommandText = "select ifnull(sum(total_amt),0) as rm from rm where ref_type = 'DR' and drsi_no = " & drNo(i) & " and b_code = '" & bcode & "'"
reader = command.ExecuteReader
While reader.Read
rm = reader("rm")
End While
conn.Close()
balance = credit(i) - pay
balance = balance - rm
totalBalance = totalBalance + balance
If CheckBox1.Checked = True Then
DataGridView1.Rows.Add(True, strDRdate(i), drNo(i), FormatNumber(credit(i), 2), FormatNumber(pay, 2), FormatNumber(rm, 2), FormatNumber(balance, 2), bcode)
ElseIf CheckBox1.Checked = False Then
If balance <> 0 Then
DataGridView1.Rows.Add(True, strDRdate(i), drNo(i), FormatNumber(credit(i), 2), FormatNumber(pay, 2), FormatNumber(rm, 2), FormatNumber(balance, 2), bcode)
End If
End If
Next
ComboBox1.Enabled = False
Button1.Text = "Cancel"
TextBox1.Text = FormatNumber(totalBalance, 2)
Else
conn.Open()
command.Connection = conn
command.CommandText = "select * from cust_branch where cust_id = '" & ccode & "'"
reader = command.ExecuteReader
While reader.Read
bcode = reader("b_code")
conn2.Open()
command2.Connection = conn2
command2.CommandText = "select entry_date, dr_no,net_total from dr where c_code = '" & ccode & "'and b_code = '" & bcode & "'and entry_date LIKE '" & yr & "'order by entry_date"
adapter.SelectCommand = command2
adapter.Fill(dset, "dr")
dtable = dset.Tables("dr")
index = dtable.Rows.Count
Dim pay, credit(index), balance, rm As Double
Dim drNo(index), strDRdate(index) As String
Dim drDate(index) As Date
For i = 0 To index - 1
drDate(i) = dset.Tables("dr").Rows(i).Item(0)
drNo(i) = dset.Tables("dr").Rows(i).Item(1).ToString.Trim
credit(i) = CDbl(dset.Tables("dr").Rows(i).Item(2))
strDRdate(i) = drDate(i).ToString("MM/dd/yyyy")
Next
conn2.Close()
For i = 0 To index - 1
pay = 0
conn2.Open()
command2.Connection = conn2
command2.CommandText = "select sum(payment) as 'pay' from invp where dr_no = " & drNo(i) & " and b_code = '" & bcode & "'"
reader2 = command2.ExecuteReader
While reader2.Read
If reader2.Item("pay").ToString = vbNullString Then
pay = 0
Else
pay = pay + reader2.Item("pay")
End If
End While
conn2.Close()
conn2.Open()
command2.Connection = conn2
command2.CommandText = "select ifnull(sum(total_amt),0) as rm from rm where ref_type = 'DR' and drsi_no = " & drNo(i) & " and b_code = '" & bcode & "'"
reader2 = command2.ExecuteReader
While reader2.Read
rm = reader2("rm")
End While
conn2.Close()
balance = credit(i) - pay
balance = balance - rm
totalBalance = totalBalance + balance
If CheckBox1.Checked = True Then
DataGridView1.Rows.Add(True, strDRdate(i), drNo(i), FormatNumber(credit(i), 2), FormatNumber(pay, 2), FormatNumber(rm, 2), FormatNumber(balance, 2), bcode)
ElseIf CheckBox1.Checked = False Then
If balance <> 0 Then
DataGridView1.Rows.Add(True, strDRdate(i), drNo(i), FormatNumber(credit(i), 2), FormatNumber(pay, 2), FormatNumber(rm, 2), FormatNumber(balance, 2), bcode)
End If
End If
Next
ComboBox1.Enabled = False
Button1.Text = "Cancel"
TextBox1.Text = FormatNumber(totalBalance, 2)
End While
conn.Close()
End If
Else
ComboBox1.Enabled = True
Button1.Text = "&Generate"
Button1.Enabled = False
CheckBox2.Visible = False
End If
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
thank you for the responses :)