Update a Database through Dataset [Resolved]

[FishGuy]

Im sure I had this working before but now it only appears to work i.e the text box updates and the code doesnt error, but the change isnt reflected in the database. Basically all I have is a text box bound to a dataset, the user can edit the text box and on clicking the button it should update the database through the data adapter.

Private Sub btnUpdateCont_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdateCont.Click
 
        Me.SqlDataAdapter1.Update(DsUser1)
        DsUser1.AcceptChanges()
        Me.lblChanged.Text = "Detail Changes Successful"
    End Sub

or does this just update the dataset and not the database? If not what is the code for doing so? pls.

[FishGuy]

Added a new command with an update statement to execiute nonquery. Woluld still like to know if there is a way for the above method to work though.

[Musician]

In the example you can't get to work what is the updatecommand for SqlDataAdapter1?

[FishGuy]

Me.SqlUpdateCommand1.CommandText = "UPDATE TBL_ITUsers SET UserName = @UserName, Password = @Password, Active = @Acti" & _
        "ve, Surname = @Surname, [First Name] = @Param7, Ext = @Ext, Email = @Email, Depa" & _
        "rtment = @Department, [Update Profile] = @Param8, [View Request History] = @Para" & _
        "m9, [Request Development] = @Param10, [Request Asset] = @Param11, [Request Suppo" & _
        "rt] = @Param12 WHERE (PK_ID = @Original_PK_ID) AND (Active = @Original_Active OR" & _
        " @Original_Active IS NULL AND Active IS NULL) AND (Department = @Original_Depart" & _
        "ment OR @Original_Department IS NULL AND Department IS NULL) AND (Email = @Origi" & _
        "nal_Email OR @Original_Email IS NULL AND Email IS NULL) AND (Ext = @Original_Ext" & _
        " OR @Original_Ext IS NULL AND Ext IS NULL) AND ([First Name] = @Original_First_N" & _
        "ame OR @Original_First_Name IS NULL AND [First Name] IS NULL) AND (Password = @O" & _
        "riginal_Password OR @Original_Password IS NULL AND Password IS NULL) AND ([Reque" & _
        "st Asset] = @Original_Request_Asset OR @Original_Request_Asset IS NULL AND [Requ" & _
        "est Asset] IS NULL) AND ([Request Development] = @Original_Request_Development O" & _
        "R @Original_Request_Development IS NULL AND [Request Development] IS NULL) AND (" & _
        "[Request Support] = @Original_Request_Support OR @Original_Request_Support IS NU" & _
        "LL AND [Request Support] IS NULL) AND (Surname = @Original_Surname OR @Original_" & _
        "Surname IS NULL AND Surname IS NULL) AND ([Update Profile] = @Original_Update_Pr" & _
        "ofile OR @Original_Update_Profile IS NULL AND [Update Profile] IS NULL) AND (Use" & _
        "rName = @Original_UserName OR @Original_UserName IS NULL AND UserName IS NULL) A" & _
        "ND ([View Request History] = @Original_View_Request_History OR @Original_View_Re" & _
        "quest_History IS NULL AND [View Request History] IS NULL); SELECT UserName, Pass" & _
        "word, Active, PK_ID, Surname, [First Name], Ext, Email, Department, [Update Prof" & _
        "ile], [View Request History], [Request Development], [Request Asset], [Request S" & _
        "upport] FROM TBL_ITUsers WHERE (PK_ID = @PK_ID)"
        Me.SqlUpdateCommand1.Connection = Me.SqlConnection1
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@UserName", System.Data.SqlDbType.NVarChar, 25, "UserName"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Password", System.Data.SqlDbType.NVarChar, 25, "Password"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Active", System.Data.SqlDbType.Bit, 1, "Active"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Surname", System.Data.SqlDbType.VarChar, 25, "Surname"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Param7", System.Data.SqlDbType.VarChar, 25, "First Name"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Ext", System.Data.SqlDbType.Int, 4, "Ext"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Email", System.Data.SqlDbType.VarChar, 50, "Email"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Department", System.Data.SqlDbType.VarChar, 50, "Department"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Param8", System.Data.SqlDbType.Bit, 1, "Update Profile"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Param9", System.Data.SqlDbType.Bit, 1, "View Request History"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Param10", System.Data.SqlDbType.Bit, 1, "Request Development"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Param11", System.Data.SqlDbType.Bit, 1, "Request Asset"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Param12", System.Data.SqlDbType.Bit, 1, "Request Support"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_PK_ID", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "PK_ID", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Active", System.Data.SqlDbType.Bit, 1, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Active", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Department", System.Data.SqlDbType.VarChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Department", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Email", System.Data.SqlDbType.VarChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Email", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Ext", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Ext", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_First_Name", System.Data.SqlDbType.VarChar, 25, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "First Name", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Password", System.Data.SqlDbType.NVarChar, 25, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Password", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Request_Asset", System.Data.SqlDbType.Bit, 1, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Request Asset", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Request_Development", System.Data.SqlDbType.Bit, 1, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Request Development", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Request_Support", System.Data.SqlDbType.Bit, 1, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Request Support", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Surname", System.Data.SqlDbType.VarChar, 25, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Surname", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Update_Profile", System.Data.SqlDbType.Bit, 1, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Update Profile", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_UserName", System.Data.SqlDbType.NVarChar, 25, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "UserName", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_View_Request_History", System.Data.SqlDbType.Bit, 1, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "View Request History", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@PK_ID", System.Data.SqlDbType.Int, 4, "PK_ID"))
        '

[Musician]

Hehe sorry I asked. you might want to trim that down a bit if it's not absolutely necessary to check that the record has changed.

Me.SqlUpdateCommand1.CommandText = 
"UPDATE TBL_ITUsers SET UserName = @UserName, Password = @Password,
 Active = @Active, Surname = @Surname, [First Name] = @Param7, Ext = 
@Ext, Email = @Email, Department = @Department, [Update Profile] = 
@Param8, [View Request History] = @Param9, [Request Development] = 
@Param10, [Request Asset] = @Param11, [Request Support] = @Param12 
WHERE (PK_ID = @Original_PK_ID)"