kasowitz
asked on
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.E
Me.PPatientTableAdapter.Up
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 !
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.E
Me.PPatientTableAdapter.Up
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 !
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I figured out what was wrong with my .commandtype = commandtext line. I had to Dim commandtext as String. Sorry.
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>"
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>"
ASKER
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.
ASKER
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=pub
oSQLConn.ConnectionString = "my connectionstring"
oSQLConn.Open()
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=pub
oSQLConn.ConnectionString = "my connectionstring"
oSQLConn.Open()
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.SqlC
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 :)
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.SqlC
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 :)
ASKER
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.SqlC
Dim updCmd As New System.Data.SqlClient.SqlC
Dim PPatientTableAdapter As New System.Data.SqlClient.SqlD
With oSQLConn
.ConnectionString = "Server=Server1;Database=T
.Open()
With updCmd
.Connection = oSQLConn
.CommandText = "Update PPatient Set Import = @fieldA"
.CommandType = CommandType.Text
.Parameters.Add("@fieldA",
.Parameters("@fieldA").Val
End With
.Close()
End With
PPatientTableAdapter.Updat
PPatientTableAdapter.Updat
End Sub
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.SqlC
Dim updCmd As New System.Data.SqlClient.SqlC
Dim PPatientTableAdapter As New System.Data.SqlClient.SqlD
With oSQLConn
.ConnectionString = "Server=Server1;Database=T
.Open()
With updCmd
.Connection = oSQLConn
.CommandText = "Update PPatient Set Import = @fieldA"
.CommandType = CommandType.Text
.Parameters.Add("@fieldA",
.Parameters("@fieldA").Val
End With
.Close()
End With
PPatientTableAdapter.Updat
PPatientTableAdapter.Updat
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!
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!
ASKER
I wasnt using the Me.PPatientBindingSource.E
ASKER
.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 !