Click here to Skip to main content
15,891,136 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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

VB
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
				'    drDate(i) = dset.Tables("dr").Rows(i).Item(0)
				'    strDRdate(i) = drDate(i).ToString("MM/dd/yyyy")
				'    If (drDate(i).ToString("yyyy") = cboYear1.Text) Then
				'        strDRdate(i) = drDate(i).ToString("MM/dd/'" & cboYear1.Text & "'")
				'    End If
				'    conn.Open()
				'    command.Connection = conn
				'    command.CommandText = "select * from dr where entry_date ='" & strDRdate(i) & "'"
				'    reader = command.ExecuteReader
				'    conn.Close()
				'Next
				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
					'If balance < 0 Then
					'    balance = 0
					'End If
					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
						'If balance < 0 Then
						'    balance = 0
						'End If
						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 :)
Posted
Updated 24-Jul-17 21:22pm
v2
Comments
Graeme_Grant 25-Jul-17 3:24am    
There is too much code here. Where is the code that is trying to extract the data? Is this a WinForm app?
Richard MacCutchan 25-Jul-17 3:41am    
Do not use string concatenation to create SQL commands. Use proper parameterized queries. And validate your parameters first; do not assume that the data passed by the user is always correct.
Member 12374765 26-Jul-17 3:54am    
hi this is the code that i am confused
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()

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900