Link to home
Start Free TrialLog in
Avatar of arcross
arcrossFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Updated record shown in datatable but not in DB

Hi, ive got a Store procedure to update a record:

CREATE PROCEDURE dbo.spUpdateCustomer
      @CName      nvarchar(50),
      @CPhone      nvarchar(13),
      @CustomerID      int
AS
           UPDATE Customers  SET CName=@CName,CPhone=@CPhone
            WHERE CustomerID=@CustomerID

In a form ive got two fields to update the selected row:
   .............
   with row                .
            BeginEdit()
            .CName = Me.txt_CustomerName.Text
            .CPhone = Me.txt_Phone.Text
            .EndEdit()
     End with

     'Now call function to send updates...
      UpdateRecords

 Private Function UpdateRecords() As Boolean
            Dim da As SqlDataAdapter
            da = New SqlDataAdapter("SELECT CName,CPhone FROM Customers", cnStr)
            da.UpdateCommand = updateCustomer()
            da.Update(tbl)
 End Function

Private Function UpdateCustomer() As SqlCommand
        Dim cmd As SqlCommand
        Dim cn As SqlConnection
        cn = New SqlConnection(cnStr)
        cmd = New SqlCommand("spUpdateCustomer", cn)
        cmd.CommandType = CommandType.StoredProcedure

        Dim param As SqlParameter
        param = cmd.Parameters.Add("@CName", SqlDbType.NVarChar, 50, "CName")
        param.SourceVersion = DataRowVersion.Original
        param.Direction = ParameterDirection.Input

        param = cmd.Parameters.Add("@CPhone", SqlDbType.NVarChar, 13, "CPhone")
        param.SourceVersion = DataRowVersion.Original
        param.Direction = ParameterDirection.Input

        '// ID
        param = cmd.Parameters.Add("@CustomerID", SqlDbType.Int, 4, "CustomerID")
        param.Direction = ParameterDirection.Input
        param.SourceVersion = DataRowVersion.Original
         Return cmd
    End Function

This goes through with no errors but the database is not been updated. any ideas??
ASKER CERTIFIED SOLUTION
Avatar of Christopher Kile
Christopher Kile
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of arcross

ASKER

yes  that was it.!
thank you!
Avatar of arcross

ASKER

but ive got another problem. I alter the update to
UPDATE Customers  SET CName=@CName,CPhone=@CPhone
            WHERE CustomerID=@CustomerID
            AND (CName = @CName OR ((@CName IS NULL) AND (CName IS NULL)))
            AND (CPhone =@CPhone OR ((@CPhone IS NULL) AND (CPhone IS NULL)))

Now when i try to update one field, when the parameters are set to CURRENt i got an concurrentcy exeception. I know why is happening but i dont know hot to solve it.
Avatar of arcross

ASKER

I figured it out. I was using the same parameters in the where clause ! doh!

UPDATE Customers  SET CName=@CName,CPhone=@CPhone
            WHERE CustomerID=@CustomerID
            AND (CName = @Orig_CName OR ((@Orig_CName IS NULL) AND (CName IS NULL)))
            AND (CPhone =@Orig_CPhone OR ((@Orig_CPhone IS NULL) AND (CPhone IS NULL)))

then add more paratemeters to the Update command

:) Glad I helped, and glad you figured out the other problem.  Good luck with the rest of your project.