arcross
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=@CPhon e
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("spUpdateCustom er", cn)
cmd.CommandType = CommandType.StoredProcedur e
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("@CPhon e", SqlDbType.NVarChar, 13, "CPhone")
param.SourceVersion = DataRowVersion.Original
param.Direction = ParameterDirection.Input
'// ID
param = cmd.Parameters.Add("@Custo merID", 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??
CREATE PROCEDURE dbo.spUpdateCustomer
@CName nvarchar(50),
@CPhone nvarchar(13),
@CustomerID int
AS
UPDATE Customers SET CName=@CName,CPhone=@CPhon
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("spUpdateCustom
cmd.CommandType = CommandType.StoredProcedur
Dim param As SqlParameter
param = cmd.Parameters.Add("@CName
param.SourceVersion = DataRowVersion.Original
param.Direction = ParameterDirection.Input
param = cmd.Parameters.Add("@CPhon
param.SourceVersion = DataRowVersion.Original
param.Direction = ParameterDirection.Input
'// ID
param = cmd.Parameters.Add("@Custo
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
but ive got another problem. I alter the update to
UPDATE Customers SET CName=@CName,CPhone=@CPhon e
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.
UPDATE Customers SET CName=@CName,CPhone=@CPhon
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.
ASKER
I figured it out. I was using the same parameters in the where clause ! doh!
UPDATE Customers SET CName=@CName,CPhone=@CPhon e
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
UPDATE Customers SET CName=@CName,CPhone=@CPhon
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.
ASKER
thank you!