Update database from Datagrid
-
Hello guys.I have a problem updating my MS Access from datagrid. The update button is ther already so I dont need it. I jus need the coding. Here is what Ive got and I cant geddit correct.Thanx in advance guys. Protected Sub GridView1_RowUpdated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdatedEventArgs) Handles GridView1.RowUpdated Dim cm As New OleDbCommand Dim con As New OleDbConnection Dim str1 As String = "Provider = Microsoft.Jet.oledb.4.0; Data source =C:\Documents and Settings\Kagisho.Mashiloane.ALFRGIS\My Documents\Visual Studio 2005\Projects\Search AL\TelephoneList.mdb" Dim str2 As String con = New OleDbConnection(str1) con.Open() str2 = "select * From Extensions" cm = New OleDbCommand(str2, con) Dim updateSQL As String = "UPDATE Extension SET Name = @Name, " & "Surname = @Surname, " & "Xtension = @Xtension" cm.Parameters.Clear() cm.Parameters.Add("@Name", Name) cm.Parameters.Add("@Surname", Surname) cm.Parameters.Add("@Xtension", Xtension) cm.ExecuteNonQuery() 'Next lblUpdatemessage.Text = "Update Successfull"
kagiso
-
Hello guys.I have a problem updating my MS Access from datagrid. The update button is ther already so I dont need it. I jus need the coding. Here is what Ive got and I cant geddit correct.Thanx in advance guys. Protected Sub GridView1_RowUpdated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdatedEventArgs) Handles GridView1.RowUpdated Dim cm As New OleDbCommand Dim con As New OleDbConnection Dim str1 As String = "Provider = Microsoft.Jet.oledb.4.0; Data source =C:\Documents and Settings\Kagisho.Mashiloane.ALFRGIS\My Documents\Visual Studio 2005\Projects\Search AL\TelephoneList.mdb" Dim str2 As String con = New OleDbConnection(str1) con.Open() str2 = "select * From Extensions" cm = New OleDbCommand(str2, con) Dim updateSQL As String = "UPDATE Extension SET Name = @Name, " & "Surname = @Surname, " & "Xtension = @Xtension" cm.Parameters.Clear() cm.Parameters.Add("@Name", Name) cm.Parameters.Add("@Surname", Surname) cm.Parameters.Add("@Xtension", Xtension) cm.ExecuteNonQuery() 'Next lblUpdatemessage.Text = "Update Successfull"
kagiso
This is just a comment ;) cause a don’t really like doing db stuff in code. You might wana review your approach to this problem. Placing the connection code inside the row update event is not the best idea. You should consider using datasets, table adapters etc. to simplify this process. For example: 1. Form init 2. grid loaded by querying a table adapter in the dataset 3. returned result is passed to .Datasource of the grid 4. grid is refreshed 5a. row changes on the grid are auto reflected on underlying data in the dataset table (not db) 5b. row changes remain in memory 6. once you actually need to write the whole thing to the db use the table adapter's .Update method on the dataset 7. dispose of the form or whatever Obvious benefits: 1. overhead of connecting to the db every time a row is changed is eliminated 2. simpler implementation allows for greater control and easier maintenance 3. dataset classes can be extended to handle everything themselves with minimal interference If you have dataset specific questions post them here, members will definitely help you out
"All your base are belong to us..." Mr. Cats (2110 A.D.)
-
This is just a comment ;) cause a don’t really like doing db stuff in code. You might wana review your approach to this problem. Placing the connection code inside the row update event is not the best idea. You should consider using datasets, table adapters etc. to simplify this process. For example: 1. Form init 2. grid loaded by querying a table adapter in the dataset 3. returned result is passed to .Datasource of the grid 4. grid is refreshed 5a. row changes on the grid are auto reflected on underlying data in the dataset table (not db) 5b. row changes remain in memory 6. once you actually need to write the whole thing to the db use the table adapter's .Update method on the dataset 7. dispose of the form or whatever Obvious benefits: 1. overhead of connecting to the db every time a row is changed is eliminated 2. simpler implementation allows for greater control and easier maintenance 3. dataset classes can be extended to handle everything themselves with minimal interference If you have dataset specific questions post them here, members will definitely help you out
"All your base are belong to us..." Mr. Cats (2110 A.D.)
-
Good Morning Kagiso, What the guy is saying is true,i see that you are getting somewhere. you have to create a dataset to Display or save the data in the datagrid, out it this, you dont save the data in the datagrid, but you save the data in the dataset. Create a dataset and one adapter, and fill the dataset and if you want to update, in your Update command of your adapter, use your update commmnd. i have an article that can show you how to do it. Am using a datagrid to save and display the data.its in vb.net, you will just have to convert. and dont forget to vote. http://www.codeproject.com/useritems/N-Tier_Application_VB.asp[^] Thanks
Vuyiswa Maseko, Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding VB.NET/SQL7/2000/2005 http://vuyiswamb.007ihost.com http://Ecadre.007ihost.com vuyiswam@tshwane.gov.za