Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Updated record shown in datatable but not in DB

Posted on 2008-11-03
Medium Priority
Last Modified: 2013-11-08
Hi, ive got a Store procedure to update a record:

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

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

     'Now call function to send updates...

 Private Function UpdateRecords() As Boolean
            Dim da As SqlDataAdapter
            da = New SqlDataAdapter("SELECT CName,CPhone FROM Customers", cnStr)
            da.UpdateCommand = updateCustomer()
 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??
Question by:arcross
  • 3
  • 2
LVL 23

Accepted Solution

Christopher Kile earned 2000 total points
ID: 22869722
You're using the Original values as input to the stored procedure for update.  I'm giving this a quick reading, mind you, but it appears to me that you should be using the Current row version rather than the Original version.  Try changing that and let me know if it works.

Author Comment

ID: 22874676
yes  that was it.!
thank you!

Author Comment

ID: 22875093
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.

Author Comment

ID: 22875154
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

LVL 23

Expert Comment

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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses
Course of the Month10 days, 22 hours left to enroll

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question