Hi,
I am new to asp. I am using the asp web matrix tool. I am using the excellent book "Beginning Dynamic Websites".

I used the editgrid example provided by web matrix.

I just need a little help on getting my data grid to work correctly. I used the editdata grid page from web matrix. I made all the changes to tailor it for my company. It works like a champ.

Because the grid returns 342 items we wanted to put a text box that when a few letters of the last name were entered would find the last names that were like the letter entered or matched the last name.

I got the text box wired to the grid and works great the first time thru. If the grid returns more than one page I get an error because there is nothing in the grid.

How can I keep the current selection from the text box and pass it so that when I page thru the grid it keeps track of the correct data??

I have attached my code below. THANKS!!

Barb


' TODO: update the ConnectionString and Command values for your application


Dim connectionString As String = "server='clevesql'; user id='sa'; password=''; database='vnaphonebook'"




Dim isEditing As Boolean = False


public SELECTCOMMAND As String


Sub Page_Load(Sender As Object, E As EventArgs)
If Not Page.IsPostBack Then

' Databind the data grid on the first request only
' (on postback, bind only in editing, paging and sorting commands)
selectcommand = "SELECT [vna_phone_numbers].[LastName], [vna_phone_numbers].[FirstName], [vna_page"& _
"r_numbers].[Pager_id], [vna_pager_numbers].[Pager_One_number], [vna_pager_number"& _
"s].[Pager_two_number] FROM [vna_phone_numbers], [vna_pager_numbers] WHERE (([vna"& _
"_phone_numbers].[Pager_Id] = [vna_pager_numbers].[Pager_id]) AND ([vna_phone_num"& _
"bers].[LastName] >= 'a'))" & _
"ORDER BY Vna_Phone_Numbers.LastName ,Vna_Phone_Numbers.Firstname "

BindGrid()
else
'Response.Write("Data is not Valid")

End If

End Sub

' ---------------------------------------------------------------
'
' DataGrid Commands: Page, Sort, Edit, Update, Cancel, Delete
'

Sub DataGrid_ItemCommand(Sender As Object, E As DataGridCommandEventArgs)

' this event fires prior to all of the other commands
' use it to provide a more graceful transition out of edit mode

CheckIsEditing(e.CommandName)

End Sub

Sub CheckIsEditing(commandName As String)

If DataGrid1.EditItemIndex <> -1 Then

' we are currently editing a row
If commandName <> "Cancel" And commandName <> "Update" Then

' user's edit changes (If any) will not be committed
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)

' turn on editing for the selected row

If Not isEditing Then

DataGrid1.EditItemIndex = e.Item.ItemIndex
BindGrid()

End If

End Sub

Sub DataGrid_Update(Sender As Object, E As DataGridCommandEventArgs)

' update the database with the new values

' get the edit text boxes and reference the Textbox
Dim Lastname as string = e.Item.Cells(1).Text
Dim FirstName As string = e.Item.Cells(2).Text
Dim Pager_id As Integer = e.Item.Cells(3).Text
Dim Pager_one_number As string = Ctype(e.Item.Cells(4).Controls(0),Textbox).Text
Dim Pager_two_number As String = CType(e.Item.Cells(5).Controls(0), TextBox).Text

' TODO: update the Command value for your application
Dim myConnection As New SqlConnection(ConnectionString)
Dim UpdateCommand As SqlCommand = new SqlCommand()
UpdateCommand.Connection = myConnection

If AddingNew = True Then
'UpdateCommand.CommandText = "INSERT INTO vna_department_names(dept_number, dept_name) VALUES (@dept_number, @dept_name)"
UpdateCommand.CommandText = "INSERT INTO vna_pager_numbers(Pager_id,Pager_One_number,Pager_ Two_number) VALUES (@Pager_id,@Pager_one_number,@Pager_two_number )"
Else
'UpdateCommand.CommandText = "UPDATE vna_department_names SET dept_number = @dept_number, dept_name = @dept_name WHERE dept_number = @dept_number"
UpdateCommand.CommandText = "UPDATE vna_pager_numbers SET pager_id = @pager_id,Pager_One_number = @pager_one_number, pager_two_number = @pager_two_number WHERE pager_id = @pager_id"
End If

UpdateCommand.Parameters.Add("@Pager_Id", SqlDbType.int, 4).Value = Pager_id
UpdateCommand.Parameters.Add("@Pager_one_number", SqlDbType.VarChar, 30).Value = Pager_one_number
UpdateCommand.Parameters.Add("@Pager_two_number", SqlDbType.VarChar, 30).Value = Pager_two_number

' 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()

End Sub

Sub DataGrid_Cancel(Sender As Object, E As DataGridCommandEventArgs)

' cancel editing

DataGrid1.EditItemIndex = -1
BindGrid()

AddingNew = False

End Sub

'Sub DataGrid_Delete(Sender As Object, E As DataGridCommandEventArgs)

' delete the selected row

' If Not isEditing Then

' the key value for this row is in the DataKeys collection
' Dim keyValue As String = CStr(DataGrid1.DataKeys(e.Item.ItemIndex))

' TODO: update the Command value for your application
' Dim myConnection As New SqlConnection(ConnectionString)
' Dim DeleteCommand As New SqlCommand("DELETE from vna_department_names where dept_number ='" & keyValue & "'", myConnection)

' execute the command
' myConnection.Open()
'DeleteCommand.ExecuteNonQuery()
'myConnection.Close()

' rebind the grid
' DataGrid1.CurrentPageIndex = 0
' DataGrid1.EditItemIndex = -1
' BindGrid()

' End If

'End Sub

Sub DataGrid_Page(Sender As Object, E As DataGridPageChangedEventArgs)

' display a new page of data

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)

' add a new row to the end of the data, and set editing mode 'on'

CheckIsEditing("")

If Not isEditing = True Then

' set the flag so we know to do an insert at Update time
AddingNew = True

' add new row to the end of the dataset after binding

' first get the data
Dim myConnection As New SqlConnection(ConnectionString)
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 rowValues As Object() = {""}


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

' ---------------------------------------------------------------
'
' Helpers Methods:
'

' property to keep track of whether we are adding a new record,
' and save it in viewstate between postbacks

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

Sub BindGrid()

Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlDataAdapter(SelectCommand, myConnection)

Dim ds As New DataSet()
myCommand.Fill(ds)

DataGrid1.DataSource = ds
DataGrid1.DataBind()

End Sub

Sub Text_lastname_TextChanged(sender As Object, e As EventArgs)
dim stitle as string = text_lastname.text

SelectCommand = "SELECT [vna_phone_numbers].[LastName], [vna_phone_numbers].[FirstName], [vna_page"& _
"r_numbers].[Pager_id], [vna_pager_numbers].[Pager_One_number], [vna_pager_number"& _
"s].[Pager_two_number] FROM [vna_phone_numbers], [vna_pager_numbers] WHERE (([vna"& _
"_pager_numbers].[Pager_id] = [vna_phone_numbers].[Pager_Id]) AND ([vna_phone_num"& _
"bers].[LastName] like '" & stitle & "%'))" & _
"ORDER BY Vna_Phone_Numbers.LastName ,Vna_Phone_Numbers.Firstname "

bindgrid()



End Sub