[2005] Problem with SQL Update / Delete query

[maco]

Hi!

I'm using the following code to update my table, it reports no error but the data isnt being updated. I have no clue what I'm doing wrong here so all suggestions are in place.

'Open connection
            Dim DBPath As String
            DBPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + "/movies.mdb"
            cn = New OleDbConnection(DBPath)
            cn.Open()
 
 
            str = "UPDATE moviedata SET Length = '" & Length.Text & "', Actors = '" & Actor.Text & "', Description = '" & Descrip.Text & "', Rating = '" & Rating.SelectedItem & "', Genre = '" & Genre.SelectedItem & "', Location = '" & FileLocation.Text & "' WHERE Title='" & Title.Text & "';"
 
            cn.Close()
 
            MsgBox("OK")
 
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
 
        End Try

[jmcilhinney]

All you've done is open a connection and create a string. That's all. You have not sent any data over that connection in either direction. Take a look at the OleDbCommand class and its ExecuteNonQuery method. That's how you execute the SQL code you have created.

[maco]

Ok, thanks, just my bad, forgot to add this:

cmd = New OleDbCommand(str, cn)
                icount = cmd.ExecuteNonQuery

The UPDATE command is working now. But I have the same problem with the DELETE command, it reports no error, but the command goes through, but the record still remains?

Code:

Try
 
                'Open connection
                Dim DBPath As String
                DBPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + "/movies.mdb"
                cn = New OleDbConnection(DBPath)
                cn.Open()
 
 
                str = "DELETE FROM moviedata WHERE Title='" & SelectMovieMenu.SelectedIndex & "';"
                cmd = New OleDbCommand(str, cn)
                icount = cmd.ExecuteNonQuery
 
 
                cn.Close()
 
                'IO.File.Delete(Application.StartupPath + "/images/" + Title.Text)
                Me.MoviedataTableAdapter.Fill(Me.MoviesDataSet.moviedata)
                MsgBox("The movie has been deleted!")
 
            Catch ex As Exception
                MessageBox.Show(ex.ToString())
 
            End Try

[maco]

Can anyone tell me a solution for this problem?

[jmcilhinney]

str = "DELETE FROM moviedata WHERE Title='" & SelectMovieMenu.SelectedIndex & "';"

SelectedIndex is a number. I doubt the name of your movie is a number.

If something doesn't work the way you expect you need to debug. That does NOT mean just look at your code and see if you can see something that doesn't look right. Just like a mechanic would start a car's engine and watch it run, you need to start your app and watch it run. You should place a break point at the top of that code and then step through it line by line, testing the values of variables at each step. If you'd done that you'd have immediately seen that the SQL code you were executing was not what you intended. Use F9 to set a break point and F10 and F11 to step through your code. I suggest you go to the MSDM library and read about debugging too.