Ado.net datasets

M

Mike

I'm working on an application that will add, update, and
delete movies.

I'm able to update or delete multiple records from the
dataset and then successfully update the "Movie" table.
However I can only insert one record at a time. If i add
two movie records to the dataset and then try to update
the "Movie" table I get a concurrency violation: the
update command affected zero records.

Any help would be appreciated

Code I currently have:


Private Sub Form1_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load


OpenFileDialog1.Filter = "Database files (*.MDB)
|*.MDB"
OpenFileDialog1.ShowDialog() 'display Open dialog
box
fileName = OpenFileDialog1.FileName

Try
'Try to connect with the movie database'
moviecnn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" &
fileName 'C:\vbnet03sbs\assn4\movie_db\movies.mdb"

moviecnn.Open()


'------------------Movieindex dataset sql
definitions------------------'
'Specify SelectCommand

movieindexadp.SelectCommand = New
OleDb.OleDbCommand( _
"SELECT Max (movie_id) " & _
"FROM Movie", moviecnn)
'movieindexadp.InsertCommand = New
OleDb.OleDbCommand( _
' "INSERT INTO Movie " & _
' "(movie_id" & _
' "VALUES (?)", moviecnn)
' movieindexadp.UpdateCommand = New
OleDb.OleDbCommand( _
' "movie_id = ?", moviecnn)
' movieindexadp.DeleteCommand = New
OleDb.OleDbCommand( _
' "DELETE FROM Movie" & _
' "Where movie_id = ?", moviecnn)

'Populate movieds
movieindexds.Clear()
movieindexadp.Fill(movieindexds, "Movie")

movIdx = movieindexds.Tables("Movie").Rows(0)
(0)

'------------------Movie dataset sql
definitions-----------------------'
'Specify SelectCommand, InsertCommand,
UpdateCommand,
'and DeleteCommand properties (movie data
adapter properties)

movieadp.SelectCommand = New
OleDb.OleDbCommand( _
"SELECT movie_id, title, movie_year,
category_id, director " & _
"FROM Movie", moviecnn)
movieadp.InsertCommand = New
OleDb.OleDbCommand( _
"INSERT INTO Movie " & _
"(movie_id, title, movie_year,
category_id, director) " & _
"VALUES (?, ?, ?, ?, ?)", moviecnn)
movieadp.UpdateCommand = New
OleDb.OleDbCommand( _
"Update Movie SET movie_id = ?, title = ?,
movie_year = ?, " & _
"category_id = ?, director = ?" & _
"Where movie_id = ?", moviecnn)

movieadp.DeleteCommand = New
OleDb.OleDbCommand( _
"DELETE FROM Movie " & _
"Where movie_id = ?", moviecnn)

'Specify parameters for DeleteCommand
movieadp.DeleteCommand.Parameters.Add
("@oldmovie_id", _
OleDb.OleDbType.Integer, 10, "movie_id"). _
SourceVersion = DataRowVersion.Original


'Specify parameters for InsertCommand
movieadp.InsertCommand.Parameters.Add
("@movie_id", _
OleDb.OleDbType.Integer, 10, "movie_id")
movieadp.InsertCommand.Parameters.Add
("@title", _
OleDb.OleDbType.Char, 69, "title")
movieadp.InsertCommand.Parameters.Add
("@movie_year", _
OleDb.OleDbType.Integer, 4, "movie_year")
movieadp.InsertCommand.Parameters.Add
("@category_id", _
OleDb.OleDbType.Integer,
3, "category_id")
movieadp.InsertCommand.Parameters.Add
("@director", _
OleDb.OleDbType.Char,
25, "director")

'Specify parameters for UpdateCommand
movieadp.UpdateCommand.Parameters.Add
("@movie_id", _
OleDb.OleDbType.Integer, 10, "movie_id")
movieadp.UpdateCommand.Parameters.Add
("@title", _
OleDb.OleDbType.Char, 69, "title")
movieadp.UpdateCommand.Parameters.Add
("@movie_year", _
OleDb.OleDbType.Integer, 4, "movie_year")
movieadp.UpdateCommand.Parameters.Add
("@category_id", _
OleDb.OleDbType.Integer, 3, "category_id")
movieadp.UpdateCommand.Parameters.Add
("@director", _
OleDb.OleDbType.Char, 25, "director")
movieadp.UpdateCommand.Parameters.Add
("@oldmovie_id", _
OleDb.OleDbType.Integer, 10, "movie_id").
_
SourceVersion = DataRowVersion.Original

'Specify parameters for DeleteCommand
'movieadp.DeleteCommand.Parameters.Add
("@oldmovie_id", _
' OleDb.OleDbType.Integer, 10, "movie_id").
_
' SourceVersion = DataRowVersion.Original

'Populate movieds
movieds.Clear()
movieadp.Fill(movieds, "Movie")
movieds1.Clear()
movieadp.Fill(movieds1, "Movie")

'Define a primary key for the Movie DataTable
movieds.Tables(0).PrimaryKey = _
New DataColumn() _
{movieds.Tables(0).Columns("movie_id")}



'------------Specify category dataset sql
defintions-----------------'
'Specify SelectCommand, InsertCommand,
UpdateCommand,
'and DeleteCommand properties (category data
adapter properties)
categoryadp.SelectCommand = New
OleDb.OleDbCommand( _
"SELECT category_id, category " &
_
"FROM Category", moviecnn)
categoryadp.InsertCommand = New
OleDb.OleDbCommand( _
"INSERT INTO Category " & _
"(category_id, category) " & _
"VALUES (?, ?)", moviecnn)
categoryadp.UpdateCommand = New
OleDb.OleDbCommand( _
"Update Category SET category_id = ?,
title = ?" & _
"WHERE category_id = ?", moviecnn)
categoryadp.DeleteCommand = New
OleDb.OleDbCommand( _
"DELETE FROM Category WHERE category_id
= ?", _
moviecnn)

'Specify parameters for InsertCommand
categoryadp.InsertCommand.Parameters.Add
("@category_id", _
OleDb.OleDbType.Integer, 3, "category_id")
categoryadp.InsertCommand.Parameters.Add
("@category", _
OleDb.OleDbType.Char, 11, "category")

'Specify parameters for UpdateCommand
categoryadp.UpdateCommand.Parameters.Add
("@category_id", _
OleDb.OleDbType.Integer, 3, "category_id")
categoryadp.UpdateCommand.Parameters.Add
("@category", _
OleDb.OleDbType.Char, 11, "category")
categoryadp.UpdateCommand.Parameters.Add
("@oldcategory_id", _
OleDb.OleDbType.Integer,
3, "category_id"). _
SourceVersion = DataRowVersion.Original

'Specify parameters for DeleteCommand
categoryadp.DeleteCommand.Parameters.Add
("@oldcategory", _
OleDb.OleDbType.Integer,
3, "category_id"). _
SourceVersion = DataRowVersion.Original

categoryds.Clear()
categoryadp.Fill(categoryds, "Category")

'Define a primary key for the category
DataTable
categoryds.Tables(0).PrimaryKey = _
New DataColumn() _
{categoryds.Tables(0).Columns("category_id")}

'Load categorybox
cbocategorybox.Items.Clear()
cbocategorybox.Items.Add("** All **")

Dim reader As OleDb.OleDbDataReader =
categoryadp.SelectCommand.ExecuteReader
Do While reader.Read()
cbocategorybox.Items.Add(reader.Item
("category"))
Loop

reader.Close()
cbocategorybox.Text = "** All **"

'Cleanup with conditional close;
If moviecnn.State = ConnectionState.Open Then
moviecnn.Close()
Else
MsgBox("Not able to close Database
connection at this time.")
End If

'Load available movie listbox
'Load movie array for search function

Load_Changes()

Catch exc1 As System.Data.OleDb.OleDbException

MsgBox("Wasn't able to connect. Make sure
the " & _
"database file is available and you have
a " & _
"connection string pointing at it." & _
exc1.ErrorCode & exc1.Message, , "Search
Movies")
strError = "State = " &
moviecnn.State.ToString()
MsgBox(strError, , "Search Movies")

Finally

End Try



End Sub
 
C

Cor

Hi Mike,

Have a look at:
OledbConnection
OledbCommand
OledbCommandbuilder
OledbAdapter.fill
OledbAdapter.Update

I think it will save you a lot of work seeing your code

I hope this helps?

Cor
 
A

Armin Zingler

Mike said:
I'm working on an application that will add, update, and
delete movies.

I'm able to update or delete multiple records from the
dataset and then successfully update the "Movie" table.
However I can only insert one record at a time. If i add
two movie records to the dataset and then try to update
the "Movie" table I get a concurrency violation: the
update command affected zero records.
Click to expand...

Seems to be a ADO.NET problem - and a question for
microsoft.public.dotnet.framework.adonet
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Custom Class with oledbcommand problem 5
Update command OledbDataAdapter problem 2
New columns and update problem...! 3
Problem in database updation through Adapter in VB.Net.. 1
Concurrency violation: The update command 0 rows affected 4
Error: Out of range value adjusted for column 'EngrAlertForNewQuestions' at row 1 1
Cannot do insert and delete 1
Error: ObjectDataSource 'ObjectDataSource1' could not find a non-generic method 'FillMenusByLocation 1

Top