Solved

extra row added to database for insert or update query

Posted on 2013-12-31
10
376 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

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

Question has a verified solution.

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

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

820 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