Programmatical Parameters w/MultiRowEdit on DataGrid

[Derek]

I have an aspx page where I use a reversebind and
templates to edit the entire datagrid at one time. I
added a textbox and button to change the parameter
criteria and then rerun the databind2 procedure. That
works fine the data is requeried and the new results are
displayed in the datagrid and the reversebind works for
saving the information. However if I add another text
box or dropdown box it does not seem to requery
correctly. If I remove the reversebind it will, but
without the reversebind I can not save changes to the
datagrid as a whole. I think I understand why using the
ddcp.selectedindex change sub will not work because it is
posting back however I can not figure out why adding an
additional text box for input and placing that within the
button1 command is not. I have looked at the stored
procedure also. I need to be able to either look at all
or a particular type of cp. I would prefer to use a drop
down box either inside or out of the button1 command.
Any help would appreciated. Thanks in advance

Here is both the code for page and stored_proc. page
also draws really slow:

Private Sub Page_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load

If Not IsPostBack Then

datcurrent = Date.Now.ToShortDateString

lblselectedcaldt.Text = DateAdd
(DateInterval.Day, 1, datcurrent)

squad = Request.QueryString("usersquad")

lblusersquad.Text = squad

lblselectedCP.Text = ddcp.SelectedValue

BindData2()

lbltoday.Text = datcurrent

txtdaysout.Text = ""

Else

ReverseBind()

End If

End Sub

Sub BindData2()

Dim cp As String

'cp = "c"

'If ddcp.SelectedIndex > -1 Then

cp = lblselectedCP.Text

'ElseIf ddcp.SelectedIndex = -1 Then

' cp = "%"

'End If

'If lblselectedCP.Text = "" Then

'cp = "%"

'Else

' cp = lblselectedCP.Text

'End If

sdate = CType(lblselectedcaldt.Text, DateTime)

If lbl4days.Text = "" Then

daysout = 36

Else

daysout = CType(lbl4days.Text, Integer)

End If

'squad = lblusersquad.Text

SqlSelectCommand1.Parameters("@sdate").Value =
sdate

SqlSelectCommand1.Parameters("@time").Value =
daysout

SqlSelectCommand1.Parameters("@squad").Value =
lblusersquad.Text

If lblselectedCP.Text = "All" Then

SqlSelectCommand1.Parameters("@cp").Value
= "%"

Else

SqlSelectCommand1.Parameters("@cp").Value =
lblselectedCP.Text

End If

'SqlConnection1.Open()

SqlDataAdapter2.Fill(DsCrewNotify1)

DataGrid2.DataBind()

_LastEditedPage = DataGrid2.CurrentPageIndex

'SqlConnection1.Close()



End Sub

Public Sub ReverseBind()

Dim gridrow As DataGridItem

Dim datarow As dsCrewNotify.og_getCrewNotifyRow

For Each gridrow In DataGrid2.Items

datarow = DsCrewNotify1.og_getCrewNotify
(gridrow.DataSetIndex)

datarow.notified() = CType(gridrow.FindControl
("chkNotify"), CheckBox).Checked

DsCrewNotify1.og_getCrewNotify
(gridrow.DataSetIndex).ItemArray = datarow.ItemArray

Next

End Sub

Protected Overrides Sub LoadViewState(ByVal
savedstate As Object)

MyBase.LoadViewState(savedstate)

If (Not Me.ViewState("Data") Is Nothing) Then

DsCrewNotify1 = CType(Me.ViewState("Data"),
dsCrewNotify)

End If

If (Not Me.ViewState("LastEditedPage") Is
Nothing) Then

_LastEditedPage = CType(Me.ViewState
("LastEditedPage"), Integer)

End If

End Sub

Protected Overrides Function saveViewState() As Object

Me.ViewState("Data") = DsCrewNotify1

Me.ViewState("LastEditedPage") = _LastEditedPage

Return (MyBase.SaveViewState())

End Function

Private Sub DataGrid1_ItemDataBound(ByVal sender As
Object, ByVal e As
System.Web.UI.WebControls.DataGridItemEventArgs) Handles
DataGrid2.ItemDataBound

If (e.Item.ItemType = ListItemType.Item Or
e.Item.ItemType = ListItemType.AlternatingItem) Then

Dim datarow As
dsCrewNotify.og_getCrewNotifyRow

datarow = CType(CType(e.Item.DataItem,
DataRowView).Row, dsCrewNotify.og_getCrewNotifyRow)

CType(e.Item.FindControl("chkNotify"),
CheckBox).Checked = datarow.notified

End If

End Sub

Sub updateData()

Dim gridrow As DataGridItem

Dim datarow As dsCrewNotify.og_getCrewNotifyRow

Dim dscrewnotify As DataSet

Dim chkbox As CheckBox

Dim strnotify As String

Dim intid As Integer

For Each gridrow In DataGrid2.Items

datarow = DsCrewNotify1.og_getCrewNotify
(gridrow.DataSetIndex)

intid = datarow.Item(0)

datarow.notified() = CType(gridrow.FindControl
("chknotify"), CheckBox).Checked

DsCrewNotify1.og_getCrewNotify
(gridrow.DataSetIndex).ItemArray = datarow.ItemArray

strnotify = CType(gridrow.FindControl
("chkNotify"), CheckBox).Checked

SqlUpdateCommand1.Parameters("@notify").Value
= strnotify

SqlUpdateCommand1.Parameters
("@schedid").Value = intid

SqlConnection1.Open()

SqlUpdateCommand1.ExecuteNonQuery()

SqlConnection1.Close()

Next

'BindData2()

ReverseBind()

End Sub

Sub doUpdate(ByVal sender As Object, ByVal e As
DataGridCommandEventArgs)

If e.CommandName = "Update" Then

DataGrid2.AllowPaging = False

' DataGrid2.AllowSorting = True

DataGrid2.DataSource =
DsCrewNotify1.og_getCrewNotify

ReverseBind()

updateData()

End If

End Sub

Private Sub Button1_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
Button1.Click

Dim numhours As Integer

If txtdaysout.Text = "" Then

numhours = 36

Else

numhours = (CType(txtdaysout.Text, Integer) *
24) + 12

End If

' numhours = (CType(txtdaysout.Text, Integer) *
24) + 12

lbl4days.Text = numhours

BindData2()

End Sub



Sub ddcp_SelectedIndexChanged(ByVal sender As Object,
ByVal e As System.EventArgs) Handles
ddcp.SelectedIndexChanged

If ddcp.SelectedIndex > -1 Then

lblselectedCP.Text = ddcp.SelectedValue

BindData2()

End If

End Sub

End Class



Stored Proc::

CREATE PROCEDURE *********

@sdate as datetime

, @time as int

, @squad as nvarchar(10)

, @cp as nvarchar(10)



AS

Select

s.scheduleid

, s.namelast

, s.namefirst

, s.startdate

, s.stopdate

, s.notified

, e.mission_id

, e.eventName

, e.eventtype

, dateadd(hh,-5,e.alerttime) as alertime

, dateadd(hh, -5,e.showtime) as showtime

, et.EventCat

, e.eventsquadron

, e.eventsid

, e.eventtitle

, p.phonehome

, p.phonecell

, cp.crw_pstn_dty_cd

--, a.FLYR_ORG_UNIT_ID

--, a.person_id

From ****** as p

join ***** s

on p.ssn = s.ssn

join ***** e

on s.eventsid = e.eventsid

join ****** et

on e.eventtype = et.eventtypelong

left join ******* a

on a.person_id = p.ssn

left join ******* cp

on a.person_id = cp.person_id

Where

--((dateadd(hh, -5,e.startdate)) between @sdate and
(dateadd(hh,36,@sdate)))

(dateadd(hh,-5,e.alerttime) between @sdate and (dateadd
(hh,@time,@sdate)))

and substring(a.FLYR_ORG_UNIT_ID,1,4) like @squad

and cp.arcft_asg_evt_id = '01'

and cp.crw_pstn_dty_cd like @cp

order by e.alerttime,e.eventtitle,s.namelast

GO

[MSFT]

Hi Derek,

I am not confident that I have understood the problem clearly. Did you need
to add a TextBox or Dropdownlist to let user select some values, and then
requery the database. If so, after user input the value and click a button
to post back, you need to execute BindData2() in PostBack, Is this right?

Luke
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Derek]

Yes, I want to call binddata2() on button1_click. It
seems the reverse bind is causing the issue however If I
remove that I can not use the save btn update the
database with multiple selections.

The page is set up so that on load if notispostback it
executes Binddata2() else it executes reversebind(). On
the page load the stored procedure is sent all default
parameters (%) to pull all the data. When the daysout
textbox receives input and the go button is clicked
Binddata2() is called and requeries fine. It is when I
add another textbox asking for input for another filter
parameter that it does not execute the stored procedure
correctly. Example they choose to filter based on "p".
It does not change the parameter to pull records only
with "p". The confusing part is that it will execute
binddata2() with the one textbox.

I would prefer to use the dropdown box with or without
the GO btn but if I could get the text box to work that
would be great. The stored procedure works fine in query
analyzer.

Hope this gives you a little more information.
I am including the Html which may help you to see what I
am trying to do. Thanks for your help, it is appreciated
very much

<%@ Page Language="vb" AutoEventWireup="false"
Codebehind="getEventTitles.aspx.vb"
Inherits="CrewNotify.getEventTitles"%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0
Transitional//EN">

<HTML>

<HEAD>

<title>getEventTitles</title>

<meta content="Microsoft Visual
Studio .NET 7.1" name="GENERATOR">

<meta content="Visual Basic .NET
7.1" name="CODE_LANGUAGE">

<meta content="JavaScript"
name="vs_defaultClientScript">

<meta
content="http://schemas.microsoft.com/intellisense/ie5"
name="vs_targetSchema">

</HEAD>

<body MS_POSITIONING="GridLayout">

<form id="Form1" method="post"
runat="server">

<asp:datagrid
id=DataGrid2 style="Z-INDEX: 101; LEFT: 24px; POSITION:
absolute; TOP: 80px" runat="server"
OnItemCommand="doUpdate" ShowFooter="True"
AutoGenerateColumns="False" DataMember="og_getCrewNotify"
DataKeyField="scheduleid" DataSource="<%# DsCrewNotify1 %

" BorderColor="#000099" BorderStyle="None"

BorderWidth="1px" BackColor="White" CellPadding="3"
GridLines="Horizontal">
<SelectedItemStyle Font-Bold="True" ForeColor="White"
BackColor="#008A8C"></SelectedItemStyle>
<AlternatingItemStyle
BackColor="Gainsboro"></AlternatingItemStyle>
<ItemStyle Font-Size="X-Small" ForeColor="Black"
BackColor="#EEEEEE"></ItemStyle>
<HeaderStyle Font-Bold="True" ForeColor="White"
BackColor="#000084"></HeaderStyle>
<FooterStyle ForeColor="Black"
BackColor="#CCCCCC"></FooterStyle>

<Columns>
<asp:TemplateColumn HeaderText="CP">
<ItemTemplate>
<asp:Label id=lblCP
runat="server" Text='<%# DataBinder.Eval
(Container, "DataItem.crw_pstn_dty_cd") %>'>
</asp:Label>
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn SortExpression="namelast"
HeaderText="Crewmember">
<ItemTemplate>
<asp:Label id=Label8
runat="server" Text='<%# Container.DataItem("namelast")
&amp;", "&amp;Container.DataItem("namefirst") %>'>
</asp:Label>
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn SortExpression="eventtitle"
HeaderText="Alias">
<ItemTemplate>
<asp:Label id=lblAlias
runat="server" Text='<%# DataBinder.Eval
(Container, "DataItem.eventtitle") %>'>
</asp:Label>
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn Visible="False"
HeaderText="Category">
<ItemTemplate>
<asp:Label id=lblCategory
runat="server" Text='<%# DataBinder.Eval
(Container, "DataItem.EventCat") %>'>
</asp:Label>
</ItemTemplate>
</asp:TemplateColumn>
<asp:BoundColumn Visible="False" DataField="scheduleid"
SortExpression="scheduleid"
HeaderText="scheduleid"></asp:BoundColumn>
<asp:TemplateColumn HeaderText="Notified">
<ItemTemplate>
<asp:CheckBox id="chknotify"
runat="server"></asp:CheckBox>
</ItemTemplate>
<FooterTemplate>
<asp:Button id="Update"
runat="server" BackColor="Transparent"
CommandName="Update" Text="Save"></asp:Button>
</FooterTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Alert">
<ItemTemplate>
<asp:Label id=lblalert
runat="server" Text='<%# DataBinder.Eval
(Container, "DataItem.alertime", "{0:g}") %>'>
</asp:Label>
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Show">
<ItemTemplate>
<asp:Label id=lblShow
runat="server" Text='<%# DataBinder.Eval
(Container, "DataItem.showtime", "{0:g}") %>'>
</asp:Label>
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Home#">
<ItemTemplate>
<asp:Label id=lblhome
runat="server" Text='<%# DataBinder.Eval
(Container, "DataItem.phonehome") %>'>
</asp:Label>
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Cell#">
<ItemTemplate>
<asp:Label id=lblcell
runat="server" Text='<%# DataBinder.Eval
(Container, "DataItem.phonecell") %>'>
</asp:Label>
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Comments">
<HeaderStyle Width="100px"></HeaderStyle>
</asp:TemplateColumn>

</Columns>
<PagerStyle HorizontalAlign="Center" ForeColor="Black"
BackColor="#999999" Mode="NumericPages"></PagerStyle>
</asp:datagrid><asp:label id="lblselectedcaldt" style="Z-
INDEX: 102; LEFT: 136px; POSITION: absolute; TOP: 40px"
runat="server"></asp:label><asp:label id="Label1"
style="Z-INDEX: 103; LEFT: 136px; POSITION: absolute;
TOP: 16px" runat="server">Mission
date:</asp:label><asp:button id="Button1" style="Z-INDEX:
104; LEFT: 376px; POSITION: absolute; TOP: 40px"
runat="server"
BackColor="Gray" Width="40px"
Text="Go"></asp:button><asp:label id="lbl4days" style="Z-
INDEX: 105; LEFT: 544px; POSITION: absolute; TOP: 40px"
runat="server"></asp:label>

<asp:Label
id="Label2" style="Z-INDEX: 106; LEFT: 24px; POSITION:
absolute; TOP: 16px" runat="server">Today:</asp:Label>

<asp:Label
id="lbltoday" style="Z-INDEX: 107; LEFT: 24px; POSITION:
absolute; TOP: 40px" runat="server"></asp:Label>

<asp:TextBox
id="txtdaysout" style="Z-INDEX: 108; LEFT: 288px;
POSITION: absolute; TOP: 40px"
runat="server" Width="40px"></asp:TextBox>

<asp:Label
id="Label3" style="Z-INDEX: 109; LEFT: 280px; POSITION:
absolute; TOP: 16px" runat="server"># days to
view</asp:Label>

<asp:Label
id="Label9" style="Z-INDEX: 110; LEFT: 544px; POSITION:
absolute; TOP: 16px" runat="server">Hours
ahead:</asp:Label>

<asp:Label
id="lblusersquad" style="Z-INDEX: 111; LEFT: 768px;
POSITION: absolute; TOP: 24px"
runat="server" Visible="False"></asp:Label>

<asp:DropDownList
id="ddcp" style="Z-INDEX: 112; LEFT: 440px; POSITION:
absolute; TOP: 40px" runat="server"
Visible="False">
<asp:ListItem Value="C">C</asp:ListItem>
<asp:ListItem Value="L">L</asp:ListItem>
<asp:ListItem Value="P">P</asp:ListItem>
<asp:ListItem Value="S">S</asp:ListItem>
<asp:ListItem Value="All"
Selected="True">All</asp:ListItem>

</asp:DropDownList>

<asp:Label
id="Label10" style="Z-INDEX: 113; LEFT: 440px; POSITION:
absolute; TOP: 16px" runat="server"
Visible="False">Filter by CP:</asp:Label>

<asp:Label
id="lblselectedCP" style="Z-INDEX: 114; LEFT: 648px;
POSITION: absolute; TOP: 40px"
runat="server" Visible="False"></asp:Label>

<asp:TextBox
id="txtSelectedCP" style="Z-INDEX: 115; LEFT: 840px;
POSITION: absolute; TOP: 80px"
runat="server" Width="64px" Visible="False"></asp:TextBox>

<asp:Label
id="Label11" style="Z-INDEX: 116; LEFT: 848px; POSITION:
absolute; TOP: 56px" runat="server"
Visible="False">CP</asp:Label></form>

</body>

</HTML>

[MSFT]

Hi Derek,

The problem is that when you click the button1, it is also a postback so
that the reverse update also get executed. Is possible to move
ReverseUpdate out of Form load and add it in the Save/Update button's Click?

Luke
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)