Grid_Update function of a DataGrid not actually up

Results 1 to 2 of 2

Thread: Grid_Update function of a DataGrid not actually up

  1. #1
    Join Date
    Dec 1969

    Default Grid_Update function of a DataGrid not actually up

    I&#039;m using a DataGrid hooked into the database via a DataAdapter feeding to a DataSet to populate the grid. As it&#039;s an editable grid, I&#039;ve got a Grid_Update function going on, which uses a custom SQL query string to update the database.<BR><BR>The problem? The database is not being updated when the query string is run. I&#039;m not good with query strings in .NET (ask me about MySQL query strings ;-) ) and I&#039;m not really sure what&#039;s going on here.<BR><BR>Here&#039;s the code.<BR><BR>[code language="C#"]<BR>protected void Grid_Update(object sender, DataGridCommandEventArgs e)<BR>{<BR>//pulling the events out of the text boxes; episodeID is non-editable<BR>string strEpisodeID=e.Item.Cells[1].Text;<BR>string strTitle=((TextBox)e.Item.Cells[2].Controls[0]).Text;<BR>//etc.<BR><BR>//query string <BR>string updateCmd="UPDATE Episode SET Title=@Title,<BR>Description=@Description, EpisodeDate=@EpisodeDate,<BR>EpisodeNumber=@Episod eNumber, Filename=@Filename WHERE<BR>(EpisodeID=@EpisodeID);"; //have put newlines in for clarity, not in original string<BR><BR>//creating the SqlCommand<BR>SqlCommand sqlUpdateCommand=new SqlCommand(updateCmd, connEpisodes);<BR>sqlUpdateCommand.CommandType=Com mandType.Text;<BR><BR>//adding parameters<BR>SqlParameter sqlpEpID=new SqlParameter("@EpisodeID", SqlDbType.Int, 4);<BR>sqlpEpID.Value=strEpisodeID;<BR>sqlUpdateCo mmand.Parameters.Add(sqlpEpID);<BR><BR>SqlParamete r sqlpTitle=new SqlParameter("@Title", SqlDbType.NText, 200);<BR>sqlpTitle.Value=strTitle;<BR>sqlUpdateCom mand.Parameters.Add(sqlpTitle);<BR>//etc.<BR><BR>//running the query<BR>sqlUpdateCommand.Connection.Open();<BR>sq lUpdateCommand.ExecuteNonQuery();<BR>sqlUpdateComm and.Connection.Close();<BR><BR>//changing the grid out of editable, rebinding<BR>gridEpisode.EditItemIndex=-1;<BR>gridEpisode.DataBind();<BR>}<BR>[/code]

  2. #2
    Join Date
    Dec 1969

    Default RE: Grid_Update function of a DataGrid not actuall

    Assuming you are executing your updates in response to a button click, it may be that you are retrieving data and populating your grid in the Page_Load event, but your updates don&#039;t occur until later when the button click is processed. If so, you are retrieving the old data from the database and overwriting the changes you had made before saving those changes to your database. To avoid this, put the grid1.Databind statment under the Page_PreRender event.<BR><BR>Your code does a very nice job of "manually" updating the database. You have created a brand new SQLCommand, connected it to the database, populated it with parameter values and updated the database. <BR><BR>You should probably be using the capabilities built in to the SqlDataAdapter and Dataset objects for updating the database. Since you are apparently updating a single table in your database, and assuming you are using the designer to create the SqlDataAdapter and Dataset objects, then these objects are hooked up to work together. The SqlDataAdapter object contains four SQLCommand properties that are populated automatically when you use the designer to create an adapter for a specific database table:<BR>1) SelectCommand - containing automatically built SQL statement to retrieve data from your table<BR>2) InsertCommand - containing SQL statement to insert data into your table<BR>3) UpdateCommand - containing SQL statement to update data in your table<BR>4) DeleteCommand - containing SQL statement to delete data from your table<BR><BR>Assuming <BR> dataAdapt is your SqlDataAdapter object<BR> ds is your Dataset object<BR>then to retrieve data into your dataset, execute the following:<BR> dataAdapt.Fill(ds)<BR>This code will use the automatically generated SelectCommand to query the database and fill the dataset<BR><BR>Similarly to update the database from the dataset, use the following statement:<BR> dataAdapt.Update(ds)<BR>This code will walk through each row in the dataset, check its status to determine whether an insert, update or delete (or no change) is required, and then make use of the InsertCommand, UpdateCommand and DeleteCommand objects as necessary to execute the appropriate update for each row. <BR><BR>In this way you only need the one DataAdapter and Dataset objects to manage data for presentation in the datagrid and for update to the database.<BR><BR>HTH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts