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 ?

        Me.Validate()
        Me.PPatientBindingSource.EndEdit()
        Me.PPatientTableAdapter.Update(Me.P06DataSet.PPatient)

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 !
kasowitzAsked:
Who is Participating?
 
cubixSoftwareCommented:
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
Me.PPatientTableAdapter.Update(Me.P06DataSet.PPatient)


HTH :)
0
 
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 !
0
 
kasowitzAuthor Commented:
I figured out what was wrong with my  .commandtype = commandtext     line. I had to Dim commandtext as String.  Sorry.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
cubixSoftwareCommented:
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>"
0
 
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.
0
 
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"
        oSQLConn.Open()
0
 
cubixSoftwareCommented:
Hi

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;"
   .open
End With

HTH :)
0
 
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"
            .Open()

            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

            .Close()

        End With

        PPatientTableAdapter.UpdateCommand = updCmd
        PPatientTableAdapter.Update(Me.06DataSet.PPatient)
End Sub
0
 
cubixSoftwareCommented:
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!

0
 
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 :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.