Delete query not working

[Chris]

Hello again

I have yet another problem, this time with a delete query.

Dim KeyAdapter
Set KeyAdapter = XDocument.DataAdapters("tblPHOTO_PHOTO_KEYWORDS")
KeyAdapter.Command = "DELETE FROM tblPHOTO_PHOTO_KEYWORDS WHERE
PPK_photo_id = 10 AND PPK_keyword_id = 10846"
KeyAdapter.Query

When running that code, I don't get an error, but the record doesn't
get deleted. If I execute the same SQL from inside Access it works
fine, so the syntax is correct. I'm sure there are some details I've
forgotten, but I can't think of any.

Thanks again
Chris

 

[Chris]

OK, I've changed over to .net code and currently have this:

Dim connRemove As New Data.OleDb.OleDbConnection(ConnectString)
Dim cmdRemove As New Data.OleDb.OleDbCommand("DELETE FROM
tblPHOTO_PHOTO_KEYWORDS WHERE PPK_photo_id = 10 AND PPK_keyword_id =
10846", connRemove)
connRemove.Open()
cmdRemove.ExecuteNonQuery()
connRemove.Close()
CType(thisXDocument.DataAdapters("tblPHOTO_PHOTO_KEYWORDS"),
ADOAdapterObject).Query()

I have a list box showing the various keywords, and the selected
keyword (ID 10846 in this example) is deleted when the user clicks a
button to run the code. That works, the keyword gets removed from the
database. However, the list box doesn't update properly. Instead of the
keyword (eg. "Hydro") disappearing, it just changes to its ID number
(eg. "10846") until the box is reloaded.

For example, I start off with a list box containing "Flooding, Hydro,
River". I select Hydro and push the Delete button and it changes to
"Flooding, 10846, River". If I then reload the page or even click in
the list box, it correctly updates to "Flooding, River".

Can anyone tell me why this is happening? I suspect that you'll need
more information in order to figure out what's happening, so ask away


Thanks
Chris

 

[S.Y.M. Wong-A-Ton]

While you have deleted the item from the database, you didn't clear the value
that was selected by the user and that InfoPath stored in the field for the
drop-down list box. This value doesn't get cleared automatically when the
data source the drop-down is bound to changes. So setting this field's value
to an empty string after you do the delete should solve the problem.

 

[Chris]

Yep, that did it

Thanks!