Updating Records

[Caden]

Hi Tek-Tips,

I'm having trouble figuring out the update function for updating records in a Datagrid.

I have the edit function and cancel function down no problem, thoes are pretty easy, but when it comes to updating, that is much more difficult.

What i'm looking for is either an easy example to follow or an easy tutorial to follow because everything I look at is really complicated.

If anyone could help, that would be great.

Thanks!

 

[adamroof]

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.HTMLControls
Imports Microsoft.VisualBasic

Public Class links_cb
	Inherits System.Web.UI.Page
    Protected TypeDDL As DropDownList
    Protected DataGrid1 As DataGrid
    Protected Message As Label

    Dim ConnectionString As String = "server=(local);database=helpdesk;trusted_connection=true"
    Dim SelectCommand As String 
    Dim SelectFilter As String = "SELECT DISTINCT LinkType FROM tblWebLink"
    
    Dim isEditing As Boolean = False
    
    Sub Page_Load(Sender As Object, E As EventArgs)
    
        If Not Page.IsPostBack Then
            BindGrid()
            BindTypeDDL()
        End If
    
    End Sub
    
    Sub BindGrid()
        Dim myConnection As New SqlConnection(ConnectionString)
        If ViewState("isFilter") = "True" Then
			SelectCommand = "SELECT * FROM tblWebLink WHERE LinkType LIKE '" & ViewState("strFilter") & "'"
		Else
			SelectCommand = "SELECT * FROM tblWebLink ORDER BY LinkType"
		End If

        Dim myCommand As New SqlDataAdapter(SelectCommand, myConnection)
    
        Dim ds As New DataSet()
        myCommand.Fill(ds)
    
        DataGrid1.DataSource = ds
        DataGrid1.DataBind()
    End Sub
    
    Sub BindTypeDDL()
    	Dim objConnection As New SqlConnection(ConnectionString)
		Dim objCommand As SqlCommand = New SqlCommand(SelectFilter, objConnection)
		Dim objAdapter As SqlDataAdapter = New SqlDataAdapter(objCommand)
		Dim objDataSet As DataSet = New DataSet()
				  		
  		objAdapter.Fill(objDataSet) 
			TypeDDL.DataSource = objDataSet
			TypeDDL.DataBind()
		objConnection.Close()
		TypeDDL.Items.Insert(0, new ListItem("Filter Type"))	
    End Sub
    
    Sub FilterDG_Change(sender As Object, e As EventArgs)
    	ViewState("strFilter") = TypeDDL.SelectedItem.Text
    	If ViewState("strFilter") = "Filter Type" Then
    		exit sub
    	Else
    		ViewState("isFilter") = "True"
    		BindGrid()
    	End If
    End Sub
    
    Sub resetFilter_Click(sender As Object, e As EventArgs)
    	ViewState("isFilter") = "False"
    	TypeDDL.SelectedIndex = 0
    	BindGrid()
    End Sub
       
    Sub DataGrid_ItemCommand(Sender As Object, E As DataGridCommandEventArgs) 
        CheckIsEditing(e.CommandName)
    End Sub
    
    Sub CheckIsEditing(commandName As String)
        If DataGrid1.EditItemIndex <> -1 Then
            If commandName <> "Cancel" And commandName <> "Update" Then
                Message.Text = "Your changes have not been saved yet.  Please press update to save your changes, or cancel to discard your changes, before selecting another item."
                isEditing = True
            End If 
        End If  
    End Sub
    
    Sub DataGrid_Edit(Sender As Object, E As DataGridCommandEventArgs)
        If Not isEditing Then
            DataGrid1.EditItemIndex = e.Item.ItemIndex
            BindGrid()
        End If
    End Sub
    
    Sub DataGrid_Update(Sender As Object, E As DataGridCommandEventArgs)
        Dim Linkid As Integer = Ctype(e.Item.Cells(2).Text, integer)
		Dim linkTypeTxt As String = Replace(CType(e.Item.Cells(3).Controls(0), TextBox).Text, "'", "''")
        Dim linkURLTxt2 As String = (CType(e.Item.FindControl("LinkURLTxtBox"), TextBox)).Text
        Dim LinkDescTxt2 As String = Replace((CType(e.Item.FindControl("LinkDescBox"), TextBox)).Text, "'", "''")
          
        Dim myConnection As New SqlConnection(ConnectionString)
        Dim UpdateCommand As SqlCommand = new SqlCommand()
        UpdateCommand.Connection = myConnection
            
        If AddingNew = True Then
            UpdateCommand.CommandText = "INSERT INTO tblWebLink(LinkURL, LinkDesc, LinkType) VALUES (@linkURL, @LinkDesc, @LinkType)"
        Else
            UpdateCommand.CommandText = "UPDATE tblWebLink SET LinkType = @linkType, LinkURL = @linkURL, LinkDesc = @LinkDesc WHERE LinkID = @id"
        End If
    
        UpdateCommand.Parameters.Add("@id", SqlDbType.int, 4).Value = Linkid
		UpdateCommand.Parameters.Add("@linkType", SqlDbType.VarChar, 50).Value = linkTypeTxt
        UpdateCommand.Parameters.Add("@linkURL", SqlDbType.VarChar,100).Value = linkURLTxt2
        UpdateCommand.Parameters.Add("@LinkDesc", SqlDbType.NText).Value = LinkDescTxt2
    
        ' execute the command
        Try
            myConnection.Open()
            UpdateCommand.ExecuteNonQuery()
    
        Catch ex as Exception
            Message.Text = ex.ToString()
    
        Finally
            myConnection.Close()
    
        End Try
    
        ' Resort the grid for new records
        If AddingNew = True Then
            DataGrid1.CurrentPageIndex = 0
            AddingNew = false
        End If
    
        ' rebind the grid
        DataGrid1.EditItemIndex = -1
        BindGrid()
        BindTypeDDL()
    
    End Sub
    
    Sub DataGrid_Cancel(Sender As Object, E As DataGridCommandEventArgs)   
        DataGrid1.EditItemIndex = -1
        BindGrid()
        AddingNew = False
    End Sub
    
    Sub DataGrid_Delete(Sender As Object, E As DataGridCommandEventArgs)
       If Not isEditing Then
            Dim keyValue As String = CStr(DataGrid1.DataKeys(e.Item.ItemIndex))
    
            Dim myConnection As New SqlConnection(ConnectionString)
            Dim DeleteCommand As New SqlCommand("DELETE FROM tblWebLink WHERE LinkID='" & keyValue & "'", myConnection)
    	
    	Try
            myConnection.Open()
            DeleteCommand.ExecuteNonQuery()
        Catch ex as Exception
       		message.Text = ex.ToString()
       	Finally
            myConnection.Close()
		End Try
    
        DataGrid1.CurrentPageIndex = 0
        DataGrid1.EditItemIndex = -1
        BindGrid()    
    End If
    End Sub
    
    Sub DataGrid_Page(Sender As Object, E As DataGridPageChangedEventArgs)
        If Not isEditing Then
            DataGrid1.EditItemIndex = -1
            DataGrid1.CurrentPageIndex = e.NewPageIndex
            BindGrid()
        End If
    End Sub
    
    Sub AddNew_Click(Sender As Object, E As EventArgs)      
        CheckIsEditing("")
        If Not isEditing = True Then
            AddingNew = True
            Dim myConnection As New SqlConnection(ConnectionString)
            
            If ViewState("isFilter") = "True" Then
				SelectCommand = "SELECT * FROM tblWebLink WHERE LinkType LIKE '" & ViewState("strFilter") & "'"
			Else
				SelectCommand = "SELECT * FROM tblWebLink ORDER BY LinkType"
			End If

            Dim myCommand As New SqlDataAdapter(SelectCommand, myConnection)
            Dim ds As New DataSet()
            myCommand.Fill(ds)
    
            ' add a new blank row to the end of the data
            Dim strType As String
            If ViewState("strFilter") Is Nothing Then
            	strType = ""
            Else
           		strType = ViewState("strFilter")
           	End If

            Dim rowValues As Object() = {999, strType, "", ""}
            ds.Tables(0).Rows.Add(rowValues)
    
            ' figure out the EditItemIndex, last record on last page
            Dim recordCount As Integer = ds.Tables(0).Rows.Count
    
            If recordCount > 1 Then
                recordCount -= 1
                DataGrid1.CurrentPageIndex = recordCount \ DataGrid1.PageSize
                DataGrid1.EditItemIndex = recordCount Mod DataGrid1.PageSize
            End If
    
            ' databind
            DataGrid1.DataSource = ds
            DataGrid1.DataBind()
    
        End If
    End Sub
       
    Property AddingNew() As Boolean   
        Get
            Dim o As Object = ViewState("AddingNew")
            If o Is Nothing Then
                Return False
            End If
            Return CBool(o)
        End Get
    
        Set(ByVal Value As Boolean)
            ViewState("AddingNew") = Value
        End Set 
    End Property
End Class