Update Not Working

I am using a web user control to make updates.  I have been working with Team Telerik on this.  Someone sent me a project back that didn't work the update feature.  I need help trying to figure out why I can't write back to the database on an update.  I am not using Automatic updates.   They don't work with this method of the Rad Grid. Do I need to write an update statement?  If so where does this statement need to go?  Below is my source code.

Web User Control Code Behind:

private object _dataItem = null;

protected void Page_Load(object sender, EventArgs e)
{

}

public object DataItem
{
get
{
return this._dataItem;
}
set
{
this._dataItem = value;
}
}

Grid Code Behind:

Public Shared connectionString As String = ("Server=XXX")

Public Shared Function GetDataTableRadGrid(ByVal Sql As String) As DataTable

Dim conn As New SqlConnection(connectionString)
Dim adapter1 As New SqlDataAdapter
adapter1.SelectCommand = New SqlCommand(Sql, conn)

Dim Grid As New DataTable
conn.Open()

Try
adapter1.Fill(Grid)
Finally
conn.Close()
End Try

Return Grid

End Function

Public ReadOnly Property ProfileTable() As DataTable

Get
Dim obj As Object = Me.Session("Profile")
If (Not obj Is Nothing) Then
Return CType(obj, DataTable)
End If

Dim myDataTable As DataTable = New DataTable
myDataTable = GetDataTableRadGrid("SELECT * FROM Profile Where Status='A' Order By KProfile")
Me.Session("Profile") = myDataTable
Return myDataTable
End Get

End Property

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Session.Clear()

End Sub

Public Sub RadGrid1_NeedDataSource(ByVal source As Object, ByVal e As Telerik.WebControls.GridNeedDataSourceEventArgs) Handles RadGrid1.NeedDataSource

Me.RadGrid1.DataSource = Me.ProfileTable

End Sub

Public Sub RadGrid1_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles RadGrid1.PreRender

If Not IsPostBack Then
Me.RadGrid1.MasterTableView.Items(1).Edit = False
Me.RadGrid1.MasterTableView.Rebind()
End If

End Sub

Public Sub RadGrid1_UpdateCommand(ByVal source As Object, ByVal e As Telerik.WebControls.GridCommandEventArgs) Handles RadGrid1.UpdateCommand

Dim editedItem As GridEditableItem = CType(e.Item, GridEditableItem)
Dim MyUserControl As UserControl = CType(e.Item.FindControl(GridEditFormItem.EditFormUserControlID), UserControl)

'Locate the changed row in the DataSource
Dim changedRows As DataRow() = Me.ProfileTable.Select("ProfileID = " & editedItem.OwnerTableView.DataKeyValues(editedItem.ItemIndex)("ProfileID"))


If (Not changedRows.Length = 1) Then
e.Canceled = True
Return
End If

'Update new values
Dim newValues As Hashtable = New Hashtable

'newValues("KProfile") = CType(MyUserControl.FindControl("KProfile"), TextBox).Text
'newValues("KName") = CType(MyUserControl.FindControl("KName"), TextBox).Text
'newValues("KFunction") = CType(MyUserControl.FindControl("KFunction"), TextBox).Text
newValues("ADName") = CType(MyUserControl.FindControl("ADName"), DropDownList).SelectedItem.Text
newValues("ADProfile") = CType(MyUserControl.FindControl("ADProfile"), TextBox).Text

changedRows(0).BeginEdit()
Try
Dim entry As DictionaryEntry
For Each entry In newValues
changedRows(0)(CType(entry.Key, String)) = entry.Value
Next
changedRows(0).EndEdit()
Me.ProfileTable.AcceptChanges()
Catch ex As Exception
changedRows(0).CancelEdit()
e.Canceled = True
End Try
End Sub

Lewis,

You definitely will need to call a SQL Update statement to get new values back into your database. You can place this statement in your RadGrid1_UpdateCommand event, passing the values you pull from the grid into parameters of a SQL statement.

Have you performed simple SQL Updates with ASP.NET before? If you need more basic information, let me know and I'll be happy to post some links to helpful tutorials.

Thanks~

I have but outside of the RadGrid.  This is my concern.  The RADGrid is very challenging.  The update command that I have already...where does the new sql updates go?  Within that update command?  I need to write a command to update only two fields.

Thanks for the quick feedback. 

Are you able to successfully extract the updated values in your Update Command yet? If you are, then all you have to do is take your new values and execute an UPDATE statement in the Update Command using a SqlCommand object (or similar). Take a look at this thread for some more help on the topic.

Thanks~

Since I am databinding from the codebehind page I have to write the SQL update within my:

Public Sub RadGrid1_UpdateCommand(ByVal source As Object, ByVal e As Telerik.WebControls.GridCommandEventArgs) Handles RadGrid1.UpdateCommand

End Sub


OR can I create I do it in the aspx page like the example? 

I assume I have to write it in the codebehind, but I am unsure on where to place it.

Put it in your code behind in the Update Command area. That is the event that will fire when an user clicks the "Update" button in your grid.

Take a look at the example in the forum thread I linked to in my last post for an example of where to put your code and how to extract the new values from the grid.

Thanks~

I'm sorry for being a little dense.  I took the example project and upload and checked it out.  It worked great.  When I try to translate that into my project it doesn't seem to work.

The current EditFormType does not support the requested editing capabilities

Here is how I translated it to fit my sql scheme...

Public Sub RadGrid1_UpdateCommand(ByVal source As Object, ByVal e As Telerik.WebControls.GridCommandEventArgs) Handles RadGrid1.UpdateCommand

Dim editedItem As GridEditableItem = e.Item
Dim manager As GridEditManager = editedItem.EditManager

Dim cmdText As String = "UPDATE Profile SET ADName=@ADName, ADProfile=@ADProfile"

Dim column As GridColumn

For Each column In RadGrid1.MasterTableView.RenderColumns

If TypeOf column Is IGridEditableColumn Then

Dim editableCol As IGridEditableColumn = column

If editableCol.IsEditable Then

Dim editor As IGridColumnEditor = manager.GetColumnEditor(editableCol)

Dim editorType As String = CType(editor, Object).ToString()

Dim editorValue As Object = Nothing

If TypeOf editor Is GridTextColumnEditor Then

editorValue = CType(editor, GridTextColumnEditor).Text

cmdText = cmdText + column.UniqueName + "=" + String.Format("'{0}', ", editorValue)

End If

End If

End If

Next column

 

Dim cnn As New SqlConnection("ConnectionString")

Dim command As SqlCommand = New SqlCommand

command.Connection = cnn

cnn.Open()

Try

'cmdText = cmdText.TrimEnd(",", " ")

cmdText = cmdText + " WHERE ProfileID = @ProfileID"

command.CommandText = cmdText

command.ExecuteNonQuery()

Catch ex As Exception

'Label1.Text = "Unable to update values: " + ex.Message

e.Canceled = True

Finally

cnn.Close()

End Try

 

End Sub

Make sure you've set the GridEditFormType property correctly in the ASPX page. Post the code for your grid from your ASPX page if you want us to take a look.

Also, just as an FYI, there is a neat little button on the editor that you use to submit posts to the forums that allows you to submit code samples in an easy to read, pre-formatted manner. It should be the last button on the second row. Give it a try with your grid code.

Thanks~

My Grid Edit Form Type is using a webusercontrol.  The project that I am modeling on was something that Konstantin Petkov sent me.  I basically took that project and changed it to point to SQL.  The only thing that was left out, like you pointed out was the SQL Update in the ASPX page.  I assumed it was working until I closed out the session and reloaded the page.  That is when I noticed the data not getting updated.  The usercontrol is all set but I pasted the codebehind anyway.  Below is my code for the grid page.  I hope you straighten this out.  I'm struggling and getting very frustrated with RadGrid today.

WEBUserControl Code Behind:

private object _dataItem = null;

protected void Page_Load(object sender, EventArgs e)

{

}

public object DataItem

{

get

{

return this._dataItem;

}

set

{

this._dataItem = value;

}

}

ASPX Page



CODE Behind Page: 

Public Shared connectionString As String = ("Server=XXXXX")

Public Shared Function GetDataTableRadGrid(ByVal Sql As String) As DataTable

Dim conn As New SqlConnection(connectionString)

Dim adapter1 As New SqlDataAdapter

adapter1.SelectCommand = New SqlCommand(Sql, conn)

Dim Grid As New DataTable

conn.Open()

Try

adapter1.Fill(Grid)

Finally

conn.Close()

End Try

Return Grid

End Function

Public ReadOnly Property ProfileTable() As DataTable

Get

Dim obj As Object = Me.Session("Profile")

If (Not obj Is Nothing) Then

Return CType(obj, DataTable)

End If

Dim myDataTable As DataTable = New DataTable

myDataTable = GetDataTableRadGrid("SELECT * FROM Profile Where Status='A' Order By KProfile")

Me.Session("Profile") = myDataTable

Return myDataTable

End Get

End Property

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Session.Clear()

End Sub

Public Sub RadGrid1_NeedDataSource(ByVal source As Object, ByVal e As Telerik.WebControls.GridNeedDataSourceEventArgs) Handles RadGrid1.NeedDataSource

MeMe.RadGrid1.DataSource = Me.ProfileTable

End Sub

Public Sub RadGrid1_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles RadGrid1.PreRender

If Not IsPostBack Then

Me.RadGrid1.MasterTableView.Items(1).Edit = False

Me.RadGrid1.MasterTableView.Rebind()

End If

End Sub

Public Sub RadGrid1_UpdateCommand(ByVal source As Object, ByVal e As Telerik.WebControls.GridCommandEventArgs) Handles RadGrid1.UpdateCommand

Dim editedItem As GridEditableItem = CType(e.Item, GridEditableItem)

Dim MyUserControl As UserControl = CType(e.Item.FindControl(GridEditFormItem.EditFormUserControlID), UserControl)

'Locate the changed row in the DataSource

Dim changedRows As DataRow() = Me.ProfileTable.Select("ProfileIDProfileID = " & editedItem.OwnerTableView.DataKeyValues(editedItem.ItemIndex)("ProfileID"))

If (Not changedRows.Length = 1) Then

e.Canceled = True

Return

End If

'Update new values

Dim newValues As Hashtable = New Hashtable

newValues("ADName") = CType(MyUserControl.FindControl("ADName"), DropDownList).SelectedItem.Text

newValues("ADProfile") = CType(MyUserControl.FindControl("ADProfile"), TextBox).Text

Dim cnn As New SqlConnection(connectionString)

Dim strUpdate As String

Dim cmdUpdate As SqlCommand

strUpdate = "Update Profile Set ADName=@ADName, ADProfile=@ADProfile Where ProfileID=@ProfileID"

cmdUpdate = New SqlCommand(strUpdate, cnn)

cmdUpdate.Parameters.AddWithValue("@ADName", "ADName")

cmdUpdate.Parameters.AddWithValue("@ADProfile", "ADProfile")

cmdUpdate.Parameters.AddWithValue("@ProfileID", "ProfileID")

cnn.Open()

cmdUpdate.ExecuteNonQuery()

cnn.Close()

changedRows(0).BeginEdit()

Try

Dim entry As DictionaryEntry

For Each entry In newValues

changedRows(0)(CType(entry.Key, String)) = entry.Value

Next

changedRows(0).EndEdit()

Me.ProfileTable.AcceptChanges()

Catch ex As Exception

changedRows(0).CancelEdit()

e.Canceled = True

End Try

End Sub

With the help of another I was able to get through this and finally get a working copy of RadGrid updating a SQL Database through a user control. 

Here is my code incase anyone needs help with this.  I think Telerik in the future needs to provide examples to all types of database updates and not just Access. Access is not a real world or corporate example.  The examples that come with the product fall short of actually doing and a database update.  I still love the RadGrid. It is an amazing piece of software.

Thanks to all your help.

 

Hi Lewis,

Thank you for the nice words about our grid control.

With the automatic operations under .NET 2.0 you do not need to write any code in order to perform the database data changes when modifying the grid content. This automatic data editing is supported with auto-generated edit forms (InPlace and EditForms edit mode) and FormTemplate custom edit form. The resources concerning this matter are linked below:

http://www.telerik.com/r.a.d.controls/Grid/Examples/DataEditing/AllEditableColumns/DefaultCS.aspx

http://www.telerik.com/help/radgrid/v3_Net2/?grdAutomaticDataSourceOperations.html 
http://www.telerik.com/help/radgrid/v3_Net2/?APIForControlling_Net2.html
http://www.telerik.com/help/radgrid/v3_Net2/?grdErrorHandling.html

I agree with you that the product documentation does not include entire imlpementation about how to update your database with values from WebUserControl custom edit form, however we plan to extend it for the next versions of the product to add this info as well. Most probably we will create samples or code library projects which represent how to update AccessDataSource/SqlDataSource from such type of edit form.

At this point the project you used for reference (enclosed to this forum post) and the directions from the last paragraph of this documentation topic:

http://www.telerik.com/help/radgrid/v3_Net2/?grdAutomaticDataSourceOperations.html

can be useful in achieving the real database update.

Best regards,
Stephen
the telerik team

Lewis,

I'm glad to see that you fixed your problem. Thank you also for posting your solution to help others who run into this problem in the future.

On a parting note, database updates to Access or Sql Server are not that different in ASP.NET. Usually, it's just the difference between using the OleDb data objects and Sql data objects. The general principle as far as grid is related is to put your database update code in the Update Command event procedure in your code- how you perform your database update is a general ASP.NET "problem".  I just want to make sure you understand that point so that you can more easily perform data access with .NET in the future by reusing the database code you created here.

In any event, have fun with your working grid and let us know if you run into any more trouble.

Thanks~

Hey Todd,

The update is working  but with one exception.  Users that have an apostrophe in their name don't get updated.  Everything works except names like Louise O'Donnell.  On the database side the table excepts varchar. 

My code is posted below.  Why wouldn't this work?

Lewis,

The apostrophe issue is an old one...it existed way back in classic ASP. You will run into this problem if you build your SQL command with text (as you demonstrated in your sample), but you can completely avoid it by using Parameters (while at the same time making your code MUCH more secure!).

You don't have to use a StoredProc to use parameters. You can simply build your SQL in code (like "UPDATE tblUsers SET userName = @userName WHERE userID = @userID") and then use the SqlCommand parameters collection to add your values to the query, like this:

myCmd.Parameters.AddWithValue("@userID", intUserID)
myCmd.Parameters.AddWithValue("@userName", strUserName)

That will elimate the problems you are having with apostrophes AND make your code less suceptible to SQL injection attacks (you'll be even more secure if you add the Sql DataType to the Parameter definition).

Give it a try and let me know how it goes.

Thanks~

P.S. If you cannot use parameters, here is an article explaining how to handle apostrophes and question marks in SQL statements.

Thanks for the reply Todd.  I tried following the SQL example and came up with the followind code:


The code still chokes on the SQL Update with an apostrophe. 

The overall code for the update command is now:


Please Advise.

Benefitted from the question and answers, thanks.

Rodney

Lewis,

I'm not sure where your SQL is choking. Everything looks okay if you must use the "string builder" SQL method. I'd still recommend that you use parameters if you have a predictable number of columns. They are much more secure and they may help you solve this problem. I always use parameters and never have trouble with apostrophes.

Also, as a general improvement to you code: if you decide to continue building your SQL as a dynamic string, you should use a StringBuilder instead of the String type. When you build a long string using the "myStr = myStr & "new values"" you are actually making a new copy of the string in memory. The "original" copy(s) do not get destroyed until the garbage collector removes them, which can have a very negative effect on performance.

The StringBuilder class, on the other hand, is designed to help you build large dynamic strings. It is a "mutable" class (vs. Strings that are "immutable"), which basically means it does not create a new copy of the string in memory as you append to it. This can deliver a good performance boost and it is (at the least) considered better practice for building long strings.

Hopefully that'll help you either way. =)

Thanks~