Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 183
  • Last Modified:

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
0
javagair
Asked:
javagair
  • 3
  • 2
1 Solution
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
13ShadowCommented:
Yes we got rid of the counter (i) It should be row.cells(0).value()
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now