|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Table doesn't update - What am I doing wrong?FirstName, LastName, and Address Here is the code to load the data ---------------------------------------------------------------------------- Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim strSQL As String = "Select * from Contact where sysid = '" & g_sysID & "'" DB = New SqlClient.SqlDataAdapter(strSQL, CN) ES.Clear() DB.Fill(ES, "Contact") If ES.Tables("Contact").Rows.Count > 0 Then txtAddress.Text = ES.Tables("Contact").Rows(0).Item("con1_02_03") txtFirst_Name.Text = ES.Tables("Contact").Rows(0).Item("First_Name") txtLast_Name.Text = ES.Tables("Contact").Rows(0).Item("Last_Name") End If End Sub -------------------------------------------------------------------------- Here is the code to update the table -------------------------------------------------------------------------- Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click Try es.Tables(0).Rows(0).Item("First_Name") = txtFirst_Name.Text es.Tables(0).Rows(0).Item("Last_Name") = txtLast_Name.Text es.Tables(0).Rows(0).Item("con1_02_03") = txtAddress.Text ES.AcceptChanges() DB.Update(ES, "Contact") Catch ex As Exception MessageBox.Show(ex.Message) End Try End Sub ----------------------------------------------------------------- Why doesn't the table update? Can anyone give me an explanation and also if I need to change code, please tell me where to put the modifications? Thanks, Gary You should start the update routine by making a call to MyDataRow.BeginUpdate
Then add the lines that create the changes, followed by MyDataRow.AcceptChanges. If you look at the fourth implementation of DataRow, you will see a second parameter called DataRowVersion. The last item in that enum is Proposed. (You can create and step through some code to test the values of the various DataRowVersions to see exactly what is happening.) When you first make the change, the value is changed only for "Proposed". When you call AcceptChanges, the Proposed value becomes the "Current" value. When complete, call MyDataRow.EndEdit. Of course, this only affects the DataTable, not the database. Changes to the database will only be happen when you execute an UPDATE statement. www.charlesfarriersoftware.com Show quoteHide quote "Gary Paris" wrote: > I have a form that has three textboxes on. I want to be able to modify the > FirstName, LastName, and Address > > Here is the code to load the data > ---------------------------------------------------------------------------- > Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As > System.EventArgs) Handles MyBase.Load > > Dim strSQL As String = "Select * from Contact where sysid = '" & > g_sysID & "'" > DB = New SqlClient.SqlDataAdapter(strSQL, CN) > ES.Clear() > DB.Fill(ES, "Contact") > > If ES.Tables("Contact").Rows.Count > 0 Then > txtAddress.Text = > ES.Tables("Contact").Rows(0).Item("con1_02_03") > txtFirst_Name.Text = > ES.Tables("Contact").Rows(0).Item("First_Name") > txtLast_Name.Text = > ES.Tables("Contact").Rows(0).Item("Last_Name") > End If > > End Sub > -------------------------------------------------------------------------- > Here is the code to update the table > -------------------------------------------------------------------------- > Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As > System.EventArgs) Handles btnUpdate.Click > > Try > es.Tables(0).Rows(0).Item("First_Name") = txtFirst_Name.Text > es.Tables(0).Rows(0).Item("Last_Name") = txtLast_Name.Text > es.Tables(0).Rows(0).Item("con1_02_03") = txtAddress.Text > ES.AcceptChanges() > DB.Update(ES, "Contact") > > Catch ex As Exception > > MessageBox.Show(ex.Message) > > End Try > > End Sub > ----------------------------------------------------------------- > Why doesn't the table update? Can anyone give me an explanation and also if > I need to change code, please tell me where to put the modifications? > > Thanks, > > Gary > > > Hi Charlie,
Thanks for the reply but I don't understand a few things. Where do I put MyDataRow.BeginUpdate? I don't have a MyDataRow defined. Do I need to specifically define a datarow? What does "the fourth implementation of DataRow" mean? I have no idea. Sorry but I am a beginner and need more explicit guidance. If you could put the changes into my code that would help lots. Thanks, Gary Show quoteHide quote "Charlie" <cfarrier at charlesfarriersoftware.com> wrote in message news:B7D627DE-9F1A-446B-B565-487639875C27@microsoft.com... > You should start the update routine by making a call to > MyDataRow.BeginUpdate > Then add the lines that create the changes, followed by > MyDataRow.AcceptChanges. > > If you look at the fourth implementation of DataRow, you will see a second > parameter called DataRowVersion. The last item in that enum is Proposed. > > (You can create and step through some code to test the values of the > various > DataRowVersions to see exactly what is happening.) > > When you first make the change, the value is changed only for "Proposed". > When you call AcceptChanges, the Proposed value becomes the "Current" > value. > > When complete, call MyDataRow.EndEdit. > > Of course, this only affects the DataTable, not the database. Changes to > the database will only be happen when you execute an UPDATE statement. > > www.charlesfarriersoftware.com > > > > > "Gary Paris" wrote: > >> I have a form that has three textboxes on. I want to be able to modify >> the >> FirstName, LastName, and Address >> >> Here is the code to load the data >> ---------------------------------------------------------------------------- >> Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As >> System.EventArgs) Handles MyBase.Load >> >> Dim strSQL As String = "Select * from Contact where sysid = '" & >> g_sysID & "'" >> DB = New SqlClient.SqlDataAdapter(strSQL, CN) >> ES.Clear() >> DB.Fill(ES, "Contact") >> >> If ES.Tables("Contact").Rows.Count > 0 Then >> txtAddress.Text = >> ES.Tables("Contact").Rows(0).Item("con1_02_03") >> txtFirst_Name.Text = >> ES.Tables("Contact").Rows(0).Item("First_Name") >> txtLast_Name.Text = >> ES.Tables("Contact").Rows(0).Item("Last_Name") >> End If >> >> End Sub >> -------------------------------------------------------------------------- >> Here is the code to update the table >> -------------------------------------------------------------------------- >> Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As >> System.EventArgs) Handles btnUpdate.Click >> >> Try >> es.Tables(0).Rows(0).Item("First_Name") = txtFirst_Name.Text >> es.Tables(0).Rows(0).Item("Last_Name") = txtLast_Name.Text >> es.Tables(0).Rows(0).Item("con1_02_03") = txtAddress.Text >> ES.AcceptChanges() >> DB.Update(ES, "Contact") >> >> Catch ex As Exception >> >> MessageBox.Show(ex.Message) >> >> End Try >> >> End Sub >> ----------------------------------------------------------------- >> Why doesn't the table update? Can anyone give me an explanation and also >> if >> I need to change code, please tell me where to put the modifications? >> >> Thanks, >> >> Gary >> >> >> MyDataRow just means the datarow you are using:
ES.Tables("Contact").Rows(0). When you see My used this way, it just means "your instance", as opposed to a shared member of an object, such as String.Join... Show quoteHide quote "Gary Paris" wrote: > Hi Charlie, > > Thanks for the reply but I don't understand a few things. Where do I put > MyDataRow.BeginUpdate? I don't have a MyDataRow defined. Do I need to > specifically define a datarow? > > What does "the fourth implementation of DataRow" mean? I have no idea. > > Sorry but I am a beginner and need more explicit guidance. If you could put > the changes into my code that would help lots. > > Thanks, > > Gary > > > "Charlie" <cfarrier at charlesfarriersoftware.com> wrote in message > news:B7D627DE-9F1A-446B-B565-487639875C27@microsoft.com... > > You should start the update routine by making a call to > > MyDataRow.BeginUpdate > > Then add the lines that create the changes, followed by > > MyDataRow.AcceptChanges. > > > > If you look at the fourth implementation of DataRow, you will see a second > > parameter called DataRowVersion. The last item in that enum is Proposed. > > > > (You can create and step through some code to test the values of the > > various > > DataRowVersions to see exactly what is happening.) > > > > When you first make the change, the value is changed only for "Proposed". > > When you call AcceptChanges, the Proposed value becomes the "Current" > > value. > > > > When complete, call MyDataRow.EndEdit. > > > > Of course, this only affects the DataTable, not the database. Changes to > > the database will only be happen when you execute an UPDATE statement. > > > > www.charlesfarriersoftware.com > > > > > > > > > > "Gary Paris" wrote: > > > >> I have a form that has three textboxes on. I want to be able to modify > >> the > >> FirstName, LastName, and Address > >> > >> Here is the code to load the data > >> ---------------------------------------------------------------------------- > >> Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As > >> System.EventArgs) Handles MyBase.Load > >> > >> Dim strSQL As String = "Select * from Contact where sysid = '" & > >> g_sysID & "'" > >> DB = New SqlClient.SqlDataAdapter(strSQL, CN) > >> ES.Clear() > >> DB.Fill(ES, "Contact") > >> > >> If ES.Tables("Contact").Rows.Count > 0 Then > >> txtAddress.Text = > >> ES.Tables("Contact").Rows(0).Item("con1_02_03") > >> txtFirst_Name.Text = > >> ES.Tables("Contact").Rows(0).Item("First_Name") > >> txtLast_Name.Text = > >> ES.Tables("Contact").Rows(0).Item("Last_Name") > >> End If > >> > >> End Sub > >> -------------------------------------------------------------------------- > >> Here is the code to update the table > >> -------------------------------------------------------------------------- > >> Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As > >> System.EventArgs) Handles btnUpdate.Click > >> > >> Try > >> es.Tables(0).Rows(0).Item("First_Name") = txtFirst_Name.Text > >> es.Tables(0).Rows(0).Item("Last_Name") = txtLast_Name.Text > >> es.Tables(0).Rows(0).Item("con1_02_03") = txtAddress.Text > >> ES.AcceptChanges() > >> DB.Update(ES, "Contact") > >> > >> Catch ex As Exception > >> > >> MessageBox.Show(ex.Message) > >> > >> End Try > >> > >> End Sub > >> ----------------------------------------------------------------- > >> Why doesn't the table update? Can anyone give me an explanation and also > >> if > >> I need to change code, please tell me where to put the modifications? > >> > >> Thanks, > >> > >> Gary > >> > >> > >> > > > Your DataAdapter.Update command needs to have an UpdateCommand property set
for DataAdapter.Update to execute. I find it easier and more straightforward to just concatenate the UPDATE statement in code, assign it to a new command, and execute the Command with MyCommand.ExecuteNonQuery. Show quoteHide quote "Gary Paris" wrote: > I have a form that has three textboxes on. I want to be able to modify the > FirstName, LastName, and Address > > Here is the code to load the data > ---------------------------------------------------------------------------- > Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As > System.EventArgs) Handles MyBase.Load > > Dim strSQL As String = "Select * from Contact where sysid = '" & > g_sysID & "'" > DB = New SqlClient.SqlDataAdapter(strSQL, CN) > ES.Clear() > DB.Fill(ES, "Contact") > > If ES.Tables("Contact").Rows.Count > 0 Then > txtAddress.Text = > ES.Tables("Contact").Rows(0).Item("con1_02_03") > txtFirst_Name.Text = > ES.Tables("Contact").Rows(0).Item("First_Name") > txtLast_Name.Text = > ES.Tables("Contact").Rows(0).Item("Last_Name") > End If > > End Sub > -------------------------------------------------------------------------- > Here is the code to update the table > -------------------------------------------------------------------------- > Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As > System.EventArgs) Handles btnUpdate.Click > > Try > es.Tables(0).Rows(0).Item("First_Name") = txtFirst_Name.Text > es.Tables(0).Rows(0).Item("Last_Name") = txtLast_Name.Text > es.Tables(0).Rows(0).Item("con1_02_03") = txtAddress.Text > ES.AcceptChanges() > DB.Update(ES, "Contact") > > Catch ex As Exception > > MessageBox.Show(ex.Message) > > End Try > > End Sub > ----------------------------------------------------------------- > Why doesn't the table update? Can anyone give me an explanation and also if > I need to change code, please tell me where to put the modifications? > > Thanks, > > Gary > > > Can you give me an example of how to do that?
Thanks, Gary Show quoteHide quote "Charlie" <cfarrier at charlesfarriersoftware.com> wrote in message news:0C2DB006-CE28-446A-A3CA-DB9914BCB4EC@microsoft.com... > Your DataAdapter.Update command needs to have an UpdateCommand property > set > for DataAdapter.Update to execute. > > I find it easier and more straightforward to just concatenate the UPDATE > statement in code, assign it to a new command, and execute the Command > with > MyCommand.ExecuteNonQuery. > > > "Gary Paris" wrote: > >> I have a form that has three textboxes on. I want to be able to modify >> the >> FirstName, LastName, and Address >> >> Here is the code to load the data >> ---------------------------------------------------------------------------- >> Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As >> System.EventArgs) Handles MyBase.Load >> >> Dim strSQL As String = "Select * from Contact where sysid = '" & >> g_sysID & "'" >> DB = New SqlClient.SqlDataAdapter(strSQL, CN) >> ES.Clear() >> DB.Fill(ES, "Contact") >> >> If ES.Tables("Contact").Rows.Count > 0 Then >> txtAddress.Text = >> ES.Tables("Contact").Rows(0).Item("con1_02_03") >> txtFirst_Name.Text = >> ES.Tables("Contact").Rows(0).Item("First_Name") >> txtLast_Name.Text = >> ES.Tables("Contact").Rows(0).Item("Last_Name") >> End If >> >> End Sub >> -------------------------------------------------------------------------- >> Here is the code to update the table >> -------------------------------------------------------------------------- >> Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As >> System.EventArgs) Handles btnUpdate.Click >> >> Try >> es.Tables(0).Rows(0).Item("First_Name") = txtFirst_Name.Text >> es.Tables(0).Rows(0).Item("Last_Name") = txtLast_Name.Text >> es.Tables(0).Rows(0).Item("con1_02_03") = txtAddress.Text >> ES.AcceptChanges() >> DB.Update(ES, "Contact") >> >> Catch ex As Exception >> >> MessageBox.Show(ex.Message) >> >> End Try >> >> End Sub >> ----------------------------------------------------------------- >> Why doesn't the table update? Can anyone give me an explanation and also >> if >> I need to change code, please tell me where to put the modifications? >> >> Thanks, >> >> Gary >> >> >> You could adapt this. You can get the exact syntax of the UPDATE statement
through various sites, in case I have a typo here... Dim UPDATE As String = "UPDATE MyTable SET FirstName = '" & txtFirstName.text & "' LastName = " & txtLastName.text & " WHERE PrimaryKey = 1" Dim CMD As New OleDb.OleDbCommand(UPDATE, MyConnection) MyConnection.Open() CMD.ExecuteNonQuery() MyConnection.Close() Show quoteHide quote "Gary Paris" wrote: > Can you give me an example of how to do that? > > Thanks, > > Gary > > "Charlie" <cfarrier at charlesfarriersoftware.com> wrote in message > news:0C2DB006-CE28-446A-A3CA-DB9914BCB4EC@microsoft.com... > > Your DataAdapter.Update command needs to have an UpdateCommand property > > set > > for DataAdapter.Update to execute. > > > > I find it easier and more straightforward to just concatenate the UPDATE > > statement in code, assign it to a new command, and execute the Command > > with > > MyCommand.ExecuteNonQuery. > > > > > > "Gary Paris" wrote: > > > >> I have a form that has three textboxes on. I want to be able to modify > >> the > >> FirstName, LastName, and Address > >> > >> Here is the code to load the data > >> ---------------------------------------------------------------------------- > >> Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As > >> System.EventArgs) Handles MyBase.Load > >> > >> Dim strSQL As String = "Select * from Contact where sysid = '" & > >> g_sysID & "'" > >> DB = New SqlClient.SqlDataAdapter(strSQL, CN) > >> ES.Clear() > >> DB.Fill(ES, "Contact") > >> > >> If ES.Tables("Contact").Rows.Count > 0 Then > >> txtAddress.Text = > >> ES.Tables("Contact").Rows(0).Item("con1_02_03") > >> txtFirst_Name.Text = > >> ES.Tables("Contact").Rows(0).Item("First_Name") > >> txtLast_Name.Text = > >> ES.Tables("Contact").Rows(0).Item("Last_Name") > >> End If > >> > >> End Sub > >> -------------------------------------------------------------------------- > >> Here is the code to update the table > >> -------------------------------------------------------------------------- > >> Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As > >> System.EventArgs) Handles btnUpdate.Click > >> > >> Try > >> es.Tables(0).Rows(0).Item("First_Name") = txtFirst_Name.Text > >> es.Tables(0).Rows(0).Item("Last_Name") = txtLast_Name.Text > >> es.Tables(0).Rows(0).Item("con1_02_03") = txtAddress.Text > >> ES.AcceptChanges() > >> DB.Update(ES, "Contact") > >> > >> Catch ex As Exception > >> > >> MessageBox.Show(ex.Message) > >> > >> End Try > >> > >> End Sub > >> ----------------------------------------------------------------- > >> Why doesn't the table update? Can anyone give me an explanation and also > >> if > >> I need to change code, please tell me where to put the modifications? > >> > >> Thanks, > >> > >> Gary > >> > >> > >> > > > Charlie,
Could you give me an example of how to do the update with a SQL command please? Thanks, Gary Show quoteHide quote "Charlie" <cfarrier at charlesfarriersoftware.com> wrote in message news:0C2DB006-CE28-446A-A3CA-DB9914BCB4EC@microsoft.com... > Your DataAdapter.Update command needs to have an UpdateCommand property > set > for DataAdapter.Update to execute. > > I find it easier and more straightforward to just concatenate the UPDATE > statement in code, assign it to a new command, and execute the Command > with > MyCommand.ExecuteNonQuery. > > > "Gary Paris" wrote: > >> I have a form that has three textboxes on. I want to be able to modify >> the >> FirstName, LastName, and Address >> >> Here is the code to load the data >> ---------------------------------------------------------------------------- >> Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As >> System.EventArgs) Handles MyBase.Load >> >> Dim strSQL As String = "Select * from Contact where sysid = '" & >> g_sysID & "'" >> DB = New SqlClient.SqlDataAdapter(strSQL, CN) >> ES.Clear() >> DB.Fill(ES, "Contact") >> >> If ES.Tables("Contact").Rows.Count > 0 Then >> txtAddress.Text = >> ES.Tables("Contact").Rows(0).Item("con1_02_03") >> txtFirst_Name.Text = >> ES.Tables("Contact").Rows(0).Item("First_Name") >> txtLast_Name.Text = >> ES.Tables("Contact").Rows(0).Item("Last_Name") >> End If >> >> End Sub >> -------------------------------------------------------------------------- >> Here is the code to update the table >> -------------------------------------------------------------------------- >> Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As >> System.EventArgs) Handles btnUpdate.Click >> >> Try >> es.Tables(0).Rows(0).Item("First_Name") = txtFirst_Name.Text >> es.Tables(0).Rows(0).Item("Last_Name") = txtLast_Name.Text >> es.Tables(0).Rows(0).Item("con1_02_03") = txtAddress.Text >> ES.AcceptChanges() >> DB.Update(ES, "Contact") >> >> Catch ex As Exception >> >> MessageBox.Show(ex.Message) >> >> End Try >> >> End Sub >> ----------------------------------------------------------------- >> Why doesn't the table update? Can anyone give me an explanation and also >> if >> I need to change code, please tell me where to put the modifications? >> >> Thanks, >> >> Gary >> >> >> Be sure to get those single quotes in there. I noticed that I did not
include them in the LastName part of the example. The single quotes are necessary for non numeric, non date fields. Numeric fields use no delineator syntax (PrimaryKeyField = 1), and Date fields use "#" (DateOfBirth = #1/2/64#) You can lay your mouse over the UPDATE variable as you build the concatenated string to see the output. You can also print it to the Command Window (available through View, Other Windows) using: ? UPDATE then press Enter You can do this only while you are stepping through the routine. Show quoteHide quote "Gary Paris" wrote: > Charlie, > > Could you give me an example of how to do the update with a SQL command > please? > > Thanks, > > Gary > > "Charlie" <cfarrier at charlesfarriersoftware.com> wrote in message > news:0C2DB006-CE28-446A-A3CA-DB9914BCB4EC@microsoft.com... > > Your DataAdapter.Update command needs to have an UpdateCommand property > > set > > for DataAdapter.Update to execute. > > > > I find it easier and more straightforward to just concatenate the UPDATE > > statement in code, assign it to a new command, and execute the Command > > with > > MyCommand.ExecuteNonQuery. > > > > > > "Gary Paris" wrote: > > > >> I have a form that has three textboxes on. I want to be able to modify > >> the > >> FirstName, LastName, and Address > >> > >> Here is the code to load the data > >> ---------------------------------------------------------------------------- > >> Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As > >> System.EventArgs) Handles MyBase.Load > >> > >> Dim strSQL As String = "Select * from Contact where sysid = '" & > >> g_sysID & "'" > >> DB = New SqlClient.SqlDataAdapter(strSQL, CN) > >> ES.Clear() > >> DB.Fill(ES, "Contact") > >> > >> If ES.Tables("Contact").Rows.Count > 0 Then > >> txtAddress.Text = > >> ES.Tables("Contact").Rows(0).Item("con1_02_03") > >> txtFirst_Name.Text = > >> ES.Tables("Contact").Rows(0).Item("First_Name") > >> txtLast_Name.Text = > >> ES.Tables("Contact").Rows(0).Item("Last_Name") > >> End If > >> > >> End Sub > >> -------------------------------------------------------------------------- > >> Here is the code to update the table > >> -------------------------------------------------------------------------- > >> Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As > >> System.EventArgs) Handles btnUpdate.Click > >> > >> Try > >> es.Tables(0).Rows(0).Item("First_Name") = txtFirst_Name.Text > >> es.Tables(0).Rows(0).Item("Last_Name") = txtLast_Name.Text > >> es.Tables(0).Rows(0).Item("con1_02_03") = txtAddress.Text > >> ES.AcceptChanges() > >> DB.Update(ES, "Contact") > >> > >> Catch ex As Exception > >> > >> MessageBox.Show(ex.Message) > >> > >> End Try > >> > >> End Sub > >> ----------------------------------------------------------------- > >> Why doesn't the table update? Can anyone give me an explanation and also > >> if > >> I need to change code, please tell me where to put the modifications? > >> > >> Thanks, > >> > >> Gary > >> > >> > >> > > > Charlie,
Thanks, I tried a couple of times and finally got to update my table. I still would like to play around with my original request of doing the BeginEdit, UpdateEdit, AcceptChanges stuff but I'll have to play around with it. I still am not sure exactly what to do but the book I have isn't all that clear with just doing modifications. Again, thanks for all your help. Gary Show quoteHide quote "Charlie" <cfarrier at charlesfarriersoftware.com> wrote in message news:7DC33B07-D2D6-4753-9C41-A76A97E11C2C@microsoft.com... > Be sure to get those single quotes in there. I noticed that I did not > include them in the LastName part of the example. The single quotes are > necessary for non numeric, non date fields. Numeric fields use no > delineator > syntax (PrimaryKeyField = 1), and Date fields use "#" (DateOfBirth = > #1/2/64#) > > You can lay your mouse over the UPDATE variable as you build the > concatenated string to see the output. You can also print it to the > Command > Window (available through View, Other Windows) using: > > ? UPDATE > > then press Enter > You can do this only while you are stepping through the routine. > > "Gary Paris" wrote: > >> Charlie, >> >> Could you give me an example of how to do the update with a SQL command >> please? >> >> Thanks, >> >> Gary >> >> "Charlie" <cfarrier at charlesfarriersoftware.com> wrote in message >> news:0C2DB006-CE28-446A-A3CA-DB9914BCB4EC@microsoft.com... >> > Your DataAdapter.Update command needs to have an UpdateCommand property >> > set >> > for DataAdapter.Update to execute. >> > >> > I find it easier and more straightforward to just concatenate the >> > UPDATE >> > statement in code, assign it to a new command, and execute the Command >> > with >> > MyCommand.ExecuteNonQuery. >> > >> > >> > "Gary Paris" wrote: >> > >> >> I have a form that has three textboxes on. I want to be able to >> >> modify >> >> the >> >> FirstName, LastName, and Address >> >> >> >> Here is the code to load the data >> >> ---------------------------------------------------------------------------- >> >> Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As >> >> System.EventArgs) Handles MyBase.Load >> >> >> >> Dim strSQL As String = "Select * from Contact where sysid = '" >> >> & >> >> g_sysID & "'" >> >> DB = New SqlClient.SqlDataAdapter(strSQL, CN) >> >> ES.Clear() >> >> DB.Fill(ES, "Contact") >> >> >> >> If ES.Tables("Contact").Rows.Count > 0 Then >> >> txtAddress.Text = >> >> ES.Tables("Contact").Rows(0).Item("con1_02_03") >> >> txtFirst_Name.Text = >> >> ES.Tables("Contact").Rows(0).Item("First_Name") >> >> txtLast_Name.Text = >> >> ES.Tables("Contact").Rows(0).Item("Last_Name") >> >> End If >> >> >> >> End Sub >> >> -------------------------------------------------------------------------- >> >> Here is the code to update the table >> >> -------------------------------------------------------------------------- >> >> Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e >> >> As >> >> System.EventArgs) Handles btnUpdate.Click >> >> >> >> Try >> >> es.Tables(0).Rows(0).Item("First_Name") = >> >> txtFirst_Name.Text >> >> es.Tables(0).Rows(0).Item("Last_Name") = txtLast_Name.Text >> >> es.Tables(0).Rows(0).Item("con1_02_03") = txtAddress.Text >> >> ES.AcceptChanges() >> >> DB.Update(ES, "Contact") >> >> >> >> Catch ex As Exception >> >> >> >> MessageBox.Show(ex.Message) >> >> >> >> End Try >> >> >> >> End Sub >> >> ----------------------------------------------------------------- >> >> Why doesn't the table update? Can anyone give me an explanation and >> >> also >> >> if >> >> I need to change code, please tell me where to put the modifications? >> >> >> >> Thanks, >> >> >> >> Gary >> >> >> >> >> >> >> >> >> I have not been coming to this site very much lately, but I did a post, just
before yours, and decided to help you out while I waited for a reply. You could write to the ContactUs email at my website (bottom of my original reply) if you have more questions, but I only check that mail once or twice a day. Glad to help. Sounds like you are making good progress. Show quoteHide quote "Gary Paris" wrote: > Charlie, > > Thanks, I tried a couple of times and finally got to update my table. I > still would like to play around with my original request of doing the > BeginEdit, UpdateEdit, AcceptChanges stuff but I'll have to play around with > it. I still am not sure exactly what to do but the book I have isn't all > that clear with just doing modifications. > > Again, thanks for all your help. > > Gary > > > > > > "Charlie" <cfarrier at charlesfarriersoftware.com> wrote in message > news:7DC33B07-D2D6-4753-9C41-A76A97E11C2C@microsoft.com... > > Be sure to get those single quotes in there. I noticed that I did not > > include them in the LastName part of the example. The single quotes are > > necessary for non numeric, non date fields. Numeric fields use no > > delineator > > syntax (PrimaryKeyField = 1), and Date fields use "#" (DateOfBirth = > > #1/2/64#) > > > > You can lay your mouse over the UPDATE variable as you build the > > concatenated string to see the output. You can also print it to the > > Command > > Window (available through View, Other Windows) using: > > > > ? UPDATE > > > > then press Enter > > You can do this only while you are stepping through the routine. > > > > "Gary Paris" wrote: > > > >> Charlie, > >> > >> Could you give me an example of how to do the update with a SQL command > >> please? > >> > >> Thanks, > >> > >> Gary > >> > >> "Charlie" <cfarrier at charlesfarriersoftware.com> wrote in message > >> news:0C2DB006-CE28-446A-A3CA-DB9914BCB4EC@microsoft.com... > >> > Your DataAdapter.Update command needs to have an UpdateCommand property > >> > set > >> > for DataAdapter.Update to execute. > >> > > >> > I find it easier and more straightforward to just concatenate the > >> > UPDATE > >> > statement in code, assign it to a new command, and execute the Command > >> > with > >> > MyCommand.ExecuteNonQuery. > >> > > >> > > >> > "Gary Paris" wrote: > >> > > >> >> I have a form that has three textboxes on. I want to be able to > >> >> modify > >> >> the > >> >> FirstName, LastName, and Address > >> >> > >> >> Here is the code to load the data > >> >> ---------------------------------------------------------------------------- > >> >> Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As > >> >> System.EventArgs) Handles MyBase.Load > >> >> > >> >> Dim strSQL As String = "Select * from Contact where sysid = '" > >> >> & > >> >> g_sysID & "'" > >> >> DB = New SqlClient.SqlDataAdapter(strSQL, CN) > >> >> ES.Clear() > >> >> DB.Fill(ES, "Contact") > >> >> > >> >> If ES.Tables("Contact").Rows.Count > 0 Then > >> >> txtAddress.Text = > >> >> ES.Tables("Contact").Rows(0).Item("con1_02_03") > >> >> txtFirst_Name.Text = > >> >> ES.Tables("Contact").Rows(0).Item("First_Name") > >> >> txtLast_Name.Text = > >> >> ES.Tables("Contact").Rows(0).Item("Last_Name") > >> >> End If > >> >> > >> >> End Sub > >> >> -------------------------------------------------------------------------- > >> >> Here is the code to update the table > >> >> -------------------------------------------------------------------------- > >> >> Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e > >> >> As > >> >> System.EventArgs) Handles btnUpdate.Click > >> >> > >> >> Try > >> >> es.Tables(0).Rows(0).Item("First_Name") = > >> >> txtFirst_Name.Text > >> >> es.Tables(0).Rows(0).Item("Last_Name") = txtLast_Name.Text > >> >> es.Tables(0).Rows(0).Item("con1_02_03") = txtAddress.Text > >> >> ES.AcceptChanges() > >> >> DB.Update(ES, "Contact") > >> >> > >> >> Catch ex As Exception > >> >> > >> >> MessageBox.Show(ex.Message) > >> >> > >> >> End Try > >> >> > >> >> End Sub > >> >> ----------------------------------------------------------------- > >> >> Why doesn't the table update? Can anyone give me an explanation and > >> >> also > >> >> if > >> >> I need to change code, please tell me where to put the modifications? > >> >> > >> >> Thanks, > >> >> > >> >> Gary > >> >> > >> >> > >> >> > >> > >> > >> > > > Also, when you put together the UPDATE statement, be sure to include the
WHERE clause. Otherwise, you will update every record, and essentially destroy your data. I don't want to insult your intelligence, but it's an easy mistake to make, and worth noting. Show quoteHide quote "Gary Paris" wrote: > I have a form that has three textboxes on. I want to be able to modify the > FirstName, LastName, and Address > > Here is the code to load the data > ---------------------------------------------------------------------------- > Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As > System.EventArgs) Handles MyBase.Load > > Dim strSQL As String = "Select * from Contact where sysid = '" & > g_sysID & "'" > DB = New SqlClient.SqlDataAdapter(strSQL, CN) > ES.Clear() > DB.Fill(ES, "Contact") > > If ES.Tables("Contact").Rows.Count > 0 Then > txtAddress.Text = > ES.Tables("Contact").Rows(0).Item("con1_02_03") > txtFirst_Name.Text = > ES.Tables("Contact").Rows(0).Item("First_Name") > txtLast_Name.Text = > ES.Tables("Contact").Rows(0).Item("Last_Name") > End If > > End Sub > -------------------------------------------------------------------------- > Here is the code to update the table > -------------------------------------------------------------------------- > Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As > System.EventArgs) Handles btnUpdate.Click > > Try > es.Tables(0).Rows(0).Item("First_Name") = txtFirst_Name.Text > es.Tables(0).Rows(0).Item("Last_Name") = txtLast_Name.Text > es.Tables(0).Rows(0).Item("con1_02_03") = txtAddress.Text > ES.AcceptChanges() > DB.Update(ES, "Contact") > > Catch ex As Exception > > MessageBox.Show(ex.Message) > > End Try > > End Sub > ----------------------------------------------------------------- > Why doesn't the table update? Can anyone give me an explanation and also if > I need to change code, please tell me where to put the modifications? > > Thanks, > > Gary > > > Gary,
There are at least two things what makes that your update does not work. You are have no commands in your dataadapter. You use the acceptchanges wrong. I have made some corrections typed (so watch typos) inline in this message so look below to them. You better create and dispose better as well the connection in those procedures because now you don't free the connectionpool. > ---------------------------------------------------------------------------- dim cb as new sqlclient.sqlcommandbuilder(db)> Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As > System.EventArgs) Handles MyBase.Load > > Dim strSQL As String = "Select * from Contact where sysid = '" & > g_sysID & "'" > DB = New SqlClient.SqlDataAdapter(strSQL, CN) Show quoteHide quote > ES.Clear() delete the row above, this means that all rowstates which are set to a > DB.Fill(ES, "Contact") > > If ES.Tables("Contact").Rows.Count > 0 Then > txtAddress.Text = > ES.Tables("Contact").Rows(0).Item("con1_02_03") > txtFirst_Name.Text = > ES.Tables("Contact").Rows(0).Item("First_Name") > txtLast_Name.Text = > ES.Tables("Contact").Rows(0).Item("Last_Name") > End If > > End Sub > -------------------------------------------------------------------------- > Here is the code to update the table > -------------------------------------------------------------------------- > Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As > System.EventArgs) Handles btnUpdate.Click > > Try > es.Tables(0).Rows(0).Item("First_Name") = txtFirst_Name.Text > es.Tables(0).Rows(0).Item("Last_Name") = txtLast_Name.Text > es.Tables(0).Rows(0).Item("con1_02_03") = txtAddress.Text > ES.AcceptChanges() changed state will be set to unchanged and the changes are accepted, so the dataadapter has nothing to change. It is implicitly done by the dataadapter when a change is done. > DB.Update(ES, "Contact") I hope this helps,> > Catch ex As Exception > > MessageBox.Show(ex.Message) > > End Try > > End Sub Cor |
|||||||||||||||||||||||
Other interesting topics