Solved

extra row added to database for insert or update query

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

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Object to array 7 28
Paging GridView 7 34
Showdialog 8 21
VB.Net - ReportViewer Windows Form many reports 2 8
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now