none
VB.Net Datagridview showing data twice..... RRS feed

  • Question

  • 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()
    

    Monday, July 15, 2019 5:32 AM

Answers

  • Hi,

    I didn't see your code where to declare ds, it should be a global variable,you can try to declare it as a local variable,declare ds in your sub.

    Dim ds as new Dataset

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Amol53462 Monday, July 15, 2019 10:27 AM
    Monday, July 15, 2019 6:22 AM
    Moderator

All replies

  • Hi,

    I didn't see your code where to declare ds, it should be a global variable,you can try to declare it as a local variable,declare ds in your sub.

    Dim ds as new Dataset

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Amol53462 Monday, July 15, 2019 10:27 AM
    Monday, July 15, 2019 6:22 AM
    Moderator
  • Here what events are there that are tied into this? Have you tried using the debugger to set breakpoints to trace thru the code path to see what is transpiring?

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, July 15, 2019 10:20 AM
    Moderator
  • Thank You.,

    It Worked., I declared it in button sub and problem solved., 

    Thank You again ., but can u check my code and tell me is it good way to calculate the opening balance ? 

    Monday, July 15, 2019 10:29 AM