Saving Data to the SQL table using a Dataset ?

I have added a datasource to my VB.NET 2005 project, its a SQL Table on my SQL Server.
I have a combo box drop down that shows the values from one of the fields in the table.
I have 3 text boxes bound to 3 other fields in the same table.
When you select a value from the combo box the text boxes update with the correct information. This is working great. I want to be able to add a button on the form that will save the information that is changed in one of the text boxes. When I change the information it remembers and saves it only while I have the form open, it is not saving those changes to the actual SQL table.  I am trying to use the following code to save the changes on the table but I am stuck. What am I missing ?


I get the error "Update requires a valid UpdateCommand when passed DataRow collection with modified rows."

Im not sure where to go with this. Any ideas ?
Thanks for all your help !
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

you need to assign a sqlCommand to the data adapter

dim updCmd as new sqlCommand

with updCommand
   .connection = myConnection   ' this is assuming you have a connection object open at this point called myConnection
   .commandtext = "Update PPatient Set fieldA = @fieldA"
   .commandtype = commandtext
   .parameters.add("@fieldA", System.Data.SqlDbType.NVarChar)
   .parameters("@fieldA").value = textbox1.text
end with

Me.PPatientTableAdapter.updatecommand = updCommand

HTH :)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kasowitzAuthor Commented:
Me.PPatientTableAdapter.updatecommand      is not a valid command for me. (Only "update" , not "updatecommand" is valid)

.commandtype = commandtext      doesnt work also.

What am I doing wrong?
I also do not have a connection object open. All I did was add the datasource. How would I open a connection object? Would it be directly to the SQL server or using the Datasource I already added ?

There is no easier way to save the changes made ? Sorry for my lack of experience.
Thanks for your reply !
kasowitzAuthor Commented:
I figured out what was wrong with my  .commandtype = commandtext     line. I had to Dim commandtext as String.  Sorry.
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

firstly, .commandtype = commandtext     should be  CommandType.Text  this was a typo on my part. Sorry.

secondly, if PPatientTableAdapter is declared as a sqlDataAdapter then you should have .UpdateCommand as a property, if not how have you declared PPatientTableAdapter ?

thirdly, when you retrieved the data, what connection string did you use   -  will be something along the lines of "integrated security=SSPI;data source=<the server name>;persist security info=False;initial catalog=<the database name>"
kasowitzAuthor Commented:
I am completely lost, sorry. Let me see if I can figure this out and get back to you. It was so easy to display the data, had no idea it would be this involved to save it :) Thanks for your help.
kasowitzAuthor Commented:
Ok, sorry..back to basics?  

I am having a problem with this line:
Dim oSQLConn As SqlConnection = New SqlConnection()

It is saying that SqlConnection is not defined. What am I missing?

        Dim oSQLConn As SqlConnection = New SqlConnection()
        oSQLConn = "Server=Aron1;Database=pubs;User ID=sa;Password=asdasd;Trusted_Connection=False"
        oSQLConn.ConnectionString = "my connectionstring"

Have you specified Imports System.Data.SqlClient at the top of your code. If not then you will have to enter the fully qualifed name, e.g.  dim oSQLConn as System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.Connection

Also, your connection string is wrong. It should be...

With oSQLConn
   .ConnectionString = "data source=Aron1;initial catalog=pubs;User ID= sa; Password=asdasd;"
End With

HTH :)
kasowitzAuthor Commented:
Thanks HTH, I finally got it :)

Only thing I am noticing is that it seems to only make my change after I click my update button after making changes to more than 1 record. Are there any other commands you can recommend that will help prevent this ? So I can get a clean update on 1 record after hitting my button once ? Here is what my code looks like now. Im working on adding a where statement into the Sql command so that it will only update the current record I am looking at. Thanks !!

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        Dim oSQLConn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection
        Dim updCmd As New System.Data.SqlClient.SqlCommand
        Dim PPatientTableAdapter As New System.Data.SqlClient.SqlDataAdapter
        With oSQLConn
            .ConnectionString = "Server=Server1;Database=Test;User ID=sa;Password=asdfasdf;Trusted_Connection=False"

            With updCmd
                .Connection = oSQLConn
                .CommandText = "Update PPatient Set Import = @fieldA"
                .CommandType = CommandType.Text
                .Parameters.Add("@fieldA", System.Data.SqlDbType.NVarChar)
                .Parameters("@fieldA").Value = TextBox1.Text
            End With


        End With

        PPatientTableAdapter.UpdateCommand = updCmd
End Sub
How are you making changes to the dataset itself - is it used as a datasource to a datagrid or some other means?

When you do the dataadapter.Update method then all the rows within the dataset will be checked and those with a Rowstate of Modified will have the updatecommand processed for them; likewise rowstate of Added will have the insertcommand processed and a rowstate of Deleted will have the deletecommand processed.

You do not need to update 2 records in the dataset before they are updated through to the database!

kasowitzAuthor Commented:
I wasnt using the Me.PPatientBindingSource.EndEdit()   command before I executed the Update command. That seems to have fixed the issue. Thanks for all your help :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.