I have a datagrid for viewing and deleting. Under that I
have a drop down with binded info and a button to add the
select drop down item into database.
1User can view datagrid and delete info.
2User can select from drop down, click add button and add
selection to database.
A)Datagrid binds and deletes fine.
B)If I select the first value of drop down addition to
database works fine.
C)<problem>if I select the 2nd value of drop down it adds
2 records into the database when it should only add 1. It
adds 1 record for 1st value of drop down and 1 record for
next value selected.
Code below for review.
*************
<%@ Import Namespace="Syst em.Data"%>
<%@ Import Namespace="Syst em.Data.SqlClie nt" %>
<%@ Page CodeBehind="Vie wItemsSub.aspx. vb" Language="vb"
AutoEventWireup ="false" Inherits="TF.Vi ewItemsSub" %>
<HTML>
<script language="vb" runat="server">
Dim intItem As Integer
Dim strSelect As String
Dim Conn As SqlConnection
Dim Cmd As SqlCommand
Dim DBCommand as SQLDataAdapter
Dim DSPageData as New DataSet
Dim ParmInfo as SQLParameter
Dim NID as Integer '
Dim TheID as String
Private Sub Page_Load(ByVal sender As System.Object, ByVal
e As System.EventArg s) Handles MyBase.Load
intItem = Int32.Parse(Req uest.QueryStrin g("id"))
Call GetAssignedStaf f()
If Not IsPostBack Then
Call BuildDropDown()
end If
End Sub
Sub GetAssignedStaf f
'Get assigned staff info
Conn = New SqlConnection(" Server=localhos t;Initial
Catalog=CA;Trus ted_Connection= yes;")
strSelect = "Select A.PNID, B.URPNAID, A.IDNum,
A.FirstName, A.LastName, A.MiddleName"
strSelect = strSelect + " from TF_Preassigned_ Table A,
TF_Unit_Request _Preassigned_As signments B"
strSelect = strSelect + " Where (A.PNID In (Select PNID
from TF_Unit_Request _Preassigned_As signments Where "
strSelect = strSelect + " URID = " &intItem & "))"
response.write ("<P>")
response.write (strSelect)
Cmd = New SqlCommand(strS elect, Conn)
Try
Conn.Open()
Datagrid1.DataS ource = cmd.ExecuteRead er()
Datagrid1.DataB ind()
Catch TfHrException As SqlException
TfHrExceptionLa bel.Text = TfHrException.M essage
Finally
Conn.Close()
End Try
'end assigned staff info
End Sub
'preassigned staff options
Sub Click_Grid(ByVa l Sender As Object, ByVal E as
DataGridCommand EventArgs)
response.write ("hello")
TheID = E.Item.Cells(0) .Text
strSelect ="Delete from
TF_Unit_Request _Preassigned_As signments Where URPNAID
= "&TheID
Conn = New SqlConnection(" Server=localhos t;Initial
Catalog=CA;Trus ted_Connection= yes;")
Cmd.CommandText = (strSelect)
response.write ("<P>")
response.write( strSelect)
Cmd.Connection = Conn
Cmd.Connection. Open
Cmd.ExecuteNonQ uery()
Conn.Close
Call GetAssignedStaf f()
End Sub
Sub Edit_Grid(sende r As Object, e As
DataGridCommand EventArgs)
DataGrid1.EditI temIndex = e.Item.ItemInde x
Call GetAssignedStaf f()
End Sub
Sub Update_Grid(ByV al Sender as Object, ByVal E as
DataGridCommand EventArgs)
End Sub
'end assigned staff options.
Sub BuildDropDown()
Conn = New SqlConnection(" Server=localhos t;Initial
Catalog=CA;Trus ted_Connection= yes;")
'strSelect = "Select * from TF_Preassigned_ Table"
DBCommand = New SQLDataAdapter( "Select PNID, LastName,
FirstName,IDNum from TF_Preassigned_ Table Order by
LastName", Conn)
'response.write (strSelect)
DBCommand.Fill( DSPageData, "s_Table")
Dim DynColumn As New DataColumn()
With DynColumn
..ColumnName = "FullName"
..DataType = System.Type.Get Type("System.St ring")
..Expression = "LastName+ ' ' +FirstName+ ' ' + IDNum"
End With
DSPageData.Tabl es("s_Table").C olumns.Add(DynC olumn)
Assigned.DataTe xtField = "FullName"
Assigned.DataVa lueField = "PNID"
Assigned.DataSo urce = DSPageData.Tabl es
("s_Table").Def aultView
Assigned.DataBi nd()
Assigned.Items. Insert(0, "Select one")
Conn.Close()
End Sub
Sub Add_Click(send as object, e as system.eventarg s)
'intItem = Int32.Parse(Req uest.QueryStrin g("id"))
'response.write (intItem)
'response.write ("<BR>")
response.write (Assigned.Selec tedItem.Value)
if Assigned.Select edIndex = 0 then
response.write( "Must select a nurse")
else
Conn = New SqlConnection(" Server=localhos t;Initial
Catalog=CA;Trus ted_Connection= yes;")
Cmd = New SQLCommand
("TF_sp_insert_ preassigned_ass ignment", Conn)
Cmd.CommandType = CommandType.Sto redProcedure
'Add Return Value Parameter
ParmInfo = Cmd.Parameters. Add("ReturnValu e", SqlDbType.Int)
ParmInfo.Direct ion = ParameterDirect ion.ReturnValue
'Add UID input parameter
Cmd.Parameters. Add("@URID", intItem)
Cmd.Parameters. Add("@PNID", Assigned.Select edItem.Value)
Cmd.Parameters. Add("@CreatedDT M", datetime.now)
Cmd.Parameters. Add("@CreatedBy ", "Admin")
Cmd.Parameters. Add("@ModifiedD TM", DateTime.Now)
Cmd.Parameters. Add("@ModifiedB y", "Admin")
Conn.Open()
Cmd.ExecuteNonQ uery()
NID = Cmd.Parameters( "ReturnValue"). Value
Conn.Close()
response.write( "<P>")
response.write( " added")
Call GetAssignedStaf f()
'Call BuildDropDown()
end if
End Sub
</script>
<body>
<form runat="server">
<asp:Label ID="TfHrExcepti onLabel"
runat="server" />
<asp:Label ID="Label1"
runat="server" />
<BR>
<asp:DataGrid ID="Datagrid1"
AutoGenerateCol umns="false" enableviewstate ="false"
runat="server"
ItemStyle-
BackColor="#DED FDE" AlternatingItem Style-
BackColor="Ligh tSteelBlue" HeaderStyle-Font-Bold="True"
HeaderStyle-
ForeColor="Whit e" HeaderStyle-BackColor="Blac k" Font-
Names="Verdana" Font-Size="X-Small"
ForeColor="Blac k"
BackColor="Whit e" Cellpadding="3" GridLines="None "
CellSpacing="1" oneditcommand=" Edit_Grid"
onupdatecommand ="Update_Gri d"
onitemcommand=" Click_Grid">
<Columns>
<asp:BoundColum n
HeaderText="Ass ignmentID" DataField="URPN AID" />
<asp:BoundColum n
Headertext="ID" DataField="IDNu m" />
<asp:BoundColum n
HeaderText="Fir st name" DataField="Firs tName" />
<asp:BoundColum n
HeaderText="Las t name" DataField="Last Name" />
<asp:ButtonColu mn
HeaderText="Cli ck to delete" ButtonType="Pus hButton"
Text="Delete record" />
</Columns>
</asp:DataGrid>
<asp:DropDownLi st
id="Assigned"
runat="server"/>
<asp:Button id="Add"
Text="Add "
OnClick="Add_Cl ick"
Runat="server"> </asp:Button>
</form>
<P></P>
</body>
</HTML>
