UpdatingRecords

[Caden]

Hi Tek-Tips

I'm using the Microsoft Web Matrix to build this little app i'm doing. I have no idea why it isn't working, it was working before, I don't know what I did, and it isn't working now. So here we go.


I'm trying to update records in a datagrid, nice and simple...here's the function.


Function Update(ByVal iD As Integer, ByVal shortDesc As String, ByVal salaryRange As String, ByVal benefits As String, ByVal longDesc As String) As Integer
Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=C:\Joel's Work\"& _
"jobs.mdb"
Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)

Dim queryString As String = "UPDATE [jobs] SET [ShortDesc]=@ShortDesc, [SalaryRange]=@SalaryRange, [Benefits]="& _
"@Benefits, [LongDesc]=@LongDesc WHERE ([jobs].[ID] = @ID)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_iD As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_iD.ParameterName = "@ID"
dbParam_iD.Value = iD
dbParam_iD.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_iD)
Dim dbParam_shortDesc As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_shortDesc.ParameterName = "@ShortDesc"
dbParam_shortDesc.Value = shortDesc
dbParam_shortDesc.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_shortDesc)
Dim dbParam_salaryRange As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_salaryRange.ParameterName = "@SalaryRange"
dbParam_salaryRange.Value = salaryRange
dbParam_salaryRange.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_salaryRange)
Dim dbParam_benefits As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_benefits.ParameterName = "@Benefits"
dbParam_benefits.Value = benefits
dbParam_benefits.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_benefits)
Dim dbParam_longDesc As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_longDesc.ParameterName = "@LongDesc"
dbParam_longDesc.Value = longDesc
dbParam_longDesc.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_longDesc)

Dim rowsAffected As Integer = 0
dbConnection.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try

Return rowsAffected
End Function



here's the sub routine to call that function...

Sub entries_Update (sender as Object, e as DataGridCommandEventArgs)

Dim iD as Integer = e.Item.Cells(1).Text

Dim shortdescTextBox as TextBox = e.Item.Cells(3).Controls(0)
Dim salaryrangeTextBox as TextBox = e.Item.Cells(4).Controls(0)
Dim benefitsTextBox as TextBox = e.Item.Cells(5).Controls(0)
Dim longdescTextBox as TextBox = e.Item.Cells(6).Controls(0)

Update(iD, shortdescTextBox.Text, salaryrangeTextBox.text, benefitsTextBox.Text, longdescTextBox.Text)


entries.EditItemIndex = -1
entries.DataSource=getjobs()
entries.Databind()

End Sub


and here is my datagrid...

<aspataGrid id="entries" runat="server" OnDeleteCommand="entries_delete" AutoGenerateColumns="False" OnEditCommand="entries_Edit" OnCancelCommand="entries_Cancel" OnUpdateCommand="entries_Update">
<Columns>
<asp:EditCommandColumn ButtonType="LinkButton" UpdateText="Update" HeaderText="Admin" CancelText="Cancel" EditText="Edit"></asp:EditCommandColumn>
<asp:BoundColumn DataField="ID" ReadOnly="True" HeaderText="ID"></asp:BoundColumn>
<asp:BoundColumn DataField="date" ReadOnly="True" HeaderText="Date" DataFormatString="{0:g}"></asp:BoundColumn>
<asp:BoundColumn DataField="shortdesc" HeaderText="Job Title"></asp:BoundColumn>
<asp:BoundColumn DataField="salaryrange" HeaderText="Salary Range"></asp:BoundColumn>
<asp:BoundColumn DataField="benefits" HeaderText="Benefits"></asp:BoundColumn>
<asp:BoundColumn DataField="longdesc" HeaderText="Long Description"></asp:BoundColumn>
<asp:ButtonColumn Text="Delete" CommandName="Delete"></asp:ButtonColumn>
</Columns>
</aspataGrid>



I click edit...I fill in something into one of the boxes...I click update, and it doesn't update. It just goes right back to what I had before...so, eg.

I have blah blah blah blah in the boxes
I erase all the info and hit update
I see blah blah blah blah still there.

If you guys can see something i'm missing, that would be awesome, i'm sure it's something tiny i'm totally overlooking.

thanks
Caden

 

[adamroof]

In page_load...
If not page.ispostback Then
bindmydatagrid()
end if

that could be culprit...when you click update you are posting back to the server, when you post back, your dg is reloaded with the values originally then updated to be what they were in the first place..

??

 

[Caden]

Sub Page_Load(sender As Object, e As EventArgs)

If Not Page.IsPostBack Then
entries.Datasource=getjobs()
entries.Databind()
End If
End Sub

Got that in there already

 

[adamroof]

Should
Update(iD, shortdescTextBox.Text, salaryrangeTextBox.text, benefitsTextBox.Text, longdescTextBox.Text)

be

Update(id, shortdescTextBox...)

not

shortdescTextBox.Text

because you declared that as e.Item.Cells(3).Controls(0)
already??

 

[Caden]

because they are declared "as textbox" it needs the .Text when I pass the variable.

 

[adamroof]

WELL FINE!

here is my dg update that i created with web matrix originally

    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

maybe since it is an item template, you need to do the find control? well, even then, you stated it worked before...

hmmm.

 

[Caden]

I'm bashing my head against the desk trying to figure out why this doesn't work.

All of these idea's are great, anyone/everyone post your wacky idea's and i'll try it!

 

[Caden]

One small thing...I took out the ID check, and it updates now...but since there is no check it will make all fields exactly the same.

I don't know if that helps, but it should

 

[adamroof]

Put the catch ex As Exception in your code for one.

make a label with id=message somewhere

after the update, toss one of the values that should be changed to that label as well. When your page posts back, you'll see the value that it had at runtime.

i would say declare the values into strings...
mystring = CType(e.Item(0).Cells(3).Controls(0), TextBox).Text

saves on code length too...
Dim iD As Integer = CType(e.Item(0).Cells(1).Text, Integer)

???

 

[Caden]

I tried that...I put a label down pulling the ID of the record.

So, when I hit update, it gave me the ID that it pulled and it pulled the right number.

So I know it is pulling the values.

 

[adamroof]

what would happen if you made it...

rowsAffected = dbCommand.ExecuteScalar()

not sure why you need to return the rows...
why is it in a function?

why not just...

dbCommand.ExecuteNonQuery

to test??

 

[Caden]

nuttin changed.

Keep in mind, the update function was created by the web matrix. The code should work. It worked before, it should work now.

 

[adamroof]

i use sql tables, but i recreated your mdb and path, and i got it to update... [jobs].[id] was changed to just [id]

Sub entries_Update(sender as Object, e as DataGridCommandEventArgs)

Dim iD as Integer = CType(e.Item.Cells(1).Text, Integer)

Dim shortDesc as String = CType(e.Item.Cells(3).Controls(0), TextBox).Text
Dim salaryrange as String = CType(e.Item.Cells(4).Controls(0), TextBox).Text
Dim benefits as String = CType(e.Item.Cells(5).Controls(0), TextBox).Text
Dim longdesc as String = CType(e.Item.Cells(6).Controls(0), TextBox).Text

Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=C:\Joel's Work\jobs.mdb"
Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)

Dim queryString As String = "UPDATE [jobs] SET [ShortDesc]=@ShortDesc, [SalaryRange]=@SalaryRange, [Benefits]="& _
"@Benefits, [LongDesc]=@LongDesc WHERE [ID]=@ID"
Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
        dbCommand.CommandText = queryString
        dbCommand.Connection = dbConnection

Dim dbParam_iD As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
        dbParam_iD.ParameterName = "@ID"
        	dbParam_iD.Value = iD
        dbParam_iD.DbType = System.Data.DbType.Int32
        dbCommand.Parameters.Add(dbParam_iD)
Dim dbParam_shortDesc As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
        dbParam_shortDesc.ParameterName = "@ShortDesc"
        	dbParam_shortDesc.Value = shortDesc
        dbParam_shortDesc.DbType = System.Data.DbType.String
        dbCommand.Parameters.Add(dbParam_shortDesc)
Dim dbParam_salaryRange As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
        dbParam_salaryRange.ParameterName = "@SalaryRange"
        	dbParam_salaryRange.Value = salaryRange
        dbParam_salaryRange.DbType = System.Data.DbType.String
        dbCommand.Parameters.Add(dbParam_salaryRange)
Dim dbParam_benefits As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
        dbParam_benefits.ParameterName = "@Benefits"
        	dbParam_benefits.Value = benefits
        dbParam_benefits.DbType = System.Data.DbType.String
        dbCommand.Parameters.Add(dbParam_benefits)
Dim dbParam_longDesc As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
        dbParam_longDesc.ParameterName = "@LongDesc"
        	dbParam_longDesc.Value = longDesc
        dbParam_longDesc.DbType = System.Data.DbType.String
        dbCommand.Parameters.Add(dbParam_longDesc)

        dbConnection.Open()
        Try
        	dbCommand.ExecuteNonQuery()
	Catch ex As Exception
		message.Text = ex.ToString()
	Finally
		dbConnection.Close()
	End Try
	
	entries.EditItemIndex = -1
	entries.Databind()
End Sub

Web matrix is not always right... my web links did same until i rewrote it myself.

 

[Caden]

Alright, I added it to my page like this...

Function Update(ByVal iD As Integer, ByVal shortDesc As String, ByVal salaryRange As String, ByVal benefits As String, ByVal longDesc As String) As Integer
Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=C:\Joel's Work\jobs.mdb"
Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)

Dim queryString As String = "UPDATE [jobs] SET [ShortDesc]=@ShortDesc, [SalaryRange]=@SalaryRange, [Benefits]="& _
"@Benefits, [LongDesc]=@LongDesc WHERE [ID]=@ID"
Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_iD As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_iD.ParameterName = "@ID"
dbParam_iD.Value = iD
dbParam_iD.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_iD)
Dim dbParam_shortDesc As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_shortDesc.ParameterName = "@ShortDesc"
dbParam_shortDesc.Value = shortDesc
dbParam_shortDesc.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_shortDesc)
Dim dbParam_salaryRange As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_salaryRange.ParameterName = "@SalaryRange"
dbParam_salaryRange.Value = salaryRange
dbParam_salaryRange.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_salaryRange)
Dim dbParam_benefits As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_benefits.ParameterName = "@Benefits"
dbParam_benefits.Value = benefits
dbParam_benefits.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_benefits)
Dim dbParam_longDesc As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_longDesc.ParameterName = "@LongDesc"
dbParam_longDesc.Value = longDesc
dbParam_longDesc.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_longDesc)

dbConnection.Open()
Try
dbCommand.ExecuteNonQuery()
Catch ex As Exception
message.Text = ex.ToString()
Finally
dbConnection.Close()
End Try

Return rowsAffected
End Function


Sub entries_Update (sender as Object, e as DataGridCommandEventArgs)

Dim iD as Integer = CType(e.Item.Cells(1).Text, Integer)

Dim shortDesc as String = CType(e.Item.Cells(3).Controls(0), TextBox).Text
Dim salaryrange as String = CType(e.Item.Cells(4).Controls(0), TextBox).Text
Dim benefits as String = CType(e.Item.Cells(5).Controls(0), TextBox).Text
Dim longdesc as String = CType(e.Item.Cells(6).Controls(0), TextBox).Text


Update(iD, shortDesc, salaryrange, benefits, longdesc)

entries.EditItemIndex = -1
entries.DataSource=getjobs()
entries.Databind()

End Sub


And the same thing happened. Although that's weird to me...anyhow. Did I add it wrong?

 

[adamroof]

Did you not get error on Return rowsAffected ?? your not declaring that...

try it all in one sub as above... Jared would be proud. ixnay on the unctionfay. just for now...

 

[Caden]

That's right out of my code...I get no errors and have the same problem.

Initially my web matrix was messed, so I restarted it. But no luck.


Sub entries_Update(sender as Object, e as DataGridCommandEventArgs)

Dim iD as Integer = CType(e.Item.Cells(1).Text, Integer)

Dim shortDesc as String = CType(e.Item.Cells(3).Controls(0), TextBox).Text
Dim salaryrange as String = CType(e.Item.Cells(4).Controls(0), TextBox).Text
Dim benefits as String = CType(e.Item.Cells(5).Controls(0), TextBox).Text
Dim longdesc as String = CType(e.Item.Cells(6).Controls(0), TextBox).Text

Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=C:\Joel's Work\jobs.mdb"
Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)

Dim queryString As String = "UPDATE [jobs] SET [ShortDesc]=@ShortDesc, [SalaryRange]=@SalaryRange, [Benefits]="& _
"@Benefits, [LongDesc]=@LongDesc WHERE [ID]=@ID"
Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_iD As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_iD.ParameterName = "@ID"
dbParam_iD.Value = iD
dbParam_iD.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_iD)
Dim dbParam_shortDesc As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_shortDesc.ParameterName = "@ShortDesc"
dbParam_shortDesc.Value = shortDesc
dbParam_shortDesc.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_shortDesc)
Dim dbParam_salaryRange As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_salaryRange.ParameterName = "@SalaryRange"
dbParam_salaryRange.Value = salaryRange
dbParam_salaryRange.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_salaryRange)
Dim dbParam_benefits As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_benefits.ParameterName = "@Benefits"
dbParam_benefits.Value = benefits
dbParam_benefits.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_benefits)
Dim dbParam_longDesc As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_longDesc.ParameterName = "@LongDesc"
dbParam_longDesc.Value = longDesc
dbParam_longDesc.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_longDesc)

dbConnection.Open()
Try
dbCommand.ExecuteNonQuery()
Finally
dbConnection.Close()
End Try

entries.EditItemIndex = -1
entries.Datasource=getjobs()
entries.Databind()

End Sub

 

[adamroof]

Ok, i have the code for you,

i can seem to add ok, but the update aint workin!

gimme a little bit to find out more, then ill post the completed code.

 

[adamroof]

Its your PARAMETER FOR THE ID!!!
Remove parameter and add string solely!

dbCommand.CommandText = "UPDATE jobs SET ShortDesc = @sd, SalaryRange = @sr, LongDesc = @ld WHERE jobID =" & LinkID!

Here comes the code...

Two pages - links.aspx and links.aspx.vb

 

[adamroof]

Links.aspx.vb

Imports System
Imports System.Data
Imports System.Data.OleDb
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 = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=C:\test\test.mdb"
    Dim SelectCommand As String 
    Dim SelectFilter As String = "SELECT DISTINCT ShortDesc FROM jobs"
    
    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 System.Data.OleDb.OleDbConnection(connectionString)
        If ViewState("isFilter") = "True" Then
			SelectCommand = "SELECT * FROM jobs WHERE ShortDesc LIKE '" & ViewState("strFilter") & "'"
		Else
			SelectCommand = "SELECT * FROM jobs ORDER BY ShortDesc"
		End If
    	Dim ds As New DataSet
    	Dim adapter As New OleDbDataAdapter()
    	adapter.SelectCommand = new OleDbCommand(SelectCommand, myConnection)
    	adapter.Fill(ds)
    
        DataGrid1.DataSource = ds
        DataGrid1.DataBind()
    End Sub
    
    Sub BindTypeDDL()
    	Dim myConnection As New System.Data.OleDb.OleDbConnection(connectionString)
    	Dim ds As New DataSet
    	Dim adapter As New OleDbDataAdapter()
		Dim SelectCommand As String = "SELECT DISTINCT ShortDesc FROM jobs ORDER BY ShortDesc"
    	adapter.SelectCommand = new OleDbCommand(SelectCommand, myConnection)
    	adapter.Fill(ds)				  		
			TypeDDL.DataSource = ds
			TypeDDL.DataBind()
		myConnection.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, int32)
		Dim shortDesc As String = Replace(CType(e.Item.Cells(3).Controls(0), TextBox).Text, "'", "''")
        Dim salaryRange As String = (CType(e.Item.Cells(4).Controls(0), TextBox)).Text
        Dim longDesc As String = Replace((CType(e.Item.FindControl("LinkDescBox"), TextBox)).Text, "'", "''")
        
        message.Text = linkid & ";" & shortDesc & ";" & salaryRange & ";" & longDesc
          
        Dim myConnection As New System.Data.OleDb.OleDbConnection(connectionString)
       	Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
        dbCommand.Connection = myConnection
            
        If AddingNew = True Then
            dbCommand.CommandText = "INSERT INTO jobs(ShortDesc, SalaryRange, LongDesc) VALUES (@sd, @sr, @ld)"
        Else
            dbCommand.CommandText = "UPDATE jobs SET ShortDesc = @sd, SalaryRange = @sr, LongDesc = @ld WHERE jobID =" & LinkID
        End If
    
Dim dbParam_shortDesc As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
        dbParam_shortDesc.ParameterName = "@sd"
        	dbParam_shortDesc.Value = shortDesc
        dbParam_shortDesc.DbType = System.Data.DbType.String
        dbCommand.Parameters.Add(dbParam_shortDesc)
Dim dbParam_salaryRange As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
        dbParam_salaryRange.ParameterName = "@sr"
        	dbParam_salaryRange.Value = salaryRange
        dbParam_salaryRange.DbType = System.Data.DbType.String
        dbCommand.Parameters.Add(dbParam_salaryRange)
Dim dbParam_longDesc As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
        dbParam_longDesc.ParameterName = "@ld"
        	dbParam_longDesc.Value = longDesc
        dbParam_longDesc.DbType = System.Data.DbType.String
        dbCommand.Parameters.Add(dbParam_longDesc)
    
        ' execute the command
        Try
            myConnection.Open()
            dbCommand.ExecuteNonQuery()
        Catch ex as Exception
            Message.Text = ex.ToString()
        Finally
            myConnection.Close()
            dbCommand.Dispose()
    		myConnection.Dispose()
        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()
    
    End Sub
    
    Sub DataGrid_Cancel(Sender As Object, E As DataGridCommandEventArgs)   
        DataGrid1.EditItemIndex = -1
        DataGrid1.CurrentPageIndex = 0
        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 System.Data.OleDb.OleDbConnection(connectionString)
       	Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
        dbCommand.Connection = myConnection
        dbCommand.CommandText = "DELETE FROM jobs WHERE jobID=@id"
        
		Dim dbParam_iD As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
        dbParam_iD.ParameterName = "@id"
        	dbParam_iD.Value = keyValue
        dbParam_iD.DbType = System.Data.DbType.Int32
        dbCommand.Parameters.Add(dbParam_iD)
    	
    	Try
            myConnection.Open()
            dbCommand.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 System.Data.OleDb.OleDbConnection(connectionString)
            
            If ViewState("isFilter") = "True" Then
				SelectCommand = "SELECT * FROM jobs WHERE ShortDesc LIKE '" & ViewState("strFilter") & "'"
			Else
				SelectCommand = "SELECT * FROM jobs ORDER BY ShortDesc"
			End If

    		Dim ds As New DataSet
    		Dim adapter As New OleDbDataAdapter()
    		adapter.SelectCommand = new OleDbCommand(SelectCommand, myConnection)
    		adapter.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

links.aspx

<%@ Page Language="VB" debug="true" Inherits="links_cb" Src="links.aspx.vb" %>
<script src="js\page.js" language="javascript" type="text/javascript"></script>
<html>
<head>
	<link rel=stylesheet type="text/css" href="css\page.css">
	<TITLE>Web Links</TITLE>
</head>
<body>
    <form runat="server">
    <div class=head>Job Test Database</div>
    <hr width=98% align=left>
    	<table border=0 cellspacing=0 cellpadding=0 width=98% class=hyper><tr><td align=left width=70%>
    	<a href="incidents.aspx">Open Incidents</a>&nbsp;&nbsp;<a href="incidentsbyuser.aspx">Client History</a>&nbsp;&nbsp;<a href="incidentsOLD.aspx">Completed Incidents</a>&nbsp;&nbsp;<A onMouseover="this.style.cursor='hand'" onClick=makeNewWindow2()><U>Add New Incident</U></A></td>
    	<td align=right width=30%>
    	Filter:&nbsp;<asp:DropDownList runat="server" id="TypeDDL" DataValueField="ShortDesc" AutoPostBack="True" OnSelectedIndexChanged="FilterDG_Change" />
    	<asp:Button id="resetFilter" ButtonType="LinkButton" onClick="resetFilter_Click" runat=server text="Reset" />
    	</td></tr></table>
	<hr width=98% align=left>
	<asp:LinkButton id="LinkButton1" onclick="AddNew_Click" runat="server" Text="Add New Web Link" class=hyper /><br><br>
        <asp:datagrid id="DataGrid1" runat="server" AutoGenerateColumns="false" width="98%" CellSpacing="1" GridLines="1" CellPadding="3" BackColor="White" ForeColor="Black" OnPageIndexChanged="DataGrid_Page" PageSize="15" AllowPaging="true" OnDeleteCommand="DataGrid_Delete" OnCancelCommand="DataGrid_Cancel" OnUpdateCommand="DataGrid_Update" OnEditCommand="DataGrid_Edit" OnItemCommand="DataGrid_ItemCommand" DataKeyField="jobID" class=dg>
            <HeaderStyle backcolor="#339966" cssClass=dgHead />
            <AlternatingItemStyle backcolor=#FFFFFF />
            <PagerStyle horizontalalign="Right" backcolor="#C6C3C6" mode="NumericPages" font-size="smaller" Position="TopAndBottom" />
            <ItemStyle backcolor="#DEDFDE" />
            <Columns>
                <asp:EditCommandColumn ButtonType="LinkButton" UpdateText="Update" CancelText="Cancel" EditText="Edit" ItemStyle-Font-Size="smaller" ItemStyle-Width=20 />
                <asp:ButtonColumn Text="Delete" CommandName="Delete" ItemStyle-Font-Size="smaller" ItemStyle-Width=20 />
				<asp:BoundColumn DataField="jobID" HeaderText="Job ID" ItemStyle-Width=50 ReadOnly="True" />
				<asp:BoundColumn DataField="ShortDesc" HeaderText="Type" ItemStyle-Wrap="False" ItemStyle-VerticalAlign="Top" />
				<asp:BoundColumn DataField="SalaryRange" HeaderText="Type" ItemStyle-Wrap="False" ItemStyle-VerticalAlign="Top" />
			<asp:TemplateColumn ItemStyle-VerticalAlign="Top" >
				<HeaderTemplate> Description </HeaderTemplate>
				<ItemTemplate>
					<asp:Label Text='<%# DataBinder.Eval(Container.DataItem, "LongDesc") %>' runat="server" />
				</ItemTemplate>
					<EditItemTemplate>
						<asp:TextBox id=LinkDescBox TextMode=multiline width=400 height=75 text='<%# DataBinder.Eval(Container.DataItem, "LongDesc") %>' runat=server />
					</EditItemTemplate>
			</asp:TemplateColumn>
            </Columns>
        </asp:datagrid>
        <br>
        <br>
        <asp:Label id=Message runat=server width="80%" enableviewstate="false" class=messages />
    </form>
</body>
</html>