[RESOLVED] Update data using SQL Parameters in vb.net

[VintageCoro]

Hey everyone

I'm trying to update a form of mine ( You can see why from this post: http://www.vbforums.com/showthread.p...met&highlight=)

Here's my code so far, it executes without any exceptions but it doesn't update the data. Can anyone tell me why?

Try
                    '=================================='
                    link_to_database()
                    Dim UpdateCommand As New SqlCommand

                    UpdateCommand.CommandText = "UPDATE MDModels SET MDModel_Name=@NewModelName, MDModel_CAT=@ModelCat, MDModel_Desc=@ModelDesc, " & _
                        "MDModel_Price=@ModelPrice WHERE MDUser_ID = @user AND MDModel_Name = @OldModelName"

                    UpdateCommand.Parameters.Add("@user", SqlDbType.Int)
                    UpdateCommand.Parameters.Add("@OldModelName", SqlDbType.NVarChar)

                    UpdateCommand.Parameters.Add("@NewModelName", SqlDbType.NVarChar)
                    UpdateCommand.Parameters.Add("@ModelCat", SqlDbType.NVarChar)
                    UpdateCommand.Parameters.Add("@ModelDesc", SqlDbType.NVarChar)
                    UpdateCommand.Parameters.Add("@ModelPrice", SqlDbType.Int)

                    UpdateCommand.Parameters("@NewModelName").Value = txtModelName_EditModel.Text
                    UpdateCommand.Parameters("@ModelCat").Value = ddbCategory_EditModel.Text
                    UpdateCommand.Parameters("@ModelDesc").Value = txtModelDesc_EditModel.Text
                    UpdateCommand.Parameters("@ModelPrice").Value = CType(Me.txtPrice_EditModel.Text, Integer)

                    UpdateCommand.Parameters("@user").Value = DatabaseModule.UserID
                    UpdateCommand.Parameters("@OldModelName").Value = ddbCurrentModel_EditModel.Text

                   
                   
                    UpdateCommand.Connection = DatabaseModule.con
                    'Opens connection to the database
                    DatabaseModule.con.Open()

                    'Transfer data to the database
                    UpdateCommand.ExecuteNonQuery()
                    DatabaseModule.con.Close()
                    '=================================='
                Catch ex As Exception
                    Dim ExpectionOccured As DialogResult = MessageBox.Show(ex.Message & " Please contact an administrator", "An error has occured!", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End Try

[jmcilhinney]

When you call ExecuteNonQuery there are only three possible outcomes:

1. The operation fails and an exception is thrown.
2. The operation succeeds and the method returns zero, indicating that no records were affected.
3. The operation succeeds and the method returns a non-zero value, indicating that that many records were affected.

Which is it in your case? You say that it's not 1 so that means that either it is 2 and your WHERE clause is faulty or it's 3 and the database is indeed being updated and you're just not looking for the changes in the right place or at the right time. If it is 3, follow the first link in my signature to learn how to manage local data files.

By the way, don't Add all the parameters and then set their Values. Do both in one operation by calling AddWithValue.

[techgnome]

1) How do you know it's not updating the data? In my signature is a link "I swear I saved my data, where'd it run off too?" ... see if it applies in this case.
b) Try also setting the CommandTextType property... I've had issues with that before where SQL wasn't being executed because I didn't explicitly set it to CommandText or CommandStoredProcedure (or what ever it its...)
thirdly) Nothing, I just wanted to use thirdly in the list.


-tg

[VintageCoro]

My mistake, the actual database has been updated but the dataset that retrieved the data from the sql server has not. (I should have checked the server to see if it had been updated first). The Where clause works fine. I needed it to use two values (Model name and User ID) so that if there is a model that had the same name as another, but was owned by a different user, then it wouldn't update the wrong record.

[jmcilhinney]

My mistake, the actual database has been updated but the dataset that retrieved the data from the sql server has not. (I should have checked the server to see if it had been updated first). The Where clause works fine. I needed it to use two values (Model name and User ID) so that if there is a model that had the same name as another, but was owned by a different user, then it wouldn't update the wrong record.

If you have a DataSet/DataTable then why are you using ExecuteNonQuery at all? The whole point of a DataSet/DataTable is that that's the data you work with. If you want to make changes to the data then that's where you make those changes. Edit the contents of the DataSet/DataTable first, then call Update on the same data adapter you used to retrieve the data in the first place to save those changes back to the database. Follow the CodeBank link in my signature and check out my Retrieving & Saving Data thread for two examples: one with a command builder and one without.