?
Solved

extra row added to database for insert or update query

Posted on 2013-12-31
10
Medium Priority
?
388 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 60

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 60

Accepted Solution

by:
Kevin Cross earned 2000 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
Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

 

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 60

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 60

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 60

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

AWS Certified Solutions Architect - Associate

This course has been developed to provide you with the requisite knowledge to not only pass the AWS CSA certification exam but also gain the hands-on experience required to become a qualified AWS Solutions architect working in a real-world environment.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

719 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