[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Saving Data to the SQL table using a Dataset ?

Posted on 2006-04-20
10
Medium Priority
?
182 Views
Last Modified: 2010-04-23
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 !
0
Comment
Question by:kasowitz
  • 6
  • 4
10 Comments
 
LVL 6

Accepted Solution

by:
cubixSoftware earned 2000 total points
ID: 16498592
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
 

Author Comment

by:kasowitz
ID: 16498746
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
 

Author Comment

by:kasowitz
ID: 16498759
I figured out what was wrong with my  .commandtype = commandtext     line. I had to Dim commandtext as String.  Sorry.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 6

Expert Comment

by:cubixSoftware
ID: 16498824
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
 

Author Comment

by:kasowitz
ID: 16498948
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
 

Author Comment

by:kasowitz
ID: 16537085
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
 
LVL 6

Expert Comment

by:cubixSoftware
ID: 16543311
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
 

Author Comment

by:kasowitz
ID: 16544131
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
 
LVL 6

Expert Comment

by:cubixSoftware
ID: 16544959
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
 

Author Comment

by:kasowitz
ID: 16546599
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question