VB.Net Datagridview showing data twice.....

[Amol53462]

Hello,

I am new to VB.net and trying to create a from about Ledger Statement along with opening balance and closing balance.

but when i am changing values in datetimepickers to get new data or clicking on button which runs code to get new data ,

my datagridview wont flush old rows, its adding new rows below old rows.

for example if i select dates criteria 01/01/19 to 30/01/19 and hit button then data will show. but when i change dates like 01/02/19 to 25/02/19, i will get 01/01/19 to 25/02/19 as result. Rows dated 01/01/19 to 30/01/19 wont dis appear

Please help.,

my code in get data button...

If DateTimePicker2.Value.Date < DateTimePicker1.Value.Date Then
MsgBox("Invalid Dates")
Exit Sub

End If


Dim con As New OleDb.OleDbConnection(dbsource)
Dim odate1 As String
' DateTimePicker1.Format = DateTimePickerFormat.Custom
' DateTimePicker1.Format = "dd/mm/yyyy"

Dim odate As Date = DateTimePicker1.Value.Date


odate1 = odate.AddDays(-1)
'MsgBox(odate1)
TextBox1.Text = odate1

Dim start As Date = "01/01/2000"
Dim todate As Date = DateTimePicker2.Value.Date

Dim sql2 As String



Dim dtdate2 As Date
Dim opening As Integer
Dim sql3 As String
Dim sql4 As String
Dim sqltemp As String

' sqltemp = "select * from cash where tdate between @fromdate and @todate order by tdate"

'sql2 = "SELECT sum(cashin) - sum(cashout) as ob1 FROM cash WHERE tdate BETWEEN ''" & start & "'' AND ''" & odate1 & "''"
sql2 = "select sum(cashin)- sum(cashout) as ob2 from cash where tdate between #" & start & "# And #" & odate1 & "#"
' sql = ("select sum(cashin) - sum(cashout) as ob from cash")
sql = "select tdate, custname, particular, cashin, cashout from cash where tdate between #" & odate & "# And #" & todate & "#"
sql3 = "select sum(cashin) as dr from cash where tdate between #" & odate & "# And #" & todate & "#"
sql4 = "select sum(cashout) as cr from cash where tdate between #" & odate & "# And #" & todate & "#"

Dim cmd2 As New OleDb.OleDbCommand(sql, con)
Dim cmd3 As New OleDb.OleDbCommand(sql2, con)
Dim cmd4 As New OleDb.OleDbCommand(sql3, con)
Dim cmd5 As New OleDb.OleDbCommand(sql4, con)

con.Open()
Dim reader As OleDb.OleDbDataReader = cmd2.ExecuteReader()
cmd2.CommandText = CommandType.Text
Dim reader1 As OleDb.OleDbDataReader = cmd3.ExecuteReader()
cmd3.CommandText = CommandType.Text
Dim reader2 As OleDb.OleDbDataReader = cmd4.ExecuteReader()
cmd3.CommandText = CommandType.Text
Dim reader3 As OleDb.OleDbDataReader = cmd5.ExecuteReader()
cmd3.CommandText = CommandType.Text


While reader2.Read
TextBox3.Text = reader2("dr").ToString
End While
While reader3.Read
TextBox4.Text = reader3("cr").ToString
End While

While reader1.Read
TextBox2.Text = reader1("ob2").ToString

End While


'MsgBox("Connection Open")

'While reader.Read
'TextBox1.Text = reader("ob").ToString

'End While


da = New OleDb.OleDbDataAdapter(sql, con)
' da.Fill(ds, "cash")

' maxrows = ds.Tables("cash").Rows.Count
da.SelectCommand = New OleDb.OleDbCommand(sql, con)
da.Fill(ds, sql)





' MsgBox(maxrows)
' da.SelectCommand = New OleDb.OleDbCommand(sql, con)
' da.Fill(ds, sql)




DataGridView1.DataSource = ds.Tables(sql)
TextBox5.Text = Val(TextBox2.Text) + Val(TextBox3.Text) - Val(TextBox4.Text)


con.Close()

Continue reading...