none
Problem executing SqlDataAdapter.Update() method. What is wrong wtih this code? RRS feed

  • Question

  • Hello, I want to show you a piece of code from my application in which I'm trying to update two tables. I've checked it many times but I haven't found anything that could give me a clue to change something to get the tables updated.


    Private AdaptadorDatos As SqlDataAdapter
    Private AdaptadorDatos2 As SqlDataAdapter
    Private SetDatosUpdate As DataSet
    Private SetDatosUpdate2 As DataSet


    Public Sub New()

    AdaptadorDatos = New SqlDataAdapter
    AdaptadorDatos2 = New SqlDataAdapter
    SetDatosUpdate = New DataSet
    SetDatosUpdate2 = New DataSet

       
    AdaptadorDatos.SelectCommand = New SqlCommand("SELECT * FROM Contacto", ConexionBaseDatos.ObtenerConexion)
    AdaptadorDatos.MissingSchemaAction = MissingSchemaAction.AddWithKey
    AdaptadorDatos.FillSchema(SetDatosUpdate, SchemaType.Source)
    AdaptadorDatos.UpdateBatchSize = 100

    AdaptadorDatos2.SelectCommand = New SqlCommand("SELECT * FROM Perfil", ConexionBaseDatos.ObtenerConexion)
    AdaptadorDatos2.MissingSchemaAction = MissingSchemaAction.AddWithKey
    AdaptadorDatos2.FillSchema(SetDatosUpdate2, SchemaType.Source)
    AdaptadorDatos2.UpdateBatchSize = 100


    End Sub

     

    Public Sub AgregarContacto(ByVal Especialidad As String, ByVal Anios As String, ByVal Certificado As Boolean)



    Dim ContactoActual(4) As Object
    ContactoActual(0) = Nothing
    ContactoActual(1) = My.Forms.NuevoContacto.NombreTXT.Text
    ContactoActual(2) = My.Forms.NuevoContacto.eMailTXT.Text
    ContactoActual(3) = My.Forms.NuevoContacto.TelefonoTXT.Text
    ContactoActual(4) = My.Forms.NuevoContacto.CiudadTXT.Text

    SetDatosUpdate.Tables(0).Rows.Add(ContactoActual)
    SetDatosUpdate.AcceptChanges()
       

    Dim RegistroActual(4) As Object
    RegistroActual(0) = Nothing
    RegistroActual(1) = Especialidad
    RegistroActual(2) = Anios
    RegistroActual(3) = Certificado
    RegistroActual(4) = SetDatosUpdate.Tables(0).Rows(0)(0).ToString

    SetDatosUpdate2.Tables(0).Rows.Add(RegistroActual)
    SetDatosUpdate2.AcceptChanges()

    End Sub



    Public Sub InsertarDatos()


    Dim BuildCommand As New SqlCommandBuilder
    Dim BuildCommand2 As New SqlCommandBuilder

    BuildCommand.SetAllValues = True
    BuildCommand.DataAdapter = AdaptadorDatos

    BuildCommand2.SetAllValues = True
    BuildCommand2.DataAdapter = AdaptadorDatos2


    AdaptadorDatos.InsertCommand = BuildCommand.GetInsertCommand(True)
    AdaptadorDatos.UpdateCommand = BuildCommand.GetUpdateCommand(True)
    AdaptadorDatos.DeleteCommand = BuildCommand.GetDeleteCommand(True)

    AdaptadorDatos2.InsertCommand = BuildCommand2.GetInsertCommand(True)
    AdaptadorDatos2.UpdateCommand = BuildCommand2.GetUpdateCommand(True)
    AdaptadorDatos2.DeleteCommand = BuildCommand2.GetDeleteCommand(True)

    Dim a As String = CStr(AdaptadorDatos.InsertCommand.Parameters(0).Value)
    Dim b As String = CStr(AdaptadorDatos.InsertCommand.Parameters(1).Value)

    Dim c As String = CStr(AdaptadorDatos2.InsertCommand.Parameters(0).Value)
    Dim d As String = CStr(AdaptadorDatos2.InsertCommand.Parameters(1).Value)

    '''''''''''''''

    AdaptadorDatos.Update(SetDatosUpdate)
    'AdaptadorDatos2.Update(SetDatosUpdate2)

    End Sub

     

    This methods are called in the same order that  I wrote them. My question is why the a, b, c, and d variables have a 'Nothing' value? (I know because I put a breackpoint) I think this situation is somehow affecting the Update process. Can someone help me please?

    Wednesday, May 5, 2010 4:23 PM

Answers

  • Hiii.....

    The Mistake you have did is provided the AcceptChanges after Adding the Rows in the DataTable. 

    '' The Accept Chagnes will Make the Added Row Status into Unchanged.
    
    SetDatosUpdate.Tables(0).Rows.Add(ContactoActual)
    '' SetDatosUpdate.AcceptChanges()
    
    SetDatosUpdate2.Tables(0).Rows.Add(RegistroActual)
    '' SetDatosUpdate2.AcceptChanges()
    
    '' Adapter will send the Data Back to the DB Source using the DataTable Rows States. 
    '' If the Rows State is Added it will send those records using the InsertCommand
    
    '' If the Rows State is Modified it will send those records using the UpdateCommand
    
    '' If the Rows State is Deleted it will send those records using the DeleteCommand
    
    ''If the Row State is Unchange then it will do nothing. In your case the Data Row you have added will be in the Status of Unchanged.
    
    '' Confirm like this SetDatosUpdate.Tables(0).Rows(0).RowState
    
    
    AdaptadorDatos.Update(SetDatosUpdate)
    
    '' Please comment those two lines and add it after the 
    SetDatosUpdate.Acceptchanges()
    

    Best Regards,
    Gopi V

    If you have found this post helpful, please click the Vote as Helpful link (the green triangle and number on the top-left).

    If this post answers your question, click the Mark As Answered link below. It helps others who experience the same issue in future to find the solution.

    Thursday, May 6, 2010 5:58 AM
  • The reason for a, b, c and d to be set to nothing is because they are not set to anything at this stage.
    In other words, at this stage you have declared the parameter, but you have not set the value for the paramter.
    This will not happen until you call Update on the adapter, it will then loop through the datatables and set the values
    per row based on the changes. If any.

    But at this stage, you have no value set for the parameter, it is only declared.

            SqlCommand cmd = con.CreateCommand();
            cmd.CommandText = "SELECT * FROM Shippers";
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            SqlCommandBuilder sb = new SqlCommandBuilder(da);
            da.UpdateCommand = sb.GetUpdateCommand();
    
            Console.WriteLine("Name {0}", da.UpdateCommand.Parameters[0].ParameterName); // Set
            Console.WriteLine("Vaule {0}", da.UpdateCommand.Parameters[0].Value);  // Not set.

    HTH
    //Michael


    This posting is provided "AS IS" with no warranties.
    Thursday, May 6, 2010 7:39 AM

All replies

  • Hiii.....

    The Mistake you have did is provided the AcceptChanges after Adding the Rows in the DataTable. 

    '' The Accept Chagnes will Make the Added Row Status into Unchanged.
    
    SetDatosUpdate.Tables(0).Rows.Add(ContactoActual)
    '' SetDatosUpdate.AcceptChanges()
    
    SetDatosUpdate2.Tables(0).Rows.Add(RegistroActual)
    '' SetDatosUpdate2.AcceptChanges()
    
    '' Adapter will send the Data Back to the DB Source using the DataTable Rows States. 
    '' If the Rows State is Added it will send those records using the InsertCommand
    
    '' If the Rows State is Modified it will send those records using the UpdateCommand
    
    '' If the Rows State is Deleted it will send those records using the DeleteCommand
    
    ''If the Row State is Unchange then it will do nothing. In your case the Data Row you have added will be in the Status of Unchanged.
    
    '' Confirm like this SetDatosUpdate.Tables(0).Rows(0).RowState
    
    
    AdaptadorDatos.Update(SetDatosUpdate)
    
    '' Please comment those two lines and add it after the 
    SetDatosUpdate.Acceptchanges()
    

    Best Regards,
    Gopi V

    If you have found this post helpful, please click the Vote as Helpful link (the green triangle and number on the top-left).

    If this post answers your question, click the Mark As Answered link below. It helps others who experience the same issue in future to find the solution.

    Thursday, May 6, 2010 5:58 AM
  • The reason for a, b, c and d to be set to nothing is because they are not set to anything at this stage.
    In other words, at this stage you have declared the parameter, but you have not set the value for the paramter.
    This will not happen until you call Update on the adapter, it will then loop through the datatables and set the values
    per row based on the changes. If any.

    But at this stage, you have no value set for the parameter, it is only declared.

            SqlCommand cmd = con.CreateCommand();
            cmd.CommandText = "SELECT * FROM Shippers";
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            SqlCommandBuilder sb = new SqlCommandBuilder(da);
            da.UpdateCommand = sb.GetUpdateCommand();
    
            Console.WriteLine("Name {0}", da.UpdateCommand.Parameters[0].ParameterName); // Set
            Console.WriteLine("Vaule {0}", da.UpdateCommand.Parameters[0].Value);  // Not set.

    HTH
    //Michael


    This posting is provided "AS IS" with no warranties.
    Thursday, May 6, 2010 7:39 AM