Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 784
  • Last Modified:

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


0
rajneesh75
Asked:
rajneesh75
  • 5
  • 4
1 Solution
 
arif_eqbalCommented:
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.


0
 
rajneesh75Author Commented:
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
0
 
arif_eqbalCommented:
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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
rajneesh75Author Commented:
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

0
 
arif_eqbalCommented:
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....


0
 
arif_eqbalCommented:
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
0
 
rajneesh75Author Commented:
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.
0
 
arif_eqbalCommented:
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.

Each dataRow tracks the changes and the type of change to the row. Only If there are any changes from the initial values it calls the RowUpdating. if you want to see for your self you can use DataRow.hasVersions, there are versions like current, proposed etc.

Now in case you make a change say modify a value the HasVersions is set to true and the old row as well as the new row is stored in the datatable. When you call DataTable.AcceptChanges (or rejectChanges) the versions are removed and hasVersions is set to False. So after update to database you should call DataTable.AcceptChanges.
These are only for the Client side data stored in the datatable the nackend is only updated through DataAdapter or CommandObject.


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

YES this is TRUE, that's what I told you earlier DataAdapter is smart it automatically creates InsertCommand, UpdateCommand, DeleteCommand given just the SelectCommand. BUT this is true only for a single table Query, if your SelectCommand given to the DataAdapter is a Join Query it will fail and give Error, then it will require explicit Queries for each of InsertCommand, UpdateCommand and DeleteCommand


3.  RowUpdated event is not firing , I have added the event handler in  constructor

It should I can't say why its not firing, Check your code something must be there somewhere


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.

You have already Done it
I meant e.Status = UpdateStatus.SkipCurrentRow
i.e. After you are updating using CommandObject the DataAdapter should not again Update it so you needed a e.Status = UpdateStatus.SkipCurrentRow


0
 
rajneesh75Author Commented:
Thanks very much for your support
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now