Operation must use an updateable query

[VIPER5646]

Hi all
I have multiple checkboxes to be used as the status of a certain order.
another words as the order is being processed the user will check the status and a date will be inserted into an access table.
Now my isue is when a checkbox is checked for the first time it inserts the date into the table but if the user unchecks it atomaticly updates the table with a null value with no errors. If the same checkbox gets checked again
or any othercheck box I get the error "Operation must use an updateable query".
Im puzzeled because if I use the same code to insert and also the same code to update does anyone know why it works the first time it is used but not the second time.

here is the Update Code.

Public Sub Update_Status()
        Try
            Dim db As New Connection
            db.FilePath = FrmMain.DBCredentials.DBPath
            db.PalavraPass = FrmMain.DBCredentials.Pass
            db.connect()
             Dim UpdatequeryString As String = "UPDATE Status_tbl SET " & Statustype & "= @1 WHERE CustomerID=" & customerID
            Dim dbcmd As New OleDbCommand(UpdatequeryString, db.con)
            db.con.Open()
            With dbcmd.Parameters
                If StatusDate.Year <> "1900" Then
                    .AddWithValue("@1", StatusDate)
                Else
                    .AddWithValue("@1", DBNull.Value)
                End If
            End With
            dbcmd.CommandType = CommandType.Text
            dbcmd.CommandText = UpdatequeryString
            dbcmd.ExecuteNonQuery()
            db.con.Close()
        Catch ex As Exception
            MsgBox("STATUS.Update_Status: " & ex.Message)
        End Try

    End Sub

[Reverend Jim]

Your update query string is

"UPDATE Status_tbl SET " & Statustype & "= @1 WHERE CustomerID=" & customerID

What is the name of the date field you want to update? You didn't specify the field name in the query. What is the purpose of that field? If it is to track the date of the last update of that record then you should never set it to NULL. You can specify the current date via the Access DATE() function. For example, to add a record you could do (using some made up field names)

"INSERT INTO mytable (lastname,firstname,thedate) VALUES('Nugent','Ted',DATE())"

and when you update a record you could do

"UPDATE mytable SET firstname='Tod', thedate=DATE() WHERE lastname='Nugent'"

Note - I am using non-parameterized queries here only for the sake of clarity.

[VIPER5646]

Thanks for your reply.

statustype will hold the name of the field name being updated.
Since there are 8 status to an order and not to creat a query for all of them I stored the field Name in the statustype that reflects the checkbox being checked .
So if the Hold checkbox is being checked or unchecked then the Statustype (field name) would be Hold.

[TnTinMN]

If StatusDate.Year <> "1900" Then

Assuming "StatusDate" is a DataTime structure, then the "Year" method should be returning an integer value and not a string. Put Option Strict On at the top of your code and see how many other issues the IDE flags for you.

I'm not saying this is the source of you current problem, but it is indicative of some sloppy coding.

Also, what is special about 1900? It is not the default year for an unset date value; date values initialize to 1/1/0001.

[VIPER5646]

Thanks for the input TnTinMN

[VIPER5646]

The

Option Strict On

did display other error.
but the problem still existes.

[TnTinMN]

The error may be in the checkbox_checked handler logic. You are obviously setting "Statustype" and "customerID" at some point in your code and then calling this method; are you sure that the values that exists when this method is call are correct and matches "Statustype" a field in the table?

Try adding this statement right after your Dim UpdatequeryString As String = statement to observe the query generated.

Debug.WriteLine(UpdatequeryString)

This will write to the Immediate Window (press: cntrl-Alt-I to open this window in VS).

[VIPER5646]

By adding the Debug.WriteLine(UpdatequeryString)the immediate window displays on the first Update
UPDATE Status_tbl SET tobedesign= @1 WHERE CustomerID=1724
the second update it displays the same string UPDATE Status_tbl SET tobedesign= @1 WHERE CustomerID=1724 but I also receive (A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll )