Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 391
  • Last Modified:

extra row added to database for insert or update query

I insert a row for each day of the month into my database using vb function to calculate days in a month then run from 0 to length of month -1
fills in say for December 12/1/2013 to 12/31/2013 then it adds 12/1/2013 again at the bottom with no data in the columns for this one extraneous entry.  All of the correct entries get all columns filled.
just for fun I went to length of month -2  then I get 12/1/2013 to 12/30/2013 and an extra 12/1/2013 at the bottom.

so if I do an update on the first 31 rows for December I get a second row with 12/1/2013 at the bottom.  something has to be telling it to write the strange rows.

gary
0
javagair
Asked:
javagair
  • 5
  • 5
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
Gary,

Are you certain this is occurring on the database side?  In other words, is it possible that the VB function's call to database is sending the extra row?  

- Please post your VB function code, especially the part with the INSERT.
- Please check database table for trigger.  If there is a trigger on INSERT, it could be adding the row inadvertently there.

Kevin
0
 
javagairAuthor Commented:
well don't know, if it was the vb code then shortening the loop should make it go away don't you think?  As you can see I have tried both a count and using  for each rows, both give the same problem.  There are no triggers in the access database.

gary
    Dim DaD3 As OleDbDataAdapter = New OleDbDataAdapter("Select * from HOURSWORKED where WORKDATE >=  '" & dateString & " '  OR WORKDATE <=  '" & datestring2 & " '  and EMPLOYESOCSEC = '" & TXBSOCSEC.Text & "' ", Con)

            Dim Dst3 As DataSet = New DataSet
            DaD3.Fill(Dst3)
            Con.Open()
            If Dst3.Tables(0) Is Nothing OrElse Dst3.Tables(0).Rows.Count = 0 Then
            Else
                'For i = 0 To Dst3.Tables(0).Rows.Count - 1
                i = 0
                For Each row As DataRow In Dst3.Tables(0).Rows


                    DataGridView1.Rows.Add(1)
                    For j = 1 To 9
                        DataGridView1.Item(j - 1, i).Value = Dst3.Tables(0).Rows(i).Item(j).ToString
                    Next
                    i = i + 1
                Next row
            End If
            Con.Close()
            insertstatement = "INSERT INTO HOURSWORKED (WORKDATE,STARTTIME,ENDTIME,STARTLUNCH,ENDLUNCH,PTOPAID,PTOUNPAID,PTODETAIL,SYMITARCODE,EMPLOYESOCSEC,WEEKOFYEAR,YEARDATE) VALUES('" & dateString & "','" & starthr & "','" & endhr & "','" & startlunch & "','" & Endlunch & "','" & paidpto & "','" & unpaidpto & "','" & ptod & "','" & symcode & "','" & empcode & "','" & weekcode & "','" & yearcode & "')"
            Con.Open()
            Dim cmd2 As New OleDb.OleDbCommand(insertstatement, Con)
            cmd2.ExecuteNonQuery()
          con.Closed()
        End If

Open in new window


update query
 Dim Con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database Password=;Data Source = c:\wages\wages1.accdb")
        Con.Open()
        i = 0
        For Each row As DataGridViewRow In DataGridView1.Rows
            savedate = DataGridView1.Item(0, i).Value()
            'savedate = row.Item(0, i).Value()
            savestart = DataGridView1.Item(1, i).Value()
            saveend = DataGridView1.Item(2, i).Value()
            savestartl = DataGridView1.Item(3, i).Value()
            saveendl = DataGridView1.Item(4, i).Value()
            saveptop = DataGridView1.Item(5, i).Value()
            saveptou = DataGridView1.Item(6, i).Value()
            saveptod = DataGridView1.Item(7, i).Value()
            savecode = DataGridView1.Item(8, i).Value()
            ' WORKDATE ='" & savedate & "',
            updatestatement = "update HOURSWORKED set  STARTTIME =' " & savestart & "' ,  ENDTIME ='" & saveend & "' ,STARTLUNCH ='" & savestartl & "', ENDLUNCH ='" & saveendl & "' ,PTOPAID =' " & saveptop & "',PTOUNPAID ='" & saveptou & "',PTODETAIL ='" & saveptod & "' , SYMITARCODE = '" & savecode & "' where WORKDATE =  '" & savedate & " ' and EMPLOYESOCSEC = '" & TXBSOCSEC.Text & "' "
            Dim Command As New OleDbCommand(updatestatement, Con)
            Command.ExecuteNonQuery()
            Command.Parameters.Clear()
            i = i + 1
        Next
        Con.Close()

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Hmm. The INSERT statement you have there is for a single record at a time; therefore, this code is part of an outer loop, correct?  With the insertstatement outside your IF, is it possible that it is inserting a row when it should not be.  In other words, does the insert really belong under one of the IF conditions?

P.S. what SQL platform and version is the database?
EDIT: Never mind, I see now you said Access database above.  I will see if I can mock up a sample.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
javagairAuthor Commented:
the if is strictly to make sure the user hadn't already created that month.
thought about adding the date to the primary key so there could be no duplicates, still might try that just for fun.

thanks

gary
0
 
Kevin CrossChief Technology OfficerCommented:
I do not see the loop to create the days in this month.  Having the date as a part of the primary key will be helpful.  You probably will want date plus employee as the date itself is not unique if I understand your table design/intent.  However, it would be good to solve the code problem.  This definitely appears to be on the VB side.  If the "if" condition is to stop the INSERT for months you already created, then it would make sense for the INSERT code to be inside the THEN.  Otherwise, it will run your IF, but perform the INSERT anyway.  Maybe I am just misreading, but that is what it appears above.
0
 
javagairAuthor Commented:
if I change the primary key to be the date then the vb code sends a message that it can continue because that would create a duplicate entry which the database is denying.  So it creates the first 31 December entries and then tries to do 12/1/2013 again.
so it definitely is in the vb code.

gary
0
 
javagairAuthor Commented:
should have sent you all of the code that the button runs, you would have seen the mistake I made immediately, when you asked me about why the code was in an if loop I checked the loop.  In the else portion which I had cut and pasted which copies the database enteries just created to a datagridview was another copy of the insert statement that I didn't delete.  So without even seeing the code you managed to spot the strange if loop.

thanks

gary
0
 
javagairAuthor Commented:
sometime one needs someone to ask if you really intended to code that into the solution.  Second set of eyes always helps.

gary
0
 
Kevin CrossChief Technology OfficerCommented:
Here is a very simplistic example.

Dim startDt As Date = "2013-12-01"
Dim Con As OleDbConnection = New OleDbConnection("{connection string here}")
Dim DaD3 As OleDbDataAdapter = New OleDbDataAdapter("Select WORKDATE from HOURSWORKED where WORKDATE >= #" & startDt.ToString("yyyy-MM-dd") & "# AND WORKDATE < #" & startDt.AddMonths(1).ToString("yyyy-MM-dd") & "#", Con)
Dim Dst3 As DataSet = New DataSet
Con.Open()
DaD3.Fill(Dst3)

If Dst3.Tables(0) Is Nothing OrElse Dst3.Tables(0).Rows.Count = 0 Then
    'Add row for every day from start date to end of month
    For i = 0 To Date.DaysInMonth(startDt.Year, startDt.Month) - 1
        Dim insertstatement As String = "INSERT INTO HOURSWORKED(WORKDATE) VALUES(#" & startDt.AddDays(i).ToString("yyyy-MM-dd") & "#)"
        Dim cmd2 As New OleDbCommand(insertstatement, Con)
        cmd2.ExecuteNonQuery()
    Next
Else
    'Date range already in table, so add code here to do something different.
End If
Con.Close()

Open in new window


I hope this helps.  You can add more error checks and nice looking code.  For example, if user enters a start date that is not the beginning of the month, maybe you want loop to go from 1 to days in month and create date using year and month of start date and loop index as the day.

When I test, I get 31 rows for December only.
0
 
Kevin CrossChief Technology OfficerCommented:
I guess you already found what I meant.
I am glad I could help.

Best regards and happy coding,

Kevin
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now