Solved

extra row added to database for insert or update query

Posted on 2013-12-31
10
372 Views
Last Modified: 2013-12-31
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
Comment
Question by:javagair
  • 5
  • 5
10 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39748845
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
 

Author Comment

by:javagair
ID: 39748900
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
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 39748920
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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:javagair
ID: 39748952
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39748962
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
 

Author Comment

by:javagair
ID: 39748963
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
 

Author Comment

by:javagair
ID: 39748987
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
 

Author Closing Comment

by:javagair
ID: 39748991
sometime one needs someone to ask if you really intended to code that into the solution.  Second set of eyes always helps.

gary
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39748998
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39748999
I guess you already found what I meant.
I am glad I could help.

Best regards and happy coding,

Kevin
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question