update query destroyes table

the following update query wipes out all data between the two dates instead of updating the rows with new data from the datagridview  
      i = 0
      For Each row As DataGridViewRow In DataGridView1.Rows
            updatestatement = "update HOURSWORKED set WORKDATE ='" & DataGridView1.Item(0, i).Value() & "', STARTTIME =' " & DataGridView1.Item(1, i).Value() & "' ,  ENDTIME ='" & DataGridView1.Item(2, i).Value() & "' ,STARTLUNCH ='" & DataGridView1.Item(3, i).Value() & "', ENDLUNCH ='" & DataGridView1.Item(4, i).Value() & "' ,PTOPAID =' " & DataGridView1.Item(5, i).Value() & "',PTOUNPAID ='" & DataGridView1.Item(6, i).Value() & "',PTODETAIL ='" & DataGridView1.Item(7, i).Value() & "' , SYMITARCODE = '" & DataGridView1.Item(8, i).Value() & "' where WORKDATE >=  '" & DateString & " '  or WORKDATE <=  '" & datestring2 & " '  and EMPLOYESOCSEC = '" & TXBSOCSEC.Text & "' "
            Dim Command As New OleDbCommand(updatestatement, Con)
            Command.ExecuteNonQuery()
            Command.Parameters.Clear()
            i = i + 1
        Next

something I do not see going on in the update query??????

thanks

gary
javagairAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
13ShadowConnect With a Mentor Commented:
Yes we got rid of the counter (i) It should be row.cells(0).value()
0
 
javagairAuthor Commented:
the reason I am looping through the data is that the datagridview only shows half of the columns in the table and I only want to update the ones that are shown

gary
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
where WORKDATE >=  '" & DateString & " '  or WORKDATE <=  '" & datestring2 & " '  and EMPLOYESOCSEC = '" & TXBSOCSEC.Text & "' "

the OR without () around it seems to be the culprit.
either it needs to be AND (instead of OR), or you need to put () around:

where ( WORKDATE >=  '" & DateString & " '  or WORKDATE <=  '" & datestring2 & " '  ) and EMPLOYESOCSEC = '" & TXBSOCSEC.Text & "' "
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
13ShadowCommented:
Reference the values of the row. You are looping through each row so reference the row directly:
Instead of DataGridView1.Item(0, i).Value() use:  row.Item(0, i).Value() so you make sure you are accessing the current row you are processing.


And if your where clause is supposed to be a date range you need to use AND instead of OR:
 For Each row As DataGridViewRow In DataGridView1.Rows
            updatestatement = "update HOURSWORKED set WORKDATE ='" & row.Item(0, i).Value() & "', STARTTIME =' " & row.Item(1, i).Value() & "' ,  ENDTIME ='" & row.Item(2, i).Value() & "' ,STARTLUNCH ='" & row.Item(3, i).Value() & "', ENDLUNCH ='" & row.Item(4, i).Value() & "' ,PTOPAID =' " & row.Item(5, i).Value() & "',PTOUNPAID ='" & row.Item(6, i).Value() & "',PTODETAIL ='" & row.Item(7, i).Value() & "' , SYMITARCODE = '" & row.Item(8, i).Value() & "' where WORKDATE >=  '" & DateString & " '  AND  WORKDATE <=  '" & datestring2 & " '  and EMPLOYESOCSEC = '" & TXBSOCSEC.Text & "' "
            Dim Command As New OleDbCommand(updatestatement, Con)
            Command.ExecuteNonQuery()
            Command.Parameters.Clear()
          Next

For example:
where WORKDATE >=  '01/01/2013'  AND  WORKDATE <=  '01/31/2013'  will give you any records with a workdate for the month of January
where WORKDATE >=  '01/01/2013'  OR WORKDATE <=  '01/31/2013' will give you any records with a workdate greater than or equal to 01/01/2013 which would be any records for the entire year.
0
 
javagairAuthor Commented:
think I was smoking something when I made the query!!!!
update one row at a time but telling it to look at 31 days.  When I changed the where to look at one row at a time it updates just fine.
0
 
javagairAuthor Commented:
135shadow

when I just put the row.item(0,i).value()
imeadiatly get a message that item is not a member of datagridview

gary
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.