[2005] Problem with SQL Update / Delete query
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.
vb Code:
'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
Re: [2005] Problem with SQL Update query
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.
Re: [2005] Problem with SQL Update query
Ok, thanks, just my bad, forgot to add this:
vb Code:
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:
vb 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
Re: [2005] Problem with SQL Update / Delete query
Can anyone tell me a solution for this problem?
Re: [2005] Problem with SQL Update / Delete query
vb Code:
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.