Update statement keeps creating a new row

[Patty05]

I am trying to update a row in a database that already holds data but instead, my code just creates a new row each time. Does the SELECT statement determine what will update? This code does not run but if I replace the SELECT statement with SELECT *, it will update the database but only create a new row with the updates.

All I am looking to do is to update one field name OutTime in a row
WHERE EmpNum = globalEmpNum AND TodayD = Today.

TodayD is of type date in the database.

Thanks for any help!

[SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] globalDeptNum = 4500 [/SIZE][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'Sales Department[/COLOR][/SIZE]
[SIZE=2]conn = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0; data source=c:\PR\Payroll.mdb")[/SIZE]

 
[SIZE=2]Dim[SIZE=2] sql [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = "SELECT * FROM Hours WHERE [EmpNum] = '"[/SIZE]
[SIZE=2]sql = sql & globalEmpNum[/SIZE]
[SIZE=2]sql = sql & "' AND [TodayD] = '#"[/SIZE]
[SIZE=2]sql = sql & Today()[/SIZE]
[SIZE=2]sql = sql & "#'"[/SIZE]
[/SIZE]

 
[SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]
 
[SIZE=2][COLOR=#008000]da = New OleDbDataAdapter(sql, conn)[/COLOR][/SIZE]
[SIZE=2]myDataTable = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataTable[/SIZE]
[SIZE=2]da.Fill(myDataTable)[/SIZE]
[SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception[/SIZE]
[SIZE=2]Console.WriteLine("Error Opening {0}", conn.DataSource)[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]
 
[SIZE=2]myCommand = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbCommandBuilder(da)[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] rowArticle [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataRow = myDataTable.NewRow()[/SIZE]
[SIZE=2]rowArticle("OutTime") = TimeOfDay()[/SIZE]
 
[SIZE=2]myDataTable.Rows.Add(rowArticle)[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] MyUpdateCommand [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDbCommand[/SIZE]
[SIZE=2]MyUpdateCommand = myCommand.GetUpdateCommand[/SIZE]
[SIZE=2]da.UpdateCommand = MyUpdateCommand[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]
[SIZE=2]da.Update(myDataTable)[/SIZE]
[SIZE=2]MsgBox("Successfully punched OUT at " & TimeOfDay())[/SIZE]
[SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] Ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception[/SIZE]
[SIZE=2]MessageBox.Show(Ex.Message)[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE]

 

[Administrator]

Dim rowArticle As DataRow = myDataTable.NewRow()
rowArticle("OutTime") = TimeOfDay()

myDataTable.Rows.Add(rowArticle)

This code ADDS a new row to your table.

 

[Patty05]

Thanks Neal...
but how do I update the row instead of adding a new row?

 

[Administrator]

See these two resources:

.NET How-To's
101 Samples

 

[zekeman]

This is DAO way :
With rstMaster
.Edit()
.Fields("ArchiveDate").Value = Today
.Update()
End With

 

[Patty05]

Thanks Zekeman! I would love for this to be such simple code like that!!!!
However, I can't figure out the ODE way. I found a way to capture one row but I can't figure out the syntax on how to change the value.
I am getting the blue wavy line under TimeofDay() and it reads Value of type ‘date’ cannot be converted to System.Data.DataRow

Can someone help me with this please?


If globalDeptNum = 4500 Then 'Sales Department
conn = New OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0; data source=c:\PR\Payroll.mdb")
Try
da = New OleDbDataAdapter("SELECT * FROM Hours WHERE [EmpNum] = '" & globalEmpNum & "'", conn)


myDataTable = New DataTable
da.Fill(myDataSet, "Hours")

Catch ex As Exception
Console.WriteLine("Error Opening {0}", conn.DataSource)
End Try

myCommand = New OleDbCommandBuilder(da)

Dim foundRows() As Data.DataRow
foundRows = myDataSet.Tables("Hours").Select("TodayD Like '08/31/2006' AND EmpNum Like globalEmpNum ")



‘NOT SURE HOW TO ACCESS THE foundRows
‘Value of type ‘date’ cannot be converted to System.Data.DataRow
foundRows("OutTime") = TimeOfDay()



Try
da.Update(myDataSet, ”Hours”)
MsgBox("Successfully punched OUT at " & TimeOfDay())
Catch Ex As Exception
MessageBox.Show(Ex.Message)
End Try
End If

 

[vis781]

In your case, found rows is a 1-Dimensional array of datarow's. You you have to declare what value in the array you want to use..

FoundRows(0).Item("OutTime") = TimeOfDay

 

[Patty05]

vis781, works like a charm now. It is so easy to understand once I see how it is done correctly. I guess I just don't completly understand datarows and datases and all the choices that come with each. I just need to work with databases more.

Thank you once again for setting me straight!