asked on

Problem with DataAdapter.Update(Dataset) method in vb.net

Dear sir

I am having some problem with OledbDataAdapter in VB.Net. I have a table
block(district_code,district_name,block_code,block_name)

Initially i made a simple form containing a datagrid which shows the 4 columns of the block table.
I made oledbdataconnection,oledbdataadapter(also generated dataset) using drag and drop tools.

When i see the code generated it goes like this

'OleDbConnection1
'
Me.OleDbConnection1.ConnectionString = "Provider=""MSDAORA.1"";User ID=election;Data Source=election;Password=election"
'
'OleDbDataAdapter1
'
Me.OleDbDataAdapter1.DeleteCommand = Me.OleDbDeleteCommand1
Me.OleDbDataAdapter1.InsertCommand = Me.OleDbInsertCommand1

and so on. After that

'OleDbInsertCommand1
'
Me.OleDbInsertCommand1.CommandText = "INSERT INTO BLOCK(DIST_CODE, DIST_NAME, BLOCK_CODE, BLOCK_NAME) VALUES (?, ?, ?, " & _
"?)"
Me.OleDbInsertCommand1.Connection = Me.OleDbConnection1
Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("DIST_CODE", System.Data.OleDb.OleDbType.VarChar, 2, "DIST_CODE"))
Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("DIST_NAME", System.Data.OleDb.OleDbType.VarChar, 15, "DIST_NAME"))
Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("BLOCK_CODE", System.Data.OleDb.OleDbType.VarChar, 2, "BLOCK_CODE"))
Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("BLOCK_NAME", System.Data.OleDb.OleDbType.VarChar, 15, "BLOCK_NAME"))

The in the form load i open the dataset using
OledbDataAdaptor(Fill,Dataset)

and when i have to insert,update or delete , I use
OledbDataAdaptor.update(Dataset)

when i use this , dataadapter calls the respective insert, update,delete command objects and the data is sent to database

My questions are
1. if i put a break on OledbDataAdaptor.update(Dataset) , i dont see vb calling the respective command objects. The how come they get executed.

2. How the parameter are passed to say oledbinsertcommand1 query. this object expects 4 parameters in my case.

3. My 3rd question is related ot 2nd question.

I am taking the oledbinsertcommand1 query inside a class, inside a function, and converting it to oracle stored procedure for inserting a new row.This stored procedure expects 4 parameters.
How can i ensure that when i call OledbDataAdaptor.update(Dataset), the 4 values will be copied to 4 parameters defined in stored procedure and the stored procedure will be called and a new row will be inserted.

I can show some code, how i am doing it , but its not working. This is the function in the class

Public Sub InsertBlock(ByVal pstrDistCode As String, ByVal pstrDistName As String, ByVal pstrBlockCode As String, ByVal pstrBlockName As String)

DAOneBlock.InsertCommand = CMDInsertBlocks

Try
With CMDInsertBlocks
.Connection = conDatabase
.CommandType = CommandType.StoredProcedure
.CommandText = "election.pkgBlocks.spInsertBlocks"
.Parameters.Add(New OleDbParameter("pDistCode", OleDbType.VarChar, 2)).Value = pstrDistCode
.Parameters.Add(New OleDbParameter("pDistName", OleDbType.VarChar, 15)).Value = pstrDistName
.Parameters.Add(New OleDbParameter("pBlockCode", OleDbType.VarChar, 2)).Value = pstrBlockCode
.Parameters.Add(New OleDbParameter("pBlockName", OleDbType.VarChar, 15)).Value = pstrBlockName
.Parameters(0).Direction = ParameterDirection.Input
.Parameters(1).Direction = ParameterDirection.Input
.Parameters(2).Direction = ParameterDirection.Input
.Parameters(3).Direction = ParameterDirection.Input
.ExecuteNonQuery()
End With
Catch ex As Exception
MsgBox(ex.Message)
Throw ex
End Try
End Sub

Also this is the code in which update method is called

Dim newBlock As DataRow
With objblocks
newBlock = .DSOneBlock.Tables(0).NewRow
newBlock("dist_code") = txtDistrictCode.Text
newBlock("dist_name") = txtDistrictName.Text
newBlock("block_code") = txtBlockCode.Text
newBlock("block_code") = txtBlockName.Text
.DSOneBlock.Tables(0).Rows.Add(newBlock)
.DSOneBlock.Tables(0).AcceptChanges()
.DAOneBlock.Update(objblocks.DSOneBlock)
End With

though this gives no sort of error yet no row is added to database

I know i have asked a lot of things at one go.

Any help

Thanks in advance

arif_eqbal

The Answer to all the three questions you asked is one
EVENTS

If you want to see how the DataAdapter is updating all your rows Handle the event RowUpdating / RowUpdated

eg.

AddHandler DataAdapter1.RowUpdated, AddressOf RowUpdated

then the function RowUpdated will be like

Private Sub RowUpdated(ByVal Sender As System.Object, ByVal e As System.Data.OleDb.OleDbRowUpdatedEventArgs)

End Sub

Here the argument e will have one member Row, this e.Row will be the Row being updated. Now this row will (in Update case) have two values for each Column, one called Original one called Current, in case of Insert it will just have one value for each column.

Your DataAdapter takes 4 arguments because you have 4 fields also because it is an Insert command if it were Update command it would have taken 8 parameters 4 for the modified value and 4 original value

So Now if you want to use a class to do the above operation, you need to pass appropraiate parameters to the class so that it is passed to the stored procedure.

It means if it is an Update operation you need to pass the old values available in Original of e.Rows accessed as e.Row.Item("Filed_Name", DataRowVersion.Original) and the current values accessible as e.Row.Item("Field_Name", DataRowVersion.Current)
so that the stored procedure compares the old value with existing data in Database finds the particular row and then assigns the new value and stores it.

rajneesh75

ASKER

Thanks for response

I wrote an event in class
Public Sub RowUpdating(ByVal Sender As System.Object, ByVal e As System.Data.OleDb.OleDbRowUpdatingEventArgs)

MsgBox("hello")
End Sub

and added the following statement in form_load

AddHandler objblocks.DAOneBlock.RowUpdating, AddressOf objblocks.RowUpdating

where objblocks is the object of the class. DAOneblock is the OledbDataAdaptor(defined in class) whose update method I will call when some button is clicked..

but when i call the update method , the RowUpdating event does not execute.

Another thing is that do I have to call the InsertBlock function in the class to insert a row or somehow the CMDInsertBlocks command object will be called to insert the row.

its getting a little messy i think. In traditional visual basic , we have to call the function in the class.
But this being vb.net, isn't the CMDInsertBlocks object will automatically be called, courtsy the following thing defined in class.

DAOneBlock.InsertCommand = CMDInsertBlocks

Help and more clarification sought

Thanks

arif_eqbal

First thing don't confuse your Classes like objblocks with VB.NET built in classes

Your classes are your way of handling the code so we will just talk of the ADO.NET classes.

DAOneBlock is your dataadpater, It is capable of generating its own InsertCommand so no need of DAOneBlock.InsertCommand = CMDInsertBlocks
However this is valid only if you are working on asingle table, i.e. your Datatable/dataset that will be updated through the dataAdapter should be dealing with only one table (No Join Queries). In case there's more than one table joined you need an explicit query and it should be assigned to the InsertCommand as you are doing.

So you check out whther you need an explicit InsertCommand or not.
In case its a simple single table situation this code will do, Put this code in the Class whose object is objblocks

Public Sub UpdateRecords
'Open a dataAdpater and pass a Select statement to it
Dim DA As OleDbDataAdapter = New OleDbDataAdapter("Select * from Your_Table", "Your_Connection_String")

'This will Handle the RowUpdated event
AddHandler DA.RowUpdated, AddressOf RowUpdated

'The CommandBulider will automatically generate InsertCommand for you if there is only one table in DataTable1
Dim CB As OleDbCommandBuilder = New OleDbCommandBuilder(DA)
DA.Update(DataTable1)
DataTable1.AcceptChanges()

End Sub

'The even Handler
Private Sub RowUpdated(ByVal Sender As System.Object, ByVal e As System.Data.OleDb.OleDbRowUpdatedEventArgs)
If e.Errors Is Nothing Then
MsgBox("No Errors")
'You can access the Row being Updated as
Dim Dr as dataRow=e.Row
Else
MsgBox(e.Errors.Message)
End If
End Sub

Put both these functions in your class then call objblocks.UpdateRecords

rajneesh75

ASKER

Thanks again

I have gone through your explaination carefully and again and again. I have got some answers but some more questions have also come up.

I have one table still i will be using explicit queries(infact stored procedures) and not using
OleDbCommandBuilder.

I am pasting my code .Kindly go though and tell if i am doing the correct thing and going in correct direction.

This is class code

Imports System.Data.OleDb
Public Class clsBlock

Public CMDFetchBlocks As New OleDbCommand
Public CMDInsertBlocks As New OleDbCommand
Public CMDUpdateBlocks As New OleDbCommand
Public DABlocks As New OleDbDataAdapter
Public DSBlocks As New DataSet

Sub New()
MyBase.new()
OpenConnection("election", "election")
AddHandler DABlocks.RowUpdating, AddressOf RowUpdating
FetchBlocks()
End Sub

Public Sub OpenConnection(ByVal strUsername As String, ByVal strPassword As String)
Try
conDatabase = New OleDbConnection
conDatabase.ConnectionString = "Provider=MSDAORA.1;user id=" & strUsername & ";data source=election;password=" & strPassword
conDatabase.Open()
Catch ex As Exception
MsgBox(ex.Message)
Throw ex
End Try
End Sub

Public Sub FetchBlocks()
DABlocks.SelectCommand = CMDFetchBlocks
Try
With CMDFetchBlocks
.Connection = conDatabase
.CommandType = CommandType.StoredProcedure
.CommandText = "election.pkgBlocks.spFetchBlocks({resultSet 99,pDistCode,pDistName,pBlockCode,pBlockName})"
.ExecuteScalar()
End With
Catch ex As Exception
MsgBox(ex.Message)
Throw ex
End Try
End Sub

Public Sub UpdateRecords()
DABlocks.InsertCommand = CMDInsertBlocks
DABlocks.Update(DSBlocks)
End Sub

Private Sub RowUpdating(ByVal Sender As System.Object, ByVal e As System.Data.OleDb.OleDbRowUpdatingEventArgs)

If e.Row.RowState = DataRowState.Added Then
Try
With CMDInsertBlocks
.Connection = conDatabase
.CommandType = CommandType.StoredProcedure
.CommandText = "election.pkgBlocks.spInsertBlocks"
.Parameters.Add(New OleDbParameter("pDistCode", OleDbType.VarChar, 2)).Value = e.Row.Item("pDistCode")
.Parameters.Add(New OleDbParameter("pDistName", OleDbType.VarChar, 15)).Value = e.Row.Item("pDistName")
.Parameters.Add(New OleDbParameter("pBlockCode", OleDbType.VarChar, 2)).Value = e.Row.Item("pBlockCode")
.Parameters.Add(New OleDbParameter("pBlockName", OleDbType.VarChar, 15)).Value = e.Row.Item("pBlockName")
.Parameters(0).Direction = ParameterDirection.Input
.Parameters(1).Direction = ParameterDirection.Input
.Parameters(2).Direction = ParameterDirection.Input
.Parameters(3).Direction = ParameterDirection.Input
.ExecuteNonQuery()
End With
Catch ex As Exception
MsgBox(ex.Message)
Throw ex
End Try
End If

If e.Row.RowState = DataRowState.Modified Then
'stored procedure for modifying which will use datarow versions
End If

If e.Row.RowState = DataRowState.Deleted Then
'stored procedure for deleting
End If
End Sub
End Class

and the form code is

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
objBlock = New clsBlock
objBlock.DABlocks.Fill(objBlock.DSBlocks)
DataGrid1.DataSource = objBlock.DSBlocks
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
objBlock.UpdateRecords()
End Sub

When i run the form and add a new row to datagrid(and not modify or delete any row), i check the execution of code. I call

objBlock.UpdateRecords()
which calls
DABlocks.Update(DSBlocks)
which calls
RowUpdating event

The stored procedure gets executed, a new row in inserted but then a run time error occurs.

Help required again
Thanks

Thanks again

arif_eqbal

What's the run time Error ???

One thing That I notice here is in Your RowUpdating event Handler since you are using Explicit queries I think You'll need to cancel the implicit one.

I am not sure though, may be you'll need to Add one more line at the end of your RowUpdating event

e.Status = UpdateStatus.SkipCurrentRow

Just check it out I am not having .NET installed here so I can't check....

arif_eqbal

I hope you understand what I mean ROWUPDATING is fired just before the row is going to be updated to the database. Now since you do it explicitly using a Command object after your code is executed the dataAdapter whose RowUpdating event you have handled will try to update the same row so you need to cancel it

so SkipCurrentRow

rajneesh75

ASKER

Hi arif_eqbal

Thanks for your excellent support . I am able to insert, modify,delete from the table using my functions and stored procedures . The run time error(though it was general kind of error, not specific) is gone after i did
e.Status = UpdateStatus.SkipCurrentRow
and whole thing is running smoothly.
Before i close the question and award you full points , there are one two small things i would like to know.

1. The RowUpdating event is firing only for new, updated and deleted rows only and not for all rows. How it detects which rows needs to be updated.
2. I notice that that i need not give these statements

DABlocks.InsertCommand = CMDInsertBlocks
DABlocks.UpdateCommand = CMDUpdateBlocks
DABlocks.DeleteCommand = CMDDeleteBlocks

still the relevant command objects run. Though
DABlocks.SelectCommand = CMDFetchBlocks
is required. Is it true
3. RowUpdated event is not firing , I have added the event handler in constructor

Sub New()
MyBase.new()
OpenConnection("election", "election")
DABlocks = New OleDbDataAdapter
AddHandler DABlocks.RowUpdating, AddressOf RowUpdating
AddHandler DABlocks.RowUpdated, AddressOf RowUpdated
FetchBlocks()
End Sub

4. and what do you mean by "One thing That I notice here is in Your RowUpdating event Handler since you are using Explicit queries I think You'll need to cancel the implicit one."
How this is done. and is there any need to do so.

Thanks again for your excellent support. Without your guidance i would not have got an insight to the working of ado.net.I look forward to support from you in future.

ASKER CERTIFIED SOLUTION

arif_eqbal

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rajneesh75

ASKER

Thanks very much for your support