[RESOLVED] OleDB updating multiple records with calculated values

[DigitalDesignz]

Hello,

I'm writing a time entry program to record when an employee checks in and when they check out. I'm struggling on the part where I need to calculate a time span (time out - time in). I am selecting the In Time, Out Time, and Total columns. I want it to take the Out Time subtract it from the In Time and place that value in the Total column. Here is the code I have so far and have tried several variations of different updates but have had no luck. Where am I going wrong?

    Public Sub CalculateTotals()
        Dim mydataset As New DataSet
        Dim mytable As New DataTable
        Dim myrow As DataRow
        Dim combuilder As New OleDbCommandBuilder
        Dim Con As New OleDbConnection(My.Settings.TimeEntryConnectionString)
        Dim DaAd As New OleDbDataAdapter

        DaAd.SelectCommand = New OleDbCommand("SELECT [ID],[In Time],[Out Time],[Total] FROM [" & cmbTables.Text & "] WHERE ((([In Time]) Is Not Null) AND (([Out Time]) Is Not Null) AND (([Total]) Is Null))", Con)
        combuilder = New OleDbCommandBuilder(DaAd)
        combuilder.GetUpdateCommand()
        Con.Open()
        DaAd.Fill(mydataset, cmbTables.Text)
        mytable = mydataset.Tables(cmbTables.Text)

        Try

            For Each myrow In mytable.Rows
                'calculate totals
                Dim time1 As DateTime
                Dim time2 As DateTime

                time1 = DateTime.Parse(myrow.Item("In Time"))
                time2 = DateTime.Parse(myrow.Item("Out Time"))

                Dim ts As TimeSpan = time2 - time1
                myrow.Item("Total") = ts.TotalHours.ToString

            Next

            'just using this to see if the timespan is calculating correctly and it is
            DataGridView1.DataSource = mytable

            mytable.GetChanges()
            DaAd.Update(mydataset, cmbTables.Text)
            Con.Close()

        Catch ex As OleDbException
            MsgBox(ex.Message & vbCrLf & ex.Source)
        End Try

    End Sub

In Time and Out Time are formatted like so: "hh:mm" (ex: 14:30) and the Total column is a total amount of hours.


I keep getting a syntax error in the UPDATE statment. I'm not very fluent with OleDB yet and I'm not sure if I'm doing the update correctly. I have no problem with OleDB and single UPDATES or single field updates but when I am updating multiple rows with different values, I'm lost.

Any suggestions are more than welcome!

Thanks!

[Shaggy Hiker]

This line is not necessary:

combuilder.GetUpdateCommand()

nor is this one:

mytable.GetChanges()

Though neither one hurts anything. Both do nothing the way they are being used.

What is the error you are getting?

[DigitalDesignz]

Thanks for the info Shaggy.

The error is "Syntax error in UPDATE statement"

I am assuming it has something to do with the way I have it working with the Access database but I'm not quite sure what...

[wes4dbt]

Is that all the error says?

[Shaggy Hiker]

That can't be the whole error. It has to say more than that or there is nothing to go on. However, I will say that the rest of it may not have been comprehensible to you. There is an issue that I have run into a few times with UPDATE commands generated by commandbuilder objects. These issues are rare enough that I often overlook them, but the solution can be maddening. I even have a couple threads on here where I was studying one of them and getting nowhere. In one case, the error went away and couldn't be brought back, though I undid the change that had caused it to go away.

I say this not to discourage you, but to state that this one could prove to be not so simple, but it is ultimately solvable. At the very worst, you can write your own UPDATE command and supply it rather than using the one created by the CommandBuilder. It's not terribly hard, but it is something you want to avoid, if you can. The key is the rest of the error message.

One thing you can do is use that GetUpdateCommand to look at the query being produced by the CommandBuilder. To do that, set a breakpoint somewhere, such as on the .Fill or .Update lines. When you hit that, highlight the combuilder and press Shift+F9. That won't show you much, but in the textbox at the top you will see combuilder, which you want to edit to say:

combuilder.GetUpdateCommand.CommandText

Then hit the refresh button, and you should have the SQL query being generated by the CommandBuilder. It's worth taking a look at, as they can be rather shockingly ugly.

[wes4dbt]

Also, what is the database field type of "Total", is it TimeSpan?

Dim ts As TimeSpan = time2 - time1

Do you have "Option Strict" set to On? This might help you fine your problem.

[Shaggy Hiker]

Even if Option Strict doesn't help with this particular issue, it's still a good idea.

[DigitalDesignz]

I get this in the output window: A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Other than that, I see nothing.

This is what the output for the update command looks like:

UPDATE 5-21-2012 SET In Time = ?, Out Time = ?, Total = ? WHERE ((ID = ?) AND ((? = 1 AND In Time IS NULL) OR (In Time = ?)) AND ((? = 1 AND Out Time IS NULL) OR (Out Time = ?)) AND ((? = 1 AND Total IS NULL) OR (Total = ?)))

[DigitalDesignz]

I turned Option Strict on, went through updated all the functions and strings ect. but still the same error.

The Total column is formatted as Text (VARCHAR)

Thanks for the help so far!

[Shaggy Hiker]

Is that really the SQL? For instance, do you really see: SET In Time = ? rather than SET [In Time] = ?

After all, without those square brackets, the SQL is incorrect. You have the square brackets in your SELECT statement, so I'd be amazed if they were coming across incorrectly, but that's what it looks like.

Rather than the output window, since you are putting up ex.Message in a messagebox, what is the message there?

[DigitalDesignz]

Yep, that is copied straight from the output.

The ex.Message is "Syntax error in UPDATE statement"

[Shaggy Hiker]

Well, then that is the problem, and it's amazing. Without the square brackets, SET In Time is as far as the SQL engine will be able to parse that SQL, because In is a keyword that will be out of place and that'll cause an error right away.

I really despise using spaces in field names in databases. If you have the ability to remove them, it will make your life MUCH easier in the long run. However, if it is already too late for that, then you won't be able to use CommandBuilder to generate the UPDATE query (and probably not INSERT queries, either), because it is generating them incorrectly. You will have to create your own UPDATE and INSERT commands and supply them to the dataadapter.

[DigitalDesignz]

Ahh, I hadn't thought of that one. I went through the list of words not to use with Access and completely forgot about the "In"

However, I replaced "In Time" and "Out Time" with "InTime" and "OutTime" respectively and still get the same error.

Here is what the UPDATE statement looks like now:

UPDATE 5-21-2012 SET InTime = ?, OutTime = ?, Total = ? WHERE ((ID = ?) AND ((? = 1 AND InTime IS NULL) OR (InTime = ?)) AND ((? = 1 AND OutTime IS NULL) OR (OutTime = ?)) AND ((? = 1 AND Total IS NULL) OR (Total = ?)))

I'm not sure where the "? = 1 AND" portion is coming from in the WHERE clause. Any ideas? If there is an easier or better way to be doing these updates, I am all for trying it.

I know access requires brackets around the table name since it has dashes in it, so I'm not sure if that is where the problem lays now or not...

Thanks again for the help so far!

[Shaggy Hiker]

Here's an example of using a CommandBuilder to create the Insert and Delete commands, but supplying a handwritten UPDATE command. It is for SQL Server, rather than Access, so there are named parameters in the query, but the same principle applies. You would have to replace the parameter names in the query string with ?, which is what you have seen in the UPDATE query produced by the Command Builder.

da.SelectCommand = cmd
cmd.CommandText = mDBTTest.GetSelectClause("HATCH_RearingUnit", False) & " FROM HATCH_RearingUnit"
Dim cb As New SqlClient.SqlCommandBuilder(da)
da.UpdateCommand = cmdU
da.UpdateCommand.CommandText = "UPDATE [HATCH_RearingUnit] SET [RearingUnit] = @p3, [RUBaseID] = @p4, [UpstreamID] = @p7, [DownstreamID] = @p8, [CreationEventID] = @p9, [TerminationEventID] = @p10, [Uploaded] = @p13, [UploadDate] = @p14, [PercentLength] = @p17, [TestMode] = @p18,[Width] = @p20 WHERE [PKID] = @p1"
                            cmdU.Parameters.Add("@p1", System.Data.SqlDbType.UniqueIdentifier, 16, "PKID")
                            cmdU.Parameters.Add("@p3", System.Data.SqlDbType.NVarChar, 400, "RearingUnit")
                            cmdU.Parameters.Add("@p4", System.Data.SqlDbType.UniqueIdentifier, 16, "RUBaseID")
                            cmdU.Parameters.Add("@p7", System.Data.SqlDbType.UniqueIdentifier, 16, "UpstreamID")
                            cmdU.Parameters.Add("@p8", System.Data.SqlDbType.UniqueIdentifier, 16, "DownstreamID")
                            cmdU.Parameters.Add("@p9", System.Data.SqlDbType.UniqueIdentifier, 16, "CreationEventID")
                            cmdU.Parameters.Add("@p10", System.Data.SqlDbType.UniqueIdentifier, 16, "TerminationEventID")
                            cmdU.Parameters.Add("@p13", System.Data.SqlDbType.Bit, 1, "Uploaded")
                            cmdU.Parameters.Add("@p14", System.Data.SqlDbType.DateTime, 8, "UploadDate")
                            cmdU.Parameters.Add("@p17", System.Data.SqlDbType.Int, 4, "PercentLength")
                            cmdU.Parameters.Add("@p18", System.Data.SqlDbType.Bit, 1, "TestMode")
                            cmdU.Parameters.Add("@p20", System.Data.SqlDbType.Float, 8, "Width")
                            da.Update(mDS, "RearingUnit")

It's a bit different, as I am using Add, which requires types, rather than AddWithValue. I'm kind of thinking that I could have used AddWithValue, but I forget the context, by now. Add requires more fields than AddWithValue.

I see two oddities in the UPDATE query the CommandBuilder created for you:

1) ? = 1. I don't see anything like that in the SELECT statement, so I'm not sure where this came from.

2) UPDATE 5-21-2012. Is your table name really a date?

[DigitalDesignz]

Thanks for the example, Shaggy.

I had initially thought of supplying my own UPDATE command but I wasn't sure about putting it into a loop. I read that it probably isn't the best idea. What are your guys' thoughts on the subject?

I'm still at a loss on the ? = 1 .... I have no idea where it is pulling that from.

As for the table name, yes it is a date. This is a time entry program, so it creates a table for the Monday of the current week. The table name is named for Monday's date because that is how the time will be put into the system. So when the first person checks in for the week, it checks to see if there has been a table created for this Monday's date (even if the employee is checking in on Wednesday because Monday and Tuesday were holidays for instance), if there hasn't it creates it and every other person that checks in/out for that week gets their time recorded in this table. Not sure if that makes any sense or not...

[Shaggy Hiker]

I personally feel that the CommandBuilder has a bug that causes it to occasionally create invalid UPDATE commands. That's the only way I can explain some of the behavior I have seen.

[DigitalDesignz]

Ok, got it working with a completely different method. Just did a function in the query.

    Public Sub CalculateTotals()
        Dim Cmd As OleDbCommand
        Dim SQL As String
        Dim Con = New OleDbConnection(My.Settings.TimeEntryConnectionString)

        Con.Open()
        Try
            SQL = "UPDATE [" & cmbTables.Text & "] SET [Total] = DateDiff(""n"",[InTime],[OutTime])/60 WHERE ((([OutTime])<>""00:00"") AND (([Total]) Is Null))"
            Cmd = New OleDbCommand(SQL, Con)
            Cmd.ExecuteNonQuery()

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        Con.Close()
    End Sub

Thanks for all the help!